Extra column in matrix

Hello,

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"