suppressing report lines with all zeros

building reports desktopintelligence
1

I have a report that displays products and various quantity and revenue fields for those products. Example:

product YTD$ YTD# CM$ CM#
prod1 10 5 2 1
prod2 0 0 0 0
prod3 20 10 4 2

You get the idea. Note that prod2 has all zeros. Is there an easy way to suppress lines that have all zeros? I do not want to do the exclusion in the data provider selection if I can help it because our quantity and revenue fields are already functions and it would result in a query from hell.

I tried filters and they work, a little. Our report is also breaking on customer. If I filter on product, filtering out those with all zeros, it only works when ALL occurrences of that product (for any customer that may have purchased it) have all zero values. If any customer has any non-zero value for that product, then that product still shows for all customers (including those with all zeros).

I seem to remember seeing something somewhere about suppressing rows or values if zero or something like that, but I cannot find it. Maybe I am hallucinating. Does anyone have any ideas??? Thanks much.

Dick McConnell
Polymerland IMO
ph: 8354-4788 or (704) 948-4788
fax: 8354-4915 or (704) 948-4915
e-mail: richard.mcconnell@polymerland.com profs: P024284

Listserv Archives (BOB member since 2002-06-25)

2

Dick wrote:

I have a report that displays products and various quantity and revenue fields for those products. Example:
product YTD$ YTD# CM$ CM#
prod1 10 5 2 1
prod2 0 0 0 0
prod3 20 10 4 2
You get the idea. Note that prod2 has all zeros. Is there an easy way to
suppress lines that have all zeros? I do not want to do the exclusion in the
data provider selection if I can help it because our quantity and revenue fields are already functions and it would result in a query from hell.
I tried filters and they work, a little. Our report is also breaking on customer. If I filter on product, filtering out those with all zeros, it only
works when ALL occurrences of that product (for any customer that may have purchased it) have all zero values. If any customer has any non-zero value for that product, then that product still shows for all customers (including
those with all zeros).
I seem to remember seeing something somewhere about suppressing rows or values if zero or something like that, but I cannot find it. Maybe I am hallucinating. Does anyone have any ideas??? Thanks much.

Listserv Archives (BOB member since 2002-06-25)

3

Glenn,

Thanks for the response, but that is exactly what I did. However, as I tried to explain in my original note, since we are also breaking on customer (at a higher level than product), if I filter on product (exactly as you suggested below), filtering out those with all zeros, it only works when ALL occurrences of that product (for any customer that may have purchased it) have all zero values. If any customer has any non-zero value for that product, then that product still shows for all customers

(including those with all zeros).

Glenn wrote:

Filters should get you what you want. Create a filter on Product and edit it. The formula should look something like this:

=(YTD$ <>0) OR (YTD# <> 0) (CM$ <> 0) OR (CM# <> 0)

This will then only filter out records that have zeroes for all the values.

Listserv Archives (BOB member since 2002-06-25)

4

In a message dated 98-08-06 11:45:57 EDT, you write:

I have a report that displays products and various quantity and revenue fields for those products. Example:

product YTD$ YTD# CM$ CM#
prod1 10 5 2 1
prod2 0 0 0 0
prod3 20 10 4 2

You get the idea. Note that prod2 has all zeros. Is there an easy way to
suppress lines that have all zeros? I do not want to do the exclusion in the
data provider selection if I can help it because our quantity and revenue fields are already functions and it would result in a query from hell.

Richard:

Here is an idea… it may or may not be what you require, but it could get some ideas of your own started. First, create a new variable called “All Zero” with the following formula:

=Sign(<CM#>+<CM$>+<YTD#>+<YTD$>)

The Sign() function takes a number and returns the sign of the value. For example:

Sign (0) = 0
Sign (123,456) = 1
Sign (-445) = -1
Sign (null) = null

The fourth item is rarely used, but is part of the “official” definition of how sign() works. So what is the formula above doing? Simple. Add the four values together. If any one of them is non-zero, then the entire row will be non-zero and you want to see it on the report. All non-zero values become “1” when processed by the sign() function. So, after building the variable you can then build a filter through the menu.

Select Format, then Filter.
Click Add, and select the All Zero object Select the “1” value.

This means that only rows with at least one non-zero value will be displayed on the report. Rows with all zero values will have a collective sign() of 0, and therefore would not pass the filter. I created a personal data file (Excel) using the data you posted and verified this result; it works fine.

There is a loophole in this formula however. If Current Month Pounds = 1,000 but Year To Date Pounds = -1,000 then the row should still show up… using my formula the total nets to zero and therefore would be filtered out. Given your data, I don’t think this is likely. Same issue for the dollar values. To get around this you could use the more complicated:

= If <CM#><>0 Or <CM$><>0 Or <YTD#><>0 Or <YTD$><>0 Then 1 Else 0

Either way, you want to end up with a binary flag (1 or 0) that you can detect and use in a filter.

Hope this helps!

Regards,
Dave Rathbun
Integra Solutions
www.islink.com See you in Orlando in … a few weeks!

Listserv Archives (BOB member since 2002-06-25)

5

Richard,

I have an ER logged for this … number 3223. I guess it’s basically about the same thing you are looking at … What I wanted to do was to apply a filter based on the locally aggregated value, but BO applies filters to the raw data.

One can reproduce this with Island Resorts by querying for Country, Resort and Revenue, and then removing the resort in slice-and-dice panel. Now, if you try to apply a filter on revenue, the list of values still shows the revenue values “in Country, Resort”, instead of “in Country”.

-Harri

Sent: 6. elokuuta 1998 19:23

Glenn,

Thanks for the response, but that is exactly what I did. However,
as I tried to explain in my original note, since we are also breaking on customer (at a higher level than product), if I filter on product (exactly as
you suggested below), filtering out those with all zeros, it only works when
ALL occurrences of that product (for any customer that may have purchased it)
have all zero values. If any customer has any non-zero value for that product, then that product still shows for all customers

(including those with all zeros).

Listserv Archives (BOB member since 2002-06-25)

6

I am facing the same problem now. I am trying to filter locally aggregated value, but not able to do it. Can anyone can help me out from this? :hb:

I am using BO 6.1b.

Peter_sandy (BOB member since 2005-08-11)