Multiple Subtotals by Groups and Sorting by Group in SSRS - Financial Statements

• rsiem

  SSC-Addicted

  Points: 479

  More actions

  August 27, 2014 at 3:26 pm

  #289550

  We are new to using SQL Server 2012 and Reporting Services (SSRS) 2012.

  We are trying to get a financial statement (Income Statement /P&L) report to subtotal accounts by custom group and by company. Created a SQL script and view for SSRS to pull the basic financial data for the current period and YTD columns.

  Trying to figure out how to get SSRS to group and subtotal accounts by Custom Group field and then by Company field. As of now there are over 240 Level 1 custom groups and that is before splitting those groups out by company.

  Example: Account Receivable accounts – see attached image file.

  Group and subtotal AR accounts for Company 001 and repeat for each company (002, 004, 005, 006). This would result in 5 Level 1 Company subtotals.

  Then subtotal the 5 Level 1 Company subtotals into one (1) Level 2 Accounts Receivable group total (i.e. subtotal all of the 1020 Custom Group accounts)

  We then will need to combine several Level 2 subtotals into a Level 3 subtotal group. (i.e. Accounts Receivable + Cash + Inventory into Level 3 - Current Assets).

  In addition there are 2 dollar columns: Current Period and YTD. Both columns will need the same subtotals as described above.

  Questions:

  1.How many groups including parent-child groups are allowed in SSRS 2012?

  2.What would be the best method to accomplish the above in SSRS?

  3.Do we need to create 200+ Case statements for each grouping?

  Example: Case when CustomGroup = 1020 then 'Accounts Receivable'

  when CustomGroup = 1100 then 'Cash'

  End as 'Current Assets'

  a. If so, then how to get the sort and subtotal by Company?

  4.Is there a method or code that could use Expressions in SSRS to do the same?

  Any help or direction you could provide would be appreciated.