Calculate growth percentage inside a group column in SSRS

Question

I'm currently bulding a report than includes a table with column group with the group on value for Month, so it would dinamically create the month column according to the range selected by my user.

table design

What is needed is that, along every Valuación column we show a Crecimiento (growth) column. For this, the [SUM(Diferencia)] of the previous month needs to be considered the 100% for the rule of the three to be applied

Currently, this is the formula i've placed on the field

=IIF(
Fields!Mes.Value = 1, 
0,
(Sum(Fields!Diferencia.Value)/
ReportItems!Diferencia.Value)
)

(Mes = Month)

But, as you might know, it always returns a 100% because Sum(Fields!Diferencia.Value) = ReportItems!Diferencia.Value) (who would've guessed)

I've tried with Fields!Diferencia.Value & Sum(Fields!Diferencia.Value, "Informacion") (the second one is what I obtain after searching Diferencia inside the Datasets category)

Datasets category

but still dont get how to reference the previous month Sum(Fields!Diferencia.Value)

any idea how is this possible?

thanks in advance guys

Edit 1: I'm adding an live preview image, to show you how the data is displayed during actual runtime

Edit 1 image

Alan Schofield · Accepted Answer · 2018-10-31T11:31:15.797

You can use the PREVIOUS function to get data from the previous scope range (in your case that will be the previous month).

I'm not sure exactly what you want to show but this simple expression will show the current month's value as a percentage of the previous month's value. For example, if January was 50 and February was 60, it would return 1.2 (120% if you format using "p2" format string)

UPDATED This part of the answer: Using something like this

= (Sum(Fields!Diferencia.Value)) / Previous(Sum(Fields!Diferencia.Value), "Mes1")

NOTE: The group name "Mes1" is case sensitive

How does it know what values to sum? Its tricky to explain clearly but the short version is that your expression is sitting inside the column group 'Mes1' so SUM(Fields!Diferencia.Value) means sum Fields!Diferencia.Value within the current scope, the current scope is the month group. When we use the PREVIOUS function, the exact same thing happens, except this time we are telling SSRS to use the Previous set of data from the same scope. Scope is still your month group Mes1 but we just get data from the previous group entry (previous month in this case). Hope that makes sense !

Sample report I built a test report based on the NorthWind sample database.

The dataset simply contained total order values by month number and country.

I Added a matrix and set the row group to Country, the column group to OrderMonth and the data values to OrderValue.

I added a few extra columns to show the previous and calculated growth values.

The design looks like this.

The Expression for each column within the group are

Current:

  =Sum(Fields!OrderValue.Value)

=PREVIOUS(Sum(Fields!OrderValue.Value), "OrderMonth")

Current/Previous =

=IIF(PREVIOUS(Sum(Fields!OrderValue.Value), "OrderMonth") = 0 , 0, Sum(Fields!OrderValue.Value))
/
IIF(PREVIOUS(Sum(Fields!OrderValue.Value), "OrderMonth") = 0 , 1, PREVIOUS(Sum(Fields!OrderValue.Value), "OrderMonth") )

The final output looks like this, which is hopefully what you need.

Thanks. I tested your suggestion, but with `Previous` it takes the upper row, instead of the column at the left which represents the previous month. — Rodrigo Jimenez, Oct 31 '18 at 00:39
I've just realised, my test did not have any row groups. I've updated my answer to include the correct scope. Try this and let me know. — Alan Schofield, Oct 31 '18 at 09:58
It worked like a charm with the correction.Thank you very much — Rodrigo Jimenez, Oct 31 '18 at 17:52
You're welcome. Glad you got it sorted. I should stop posting answers on StackOverflow late at night, too many mistakes ! :) — Alan Schofield, Oct 31 '18 at 21:31

Calculate growth percentage inside a group column in SSRS

1 Answers1