Search code examples
reporting-servicesssrs-2008-r2

Need to Add / Subtract Group totals for Income Statement

I have designed an Income Statement in SSRS. It has two row groups, the account type and the account from the chart of accounts makes up the detail. The problem I have is that I need to add and subtract some of the group totals.

For example, there is a group of accounts names Sales, the next group is Returns and Allowances, the next group is COGS. I need to to insert a row that sums the Sales and Returns and Allowances groups. Then I need to insert a row after the COGS group that subtracts the COGS to total from Sales and Returns and Allowances totals.

For example:

Sales
  Acct 1
  Acct 2
Sales Total

Returns and Allowances
  Acct 3
  Acct 4
Returns and Allowances Total

Net Sales = Sales Total + Returns and Allowances Total

COGS
  Acct 5
  Acct 6
COGS Total

Gross Revenue = Net Sales - COGS  (or Sales Total + Returns and Allowances Total - COGS Total)
Solution

  • Without understanding what your data looks like it's difficult to give an exact answer but I would make life easier by adding an extra group to your dataset. You could easily do this in the dataset query.

    I created some sample data with an additional outer grouping column. I also added a sort column just for simplicity but you could sort that out however suit you best.

    Here's the sample data script.

    DECLARE @t TABLE(OuterGroup varchar(20), AmountType varchar(20), Account varchar(20), Amount decimal(10,4), SortOrder int)

INSERT INTO @t VALUES 
('Net Sales', 'Sales', 'Account 1', 10.1, 1),
('Net Sales', 'Sales', 'Account 2', 15.5, 2),
('Net Sales', 'Returns & Allowances', 'Account 3', 2.6, 3),
('Net Sales', 'Returns & Allowances', 'Account 4', 3.7, 4),
('COGS', 'COGS', 'Account 5', 1.1, 5),
('COGS', 'COGS', 'Account 6', 1.2, 6)

SELECT * FROM @t

    I used this directly in the dataset query. As you can see the 'outer gorup' will allow us to group on this in the report.

    The report design was a simple table with totals added to each of the three groups. The groups were on OuterGroup, AmountType and then the default details group. I added a total to each of these.

    Here's the design

    enter image description here

    Other than adding a bit of test to the total caption expressions the only thing I edited was the final Gross Rev. expression

    The expression is

    =Sum(Fields!Amount.Value * IIF(Fields!AmountType.Value="COGS", -1, 1))

    All we are actually doing here is summing all the amounts but multiplying the COGS amounts by -1 first to make them negative.

    This gives the following results

    enter image description here

    You could then Delete the first column (but not the group!) if you moved the gross rev. caption to the next column obviously...