SQLTeam.com | Weblogs | Forums

SQL Stored Procedure in SSRS Report

SSAS and SSRS
#1

I have a SQL Stored Procedure that works great for one of our applications we use for cost reporting. I want to try and use the same SQL Stored Procedure in an SSRS report.

I am able to copy the the Stored Procedure into Report Builder with no issues, but I can never get any data to pull up. Wondering if edits need to be made to the Stored Procedure for it to work in SSRS? I am new to SSRS coding, etc. Any help would be appreciated.

Thanks!

#2

Welcome.

Can you show us your report in design mode? Including data source

#3

@yosiasz
Not sure if this helps. Please let me know if you need more information to help. Thank you!

ssrs1
ssrs11609×829 63 KB

#4

Yes that is it. Does the stored procedure take a paremeter?

#5

@yosiasz
When I used the Stored Procedure, Report Builder automatically filled in the the Parameters, see screenshot below.
ssrs2

#6

So probably some default parm is being aend maybe null and returns nothing

#7

@yosiasz
I am not really sure, that is why I need some assistance. Do you need more info? Tell me what I can provide you? Thanks for your help!

#8

The other thing is the Stored Procedure is currently used in a Crystal Report. We are wanting to move this report into SSRS. so thought we could use the same SQL Stored Procedure.

#9

in the Crystal report are there drop downs for users to choose or fields to populate to run the report? for
JCCo, BeginJob, EndJob etc. Right now I do not see any user interface for them to choose from to specify the parameters you listed out. Can you share the top snippet of the stored procedure where the parms are specified? Or run the stored procedure manually and see if returns a result set, I doubt it would. If there is no data sent to the stored procedure for the parameters then it wont return anything unless you have defaults for those parameters.

#10

@yosiasz

See screenshots and code from Stored Procedure below. The form is where users input data to run the report that uses the Stored Procedure.

ssrs3
ssrs3570×662 22.7 KB

Here is code from top part of Stored Procedure:

USE [Viewpoint]
GO
/****** Object:  StoredProcedure [dbo].[vrptJCUnitCostCT]    Script Date: 3/15/2022 2:01:20 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER proc [dbo].[vrptJCUnitCostCT]
       (@JCCo bCompany, @BeginJob bJob='', @EndJob bJob='zzzzzzzzzz',
       @BeginDate bDate = '01/01/50', @EndDate bDate, @DateActPost varchar(1) = 'P', @JobActivity char(1))
       
    with recompile   
  
   as
declare @BeginPostedDate bDate,@EndPostedDate bDate,@BeginActualDate bDate,@EndActualDate bDate
   
   if @JCCo is null begin select @JCCo=0 end  --workaround for Crystal null issue
   
   select @BeginPostedDate=case when @DateActPost = 'P' then @BeginDate else '1/1/1950' end,
   	@EndPostedDate=case when @DateActPost = 'P' then @EndDate else '12/31/2050' end,
    	@BeginActualDate=case when @DateActPost <> 'P' then @BeginDate else '1/1/1950' end,
    	@EndActualDate=case when @DateActPost <> 'P' then @EndDate else '12/31/2050' end
#11

@yosiasz
And here is the Parameters when I open the SSRS report

ssrs4
ssrs41441×209 4.38 KB

#12

So now do the following.

Run SQL Analyzer on the server that has that stored procedure then

  1. Run the Crystal report without choosing any parms and record what the Analyzer shows for the values of the parms
  2. Then run the SSRS report and without choosing any parms and record what the Analyzer shows for the values of the parms

Then compare the values of both. Somehow sounds like the SSRS might be sending parameters that are invalid and hence the report returns nada

#13

@yosiasz
How do I run Query Analyzer? Is that in SSMS?

#14

Yep sure is. Look for it on menu

#15

@yosiasz
I am running SSMS v17.2 and I do not see Query Analyzer in any menu. Can you guide me to where Query Analyzer is within SSMS. Thank you.