asked on

Agreegation in CrossTab

Hi Experts,

Here am using the Web intelligence. I have cross tab which is displaying State,Product line and Revenue. where as I have multiple product lines which are displaying. out of which I would like to group few objects and sum it .

For example: I have Accessories,City Skirts,City Trousers,leather,overcoats in this I would like to display the City Skirts+City Trousers together in one column.

Thank you so much for your time.

Regards,
MR

Maruthi Rao Yarapathineni

ASKER

BI-launch-pad--Efashion--Google-Chrome-2

Maruthi Rao Yarapathineni

ASKER

find the attached report file.

Hamed Nasr

Here is an idea, expressed in Access SQL.
Assume table: a (adesc, type, value)
Sample data:

adesc	type	value
California	Accessories	15
California	City Skirts	15
California	City Trousers	20
DC	Accessories	15
DC	City Skirts	10
DC	City Trousers	10
Florida	Accessories	20

Open in new window

Create 4 queries:
aggregate_2_columns_1 - from table a excluding the two unrequired values

SELECT a.adesc, a.type, a.value
FROM a
WHERE (((a.type) Not In ("City Skirts","City Trousers")));

adesc	type 	value
Florida	Accessories	20
California	Accessories	15
DC	Accessories	15

Open in new window

aggregate_2_columns_2 - from table a containing the sum of aggregating the 2 columns

SELECT a.adesc, "City Skirts + City Trousers" AS type, Sum(a.value) AS SumOfvalue
FROM a
WHERE (((a.type)="City Skirts" Or (a.type)="City Trousers"))
GROUP BY a.adesc, "City Skirts + City Trousers";

adesc	type	   SumOfvalue
California	City Skirts + City Trousers	35
DC	City Skirts + City Trousers	20

Open in new window

aggregate_2_columns_3 - UNION both queries aggregate_2_columns_1 and
aggregate_2_columns_2

SELECT aggregate_2_columns_1.adesc, aggregate_2_columns_1.type, aggregate_2_columns_1.value
FROM aggregate_2_columns_1
UNION ALL SELECT  aggregate_2_columns_2.adesc, aggregate_2_columns_2.type, aggregate_2_columns_2.SumOfvalue
FROM aggregate_2_columns_2;

adesc	type 	value
Florida	Accessories	20
California	Accessories	15
DC	Accessories	15
California	City Skirts + City Trousers	35
DC	City Skirts + City Trousers	20

Open in new window

aggregate_2_columns_crosstab - from aggregate_2_columns_3

TRANSFORM Sum(aggregate_2_columns_3.value) AS SumOfvalue
SELECT aggregate_2_columns_3.adesc
FROM aggregate_2_columns_3
GROUP BY aggregate_2_columns_3.adesc
PIVOT aggregate_2_columns_3.type;

adesc	Accessories	City Skirts + City Trousers
California	15	35
DC	15	20
Florida	20

Open in new window

Vitor Montalvão

It would help if you tell us with database are you using (MSSQL, MySQL, Oracle, DB2, Sybase, Access, Excel files, ...?) and also which application is at the frontend.

Maruthi Rao Yarapathineni

ASKER

Sure. Back end is the MSSQL and Front end is SAP Business Objects Web intelligence.

Actually I would like to see

A B C D are the columns, I would like to see the display as A,B+C,C,B+D . More details are attached as a file with figures.
Requirement.xlsx

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial