I am planning to use Reporting Services - SQL 2005 to do the following.
Our customers need to get a summury of charges every day pertaining to a month worth of data. I have written a stored procedure that delivers relevent data of the customers (around 200) of them into 200 tables.
Here the part I need help: I am suppose to integrate each of these tables with the Reporting template that I have created and thus generated report of each customer needs to be exported to Excel. Also, the process needs to be automated to be scheduled to take place everyday at a specified time.
Can someone pitch in ideas as to what features of Reporting Services can deliver me what I plan to do.
If not, then you'll need seperate reports for each.
If they are all the same, then I'd question why you need 200 tables. Why not put them all into the same table, with an identifier labeling the customer. You can then use data driven subscriptions, assuming you're using enterprise edition.
This lets you run a query to determine email addresses or files to send reports to, you can then pass the customer as a parameter to the report, which will filter the data according to the customer.
One report, with one data driven subscription will then take care of all of your requirements.
The table structure is the same for all the 200 customers.
I am using SQL 2005 Standard Edition. Can what you indicated be done using Standard Edition. If not, pls suggest an alternative way of doing the same (may be using some code) using Reporting Services.
I just found out the answer - 'Data Driven Subscriptions' feature is not present in Standard Edition.
However, I would really appreciate it if you can suggest a way to mass export the Reports to Excel Files to a Folder with customer name + todays date as the file name ?
Sorry, the data driven subscriptions are only available when using Enterprise (or developer) edition.
The other methods you have available to you are: 1) Create 200 seperate subscriptions, each one to run a report and email or export the results. They can all use the same shared schedule, so they all execute at the same time. 2) Write a program that runs each of the reports and saves the results. Essentially this is creating your own data driven subscription system.
I've previously had to go down the first route - it took an age, but works!
You can have ordinary "Subscriptions" in Standard, which allows you to kick of a report automatically and email it to anyone you want, it's just that the email addresses are typed into the subscription when you set it up. "Data driven subscriptions" allow you to alter the email recipient list dynamically from data. Enterprise edition is expensive - would this feature justify your company buying it? It would be your best option.
The poor mans solution ;-) is to do this all in SQL, generating the data as individual CSV files in dynamic folders, you can even email them off to the customers from your SQL stored procedure directly.
As CSV, the files wont be pretty, but if you want to get clever, generate the files with HTML in and email the customers their personalised web page!
