March 26, 2012 at 8:47 am
Hi, I'm fairly new to SSRS and I'm having trouble trying to add percentages into a matrix. Some background info..
The matrix summarises the number of opportunites won/lost for a given ownerid. I have a row group of ownerid and a column group of statecode (won/Lost) within this it counts the number of opportunityid's, basically the report looks like this:
Won Lost Total
Owner 1 10 11 21
Owner 2 20 30 50
Owner 3 5 5 10
Total 35 46 81
What I'd like to be able to do is add a percentage against each of these,ie:
Won % Lost % Total %
Owner 1 10 29 11 24 21 26
Owner 2 20 57 30 65 50 62
Owner 3 5 14 5 11 10 12
Total 35 100 46 100 81 100
Can anyone out there help? Whatever I try just doesn't seem to work.
Many Thanks
Andy
March 26, 2012 at 11:21 am
Hmm am not so sure where exactly you are having the bother, and whether its to do with getting the percentage numbers, or formatting the cell as a percent, but here goes:
> You have your Total column (i assume from the dataset itself) which you can use to calculate the percentage for win and loss. This can be done in 3 places in SSRS, either:
> Add the calculation on the DataSet itself, by right-clicking the DataSet and clicking on "Add Calculated DataField...".
> Add the calculation on the underlying data-source, so the stored proc or query text if your datasource connects to SQL. (recommended, particularly if you are reusing that value)
> As a column expression in the report itself, so it will be something like: Fields!Win.Value/Fields!Total.Value
> After you have your numbers, just add them to your reports table as new columns (or replace the existing Win/Loss columns), if you opted in for the 3rd option above this should already be done for you.
> Format the table cell to be "Percentage"; you can reach the cell formatting menu by right-clicking on the cell, and selecting Text Box properties. so essentially 0.5 will be formatted to 50%, and so on.
Hope this helps!
-----------------
... Then again, I could be totally wrong! Check the answer.
Check out posting guidelines here for faster more precise answers[/url].
I believe in Codd
... and Thinknook is my Chamber of Understanding
March 28, 2012 at 7:50 am
There are two important gotchas here.
First is to avoid divide by zero. Using an expression for the lost percentage would look something like:
=fields!lost/fields!total
Then format the cell as a percentage. If you are lucky and total is never zero then that works, otherwise you need to wrap it in an iif function:
=iif(fields!total = 0,0,fields!lost/fields!total)
warning here though, there is what I would call a bug here but apparently its a design feature. All parts of the iif will always be evaluated whether they are used or not in a particular result row.
So the fixed version is this:
=iif(fields!total =0,0,fields!lost/iif(fields!total = 0,1,fields!total))
Note the first case of the second iif will never be run for a real result, so it will never divide by that 1. It just tricks the pre run expression evaluator.
The second gotcha with percentages is that you can't aggregate them.
Directly averaging the percentage at the group level will not work as you might instinctively think, for mathematical reasons. weighted averages come into play.
Eg
Lost Total Percent
4 50 8%
24 150 16%
28 200 12% (group average)
There are 2 rows that make up the group, but the percentage of each carries a different weighting. The real answer is 14 % because the 24/150 carries a higher weighting than the 4/50.
The expression you put in the group percentage field needs to sum the lost and sum the total independently.
You might think that this should all be done in the database but I think that gets complicated quickly and it's probably good practice not to store calculated results anyway. If you're using a cube then an mdx calculated measure would work well with aggregates but in SQL I am seeing visions of clever but cryptic window functions to accomplish this.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply