Search code examples
sql-serverreporting-servicesssrs-tablix

How to calculate a percentage using columns of data in a SSRS TABLIX that I have grouped

I have an SQL query that gives me a data set with 3 columns:

  • Contract Code
  • Volume
  • MonthRegistered

SQL Result Set

I want to present this data grouped on rows by Contract_Code and columns by MonthRegistered: Tablix layout

I then want to calculate a Percentage difference between the months: Sample Output

I will only ever in this case have 2 months worth of data - Each 1 year apart. I am trying to express the percentage variation from one year to the next for each row of data.

I did this expression:

=(Fields!Volume.Value)/(Fields!Volume.Value)

but CLEARLY it was not right - and how it is not right is it is not addressing the columns independently.

I did format the TABLIX text box as a percentage so at least I figured that one out.

in this Technet article: Calculating Totals and Other Aggregates (Reporting Services) it states:You can also write your own expressions to calculate aggregate values for one scope relative to another scope. I couldn't find reference to how to address the separate scopes.

I would appreciate any pointers on this one please!

Sorry for posting my examples as JPG rather than actual text but I needed to hide some of the data...

Solution

  • This only works because you will only ever have two months worth of data to compare. You have to make sure that your SQL has already ordered by MonthRegistered. If you do not order in your query then SSRS's own sorting will be applied to determine which value is first and last.

    =First(Fields!Volume.Value) / Last(Fields!Volume.Value)

    Because you have performed the aggregation in SSRS you may have to wrap each statement in a SUM expressions.
    It would be advisable to perform the aggregation in SQL where possible, if you only plan on showing it in this way.