Using a Stored Procedure in SSRS Report Builder

Question

Post reply

Using a Stored Procedure in SSRS Report Builder

Hawkins_IT

SSC Enthusiast

Points: 186
More actions
March 22, 2022 at 2:14 pm

#4004229

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!

Viewing 13 posts - 1 through 12 (of 12 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

Can you execute the stored procedure through SSMS? What error message do you get?

Hawkins_IT SSC Enthusiast Points: 186 More actions · Answer 2

Yes, the Stored Procedure executes successfully in SSMS. When I run it in SSRS, I get no data returned.

irgp SSC Journeyman Points: 80 More actions · Answer 3

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?

Hawkins_IT SSC Enthusiast Points: 186 More actions · Answer 4

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!

ssrs1 ssrs2 ssrs3

pietlinden SSC Guru Points: 63506 More actions · Answer 5

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).

Hawkins_IT SSC Enthusiast Points: 186 More actions · Answer 6

This is the only WHERE statement in the Stored Procedure.

where @JCCo=JCCH.JCCo and JCJP.Job >= @BeginJob and JCJP.Job<=@EndJob

irgp SSC Journeyman Points: 80 More actions · Answer 7

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

Hawkins_IT SSC Enthusiast Points: 186 More actions · Answer 8

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?

Hawkins_IT SSC Enthusiast Points: 186 More actions · Answer 9

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.

ssrs4

pietlinden SSC Guru Points: 63506 More actions · Answer 10

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?

Jo Pattyn SSC-Dedicated Points: 32350 More actions · Answer 11

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 ?

jimkistner SSC Veteran Points: 243 More actions · Answer 12

I have had success using the following syntax. It is the same syntax as used in SSMS.

SSRSCapture