Setting up a tabbed report that exports to Excel from SSRS

Question

I have a report that needs to be exported from Web Reports/SSRS to Excel while generating a tabular structure that includes a 'Summary' tab with all the information, which also includes a 'Total Count' formula/expression and a 'Total Cash' formula/expression. What I know how to do right now is have the report output tabs based on the parameter I need to sort by. What I'm having trouble with is getting the two 'total' expressions to update between different tabs as well as generating a 'summary' tab that includes all the information.

Answer 1

So, I did find an answer to this on my own and figured I should post it here if anyone else looks at this:

I set up two groups with page breaks between them. Group 1 was the summary data (all data) and was only organized by the fields I needed to order by. The second group was actually separated with page breaks as I needed them to be and included the totals for each group so that it showed properly.

For more info, I used this lesson on MS's SSRS page: https://docs.microsoft.com/en-us/sql/reporting-services/lesson-6-adding-grouping-and-totals-reporting-services?view=sql-server-2017

Answer 2

Your best bet it so add the totals in your query, and separate the total out like another grouping so that it gets exported to Excel in a new tab. For example, say I want a report of revenue by state:

SELECT l.JurisdictionState
    , Price = SUM(l.Price)
    , SortOrder = l.JurisdictionState
FROM dbo.Reporting_LineItemDW l
GROUP BY l.JurisdictionState
UNION ALL
SELECT JurisdictionState = 'Total'
    , Price = SUM(l.Price)
    , SortOrder = 'ZZ'
FROM dbo.Reporting_LineItemDW l

Now, each state will export into Excel on a separate tab, as will the "Total" group, which SSRS just interprets as another JurisdictionState, like any other. This sort of calculation is often better done on the SQL side than on the client-side, anyway.