Subscription of multiple valued parameterized report in SSRS

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Reporting Services » Reporting Services » Subscription of multiple valued parameterized...

Subscription of multiple valued parameterized report in SSRS

Rate Topic

Display Mode

Topic Options

Author

Message

rahulpatange

Posted Wednesday, October 31, 2012 4:31 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:29 AM
Points: 3, Visits: 15

Hi,

I have a report in SSRS report server URL, it takes location as a parameter from the query dynamically has a input to the report.

when I click the report, it has the drop down list where I need to select location, and then i can view the report.

Now, I need to subscribe this report, and i want this report on the windows share folder for all the location, i need these reports on a particular time and it should automatically take parameter from the drop down list, and generate the report saved in the windows share folder.
Please suggest/help me in this.

Thanks in advance..

Post #1379231

Daniel Bowlin

Posted Wednesday, October 31, 2012 10:39 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:53 AM
Points: 2,672, Visits: 2,416

If you use the enterprise version you can make a data driven subscription and build your parameter query there. However, if you need multiple values for a single parameter the task becomes more difficult because that functionality is built into the product. The easiest way I have found is to use a stored procedure and build the multi value parameter query into the stored procedure using an input parameter to control whether or not that section of the SP is run.

Post #1379434

rahulpatange

Posted Wednesday, October 31, 2012 12:33 PM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:29 AM
Points: 3, Visits: 15

Thanks Daniel..!

I am using enterprise edition only.. One more help.. is there a any way that the part of the report name is Dynamically generated by a query and get append to the existing report name.

For example -

Report name - Loc-XYZ.xls

where the "Loc" should dynamically get generated from a query,and also this "Loc" is passed as a parameter to this report.

Post #1379486

Daniel Bowlin

Posted Wednesday, October 31, 2012 2:33 PM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:53 AM
Points: 2,672, Visits: 2,416

Well, that all depends on what you mean by report name. If you mean the actual name, it is possible but it is very difficult and involves doing some things that if done wrong can really mess up your report catalog and it is not supported by Microsoft. If you are talking about the report title that the user sees at the top of the report, simply make an expression in the title box something like ="Title here " & Parameters!ParameterName.Value

Post #1379528

rahulpatange

Posted Thursday, November 01, 2012 1:28 AM

Forum Newbie

Group: General Forum Members
Last Login: Friday, November 02, 2012 7:29 AM
Points: 3, Visits: 15

I am talking about the actual name can you please tell me how to do that.
i need location name appended to my actual report name, and this location field is getting from the query dynamically.

Thanks..

Post #1379649

atfalatitkb

Posted Thursday, November 01, 2012 2:36 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 15, 2013 1:30 PM
Points: 163, Visits: 528

I haven't actually tried this but i think it should work for any version of sql server that includes reporting services. if the location is the default parameter on the file-share subscription, you could create a dot net application that could a. for each report in the folder that the report was delivered to, query the subscriptions table to grab the default location parameter (XYZ), store it (XYZ) in a string variable (strloc), then rename the report , inserting the location (strloc) before the file extension (Loc-strloc.xls).

Post #1380028

Daniel Bowlin

Posted Friday, November 02, 2012 7:20 AM

SSCrazy

Group: General Forum Members
Last Login: Friday, May 17, 2013 11:53 AM
Points: 2,672, Visits: 2,416

rahulpatange (11/1/2012)

It involves going to the catalog table in the reporting services database and changing the name of the report and running the report and then going back to the catalog and changing the name to the next name you need. I believe I saw more detailed instructions right here on SSC in the forums, maybe an article or blog. I must warn you though, you do this at your own risk. The impact of modifying the catalog table incorrectly can impact your report server. You would probably be better off just making a different version of the report for each location.

Post #1380349

« Prev Topic | Next Topic »

Permissions