ReportItems Sums by Grouping

Question

I have a data like this Original Data

and i intend to create the output like this Output

I have made the SSRS with Expression for debit like this

=IIF(SUM(Fields!Amount.Value)>0,SUM(Fields!Amount.Value),0)

and on credit like this

=IIF(SUM(Fields!Amount.Value)<0,SUM(Fields!Amount.Value),0)

The problem i have was i don't know how to total the report items on debit and credit column. I tried this expression for totaling the debit and credit

=SUM(ReportItems!DebitColumn.Value) =SUM(ReportItems!CreditColumn.Value)

the result is error. Is there an expression or something to total those debet and credit column per group A ?

Answer 1

I would do some of this work in SQL, this will make the report simple.

I started by recreating your sample data and aggregating as follows...

DECLARE @t TABLE (Group1 char(1), Group2 int, Amount float)
INSERT INTO @t VALUES
('A', 1, 1000),('A', 1, -500),('A', 1, -250),('A', 1, -500),
('A', 1, 100),('A', 2, 200),('A', 2, -500),('A', 2, -200),
('A', 2, 400),('A', 2, 200),('A', 3, -50),('A', 3, 0)

SELECT Group1, Group2, SUM(Amount) AS Amount
FROM @t
    GROUP BY Group1, Group2

Then in the report I added a matrix with row groups from Group1 and Group2.

I added a column grouping using the following expression.

=IIF(Fields!Amount.Value >0, "Debit", "Credit")

I changed the column group sorting to use the same expression with order Z to A

Finally, I also set the column header expression to the same expression.

I then added a total to the Group1 row group.

The final output looks like this.