I created the following Matrix in SSRS:
[Account] Total
Deposits [Sum(Deposit)] [Sum(Deposit)]
Withdrawals [Sum(Withdrawals)] [Sum(Withdrawals)]
Withdrawal Ratio ?????????? =Sum(Fields!Withdrawals.Value, "DataSet1")/Sum(Fields!Deposit.Value, "DataSet1")
Example:
Account A.......Account B Total
Deposits 75..............25...............100
Withdrawals 10..............25...............35
Withdrawal Ratio 13%.............100%.............35%
Source data:
Date........Account.....Deposits.....Withdrawals
2013/08/01..A...............20................2
2013/08/02..A...............20................2
2013/08/03..A...............20................2
2013/08/04..A...............15................4
2013/08/01..B...............10................8
2013/08/02..B...............15................17
The [Account] column is a group. Currently we have 2 different types of Accounts. I'm trying to calculate the withdrawal ratio (Withdrawals/Deposits). It works for the total. But I can't get it to work for the individual groups. It displays the ratio for the combined accounts.
Any thoughts?
You should be able to do this natively in SSRS.
I've used your data:
And created a Tablix:
I based this off a Matrix initially, but removed the row group - you can see that only grouping is the Account
column.
The Withdrawal Ratio expression is just:
=Sum(Fields!Withdrawals.Value) / Sum(Fields!Deposits.Value)
Compared to your example I'm not specifying a scope for the aggregate - since this gets executed in both the column scope (i.e. Account) and the total scope this will give the required results in each section:
Once the grouping for the Tablix is set up correctly everything else should follow.