Execute SSRS report from SQL SERVER stored procedure.

Question

Execute SSRS report from SQL SERVER stored procedure.

NishchalG 41

Hello :),

I tried to search the forum but I could not find the solution.

I have a SSRS report which works fine. Can I call it From a Stored Procedure ?

Thanks,
Warm Regards,
Nishu.

ANDYKay 0 Reputation points

2023-03-23T23:03:00.97+00:00
Simple answer…Yes, a RS report can be called from a stored procedure. We have one of our reports that is sent out to 23 different groups of individuals running different parameters on a single report.

create a copy of your report and change all the parameters into text boxes, and remove the parameter code as you, the writer of this SP, will ensure that the values you pass in are valid values

create a one time subscription to your report with a unique name with your desired output, using REPLACEPar1, REPLACEPar2, etc. as the entries for the parameters you wish to pass into the report

in the first few lines of your SP, create a backup of 1) the ENTIRE RepoertServer.dbo.Subscription file and then 2) another backup of just your unique subscription name.

next you’ll need a file with all the various parameters you’ll be running the report for keyed by a single numeric field called ReportNum

do a SELECT MAX(ReportNum) FROM your table name, then do a while loop to loop from 1 to the MAX#

at each iteration, you’ll need to retrieve the parameter values from you table

you’ll then need to do a REPLACE on the appropriate REPLACEPar variables (step2) you defined in your subscription

Step 7 will post the variables you want the report to run with to the server

You’ll then need to issue an EXEC command to run the report.

the last thing you need to do is restore the file settings for just your subscription back to the way it was before your SP started changing things, so that the next iteration of your while loop can find the same REPLACEPar names to do the next set of replaces on one thing you have to beware of is timing. You can’t reset the values back to their original settings until RS has had a chance to run your subscription with your newly inputted parameters

Answer accepted by question author

0 additional answers

Your answer

ANDYKay 0 Reputation points

2023-03-23T23:03:00.97+00:00

Simple answer…Yes, a RS report can be called from a stored procedure. We have one of our reports that is sent out to 23 different groups of individuals running different parameters on a single report.

create a copy of your report and change all the parameters into text boxes, and remove the parameter code as you, the writer of this SP, will ensure that the values you pass in are valid values

create a one time subscription to your report with a unique name with your desired output, using REPLACEPar1, REPLACEPar2, etc. as the entries for the parameters you wish to pass into the report

in the first few lines of your SP, create a backup of 1) the ENTIRE RepoertServer.dbo.Subscription file and then 2) another backup of just your unique subscription name.

next you’ll need a file with all the various parameters you’ll be running the report for keyed by a single numeric field called ReportNum

do a SELECT MAX(ReportNum) FROM your table name, then do a while loop to loop from 1 to the MAX#

at each iteration, you’ll need to retrieve the parameter values from you table

you’ll then need to do a REPLACE on the appropriate REPLACEPar variables (step2) you defined in your subscription

Step 7 will post the variables you want the report to run with to the server

You’ll then need to issue an EXEC command to run the report.

the last thing you need to do is restore the file settings for just your subscription back to the way it was before your SP started changing things, so that the next iteration of your while loop can find the same REPLACEPar names to do the next set of replaces on one thing you have to beware of is timing. You can’t reset the values back to their original settings until RS has had a chance to run your subscription with your newly inputted parameters

Answer 1

Hi @NishchalG ,

What do you mean of call the report from a Stored Procedure?

If you want to render a SSRS report to PDF using TSQL/stored procedure, you may use the Reporting Services web services to render a report to PDF format. We are able to use CLR stored procedure to call a web services.

Take a reference here.

If you want to create a subscription of the report, you may set the subscription as an agent job which can also be started from stored procedure.

Regards，

Zoe

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

NishchalG 41 Reputation points

2021-11-09T13:12:35.643+00:00

Hello Zoehui-MSFT ,

Thanks Zoehui-MSFT. That is what I want to do.

Reagrds
Nishu

Share via

Execute SSRS report from SQL SERVER stored procedure.

0 additional answers

Your answer