I have a great mockup for a small Xcelsius widget to be used in a dashboard, which shows payroll data for 4 timeframes. It has one radio button option to toggle between a % of payroll vs Actual dollars. So far so good…
But they want to be able to filter it from Total Company… By Department, or By Location.
So we end up with 2 dropdowns… one for Locations and one for Departments. Along with an “All” value for both.
That leaves 4 combinations:
All Locations + All Departments
All Locations + Selected Department
Selected Location + All Departments
Selected Location + Selected Department.
I have everything working fine with my sample Excel data which isn’t hooked up to QAAWS just yet… but the rendering time is HORRIBLE. Mostly because I have to use some rather complex Vlooups, Indexs and SumIF statements to slice the data for filtering purposes.
My question is i’m thinking of perhaps creating 4 seperate WebService queries… and have it trigger the appropriate query when a dropdown item is selected… but I’m worried about the actual Query time and ‘connection’ time.
Our database is not up and running just yet so I can’t actually time it…
Does anyone have experience using Vlookups on column keys to slice data vs seperate queries? My only alternative if neither of these work is to seperate the filters into separate xcelsius widgets and force them to choose.
EDIT: Opps almost forgot the dropdowns must be Dynamic, as each client viewing the dashboard may have a different set of Locations and Departments available to them. (Security is controled in universe). Thus the locations should only show locations in the dataset retrieved etc…
as far as my experience with xcelsius, having separate queries are faster than dumping huge amount of data in your excel and use vlookup or sumif to slice it.
and with the dynamic dropdowns, i think you can achieve it. just need to trigger the refresh time of that dropdown list when a certain value is changed or selected.
I do not have access directly, but tables can be created. However I don’t think that would really help, the queries themselves aren’t that long, a few seconds at most, so doing queries for distinct values for the dropdown are already possible.
I am more concerned about the overhead for xcelsius to open the connection, query the data, close the connection for multiple queries vs the rendering time if it was done in one query.
I guess I just need to wait this one out until our database is available to me in a few weeks then I can test both methods.
Yes and that is what I’m using at the moment, however Xcelsius does not do well with SumIF and vlookup (though index seems to work fine strangely).
At the moment a given query might have 5-15locations by about 15 or so depts per ‘batch’, and the request is to have at least 6mo to a years worth of data in the dashboard accessable via a slider or some other mechanism.
So the number of unique lowest level rows would be about 900 to over 5000rows per output. Way too many for Xcelisus to manage with SumIf statements.
I figure I can cut it down to about 24-50 rows if I re-query the database whenever locations or depts are altered (showing all locations/all depts upon initial load).
I had another thought today about using a union query where I might be able to utilize Index instead as that seems to work better, but I’m still concerend about the huge amount of data. I’m much rather build a few smaller widgets but they want it all in one
(BOB member since 2008-06-05)
(BOB member since 2008-10-28)
(BOB member since 2005-01-28)