Suppress rows with zero value

Sometimes, when running a query, we will get measures with a value of zero (0), and we may not want those rows displayed. This is a little trick to hide those rows with zero values. For this demo, I used the eFashion universe. I created the table below. Notice that the “Day wear” category has zero Sales revenue.

The first step is to create a variable that will convert the zero values to NULL values. For this, I created a variable called Revenue, with the following formula:

=If([Sales revenue]<>0;[Sales revenue])

Notice that there is no “Else” value for this formula. The “Else” argument of the If function is optional, and, if left out, results in a NULL value being returned if the first argument returns false. In this case, if Sales revenue is zero, the variable will return NULL.

Next, drag the new variable from the Report Manager into the table, replacing the Sales Revenue object.

At this point, the zero values are replaced with NULL values:

Next, select the table by clicking on the outside edge of the table, where you see the four-pointed arrow.

Finally, go to the Properties tab of the Report Manager, and, in the Display section, uncheck “Show rows with empty measure values”.

The rows with NULL values are now hidden:

Advertisements

15 Responses to Suppress rows with zero value

  1. VP says:

    Nice,but why you just can’t create the restriction in Query panel, something like sales revenue!=0, result must be the same ?

  2. bose says:

    Hi Michael

    Bunch of Thanks.

    bose

  3. What if instead of working with #’s you are working with Dates?

    I’m having trouble including multiple dates on one row… I thought I might try this solution.

    thanks

  4. Peter says:

    Hi Michael,
    Thanks for this post (and others as well). I tried the same for eFashion universe where, I ran a report for Year (2005 only)-Category-Sales Revenue-Promotion Cost. I right clicked on the table (where we can see four pointed arrows) –> Format Table –> and cleared ‘Show empty rows with measures’. I did not get the result as expected.
    I am using WebI from BI Launch pad (4.0)
    Any ideas please

  5. RN says:

    Hello Michael,

    Will this work on a Stacked chart too. reason I ask is I tried to use a variable/report filter to filter out zero’s in a report but my stacked chart values dives down to negative values ??

    Also I have another question related to formating- Can we conditionally format a number in chart say it should display ###0.00% for values 1. I know in table its easy but in a chart can this be done?

    Thanks

  6. Mayur says:

    Hello Michael,

    i am using SAP BO 4.1.
    After applying report level filter to show only required rows, i am able to get the desired results but when i am using set as section, i am still getting black spaces.
    can we suppress these blank spaces on conditional basis.

  7. Mayur says:

    @ the section level as I am hiding unwanted rows…

  8. Mayur says:

    is there any option so I can send your screenshot of my report.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: