1

I have a requirement in which the SSRS 2008 reports need to be run automatically overnight, exported to excel and then saved on a physical folder. These reports take long time to execute so they don't want the end user to spend time in the morning in just generating the reports. So, basically they want to automate the process of report generation.

I tried setting up subscriptions within the reports for the simple ones (without any parameters) and it seems to be working fine. The problem is with the reports which have parameters (single or multiple). Is there a way we can create a scheduled task/package or maybe run a script in SSIS that can generate reports by iterating through the parameters and generate those many number of reports? We need to give a dynamic file name to the generated excel file based on the parameter.

For e.g. Product Sales Report. It has a parameter Product Manager, which is a list of 15-20 managers. This list changes quite often. So a sales report has to be generated for each of the manager, exported to excel and saved on a physical folder with the product manager name as part of the file name.

I do have a basic understanding of SSIS but I don't have a strong hold on it.

John Saunders
  • 160,644
  • 26
  • 247
  • 397
Romanshu Goel
  • 103
  • 3
  • 9

2 Answers2

1

I finally managed to make the DDS work with the MDX query. For that I created a LINKEDSERVER under database instance and used the synatx : Select * from (LinkedServer Name) ((MDX Query))

Using this I was able to retrieve the list of managers as one of the parameters and assigned its value in the DDS setup, which worked like a charm.

Thanks for your help Cory.

Romanshu Goel
  • 103
  • 3
  • 9
0

Take a look at: How to pass a parameter to SSRS report from SSIS package

Why do you even need SSIS for this? Set up a subscription in SSRS to export the report where you need in the format you need on whatever schedule you want. The Subscriptions in SSRS allow for parameters to be stored with the subscription as well.

Community
  • 1
  • 1
Cory
  • 12,404
  • 7
  • 33
  • 28
  • Hi Cory, Thanks for your reply. I have tried setting up a Data Driven Subscription(DDS) for the report with the parameter but it is not working with an MDX query. I'm using an MDX query to retrieve the list of managers and need to generate report for each of those managers in the list. DDS is failing at the point of validating the query and is not proceeding further. But when I execute the same query in SSMS, it works fine. Can you help with me with this? I'll post you the exact error soon. Thanks RG – Romanshu Goel Jul 30 '11 at 06:20