Using a Stored Procedure in SSRS Report Builder
-
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!
-
Can you execute the stored procedure through SSMS? What error message do you get?
-
Yes, the Stored Procedure executes successfully in SSMS. When I run it in SSRS, I get no data returned.
-
You say "I am able to copy the Stored Procedure into Report Builder " Could you describe how you did that? I would expect to see in the Report Data tab a dataset where the query type was "Stored procedure" and the box below that was populated with the name of the stored procedure you are using
Does the stored procedure have any parameters?
When you run the report, do you get any error messages?
Do you have at least one tablix on the report that you have a column from the stored procedure selected?
-
I do not get any errors when I run the report, just blank with no data. See screenshots attached here. Thank you for your help!
-
It sounds like you could be filtering based on domain username, and if you're using impersonation or whatever to run SSRS, then that's getting lost. Strictly conjecture though.
Could you post the WHERE clause for your stored procedure? (I don't think the rest is relevant, since it works when you run it).
-
This is the only WHERE statement in the Stored Procedure.
where @JCCo=JCCH.JCCo and JCJP.Job >= @BeginJob and JCJP.Job<=@EndJob
-
First thought: the report has seven parameters. Are you supplying exactly the same parameters for the report as when you run it in SSMS? I'd assume you are running the procedure in SSMS by creating a statement such as
EXEC vrptJCUnitCostCT Parameter1, Parameter2, Parameter3, Parameter4, Parameter5, Parameter6, Parameter7
substituting Parameter1 etcetera with the actual values
Second thought: The tablix appears to sum the numeric values - is there any grouping in the report? Is that what you wanted? If not I'd suggest changing the Sum() values to the column names. Alternatively I'd suggest deleting the tablix you have and creating a new one (remember to set the DataSetName property to the dataset if you do) - that way you'll at lease see the same level of detail as in SSMS. You can always aggregate the data later if you want
Third thought: You've got the reporting tools - isn't there anyone in your organisation that can help you? Because we can't see the code or the report, as you probably know by now we have to guess from a wide variety of potential issues what the cause(s) could be
-
Thank you for all your feedback, I truly appreciate it as I am trying to teach myself as much as I can about SQL and SSRS. I am the only person in our company that could possibly do this. So I am hoping to get enough guidance to build this SSRS report since we already have a Stored Procedure.
I have not tried running the Stored Procedure in SSMS with values for the parameters.
I changed the table and removed the SUM function, as that is not necessary.
Would it be helpful if I showed you the Stored Procedure?
-
I ran the Stored Procedure in SSMS and put the same Parameters that I used in SSRS and got results but still no results in SSRS. See screenshot from SSMS. Thank you.
-
Did you drop a tablix in and then drop the fields into the columns of the tablix? I've never ever had SSRS just not return any data based on a stored procedure.
Please explain how you added fields to your report. And for fun, make a copy of the stored procedure, but remove the WHERE clause entirely. Does it render then?
-
Also not sure how SSRS passes the parameters as you have a custom datatype dbo.bData
Does it pass the parameters as text or date ?
-
I have had success using the following syntax. It is the same syntax as used in SSMS.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply