Hi all,
Read a lot of posts, but don't read the solution yet.
(or I don't understand it :-\)
I want to create a Report Studio report (crosstab) with a "select a year user prompt.
When the user selects a year the crosstab must display 12 month in the Colums, even there is no fact data for a month.
When the user selects a year where 12 months of data is in the database, the crosstab shows fine.
But when there is missing some (month) data.....
Can I get a solution / an example report on GOSALES ?
Thx!
BI-Forme
One (1) solution involves creating the crosstab from a union:
Query 1: Fetches the actual data
Query 2: Fetches all combinations with 'dummy' zero values
Query 3: Union of 1 and 2 (1 and 2 must have the same number and type of dataitems)
The idea is that adding a zero to an actual value is always harmless, whereas if no actual exists , then the combo is added to the set.
The tricky bit is to devise Query 2, which may involve allowing a crossjoin.
To be honest, I have used this more often within special Query subjects within the Framework model where you can write the whole expression in one go