Crosstab: Showing full year, even no facts in database

[BI-forme]

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

[blom0344]

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