February 2, 2011 at 6:15 am
Hi there
I'm an SSRS dev newbie & need some assistance with a tricky report. The data source is SSAS but that probably doesn't matter in this case.
Basically I have lists of textual KPIs and their answers. The answers are number values. So for example, a KPI might be:
Profits exceed 20% of revenue
And the answer value for 2010 might be 18% (i.e. the firm made 18% profit in 2010) and for 2009 might be 21%. The report will show two years side by side for comparison - the years can be selected from a parameter drop down list on the report. I've got this working, but what I want to do is have a third column on the report that shows the difference between the two columns. So a matrix report like this:
2009 2010 Difference
Profits exceed 20%....... 21% 18% -3 %
So for the difference field, I need an expression that would work out the difference between 2010 & 2009, but keeping in mind that the years are dynamic (selected by user from a drop down list) and can change. The percentage values are measures from an SSAS cube, the years/KPI questions are dimension attributes...
I've no idea where to start with this one so any help would be much appreciated!!
Thanks
Doodles
February 2, 2011 at 6:26 am
If thre are only going to be two years and the parameters filter the dataset then you can make use of the first and last aggregate function and build an expression to calculate the difference.
To add an expression right-click on the cell and click expression something along the lines of
=Last(Fields!Profit.Value, "DataSet1") - First(Fields!Profit.Value, "DataSet1")
should work.
February 2, 2011 at 6:46 am
thanks for your quick response Steve, i've tried it out but unfortunately it doesn't work for me, but that's cause i haven't explained myself fully.
The user will be able to filter on the KPI Stategy, so for example, Sustained Profit. By choosing this, they will get 3 or 4 KPIs listed:
Profits exceed 20% of revenue 21% | 18%
Statutory Gross Revenue (YTD) ...... | ...
Gross margin (%) (YTD) 55% | 54%
For each of the KPIs above, they will get values listed for the two years they are filtering on, so 2009 and 2010.
When i use the expression you have given, I don't get the difference between 2010 and 2009 for each KPI, i just get the difference between the last value listed (55%) minus the last value listed (21%), repeating across all rows... know what i mean?
Is there another way i can do this??
Thanks!
February 2, 2011 at 7:36 am
have you tried doing the calculation in the dataset? sorry i can't offer any more specic help as i am having trouble visualising the report and data
February 2, 2011 at 8:16 am
I'm not sure how i would go about doing that calculation as the data source is ssas...
let me try and use another example. So for example if you were reporting off of the adventure works db, say you are displaying products on the rows, and the years as columns and the quantity sold as the measure values. So for three products (say bikes, Clothing & Accessories), for years 2009 and 2010, I want to show the difference between the quantities sold (so 2010 values - 2009 values) in an adjacent column.
does that help with the visualization of what I want to do?
thx!
February 3, 2011 at 4:38 am
Okay that makes sense,
the first bit of code i posted had the scope wrong for your report, you need to set the scope equal to the row grouping for that matrix
If you add a new column to to matrix, make sure it is outside of the group and in this column set the expression
=Last(Fields!Profit.Value, "RowGroup") - First(Fields!Profit.Value, "RowGroup")
if i were to use your adventure works example then it would look something like
=last(Fields!Order_Count.Value, "RowGroupModelName") - first(Fields!Order_Count.Value, "RowGroupModelName")
February 3, 2011 at 5:31 am
thanks for helping me with this Steve. I tried out your expression but get an error along the lines of:
"The value expression for the text box "..." has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of the containing group, the name of the containing data region, or the name of the dataset."
Doing some research on google it appears that this is an issue when you are trying to sum values, which I'm not trying to do.. I haven't specified any aggregation in my inner group...
In any case I managed to find a solution to my issue from another post! Basically what i'm after is a totalling but instead of SUM or AVG, I want DIFF. So you need to overwrite the default totalling expression.
Here's a link to the article:
In the article, the years are hard-coded, in my example, i replaced the values with references to the parameter values & it works a treat!!
But if your method is another way of achieving the same thing then i'd like to get it working too. Let me know if you can think of a way of getting around the error above...
Thanks!
Doodles
February 3, 2011 at 5:44 am
doodlingdba (2/3/2011)
thanks for helping me with this Steve. I tried out your expression but get an error along the lines of:"The value expression for the text box "..." has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of the containing group, the name of the containing data region, or the name of the dataset."
Doing some research on google it appears that this is an issue when you are trying to sum values, which I'm not trying to do.. I haven't specified any aggregation in my inner group...
That error can relate to having the wrong scope in the expression from my example my RowGrouping is called "RowGroup" - you would need to change this to match the name of your row-group which you should see on the bottom left of your screen when you select the matrix,
That other method you posted looks interesting, i will have go with it when i have some time thanks for the link.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply