Help with Matrix

Ok, I am having a little trouble figuring out if this is possible...

At first it looked easy (It always does), but as I started messing with matrix groups, it seems that i cant be done in 1 matrix.

Here is the display I want:

Value 1 Value 2 Total

Period 1 Period 2 Var % Period 1 Period 2 Var % Period 1 Period 2 Var %

Row

Row

Row

Row

I was thinking of having 2 groups, and then add a column to the 2nd group to calculate the variance column, and have that total. I cannot seem to get it to do that.

This is the first time I have really pushed the matrix for more than the basics.

Any ideas?

Thanks!!

you can have multiple column groups

e.g. sales region, sales person

but these will always relate to the same data

e.g. amount of sales in $

so if you want to report different things

e.g. percentages, sales, volts, centigrage, apples, bananas

you need to create a pseudo grouping

e.g. group on "1"

and then hide the grouping's borders/cells

and then you will need to use the inscope function to determine which column the report is being against

--

so in your example you will have the initial column grouping "period" and then another grouping called "variance"

"period" will be grouped on the actual period, but "variance" will be grouped on "1"

then in the data cell you just put

carnt remember the syntax but its something like....

=iif( inscope("matrixname_variance", myvariancedata, myperioddata)

you can also do this for rows

|||

Great, thanks! I now have the variance column... However I am assuming that I cannot use the calculations within the matrix to calculate the variance, is this correct? I see no way of referencing Period 1 and Period 2 from the Variance cell.

Thanks again!

BobP

|||

there should only be one cell in the 'data' area

this is the contents of my only 'data' cell in a particular report

=

IIF(

InScope("TradeCount"),

IIF(InScope("NotionalToBaseCurrency"),

switch(

Parameters!RevenueDisplayType.Value = 1,cdbl(SUM(Fields!RevenueAmountToBaseCurrency.Value)),

Parameters!RevenueDisplayType.Value = 2,cdbl(SUM(Fields!RevenueBPA.Value*Fields!RevenueAmountToBaseCurrency.Value)/ iif(Sum(Fields!RevenueAmountToBaseCurrency.Value)=0, 1, Sum(Fields!RevenueAmountToBaseCurrency.Value))),

Parameters!RevenueDisplayType.Value = 3,cdbl(Sum(Fields!RevenuePIPS.Value*10000*Fields!RevenueAmountToBaseCurrency.Value)/ iif(Sum(Fields!RevenueAmountToBaseCurrency.Value)=0, 1, Sum(Fields!RevenueAmountToBaseCurrency.Value))),

true=true,cdbl(Fields!RevenueAmountToBaseCurrency.Value)

)

,

iif(inscope("ParentGroup")

,cdbl(SUM(Fields!NotionalToBaseCurrency.Value)/countdistinct(Fields!RevenueTypeID.Value))

,cdbl(Code.CalculateSum(Fields!DealID.Value,Fields!NotionalToBaseCurrency.Value))

)

)

,

cdbl(CountDistinct(Fields!DealID.Value))

)

Where "parent group" is a row grouping and the top 2 are column groupings

so....

trade count shows as a single column with a numeric integer

whereas the "notional to base currency" displays some other data

in your scenario, trade count = variance, n2bc = period

sorry it is a bit of complex expression but i don't have any others to hand

|||

Ok, here is what I wound up doing.

First of all, thanks for the great ideas above! They were invaluable.

I created the matrix with 1 column as indicated, then I created 3 groups.

1. International/Domestic/Total

2. 1 (Called Variance)

3. Period 1/Period2

Then I added a sub total to the Variance group.

In the data cell, I used the InScope to display either the sum(data) or 1-Sum(first(data))/Sum(Last(data)), which gave me the variance %.

In the SQL, I had 2 SQL statements unioned. The first one grouped on International/Domestic and the Period (using a case statement on the data parameters passed in)

This worked like a charm.

Thanks again for all of the help and the direction!!

BobP