Multiple Measures in a Single Cross Tab

MarkP · November 15, 2010, 10:59am

Hello,

Not sure this is possible to achieve the following in a single table - Webi 3.1
I could do it in two myself, not a problem, but I need to see if it can be done in a single crosstab:



              |          Number of Calls Made             |          Number of Calls Answered         |
              | Jan | Feb | Mar | Apr | May | ..... | Dec | Jan | Feb | Mar | Apr | May | ..... | Dec |
Sales Person1 | 123 | xxx | ... and so on                 | 456 | yyy | ... and son on                |
Sales Person2 | 123 | xxx | ... and so on                 | 456 | yyy | ... and son on                |

Thanks in advance for your help - I may be missing something obvious but it wants to do Month then Calls Made and Calls Answered but won’t split out the other way round (as per above).

Regards,
Mark

system · November 15, 2010, 11:56am

Mark,

I don’t know a simple way of doing it, as effectively you want the value of your measure to act as a dimension (eg a dimension with the values of “Calls Answered” and “Calls Made”. Are the measures coming from the same query or separate ? If separate queries you may be able to use a local variable to identify the label.

I had a similar scenario which I resolved by using a table instead of a crosstab, and creating a multitude of variables in the report to pivot the measures (eg in your case Calls Made Jan, Calls Anwered Jan etc. As you have a finite number of dimension values (Jan - Dec) this would work for you, it’s just ugly

margarita (BOB member since 2005-11-08)

MarkP · November 15, 2010, 12:05pm

Yep, that’s what I feared. The big problem is that I expect them to want data at a daily level. That said, I can use the day offset approach for that so that’s still not too bad. You wouldn’t think that it’s beyond the capabilities of Webi though, would you?

system · November 15, 2010, 12:30pm

Hi Mark,
I think is possible to do that in a single cross tab…

Give me some time, let me try and attach s screen shot.

sati_sunil (BOB member since 2010-09-16)

system · November 15, 2010, 2:25pm

Hi Mark,

there is a way to do this.

You have to create a variables in the universe level.

Var1: SUM(case when date = jan then measure )

this might give you some idea. Here you need to create a 12 variables from jan - dec. Though this is a bit lengthy you can do it in cross tab itself.
get the measure values when the date is jan or what ever.

sandeepmunagala (BOB member since 2010-09-07)

MarkP · November 15, 2010, 2:27pm

That’s pretty much the same as margarita suggested, just moving the ugliness to the universe and confusing users. Thanks anyway.

system · November 15, 2010, 2:32pm

Hey i am sorry i overlooked it. It looks ugly but it is useful in some cases. I am using the same kind in one of my report.

sandeepmunagala (BOB member since 2010-09-07)

MarkP · November 15, 2010, 2:49pm

Don’t worry. Like I said, I can see it going down to day level. Thanks for looking at it anyway.

system · November 15, 2010, 2:53pm

Hi Mark,
I created a report based on some sample data where i am showing exactly what you want…

I feel its more about how your data is organised, if your data have

Number of Calls Made
Number of Calls Answered
as rows in a column, then it should be easy to implement.

sati_sunil (BOB member since 2010-09-16)

MarkP · November 15, 2010, 2:55pm

Why is the data duplicated?

What type of table is that - crosstab or vertical?

system · November 15, 2010, 3:02pm

Why is the data duplicated? - Its a sample data, created in xls using Copy/paste

What type of table is that - crosstab or vertical? - Its a crosstab.

Just changed the image, now showing one measure instead of two…

sati_sunil (BOB member since 2010-09-16)

system · November 17, 2010, 12:42am

Hi,

I have a similar issue, but I want to hide one of the months if the column is blank. Any ideas??? the crosstab property to hide a column when empty does not seem to work.

zephyrous (BOB member since 2007-01-12)

system · October 12, 2012, 2:58pm

Were you able to design this cross tab? I am trying to design exactly same report.

Geekay_bocog (BOB member since 2006-12-08)

system · November 13, 2012, 8:07am

Sorry for the bump.

We have similar requirement for the XIR3 SP3 report format. One column is Price measure based on Product Type (P1, P2, P3) and the other column is quantity measure based on UOM (U1, U2, U3). For each product, user need to show all the price first and then UOM next.


----------| -------P-----||--------U-------|
product --| P1 | P2 | P3 || U1 | U2 | U3
-----------------------------------------------
Product A | 10 | 0 | 20 -|| 5 -| 0 -| 10
Product B | 0 | 10 | 20 -|| 0 -| 5 -| 10

However, it is found that there is no property setting in Crosstab for that format.

In the past, I used two tables to handle in XIR2. But I hope that I can do it via simpler and clean way in XIR3.

summoner (BOB member since 2008-12-18)

system · November 13, 2012, 8:50am

You could just stack them:

=[Measure]+char(13)+[Measure 2]

HTH

NMG

mcnelson (BOB member since 2008-10-09)

system · July 2, 2014, 4:05pm

Anyone found solution (in single crosstab) for this in BI4? I have the same requirement and I am currently using 2 tables aligned next to each other.

Thanks.

FunwithBO (BOB member since 2011-05-09)

system · February 18, 2016, 1:51pm

Hi,
When I arrive at this problem, this has been discussed years back!
Anybody did achieved it with single cross tab? Please reply.

Thanks,
Murugan S

MuruganS (BOB member since 2009-12-09)

MarkP · February 18, 2016, 2:29pm

Can you give us an example of what you’re trying to achieve?

For example, months across the top, products down the side, sales cost and sales value as the measure in the intersect?

system · February 19, 2016, 2:34pm

I have the below objects in select:
Country|Division |Region | Bill no. | Bill amount | Bill date

I need the cross tab like below:

                         |Number of Bills                            |Total billed amount                        | 
                         | Jan | Feb | Mar | Apr | May | ..... | Dec | Jan | Feb | Mar | Apr | May | ..... | Dec | 
Country|Division |Region | 123 | xxx | ... and so on                 | 456 | yyy | ... and son on                | 
Country|Division |Region | 123 | xxx | ... and so on                 | 456 | yyy | ... and son on                | 


What I am getting is like this:


                         | Jan | Jan | Feb | Feb |.................. | Dec |  Dec |
Country|Division |Region | 123 | 456 | xxx | xxx | ... and so on     |     |      | 
Country|Division |Region | 123 | 456 | xxx | xxx | ... and so on     |     |      |

MuruganS (BOB member since 2009-12-09)

system · February 24, 2016, 5:29am

We have a requirement like this as well. We need to move BEx queries into a WEBI report and copy the layout.

There are only two dimension in the BEx: a range of months that’s determined at run time, and one a list of regions. Data is presented in a crosstab with the Region as the vertical axis. We need to display three kinds of measures.

When I lay down the report, the required behavior is as such:



         | Jan | Feb | Jan | Feb | Jan | Feb 
Region 1 | Ms1 | Ms1 | Ms2 | Ms2 | Ms3 | Ms3 
Region 2 | Ms1 | Ms1 | Ms2 | Ms2 | Ms3 | Ms3

The Jan-Feb is the month range and in this example and Ms1-3 represents the three kinds of measures that I need to show per region per month. However, the following happens in WEBI:



         | Jan | Jan | Jan | Feb | Feb | Feb
Region 1 | Ms1 | Ms2 | Ms3 | Ms1 | Ms2 | Ms3 
Region 2 | Ms1 | Ms2 | Ms3 | Ms1 | Ms2 | Ms3

As you can see, it will just get the values of the three measures and arrange them for that month instead of being able to directly compare them to each other (that is, immediately be able to see the Ms1 difference between Jan and Feb).

What I did is use three cross tabs, aligned next to each other. I tried splitting the month dimension to try and get each individual month, but I will then face an issue due to the user potentially filtering for more than two months. I need to be able to do this in one cross tab so that if the user adds another dimension in the future, he can easily put said dimension in the report.

EDIT: We’re using WEBI 4.1 SP7.

hueysantiago (BOB member since 2015-08-03)