Have you searched this forum? I’m sure this has come up before, although not for a while. I think there are solutions within xcelsius, though I can’t remember what they are. As you’ve discovered - you can’t refresh a pivot once it’s embedded into a SWF file.
This is why I don’t use qaaws - I use raw SQL and code my crosstab-effect at database level.
Can you please let me know how do you achieve this at database level…?
Currently, am working on BO4.0 and dashboard designer 4.0. Due to some security issue, we are unable to connect to universe directly from the dashboard designer…hence we are using Qaaws…
please suggest if you have any better solution instead of using qaaws…thanks
The only way I know how to do it at a database level is through a series of CASE statements. Which only works if the column headers are going to be static. In your case as stated, this might work.
SELECT Status,
SUM(CASE WHEN Rating = "high" THEN Count ELSE 0 END) AS High,
SUM(CASE WHEN Rating = "medium" THEN Count ELSE 0 END) AS Medium,
SUM(CASE WHEN Rating = "low" THEN Count ELSE 0 END) AS Low
FROM Table
GROUP BY Status
This gives a crude crosstab. But, obviously, it requires the list of columns to be hard-coded. If someone introduces a Rating of “none” then you would have to modify your query to account for it.
There is a way to create dynamic crosstabs using PL/SQL that I saw a few years back. It involves multiple queries and cursors. I unfortunately have long ago lost my link to it, but Google would likely turn something up. As a note, though, performance with it was never good.
Basically I use aspx pages to host raw SQL which queries the database directly (using case statements much like Lugh suggests to create a crosstab). The database then returns xml to the dashboard. I run fast queries in real-time - passing user-selected parameters back to the query to bring back subsets of data. For longer more complex queries, I run the files overnight and stores the xml on the web server to be used as required.
(BOB member since 2005-03-01)
(BOB member since 2010-09-28)