How Do I Dynamically Export SSRS (Filename/Destination) Using Rpt Parameters

SSAS and SSRS
1

I have an SSRS report (SQL Server 2012) that is generated every month to PDF or Excel depending on each user's needs (there are 9 users).
In the end, there are about 200 client reports that get generated each month from this single SSRS report. I am looking for a way to automate this process and auto-generate the reports and drop them into specific folders based on a couple parameters. (the entire process would be manually started by a trigger/button when IT says "go")

A few dynamic items to consider:

  • Filename needs to be created dynamically. So it would be put together with these variables:
    YYYY_MM_CompanyAbbreviation_ClientID_EOM_Type
    • Where YYYY_MM is pulled from the report (so if the report is run for Feb, then it is getting tagged for Feb)
      For example: 2016_02_CMPY_CLIENTXYZ_EOM_Summary
  • Folder destination needs to be dynamically set based on date YYYY_MM/EmployeeName.
    Example: /2016_02/Joe Schmoe
  • Each company has a web page with parameters (what type of report to generate: Excel/PDF, detailed/summary, show/hide specific fields, etc.)

Currently, each employee/user has to run this report individually for every clientID for every report type (some clients receive 2 Excel and 2 PDF reports). In other words, fill in the parameters on the SSRS front-end and manually export. It gets pretty time consuming and will only increase.
I want to have a trigger or button somewhere that IT can click to start or schedule the report generation process for all reports. It will pull the parameters from the Company pages and run the report for every clientID, then drop the exported reports in the specified folders (also based on parameters) or document library in Sharepoint.

I have no problem with grabbing the fields necessary to get the data/parameters.
The IT "Start" button would be in Salesforce or Sharepoint. I am not automating this because the start date might change month to month and I don't want it automatically running early. I can get this part setup to run a SQL SP, but I am not sure how to do all the dynamic stuff and spit it back into Sharepoint.
SSRS Subscriptions won't work. Data-driven Subscriptions may work. I'm actually researching data-driven now, but I wanted to post this to start getting help.

Hopefully this is clear.
Anyone have any good solutions or ideas?

Thanks.

2

You want data driven subscriptions. Almost everything that you need to accomplish can be done with that. You are correct in that it won't "schedule" it if the date/time is variable depending on outside parameters (like a process being finished that is outside of your control). You CAN have it setup as a data driven subscription with a schedule that will never run. Then, you can create a process that will trigger the schedule to execute like putting a button on a page for a user to click on. You can trigger a schedule to run on-demand easily. I have done it with earlier versions of SSRS and it may have changed since then but you basically trigger it on the report server via SQL.

Below is the code I would use to do that. Of course you would need your GUID value for the schedule you want to trigger. It is run via SQL so you can wrap that up in any client application. You can even create specific "schedules" for custom clients that may need different behavior. In short, you can do just about anything.

ReportServer.dbo.AddEvent @EventType='TimedSubscription', @EventData='9C4FC551-9522-415E-94BC-6D9C98A1503B'

3

Please go this one here you may find the help. http://weblogs.asp.net/srkirkland/exporting-a-sql-server-reporting-services-2005-report-directly-to-pdf-or-excel

4

Thanks James, I'll take a look at doing it this way.