I'm trying to make a report with the following layout:
Car Sales 2004 2005 Var %
Total 10 20 50%
Green 7 14 50%
Red 3 6 50%
I'm using a matrix and the data is coming from a cube. One dimension called 'Years' is used to fill the matrix columns and the data corresponding to the car sales is filled by a measure called 'Sales'.
I've built part of the example but I can't add the final column ( the VAR% column).
If I try to add a static column in the end it apears only one year.
The behavior that I would like to have is simillar to the situation when we add the Subtotal column. Although, instead the sum() made by subtotal I would like to calculate the variation percentage between years.
Is there any way to add a final column into a matrix avoiding the problem that I'm having or maybe change the behavior of Subtotal column?
Thanks and best regards.
vjn
I have a very similar need. I have two levels of columns and two row groups. I need to calculate a percentage difference between the 2nd level column values:
Change above to this:
I need to calculate a % Change column. I can't seem to find a way to do this. I can add a subtotal easily enough but how can I do a custom calculation? This is based on a sql query not an analysis cube. I had to use a matrix to "cross tab" the data. The data is not in cross tab format at the source otherwise I would just calculate the % in the query.
Can anyone help?
Thanks!
|||Right click in the cell where you need to add the extra column to its right and select Add Column from the popup menu. HTH.|||Thanks for the reply however that does not get me to what I need. Adding a column simply changes it to this:
I need the % change from Mar-06 to Mar-07, Q1 2006 to Q1 2007, and 2006 to 2007.
|||I also need to be able to add an extra column to my matrix - I've searched high and low on the net and I cant seem to find the answer anywhere.
I have grouped data which displays as it should and I can get the SubTotal part for my Dailys to run - I need to add a total for MTD and YTD (which is a part of the SQL Data) after the subtotal - is there a straight forward way of doing this?
This is an example of how I want my report to look (I can already achieve the bits highlighted bold)
and this is an example of the data being returned by my Sproc
Like the person above - if i use the "Add Column" functionality - I get a repeat of the MTD and YTD under each of the groupData cells - where as i only need it as a summary after the subtotal.
Does anyone out there know how to do it?
|||Hi Everyone,
I solved a similar issue by adding a new column to the matrix and using this expression to find the percentage of change from month to month.
=(Sum(Fields!TotalSales.Value)-(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value)))/(SUM(Fields!TotalSales.Value,"matrix1_FiscalMonth")-Sum(Fields!TotalSales.Value))
NOTE: matrix1_FiscalMonth is my row group name
To find the percentage of change take the “after” amount, minus the “before” amount and divide that result by the before amount. i.e.(60-50)/50 = 20%
Regard,
AA
"Small things done consistently in strategic places create major impact"
sql
No comments:
Post a Comment