Data in Crosstab

building reports desktopintelligence
1

Hi,

I do have group information stored in data as mentioned below

ID         Group     Rev
123        ABC       100
123        TTT       100
234        ABC        50
567        ZZZ        75
567        ABC        75

I need this data to be displayed in cross tab as shown below

 	   ABC	TTT	ZZZ
ABC	225   100    75
TTT	100   100	  0
ZZZ	 75	  0    75

I have already tried Normal Cross tab, tried multiple DPs and all the stuffs that I could thing of.

Let me know if it’s achievable in BO.

THANKING YOU in advance for your replies!

[Edited, applied the code option for formatting. It will preserve any indenting or formatting that you may have done, so your crosstab will display properly. Thank you, Andreas.]

Gaurav13 :india: (BOB member since 2005-09-30)

2

Attaching data format in excel file.
Data in Crosstab.xls (16.0 KB)

Gaurav13 :india: (BOB member since 2005-09-30)

3

Hey Gourav,
With Cross tab you cannot have it in the format you suggested. and it just doesn’t make sense to have data in that format.

dvijaysaradhi :us: (BOB member since 2005-09-12)

4

Hi, Thanks for your reply…

One thing I understand is, it does not seem to be possible.
Another thing, I would like to explain that how does it make sense to me.

Mt desired format clearly shows the below analysis
Total Rev for “ABC” group is “225”
Total Rev for “ABC” and “TTT” combination is “100”
Total Rev for “ABC” and “ZZZ” combination is “75” and so on for left two row headers.

I understand, there is some repetition of the information but this is what my user has requested me to generate a report in this format.

Let me know if you or anybody else can think of anyway to achieve this except asking/suggesting user to get the data in different format.

Thanks!

Gaurav13 :india: (BOB member since 2005-09-30)

5

Gaurav, Dont Comply to whatever the users demand, Always ask them Why do they need it, how are they going to interpret the data.

this is very simple report, no need for Cross Tab. you just have to change the orientation based on users preference.

skm_user (BOB member since 2006-10-05)

6

Thanks skm_user,

I think, my previous mail explains why do they need it, how are they going to interpret the data.

Technically, I still would like to know whether its doable or not.

I am still hoping to get some strong reply from experts/moderators.

Thanking you!

-Gaurav

Gaurav13 :india: (BOB member since 2005-09-30)

7

Gaurav,
the best you could get is the below results, would this be ok ?

  ABC   TTT   ZZZ

ABC 225
TTT 100
ZZZ 75

skm_user (BOB member since 2006-10-05)

8

Gaurav,
I am attaching the spreadsheet, the output in my earlier post is all messed up. Let me know if it would be ok
output.xls (19.0 KB)

skm_user (BOB member since 2006-10-05)

9

What about the deals/revenues where ABC and TTT both contributed togather and so on for ABC & ZZZ and TTT & ZZZ combination.

The oputput you sent is an output that you get in ordinary crozz tab and here it looks like I need to get something extraordinary.

Thanks for your time.

Still waiting for resolution.

-Gaurav

Gaurav13 :india: (BOB member since 2005-09-30)

10

G:

The solution for your problem is to employ the use of the cross join feature in the SQL language. I’ve used SQL for the past 16 years and yours is only the second case I’ve ever advised on using the cross join (making multiplication tables in sql doesn’t count, right?) Anyhow, here is a short SQL script for building your table and then pulling the data down (SQL script below is just a refresher for you so that you can see the method to my madness)

create table Gaurav13 (id1 int,group1 char(3),rev int)
insert into Gaurav13 values (123,‘ABC’,100 )
insert into Gaurav13 values (123,‘TTT’,100 )
insert into Gaurav13 values (234,‘ABC’,50 )
insert into Gaurav13 values (567,‘ZZZ’,75 )
insert into Gaurav13 values (567,‘ABC’,75 )

select Gaurav13_1.group1,Gaurav13_2.group1,sum(Gaurav13_1.rev),sum(Gaurav13_2.rev)
from Gaurav13 Gaurav13_1 join Gaurav13 Gaurav13_2 on Gaurav13_1.group1=Gaurav13_2.group1
group by Gaurav13_1.group1,Gaurav13_2.group1
order by 1,2

I am uploading an excel spreadsheet that uses pivot table feature which is the same functionality as cross-tab in webI, so that you can see the product from the above SQL data pull and the output.

Good luck with this and post back if you have any questions/clarifications.

Thanks,
John
Book1.xls (13.0 KB)

jsanzone :us: (BOB member since 2006-09-12)

11

Hi John,

Thanks so much for your time; I just had a look in to file you sent but It is not getting us the desired result.

If you seem, we are getting only numbers for ABC-ABC, TTT-TTT and ZZZ-ZZZ combinations.
I also need to display numbers for each and every possible combinations like ABC-TTT, ABC-ZZZ, TTT-ABC, TTT-ZZZ, ZZZ-ABC and ZZZ-TTT.

In other words, none of B5 to D7 cells should be blank in “sheet2” of attached file.

Feel free to let me know if you have any doubt(s) on this.

-Gaurav

Gaurav13 :india: (BOB member since 2005-09-30)

12

Guarav,

I am truly stumped in how to fix this for you, but I can say is good luck!

John

jsanzone :us: (BOB member since 2006-09-12)

13

Gaurav,

I am providing solution based on Johns data, your last posting was very clear…ok here it is

create table Gaurav13 (id1 int,group1 char(3),rev int)
insert into Gaurav13 values (123,‘ABC’,100 )
insert into Gaurav13 values (123,‘TTT’,100 )
insert into Gaurav13 values (234,‘ABC’,50 )
insert into Gaurav13 values (567,‘ZZZ’,75 )
insert into Gaurav13 values (567,‘ABC’,75 )

select group1, group2, rev2 from

(select id1, group1, sum(rev) rev1 from Gaurav13
group by id1,group1 ) a,
(select id1,group1 group2 , sum(rev) rev2 from Gaurav13
group by id1,group1 ) b
where a.id1=b.id1

create cross tab based on the above sql. (see attached output)

Good luck

~SKM
newoutput.xls (13.0 KB)

skm_user (BOB member since 2006-10-05)