Can I programmatically generate reports that are emailed as Excel docs from SSRS?

Question

I've searched already and don't see (or can't decipher) a solution for my issue. I have SSRS emailing a report for 1 district (with 12 retail stores). However, each Region has over 8 Districts, and I need to put each District on its own tab. I have multiple tabs working, but I'd like to avoid creating each sheet if possible. I also need to generate 4 of these master spreadsheets - is there a way to make some sort of template for one worksheet using parameters? We're using SQL Server 2008 R2 and Report Builder 3.0

Answer 1

Ini. My first thought here is that you could use a List control to iterate across Districts. SQL Server MVP Jes Borland has a great intorductory post on Lists here: http://jesborland.wordpress.com/2010/11/17/the-power-of-reporting-services-%E2%80%93-lists/

Inside the list control will be a rectangle. By setting the Page properties of that rectangle, you can create yours tabs for each District, dynamically setting the Page Name property to an expression that returns the District.

You could configure your dataset (ideally, a stored procedure) to take the Region as a parameter and then you can run this ONE report for each of your Regions and they will all work the same and keep you from having to create multiple copies of the same object.

Assuming I understood your question correctly, this should get you moving in the right direction.

Answer 2

Beyond @MarkVSQLIsHandsomeAndWitty's response, another option for creating each worksheet from SSRS is to just use a page break to per district. When that exports to Excel, they go to different tabs. Last time I tested it, I had an issue wherein subsequent tabs always had a leading empty row but that's been a few years.

https://stackoverflow.com/questions/462902/reporting-services-export-to-excel-with-multiple-worksheets

You can further automate it by calling the render method for the report with the Region code in the URL with a flag for output to be Excel, write that bytestream out to a file with an xlsx extension and blammo, you have automated your Excel extracts.