Anyone had any issues basing a graph over a pivot table, where the pivot table can have a variable number of rows and columns, depending on user selection of the x/y values to display?
It looks straightforward enough…
Debbie
ETA … or maybe not. How on earth do I force the pivot to refresh when the underlying data changes?
ETA again … Googling around - doesn’t look like I can. Trying to figure out how I can reproduce a crosstab without doing it at database level (which I usually do).
No. It doesn’t work. As I read up online, while you can use a pivot in the excel part of xcelsius, once it’s converted to a SWF, it can’t then handle a data refresh. So I can refresh my underlying data but there is no way to refresh the pivot table.
I normally create a dummy crosstab with my SQL code - since I’m using a 6 x 6 user-selected matrix, I wanted to avoide hundreds of lines of code. I’m working on it…
XML. I could do it easily if I hardcoded everything, but I was trying to make it truly dynamic by letting the user select any two dimensions to graph and write one bit of code to handle all of it.
Finally did it using oracle dense_rank. Pulled the raw data summarised by 6 dimensions. Used this as an inline view and extracted and ranked by x and y, where x and y are input at runtime by the user.
Yep. I write raw SQL to access my databases and host it in an aspx script - it returns XML. Either real-time for quick queries or those that require user-interaction, or overnight on a batch file to an XML file for slower queries.
No business objects, no messing around. I can write a script that gives me precisely the data I want in the exact format.
There are sample scripts in this forum somewhere that I’ve posted in the past.
(BOB member since 2005-03-01)