Search code examples
matrixreporting-servicesssrs-2008

SSRS Ratio is matrix


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?


Solution

  • You should be able to do this natively in SSRS.

    I've used your data:

    enter image description here

    And created a Tablix:

    enter image description here

    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:

    enter image description here

    Once the grouping for the Tablix is set up correctly everything else should follow.