SSRS Report for Excel output, specifying different sheets

Epicor ERP 10
1

Is there a way in SQL Server Report Builder to have different tablixes (or is it tabli?) appear on different worksheets in a Excel file?

I’ve got one set of data that I need to show in several formats (grouped by GL, grouped by Invoice Num, etc…), and I’d like to have the one report create the different formats as different sheet tabs in Excel.

I had this happen by accident on one report - It was a custom SSR, and it happened to put each warehouse on its own sheet tab in Excel. But want to know how to do it on purpose. :slight_smile:

Calvin

2

?

3 Likes
3

In the interest of full disclosure, I was going to post this link instead, but I didn’t want to let everyone know all of Support’s best tricks:

2 Likes
4

Thanks.

So a “Page Breaks” mean new sheet when the destination format is Excel.

And the PageName is what the Sheet will be named in Excel.

In my case, I have 4 separate tablixes (all using the same dataset), all in the body, one right after each other. I set the PageBreakBefore property of each Tablix to true, and set the PageName to what i want the Excel sheet to say.

FWIW - This is so the report can have the raw data in one excel sheet, and the summarized data (by various groupings) in other tabs. Saves the Accounting folks from having to make Pivot tables on the raw data, every time the report is re-run.

5

Well @aidacra, I guess the cat is out of the bag now isn’t it?

image

Uuuuuh…kinda. :slight_smile:

You Support people and your Super Secret Tricks!

1 Like
6

And I was only familiar with doggy bags…

DOGsack

1 Like
7

Calvin,

Use a page break after each Tablix and and those tablix you want to print in Excel only use:

=IIF(Globals!RenderFormat.Name = “EXCEL”, False, True)

I hope that helps,

Jonathan

1 Like
8

Never mind I see you have a solution. :slight_smile:

9

But that extra info is good. That condition be the Visibility for the tablix?

10

Yes sir.

11

LOLRH! I’m sorry, that deserved a laugh! :joy: