Search code examples
ssrs-2008expressionssrs-2012percentage

SSRS expression for calculation using a specific value

I've attempted to create an expression which should calculate when a field equals 'Mid' and another equals 'Red' then calculate a percentage based on a field / specific number.

Here are my attempts so far:

=count(IIF(Fields!loc.Value="Mid" AND Fields!Status.Value ="Red",1,Nothing)) / count(Fields!Total.Value / 500) *100

=IIF(Fields!loc.Value="Mid" AND Fields!Status.Value="Grey",(FormatPercent    (Count(Fields!Total.Value) / 500 ,0))

Expected results from the calculation would be a percentage: 34.83% (to two DP)

Loc field contains locations: Mid, Lon, Manc, Newc etc etc
Status field contains colours for statuses: Red, Green, Blue, Yellow etc etc
Total field contains 'total' values for locations.

Neither seem to work and I'm getting myself confused. Once this one part is done, I can then add multiple locations and colours too.

Solution

  • Supposing a dataset like this:

    Loc    Status    Total
Mid    Red        100
Mid    Red        200
Lon    Blue        90
Manc   Yellow      50

    And you want to calculate the percentage of occurrences where Loc = "Mid" and Status = "Red" Using an expression like this:

    =COUNT(
  IIF(Fields!Loc.Value = "Mid" and Fields!Status.Value = "Red",Fields!Loc.Value,Nothing)) /
COUNT(Fields!Loc.Value,"DataSetName")

    Replace DataSetName by the actual name of yours.

    You will get 2/4 = 0.5 (50%) if you format the cell to a percentage number.

    Hope it helps.