How to filter rows in Webi Crosstab?

[Former Member]

Hi,

I am working on XI 3.1 SP5 / Java 6 / IE 8.

I have crosstab in Webi in below format with empty Left header:

With data, it looks like below:

the [measure 2] is returning zeros and I wish to filter the row; however, it is not working.

I tried to use a report filter like [measure 2] <> 0 at report level; however, it is hiding the whole block.

There is an option for Crosstab properties: "Show rows with empty measure values".

I searched on internet and found that the ASCII value for NULL is '0' so i tried to replace zero values with NULL by using below logic:

=IF ([measure 2] = 0 Then Char(0) Else [measure 2]     But I was unable to utilize the aforementioned option as it is not returning NULL but just a space.

Even =IsNull(Char(0)) returns 0 i.e. Char(0) is not returning NULL.

Can anyone please help me in hiding the second row with 0s?

Regards,

Yuvraj

[Former Member]

IF you have empty left header why you are having it in a regular cross tab. In a cross tab you can not filter any single row using filter.

I will suggest to build a dummy cross tab with calculated columns based on formulas as you have a relative constant set of columns. You will have much more flexibility in that way although building is not going to be easy. If you want to conditionally hide a column in that table based on data availability using auto width option and conditionally formatting to match back ground.

Please check http://scn.sap.com/thread/3371255 for ideas to conditional hiding of column...

[Former Member]

Hi Durga,

The left header is not actually empty but it has some hardcoded titles (no dimension).

Seems like the only option is to apply alterter and format the row as white when values are coming as 0.

I will try it and close the thread if successful.

Regards,

Yuvraj

[Former Member]

Hi Yuvraj,

Just to explain you the problem from different perspective,

As your left most column in your cross tab is not a dimension and its fixed text, If you rotate your current cross tab clockwise, it will look like following:

10	0	201211
11	0	201210
15	0	201301
14	0	201302
8	0	201303

In above representation, 0 value is not a row but its a column. So the moment you try to filter 0 value records, it will not display any records. So you requirement is to hide a column and not row.

Regarding hiding column in XI 3.1, you can following below post:

http://blog.mohanraj.org/2010/06/web-intelligence-table-hiding-column.html

[Former Member]

Hi Hardik,

Thanks for your reply. Even I was thinking in the same way that once converting to vertical tab, the measure will actually become a column and wont filter.

Regards,

Yuvraj

[Former Member]

Hi,

Please refer the below thread, it may help:

http://scn.sap.com/thread/3296375

Thanks.