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:
Month
Quarter
Year
Mar-06
Mar-07
Q1 2006
Q1 2007
2006
2007
Company
Product
$1,000
$2,000
$3,000
$6,000
$12,000
$24,000
Change above to this:
Month
Quarter
Year
Mar-06
Mar-07
% Change
Q1 2006
Q1 2007
% Change
2006
2007
% Change
Company
Product
$1,000
$2,000
$3,000
$6,000
$12,000
$24,000
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:
Month
Quarter
Year
Mar-06
Mar-07
Q1 2006
Q1 2007
2006
2007
Company
Product
1000
2000
3000
6000
12000
24000
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)
Daily Data
Commission (GroupData cell 1)
Interest (GroupData CELL 2)
GroupData etc …
SUBTOTAL (DTD)
MTD TOTAL
YTD TOTAL
Book
Currency
=sum(Fields!DTD.VALUE)
........
=sum(Fields!MTD.VALUE)
=sum(Fields!YTD.VALUE)
and this is an example of the data being returned by my Sproc
Book
CCY
GroupedData
DTD
MTD
YTD
ABC
GBP
Commission
￡0.01
￡0.09
-￡0.10
ABC
GBP
Interest
￡0.02
￡0.29
￡0.11
ABC
GBP
Brokerage
￡0.12
￡0.06
￡0.20
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.
Daily Data
Grouped Data CELL 1
Grouped Data CELL 2
Book Name
Currency
DTD TOTAL
MTD TOTAL
YTD TOTAL
DTD TOTAL
MTD TOTAL
YTD TOTAL
=sum(Fields!DTD.VALUE)
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