How to display null values in a table?

[Former Member]

Hello guys,

I have a webi report with months in the columns and some measures in the nrows. This report gets its data from a BEx query, that have two structures: in columns a structure with months and in rows a structure with ratios. The BEx query ask for a month and fills columns from january to the month given. The other columns remain empty.

The Universe generated from the BEx query has a unique dimension wich represents the structure for columns (months) and the ratios in the structure of rows.

In the webi report I need the same functionality, say all 12 months being displayed, but the webi report only shows the columns (months) with data; the months without data are not displayed, but I need to show all 12 months, no mattetr what month supply the user. How I can get this functionality?

Thank you

[Former Member · Accepted solution]

Check Table propertis of BO WebI,

1. Show empty values is Checked.

If it not works...

2. In BO Query:

Create one more query with same universe & Drag only

Month Object

Measure Objects( 1st try without these)

In report, Use this Month object, from second query. (Not query1.Month)

If not add Measure objects also to query.

For me adding Month, solved the issue. It should work.

I'm Back

[Former Member]

David Velasquez,

Did u try my solution... I do have same requirement & resolved it by using that scenario.

Hope it should work.

I'm Back

[Former Member]

Thank you, your scenario worked with the second query with the months only. The table now show months with null values. But a problem remains: months are shown in alphabetical order, not in the natural order of months; I tried the "customer order" on the months, but this option is gray, unavailable. Do you know how can I order the months?

Thank you

[Former Member]

David Velasquez

Only option we have Custom Order for Months. No other way.

What you tried was exact.

Can you check your Month object type?? Dimension or other...

I'm Back

[Former Member]

Well what i do always is to create aquery that brings only months a query exclusive for time then in that query you are sure that the 12 months are coming and then i create a merge dimension and use that in the table.

It always works for me ...

Regards

[former_member142702]

Which version of BO do you use?

Regards,

Stratos

[Former Member]

Hi Efstratios,

the version we are using is XI 3.1 SP2

[former_member142702]

SP3 introduced a new option in the BW MDX driver that allows you to fetch also empty data from SAP BW (ie. data for which no values have been commited in the cube).

You may want to consider to upgrade.

Regards,

Stratos

[chinmay_godbole]

Hi Stratos,

We have a deployment where BOE XI R3.1 SP3 is installed. We are facing the same issue where 'null values' from BI are not displayed in the BO WebI report. We can use the workaround successfully of second query to get the null values, however; I would like to know which setting is provided in SP3 to get this issue resolved. Is there any manual setting where we can set the value of 'Show Empty Data' to 'Yes' / 'True'? Is there any registry entry to be added for the same?

Please find the extract (on page # 27) that I am referring from 'Whats New in SAP BO XI3.1 SP3', pasted below:

-

---

SAP Integration Kits

Option to distinguish between empty and non empty rows for SAP NetWeaver BW Data

When you want your report to show all SAP NetWeaver BW data records,

including the empty ones, you can use the new u201CShow Empty Datau201D option.

This option has been added to the SAP NetWeaver BW MDX driver to allow

empty data records to be displayed.

The u201CShow Empty Datau201D option can be selected when you create the

connecetion (on SAP System Login).

When the u201CShow Empty Datau201D option is selected, empty data rows will be

displayed in the report as valid data.

Note:

Setting u201CShow Empty datau201D to true will downgrade the data retrieval

performance, so the default value for the option is False. This also applies

when opening a report created with previous versions of this product.

-

---

I did not find any option to select 'Show Empty Data' while creating a universe connection or while installing SAP IK.

I am not sure where I can select 'The u201CShow Empty Datau201D option while creating the connecetion and what is the meaning of

'(on SAP System Login).'

Any help / possible directions to solve this mystery (for me) is highly appreciated

Regards,

Chinmay

Edited by: CSG on Aug 8, 2011 12:40 PM