suppressing data

[Former Member]

Hi All,

I have a report and data like this

Brand : a

Category:C1

time                  sales

6                       455

7                       434

8                       767

Brand : a

Category:C2

time                  sales

6                      0

7                      0

8                      0

Brand : b

Category:C1

time                  sales

6                       45

7                       43

8                       76

Like sales I have many numeric figues. I want to suppress categories data if all measure values are 0 only.If any one is non 0 I have to show that category values with all times.

As per attachment I want to hide these 2 categories

[Former Member]

Hi Divya,

You can even directly filter these records through record selection by giving following formula :

{firld1}+{firld2}+{firld3}+{firld4} <> 0

This will sum up your fields and check if it is zero or not.  If zero it will not display else will display that record.

-Sastry

[Former Member]

Hi Abhilash and Sastry,

Please suggest me.

I applied the filter as per my requirement.

My doubt is will this filter apply before it will get all the records or it will get all the records first and then filter ?.

please suggest.

[abhilash_kumar]

Hi Divya,

This filter will Not be passed back to the database! Meaning, it will get all the records first and then filter locally.

If you want the filter to be passed back to the database, then you have two choices:

1) If you're reporting against a Table/View, create a SQL Expression Field with this logic:

"Field1" + "Field2" + "Field3"

Then, add a Record Selection Formula with this code:

{%SQL Expression} <> 0

2) If you're reporting against a Command Object or Stored Proc, include this logic in the where clause of the query.

-Abhilash

[Former Member]

Hi Abhilash,

i am using command objects.

I have to use the below condition  in my query ? or something different.

please suggest

{numeric field1} = 0 AND {numeric field2} = 0 AND {numeric field3} = 0 AND {numeric field4} = 0

[abhilash_kumar]

Yes, that's what you need to do.

-Abhilash

[Former Member]

Hi Abhilash,

I think I have to use or condition in where clause of above code.

Please suggest.

I did like below but I am missing time records where I have zeros for sales figures for  every category.

sale <> 0 OR sale_stretch <> 0

[abhilash_kumar]

I'm sorry, I overlooked the requirement. This will suppress any 'row' where all the numeric fields are zero. I guess what you really want to do is suppress 'every group' where all rows have zeroes.

Please use the logic I suggested in the first reply.

-Abhilash

[Former Member]

Hi Abhilash,

I am trying to do this filter in query level.

And the above screenshot is the report data after I applied below condition in where clause

sale <> 0 OR sale_stretch <> 0

please suggest

[abhilash_kumar]

If you do this filter at the query level, it will take out rows where all numeric fields on it are zero.

If you're trying to do this at the query level, then you would also need to group at the query level and get rid of 'groups' where the summary of all numeric fields in a group is zero.

-Abhilash

[abhilash_kumar]

Hi Divya,

I hope you have a group on the 'Category' field.

Try this please:

1) Create a formula called (@zeroes) with this code:

If {numeric field1} = 0 AND {numeric field2} = 0 AND {numeric field3} = 0 AND {numeric field4} = 0 then 0 else 1

2) Go to the Group Selection Formula (Report > Selection Formulas > Group) and use this code:

Sum({@Zeroes}, {Category}) > 0

-Abhilash