Wrong values while Hiding rows/columns in a table

[Former Member]

Hi All,

I have a situation where i have created a webi report containing the rolling 12 months running sum of a measure object.

I am using cross table to display the values along with year, plan/actual, revenue, months

I am getting the below mentioned output.

and the properties of the table are shown in the below figure.

Now I want to hide the rows that are showing empty in the table. So I unchecked the "show rows where measure values =0" and even wrote a formula  to hide, then the output is as follows.

the properties for the above table output is as below.

Now if we observe, the values in the cross table are different. If i uncheck the values are changing and giving me wrong results.

The values in the first figure are correct but when i try to hide the empty rows it is giving me wrong results.

I tried to remove the Year dimension and checked the output, then it is giving me "#multivalue" error. I tried with putting Sum() function to rectify the error but of no use.

I tried to put block level filter for year object to the cross table, but values are coming wrong.

Any help highly appreciated.

Regards

Ram

[jyothirmayee_s]

HI,

"Hide when formula is true" option hide/unhide depending on the formula.

If you want to hide/unhide rows then apply a report filter.. you cannot directly hide a row if it is null..

Try the same logic in "Hide" for Report filter,

Where is your running sum in Crosstab though?.

Thanks,

Jothi

[Former Member]

Hi Jyothirmayee S,

Yup, i have tried all options in hide and every checked with multiple ways as i mentioned in the discussion and attached pictures of it.

My rolling runningsum() formula is as below

=If([Aggregate Data].[Year]=[Current year] And [Aggregate Data].[Month Number]<=12) Then RunningSum([Aggregate Data].[Plan Value])-Previous([Aggregate Data].[Plan Value];13)

without year field i couldnot able to get the required output and if show year then I dont want to show the months for previous year. I dont know how can i achieve this.

Regards

Ram

[Former Member]

Hi Jyothirmayee,

Yup, i tried all options in HIDE and even checked with multiple deselect of the check boxes but of no use.

I restricted using block filter, using filter variable and all sorts of filter.

If i remove the year then my report is not showing accurate values but if I show Year then it is displaying me the exact values for current year and blank values for last year. Now I wanted to hide those blank values.

My runningsum() formula is as below:

=If([Aggregate Data].[Year]=[Current year] And [Aggregate Data].[Month Number]<=12) Then RunningSum([Aggregate Data].[Plan Value])-Previous([Aggregate Data].[Plan Value];13)

Even we are trying for Universe level filter and output of one query as an input to another query concept.

Dont know how to resolve this issue

Regards

Ram

[jyothirmayee_s]

Hi,

Formula is context on Year and you need to show it on report block. Why not display "Year" in block and hide it?.

Thanks,

Jothi

[Former Member]

Hi Jyothirmayee,

I have displayed year on the block and trying to hide.

If you see the first picture that i have uploaded you can clearly view year 2012 with no values and year 2013 with values.

Now I want to hide the empty rows for the year 2012.

I can hide by unchecking the Show measure values = 0 in table properties, but the values are displaying incorrect as you can see in figure 2.

Any help highly appreciated.

Regards

Ram