BusinessObjects Board

How to display even blank data

building reports desktopintelligence
#1

Hi All,

I have a strange issue, I have two dimensions and one measure, and I want all dimension values to be displayed even though if there is no measure associated with it.

Example:

Current Report

Uk (dimention1)
Reading (dimention2)

Requirement

Uk (dimention1)
Reading (dimention2)
London (dimention2)

Many thanks,
Uma

UmaM (BOB member since 2004-11-04)

Need Help on Null Values.....
Substitute NULL with zero
Problem: Missing data in crosstab after filtering
#2

Use two data providers. The first brings back the “real” data (measures from the fact table). The second data provider queries just the dimension table, and brings back the “master list” of dimension values. In the report, link the two data providers together (simulates a “full outer join”), and the values that are “missing” from the first data provider will display with null as the measure.

Dwayne Hoffpauir :us: (BOB member since 2002-09-19)

#3

As an alternative ask your universe designer to implement an outer join or have the data warehouse team introduce dummy rows into the fact table (be careful when it comes to calculating averages though).

Andreas :de: (BOB member since 2002-06-20)

#4

Hello Dwayne,

Thanks for the useful tip, It worked well for standard report, i.e, dimention2 (query2), measure (query1), dimention1 (query1)

But, null’s are not being displayed for crosstab or section’s format, and the requirement is for sections as below:

dimention2(query2)||

dimention1(query1)||measure(query1)

Please can you advice me in this regard.

Many Thanks,
Uma

UmaM (BOB member since 2004-11-04)

#5

Andreas :de: (BOB member since 2002-06-20)

how to show default column
Need to show empty column in Cross Tab report
How to show empty row without data in the database
Report issue
Difficulty Faced in Cross Tab report to show all Months
#6

Thanks Andreas for the replay and link info with very useful information. I went through all the information and worked on sql query (option A) options and inclusion of data providers (option D), they seem to work fine with standard/tabular reports and for crosstab reports but they do not work for Master/Detail sections.

I tried break’s instead of Master/Detail section, but it is not showing the blank values for individual breaks (dimension data) instead it is displaying only the data that does not fall under any of the dimension values, while MY REQUIREMENT IS TO DISPLAY ALL DATA WHETHER WITH DATA OR AS NULL FOR EACH SECTION. Is there any way to work it out for Master/Detail sections with complete (with data and without data should come as null) data for EACH SECTION?

I would like to explore it without making any changes to the Universe and tables level. If not possible at report level only then, my final option would be Universe level.

Thanks in advance,
Uma

UmaM (BOB member since 2004-11-04)

#7

Any input’s from anyone please!

UmaM (BOB member since 2004-11-04)

#8

Hi Uma,

Option D(from Andreas) will not work, if the report is sectioned.

Regards,
Biswajit.

Biswajit Sarkar :india: (BOB member since 2003-11-25)