Generating report using batch process

Hi all,

I have tried asking the same question in other forums. All i get is links

Please help me. I have the following requirement:

I have the following tables:
Theater - TheaterId, TheaterName, Revenues,locationid, stateid
State - StateId, StateName
Location - LocationId, LocationName, StateId

I want to generate reports that will tell me the revenue generated for each theater in each location in a state. I want to run a batch process which will loop through the 3 tables and will passing the location and state id as parameters one by one. I want each report to be generated as a pdf and stored in a location. How do I do this?

Thanks.

Two methods come to mind:

1. You can run reports from the command line using the rs command (documentation) as described in the following SQLJunkies article:

http://sqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk

which doesn't seem to be up for me so here is the Google cache link:

http://64.233.167.104/search?q=cache:XvoyQYWNs50Jqljunkies.com/Article/B197B9E7-EF3D-4D70-9B5E-47B74E57EF38.scuk&hl=en&gl=us&strip=1

Within the VB script that you run, query your tables and feed the parameters.

2. You could use a variant of a Data Driven Subscription. You will need the Enterprise version of SSRS to use Data Driven Subscriptions. Here is a tutorial on building one:

http://msdn2.microsoft.com/en-us/library/ms169673.aspx

The change that you will make to the standard script is in the Define a query to retrieve subscriber data section. You will just add to the standard query a join to your three tables and use the query results as parameters to the report.

I came accross this article by Jason Selburg which claims to allow you to build your own data driven subscriptions using Standard Edition:

http://www.sqlservercentral.com/columnists/jselburg/datadrivensubscriptions.asp

and his follow up:

http://www.sqlservercentral.com/columnists/jselburg/2824.asp

The reason that you are getting links is that people are willing take the time to give assistance and pointers but not specific solutions. The problem you describe is an interesting one and one that will I will now probably try and cover in my blog in the near future. When I do, I will post the link here and you should be able to get code that is closer to a complete solution.

Larry Smithmier

|||

dear larry,

thanks for the link.

vidkshi