crosstab format in Qaaws?

system · June 9, 2011, 10:08am

Hi All

I want create one drill down report in Xcelsius using with Qaaws

we have state, monthly counting polls(i.e count ()), Date objects for last 6 month.

in Webi we can create crosstab, but how we can implement in Qaaws?

Could you please help on this.

kalabo (BOB member since 2008-05-14)

system · June 9, 2011, 10:14am

You have to write the query so it gives the appearance of a crosstab.

So instead of:

select month,area,sum(sales)
from table
group by month,area 
order month

you’d write

select month,
sum(case when area = 'A' then sales end) as sales_a
sum(case when area = 'B' then sales end) as sales_b
sum(case when area = 'C' then sales end) as sales_c
group by month
order month

Debbie

Debbie (BOB member since 2005-03-01)

system · June 9, 2011, 10:14am

Hello,

We have a limitation with QaaWS and we cannot create cross tabs with the same. You might look at adding formulas and rearranging the data after its pushed to Excel.

Regards,
Ameet

joeameet (BOB member since 2011-06-09)

system · June 9, 2011, 10:22am

You could also look at using Live Office, if you cannot implement Debbies solution.

Mak 1 (BOB member since 2005-01-06)

system · June 9, 2011, 11:47am

Thanks for Quick reply,

you want say to create Derived table?..

thanks

kalabo (BOB member since 2008-05-14)

system · June 9, 2011, 1:12pm

Through QAWS ,bring all the fields and then build the logic in xcelsius to create a cross tab by using Match() and Index function or you can use vlookup().

rizwan syed (BOB member since 2010-07-08)

system · June 9, 2011, 1:17pm

Possible, but, not ideal. Depending on the amount of rows performance could be poor. The other two things suggested here are better approaches.

You could create indivdual objects for this using Case.

Mak 1 (BOB member since 2005-01-06)

system · June 9, 2011, 1:23pm

If the number of rows pulling are less then, i really suggest to go with the excel functions in xcelsius or else you could go with the Mak1 by creating the derived tables in the universe level and then build the QAWS

rizwan syed (BOB member since 2010-07-08)

system · June 9, 2011, 1:27pm

The best successes with Xcesius come from having the data in the correct shape before it gets there :).

I actually suggested creating individual objects using Case. I’m nota fan of derived tables and avoid, wherever possible ;).

Mak 1 (BOB member since 2005-01-06)

system · June 9, 2011, 1:55pm

Kalabo

Do as much as you can with your data before it gets anywhere near xcelsius. You can pull in huge chunks of data and manipulate it with vlookups etc, but you really are storing up problems for yourself in terms of performance and maintenance.

As I suggested and Mak 1 elaborated, create objects in your universe to build case statements for each total you require, so that the generated query SQL looks similar to my example. Basically you need to generate SQL which will always return a fixed number of rows and columns - this is the essence of a crosstab.

debbie

Debbie (BOB member since 2005-03-01)

system · July 11, 2011, 3:00pm

Hi Kalabo,

QAAWS is very limited when compared with BIWS. Why not create your Cross Tab in WEBI and then publish the block as a BI web service. Use the QAAWS connection in Xcelisus.

Aeron (BOB member since 2011-07-09)

system · July 14, 2011, 7:08am

Thank you Aeron , I tried that option also

but while i am publishing the block getting error message like “Unknow” and not detail information on that error message.

Do you have any idea on that./

thanks

Kala

kalabo (BOB member since 2008-05-14)

system · July 14, 2011, 7:48am

Good post on BIWS:-

http://everythingxcelsius.com/xcelsius-training/xcelsius-and-business-intelligence-web-services-biws/3642

Mak 1 (BOB member since 2005-01-06)

system · July 14, 2011, 9:05am

Thanks Mark for Quick reply…

actually I followed those steps, but while I click create button then I am getting error “unknow”

Thanks

kalabo (BOB member since 2008-05-14)

system · July 14, 2011, 9:33am

Can you connect to the repository from RC? What version of Java are you using? Does QAAWs work from your PC?

Mak 1 (BOB member since 2005-01-06)

system · July 14, 2011, 10:48am

Yes Mark I am able to connect RC
Yes Qaaws working my PC
and BOXI 3.1 SP2 version using.

Thanks

kalabo (BOB member since 2008-05-14)

system · July 14, 2011, 11:17am

Mak 1 (BOB member since 2005-01-06)

system · July 14, 2011, 11:33am

Hi Mark,
Java version ‘jre1.6.0_03’
but, how I know server side Java version and one more question why we need java version here… we are using rich client right… so ?

Thanks

kalabo (BOB member since 2008-05-14)