Question Re: suppressing data in Technology Q&A

suppressing data

Former Member — Thu, 17 Jul 2014 10:54:52 GMT

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

Re: suppressing data

abhilash_kumar — Thu, 17 Jul 2014 11:07:00 GMT

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

Re: suppressing data

Former Member — Thu, 17 Jul 2014 11:54:40 GMT

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

Re: suppressing data

Former Member — Thu, 17 Jul 2014 13:20:53 GMT

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.

Re: suppressing data

abhilash_kumar — Thu, 17 Jul 2014 13:29:47 GMT

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

Re: suppressing data

Former Member — Thu, 17 Jul 2014 13:39:23 GMT

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

Re: suppressing data

abhilash_kumar — Thu, 17 Jul 2014 13:44:34 GMT

Yes, that's what you need to do.

-Abhilash

Re: suppressing data

Former Member — Thu, 17 Jul 2014 14:25:29 GMT

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

Re: suppressing data

abhilash_kumar — Thu, 17 Jul 2014 14:53:31 GMT

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

Re: suppressing data

Former Member — Thu, 17 Jul 2014 15:07:23 GMT

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

Re: suppressing data

abhilash_kumar — Thu, 17 Jul 2014 15:36:55 GMT

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