CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-tablixssrs-2008-r2ssrs-grouping
user3581406
user3581406

Reputation: 1

Need To Calculate Percentage Difference In ssrs with multiple row and column Grouping

I have row and column grouping.

I need difference and percentage difference for each month as shown below:

Example: 

                 march                diff
             2013      2014  
Hyderabad     12        15              15-12=3

Like this result I want and also I need percentage difference ---- (This Year – Last Year) / Last Year

Upvotes: 0

Views: 3813

Answers (1)

Ron Smith
Ron Smith

Reputation: 3266

I typically try to handle this in the SQL and make the reports dumb. In your case, I would make the result set look something like this:

City         MonthID    Month    YearDiffID    YearDiff    Value
---------    -------    -----    ----------    --------    -----
Hyderabad    3          March    2013          2013        12
Hyderabad    3          March    2014          2014        15
Hyderabad    3          March    9998          Diff        3
Hyderabad    3          March    9999          % Diff      0.25

Your Row Group would be City. Your Column Group 1 would group/sort on the MonthID integer, and display the Month string. Your Column Group 2 would group/sort on the YearDiffID integer, and display the YearDiff string.

If you provide your SQL, I can help you get the result set to look like this.

If you insist on calculating this in the report and all you have is City, Date, and Value fields, your expressions will look something like this:

Diff calculation, assuming you are grouping by City (Row) and Month (Column 1) and this calc is outside the Year (Column 2) Grouping and have added a Parameter for @ThisYear so this report will keep working in future years: =sum(iif(Year(Fields!Date.Value) = @ThisYear,Fields!Value.Value,nothing)) - sum(iif(Year(Fields!Date.Value) = @ThisYear-1,Fields!Value.Value,nothing))

% Diff calculation, with the aforementioned assumptions: =(sum(iif(Year(Fields!Date.Value) = @ThisYear,Fields!Value.Value,nothing)) - sum(iif(Year(Fields!Date.Value) = @ThisYear-1,Fields!Value.Value,nothing)))/sum(iif(Year(Fields!Date.Value) = @ThisYear-1,Fields!Value.Value,nothing))

Upvotes: 2

Related Questions