Include data for all months in a crosstab with section

building reports desktopintelligence
1

How can I include data for all months in a crosstab, even if a particular month has no data and the report is sectioned?

klaunert (BOB member since 2009-03-19)

2

You could have done a search and avoided this post

Create a separate master query to get all the months from calendar table.
Merge the queries and display months column on the crosstab block from the master query

.

haider :es: (BOB member since 2005-07-18)

3

I habe already tried this solution but it does not work for me. In my opinion this solution only works in reports without sections.

I have 2 DP.

In DP1 is only one field “Months”. In the 2 DP are measures and other dimensions. By using a crosstab within a section only those months are shown for which measures exists. I think this is the case because of the global filter which filters a dimension from DP 2.

Thanks for your help!

klaunert (BOB member since 2009-03-19)

4

Ok, do this.
Bring the dimension object (on which section has been applied) also into the master query
Then all months should be shown

.

haider :es: (BOB member since 2005-07-18)

5

Thanks for your reply. Unfortunetaly it is still not working for me.

But I have forgotten something to tell.

In addition to the Global filter, there is another filter on a dimension from DP2.

Only those months are shown where measurse exists that belongs to both the global filter and the local filter. I want to show all “Months” even when there are no measures for the local filter! Sorry for that!

klaunert (BOB member since 2009-03-19)

6

To be more precisely:

DP 1: dim1, dim2 (with 7 values for dim1)

DP 2: dim1, dim3, dim4, …,measure1, measure2, measure3

I have linked DP1 to DP2 on dim1.

The report has a section on dim3.

There is a local filter on the crosstab for the dim4
crosstab:

             dim4

dim1 measure1

I want to show all values from DP1 for the dim1 even when there are only values for measure1 that are filtered out by dim4.

Any ideas? Thanks in advance!

klaunert (BOB member since 2009-03-19)

7

Just hit this problem in Deski 3.1
The filter is the problem. The crosstab displays all months in the year until I apply a filter.

I’ve tried wrapping the period in NoFilter() but that doesn’t work - bit of a long shot, but you never know until you try it!

Has anyone managed to get round it?

Addendum:

It would appear that this behaviour is consistent with Webi, given that I’ve successfully replicated it. :wah: