Suppress null values but not zeros in Query Designer

[Former Member]

Hello everyone,

What do I have to do if in the BEx Query Designer if I want to suppress rows with only Null Values. The problem is that I have only found an option that suppresses Zero values in columns or rows (which also suppresses null values), but I want to display Zero values, and not Null values.

I hope my question is clear. Thank you!

Gerardo Figueroa

[Former Member · Accepted solution]

Hi,

u can supress Null values in BeX Analyzer 7.0:

1. Just open query properties

2. Goto Tab "Display options"

3. Choose "none" for Displaying zero values

Cheers

Tobias

[salnuaimi · Accepted solution]

This helped in my case! thanks Tobias!

[Former Member]

Thank you all for your replies!

However, to solve the problem I had to add a formula in the Columns with the Delta function, which returns 1 if the value of a key figure is = 0, and returns 0 if otherwise. Then I used the Suppress Zero Values option for rows in the query properties, thus suppressing the rows which had all values 0 or Null, but since I had the column with the Formula, rows with Zeros had a 1 in the Formula column, and the row wasn't suppressed. Rows with only Null values had a 0 in the Formula column, and therefore the row was suppressed.

[Former Member]

Hi Gerardo,

I am facing same kind of problem. As you have written I used delta function but for Null Values again it is producing null values. How you achieved 0 for a null value?

Regards:

Jitendra

[Former Member]

Hi,

As per my understanding u dont wanna display Null values, which are not linked so i assume them to be #values.

U can do this. Select >Rt Click on the characterisitcs which has Null values>Click on Select Filter value.>Drag Drop the Null # value from Left 2 rt then Click on Red Button to Exclude it> then click on Ok,.

It vll nt display Null values,,But vll show the 0values,, and the values that are posted.

Thanks

Assign points if helped.

[Former Member]

Hi Gerardo,

Excuse me if you already know this, in your row of your query designer, highlight the characteristics, then select the "Advanced" tab from its properties menu. See if the setting for "Access Type for Result Values" option is set to "Posted Values" setting and "Filter Value Selection During Query Execution" is set to "Only Values in InfoProvider". These setting should only show characteristics with corresponding transaction entries, meaning new master data created but no transactions are associated with this new master data.

Let me know if this is the case for you.

cheers.