[closed]Cross Tab with different Aggregation

[BIsrik]

Hi,

I have a requirement in which i need to have different aggregation for rows and columns in cross tab. for eg the usual cross tab report would be like this:

Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Product AÃ,  Ã,  Product BÃ,  Ã, Product CÃ,  Total
Ã,  2004Ã,  Ã,  Ã,  20Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 30Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 50Ã,  Ã,  Ã,  Ã,  Ã, 100
Ã,  2005Ã,  Ã,  Ã,  20Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 35Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 45Ã,  Ã,  Ã,  Ã,  Ã, 100
Ã,  TotalÃ,  Ã,  Ã,  40Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 65Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  95Ã,  Ã,  Ã,  Ã,  200

But i want the same cross tab to have a difference in aggregation in rows level and addition in column level. The Same Cross tab would then look like:

Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Product AÃ,  Ã,  Product BÃ,  Ã, Product CÃ,  Total
Ã,  2004Ã,  Ã,  Ã,  20Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 30Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 50Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 0
Ã,  2005Ã,  Ã,  Ã,  20Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 35Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 45Ã,  Ã,  Ã,  Ã,  -10
Ã,  TotalÃ,  Ã,  Ã,  40Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã, 65Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  Ã,  95Ã,  Ã,  Ã,  Ã,  -10

Notice the column aggregation is total and row aggregation is difference. How shall i do this in Cross Tab or any other ways.

Srik

[jcasella]

Have you tried just adding a calculation to the end of the table, and then aggregating the entire table.  For instance you table would look like this:

          Product A       Product B     Product C       Calculation
Year      A                     B                 C                    C - (A+B)

2004    20                   30                50                  0
2005    20                   35                45                  -10


Then put a total aggregate on the bottom of the table.

[BIsrik]

Ok ur suggestion would help me if i have fixed number of values. What i had given is just an eg. It is not always fixed to 3 products. Moreover in cross tab the values of a single dataitem are populated. U can't insert calculation at the aggregation level as u could do it in list. If the values are fixed then ur suggestion would be great help to us. But in real situation it is not so, the  number of values can vary.

Any other thoughts.

Srik