Summary data in Crosstabs.

[Former Member]

Hi ,

I'm working on a report which has 4 tabs within it , the first one being detail and the rest 3 crosstabs. The crosstabs represent the summarised data of teh detail table which is grouped n summed up based on field say A1 , A2 , A3 and so on.

I want to dispaly at the end of the corsstab a single row which will hold the Total sum of all the data shown in the crosstabs. say "A" which is A=A1A2A3 .

I tried using the formula sum(field1) ForAlll 'Afield' but it repeated below every row of A1 , A2 , A3 ..

Rows : A1 , A2 , A3

Coulmns : field 1 , field 2 ..

Total : Sum (Field1) for all A's.

Please help me with the formula.

[Former Member]

Hi

can you explain with an example ?

[Former Member]

Ok. Say I have a report which shows the sales of wallmart. I have all details in the details tab which is the first report . This is followed by 3 tabs which contains crosstab information of the details report as Summarised data.

So In crosstab1 I have rows of data on "states" where wallmart stores exist, there are multiple wallmarts in each state(say , California , Newyork , Texas , chicago and so on) so I sum them up statewise. and the columns on say purchase order , actualprice , totalrevenue ,%profit , averagerevnue.

The detail report has the individual records for all the states . So In teh crosstabs I use the function sum(purchasedorder) or sum(actualprice) to get their sumarised data for corresponding states. I use Filter in detail table so only the states I filter on is displayed in the crosstab.

Now I want a last row which displays "Wallmart" and should contain the sum of PO , actualprice , totalrevenue , avgrev for all the states displayed in crosstab. Its like the grand total of all fields. I couldnt use funtion sum on the variables which I created to find sum statewise. The grandtotal was dispalying as a row after every state when it should be displaying as a single row at teh bottom of the crosstab.

I hope Its clear now. I guess I should be using aggregate tables .I'm not sure though Just Incase there is any easier way to resolve this.

[Former Member]

Will we have to create aggregate tables for this??