Add a calculated percentage in a report

Question

Add a calculated percentage in a report

josep_bcn

SSC Veteran

Points: 284
More actions
August 6, 2011 at 7:18 pm

#241426

Hi,
I am trying to create a report (matrix or table, doesn't matter), to compare current sales to previous year's sales, showing the % increase:
Jan Feb Mar ... Dec
2010 23 20 20 12
2011 30 34 22 17
% Incr 30.4% 70% ....
I'm having problems adding the percentage increase, which should be a simple calculation of CurrentYearValue / LastYearValue.
The dataset I'm using has basically two fields: Date, Sales, and many rows with a sales order in each one.
How would you calculate and display the percentage?
thank you,
Josep

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 1

If your using the report builder wizard there is a check box to include grand totals, select it and you will find a row at the end of the table which has a sum function , you can simply replace the formula in this row to calculate year on year increase.

This assumes the dataset your using already has all the values your looking for.

Jayanth Kurup[/url]

Jeff Moden SSC Guru Points: 1004441 More actions · Answer 2

@Josep,

Do you always want to show Jan thru Dec or do you really want to show comparisons for the previous 12 months? For example on the previous 12 months... a report generated today would show Sep 2010 thru Aug 2011 in the header and (for example) Sep 2010 would be compared to Sep 2009.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

josep_bcn SSC Veteran Points: 284 More actions · Answer 3

@Jayanth_Kurup I am not using the report wizard, although I tried using it and can't find how to customize the Total.

Is it possible to change the "Total" formula without the wizard? I'm trying, but I can only seem to be able to add a "Sub Total" but not customize the formula. Remember I'm on SSRS 2005 (not 2008 which appears to be simpler in some cases).

@jeff Moden I would like to have January to December, 12 months as columns. Then two rows (year 2011, Year 2010), and ideally another row that gives the increase from 2010 to 2011 (which would be a simple division of the two 'Cells' above.

Maybe because I've used Excel extensively, but I find it very difficult to do things in SSRS that would be extremely easy in Excel. The current problem is an example: I now how to "fill" the cells for 2011 and 2010 by month, but then it is very difficult to add a simple division of two "cells" at the bottom of each column.

After much google, reading this forums and some imagination, this is what I am using, could you comment on whether this is the 'right' way?

- A matrix with a row group and a column group

- The first column is a row group that equals to "Nothing" (basically a 'dummy' row group)

- Next column contains 3 cells in 3 rows (part of the row group) that function as labels: "2010", "2011", "% increase"

- Next column is a column group that contains "=Fields!SalesMonth"

- Below, within the scope of the column group and the row group, 3 cells that contain data for 2010, 2011 and a calculated % increase.

- I'm playing with the scope in the expression to get the numbers I want in each cell.

- For example, in the '2010' cell, I have =(sum(iif(fields!SalesYear.Value=2010, Fields!SalesQuantity.value,CDbl(0)))

- In '2011', I have: =(sum(iif(fields!SalesYear.Value=2011, Fields!SalesQuantity.value,CDbl(0)))

- And in the "% increase": simply the division: = (sum(iif(fields!SalesYear.Value=2011, Fields!SalesQuantity.value,CDbl(0)) / (sum(iif(fields!SalesYear.Value=2010, Fields!SalesQuantity.value,CDbl(0)))

Basically I use the same system for other calculations, using the optional "scope" in the expressions when needed.