CODEWITHSUNDEEP
reporting-servicesssrs-2012calculated-field
Faiz
Faiz

Reputation: 79

add calculated fields based on year by year performance

I am generating a report about sales and want to know if there is a way to calculate an aggregated column based on dates. e.g. Data stored in a matrix is orders, column is month and calender year. each month shows 3 years. I want the calculated field to take the most recent months and calculate the percentage change between them. see the following image to understand what I mean.

enter image description here

As you can see there are 3 years. I want to ignore 2008 and wnat my calculated field to do the percentage value of increase from 2009 to 2010. The design of this is shown in following image. enter image description here

Regarding the other question, see the following images:

In the design view my matrix looks like this: enter image description here

The output is report is:

enter image description here

The original data is:

enter image description here

And I just want to see the recent 2 years and ignore the rest of the years, like follows:

enter image description here

Thank you all in advance

Upvotes: 0

Views: 1374

Answers (1)

alejandro zuleta
alejandro zuleta

Reputation: 14108

Supposing your dataset returns one row per month/year you cannot use a calculated field.

However, you can calculate the increment or decrement percentage using an expression.

Add a column to the right under the Month column group.

enter image description here

Add the following expression to the cell is highlighted in my matrix.

=(Sum(iif(Fields!Year.Value=2010,Fields!Total_Ord.Value,0))-
Sum(iif(Fields!Year.Value=2009,Fields!Total_Ord.Value,0))
)/Sum(iif(Fields!Year.Value=2009,Fields!Total_Ord.Value,0))

Note my formula is (2010-2009)/2009 it will return a positive percentage if 2010 Total_Ord is greater than the 2009 Total_Ord. Otherwise the percentage will be negative.

This is the preview of the matrix:

enter image description here

UPDATE: Calculate percentage inc/dec of the last year and the last previous year.

Use this expression to compare the last year in my case 2015 and the last previous year 2014:

=(Sum(iif(Fields!Year.Value=Max(Fields!Year.Value),Fields!Sales.Value,0))-
Sum(iif(Fields!Year.Value=Max(Fields!Year.Value)-1,Fields!Sales.Value,0))
)/Sum(iif(Fields!Year.Value=Max(Fields!Year.Value)-1,Fields!Sales.Value,0))

It will preview this matrix:

enter image description here

Let me know if this can help you.

Upvotes: 1

Related Questions