HOW TO HIDE A ROW ON WEBI REPORT WITH 0 AND NULL VALUES

[Former Member]

Hi gurus,

I am doing a webi report, the report have 3 columns and in some rows the report has in column 1 NO VALUE (it means the cell is empty or null) in column 2 0 and in column 3 0 again. For me 0 or NULL is the same and I need to hide or delete these rows but I don't know how.

[erickalberto_romeroneyra · Accepted solution]

Hi Jorge

Another option is to edit the query, drag the measure to the query filter and use the "is not null" operator and "not equal" or greater than" 0 for it.

This will filter the result from nulls and zeros.

Best Regards

Erick

[Former Member · Accepted solution]

Hi Erick,

Thanks for your response, the problem is tha the filter would help if there was only one column but the row has to be deleted only if the filter is true in all the columns.

[Former Member · Accepted solution]

Hola Jorge, qué tal?

Please follow these steps:

1. Insert a new cell

2. In the new cell place the next formula (i don't know which dimensions you are using so I'll asume they are distinct measures for every row):

 =If ( Sum([Dimension1]+[Dimension 2]+[Dimension 3]) = 0 ; "Hide" ; "Show" )

3. then press Create Variable button and name it "Hide or Show"

4. Select the table you want to apply the filter. MAke sure it appears selected.

5. Press Show/Hide Filter Pane icon. It will display a pane for filters.

6. then from the Data tab drag your new variable "Hide or Show" and drop it in the filter pane area.

7. It will display a window. In the textbox "Type a value, the add it" write: Show

8. Press the greater than button, then press OK. And your table won't show the 0 values anymore.

[Former Member · Accepted solution]

Hi Padawan Girl,

Your solution works fine, I had something similar in mind, but your suggestion was better, I'll give you points for you answer.

Thanks every one for your suggestions.

Best Regards

[Former Member]

To be more appropriate we sholud use absloute function e.g.

if  Dim1 =(100)

   Dim2 =(-50)

   Dim3  =(-50)

simple sum of above will be "0". And row will be hidden.

To avoid such condition we should use absolute function

=If ( Sum(abs([Dimension1])+abs([Dimension2])+abs([Dimension3])) = 0 ; "Hide" ; "Show" )

--Aniket

[Former Member]

we can not delete or remove particular column field. there is only possible remove row or column or table.

there are only two methods for manuplating a report

1) Restriction using any filter

2) using formulas(functions).

Report filter--->we doesn't use this fiter for measure objects it is use only for detail and dimension objects

Query filter: drag the object -

---

>not eual to-----> 0

so you can rectify " 0 or NULL " values on your report.

formulas: use the formula from formula editor dailog box and restrict your data.

and one more is using alerts:

the alert option hide the data like "0 or NULL" to empty when you apply the font color is default..

but when you use calculation---> average the 0 is also counted.

just the alert like mask of the value..

All the best..

[Former Member]

Hi,

In the query properties ---Rows/colums tab ---supress Zeros select active --- select third option in the Table delete the row if all are zeros....

thanks

[Former Member]

I had tried activating the supress cero option on query designer, but it didn't work.

Edited by: Jorge Arjona on Jan 27, 2011 4:19 PM

[Former Member]

Hi,

This may help for you

Here we can resolve the problem by using alerter

1.click the alerter button from the Report

2. In this click create new alerter

3. The alerter window display

4. In this sub alerter field check the formula

5. It opens sub alerter window

6. click fx, left side of the window

7. It display formula editor window

8. then write the formula column=0 then click ok

9. click format button, select color:default then click ok

10.this alerter added to available alerters window.

11. select the column which you want to apply,and click alerter from report then check it.

12. the record which u don't want to delete.

[Former Member]

Hi Narenda,

Thanks for the response, I did step by step your suggestion but It didn't work, the alert just change the format of the rows that are 0 or NULL, but the row it is still there.

[Former Member]

Hi Jorge,

Have you tried unchecking the report options when the table is highlighted for "Show rows with empty measure values", or "Show rows with empty dimension values", or "Show when empty"?

Thanks

[Former Member]

Hi Wavery,

Yes I already unchecked those options and it didn't work cause there are columns with 0 and WEBI doesn't take 0 as an empty value.

[Former Member]

Hi Jorge,

I am sort of remembering dealing with a similar problem I had..... Try reformatting the columns that show 0's to be blanks. That might work.

[amrsalem1983]

why you dont apply a quick report filter to hide ZERO and NULL values?

[Former Member]

Hi

Create a variable for each column with this formula =If([Column1] <>0) Then([Column1]) in Web Intelligence.

In Universe create an object with condition if column is not equal to 0 or null then display column.

Edited by: Swarna K on Jan 27, 2011 6:06 AM