I am trying to build dashboard, my objects are date, country, state, town, Sales. Dates (14 days), Country(7 countries), States (36 states) Towns (more than 100).
When I build query component its gives me out put in Columns. But the report needs to be combination of rows and columns…
Town1 Town2 Town3
Date Country State X X X
I tried to build query and bring all the data into excel and building cross tab its huge database and report may not execute the dashbaord as its beyond 512 rows…14736 3528 rows.
Correct me if I’m wrong, but a given town should only exist in one state, and one state in one country. Right? If so, I think that your data structure is odd. You are going to have a LOT of blank fields.
It makes far more sense to put the State and Country on the same side as Town. Whether that is column header or row header is largely irrelevant. That will give you the same data, but in a table that is only Date X Towns large.
It may be that your needs won’t support that. If that’s the case, you may just have to deal with the huge table. Given the number of blank fields, it probably won’t overload the dashboard.
Is this a different requirement from your first post? Because this is a pretty straightforward crosstab table.
Unless the bit you’re getting stuck on is “display the top three materials and an OTHER column”. Which is a bit of a tricky thing, as ranking in WebI doesn’t support “other” for reasons that baffle me.
However, if you google something like “webi rank other” you can find a number of blog posts and forum topics with some rather ingenious solutions.
(BOB member since 2005-01-28)