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

[Former Member · Accepted solution]

Thank you all for your guidance and support to me

Finally I could able to get the solution for the above problem.

The following are the steps i followed in resolving the problem.

I changed the formula to:

=NoFilter(RunningSum([Act/Fcst])-RunningSum(Previous([Act/Fcst];12)))

and applied filter to the block to current year as i wanted to display only rolling 12 months runningsum for current year.

The final result is as below

Regards

Ram

[jyothirmayee_s · Accepted solution]

Hi,

If the report runs only for 2013 then solution works.. What if the user runs the report for 2012 or 2014?.

Thanks,

Jothi

[Former Member · Accepted solution]

Hi,

The requirement is as such that the report has to display for the values for the current year and the previous year.

So I filtered the report with Current Year variable, instead of 2013 as shown in the picture because for any current year change the values has to be displayed.

(For example, I have shown in the picture as 2013)

If you want the report to be displayed with 2012 or 2014 then report should contain prompts or any other way.

If it is prompted for any year then using UserResponse() i will catch the value in a variable and will apply for the report filter accordingly.

Since the formula doesnot contain any year in that so I believe it will run for any year with year containing in the table.

Only thing is that I have to choose the table/block/report filter wisely as per the report requirement.

Regards

Ram