Showing years with no data

building reports webintelligence
1

Hi,
I am not sure what this is called - I am sure it has a name, but the searches I did didn’t lead me to anything… so apologies if this is mentioned elsewhere.

I am reporting by years and regions in sections, but I need to be able to show a year when there isn’t any data for it. I use =year() to disaplay the years from dates and to sort the data, but I dont’ want it showing a different number of columns for our different regions.

For Example:

Region 1 has data for 2000, 2001 and 2002
Region 2 has data for 2001 and 2002

If I section these then the number of columns along the top of a crosstab will be unequal.
I want it to show:

Region 1
2000 2001 2002
100 200 300

Region 2
2000 2001 2002
0 100 200

Is this possible?

We are using BOXI XIr2, SP 4 with Webi.

Thanks.

David.Mitchell :uk: (BOB member since 2008-04-28)

2

I think merge dimension should work…

Regards,
Sachin.

sachineb :uk: (BOB member since 2006-05-30)

3

There iss only one dimension - to explain a little more, here are the example objects:

[Region]
[Date]
[Orders]

A variable would be created, like this:

=year([Date])

This would provide the year for the top of the crosstab.

Thanks.

David.Mitchell :uk: (BOB member since 2008-04-28)

4

I think this will not be possible with a single data provider. You have to create one more Data provider just for year dim object and merge.

Regards,
Sachin.

sachineb :uk: (BOB member since 2006-05-30)

5

See this topic too. In short, forcing an outer join will help to retrieve years with no data or as suggested already, create another data provider to retrieve all the years and use this in the block.

Jansi :india: (BOB member since 2008-05-12)