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

[former_member158249]

Hi Ram,

I have implemented same concept to achieve rolling 12 month aggregation, but let say i want to calculate for year 2013, Jan to Dec. If in the report prompt i give year 2013, my 2012 data will get filter out.

eg: Jan 2013 rollup = Feb(2012)+.......Jan(2013)

but due to report prompt, 2012 hass been filtered out. hence the results wont be corrent. NoFilter works in case of report filter, not in case of prompts.

Can you help on this?

How to achieve the same in universe level?

Regards

Sushant

[Former Member]

Sushant,

For handling the previous year values for the measures, you can use @Prompt function in the Previous Year Measure objects as below(Pseudocode):

@Measure_PY Where @Select(Time\Fiscal Period) = @Prompt("Enter Selection year") - 1 Year

(the underlined message should be the same as the current prompt message which has been used as a prompt for selecting current year)

For other Measures:

@Measure Where @Select(Time\Fiscal Period) = @Prompt("Enter Selection year")

Rgds,

Nagavaibhav

[former_member158249]

Hi nagavaibhav,

Can you please explain?

Do you mean i should create this measure in the universe level?

Regards

Sushant

[Former Member]

Yes create the measure on the Universe.

Since Prompt message is same, you will have single prompt and the value returned should be handled in the Measure's formulae (the place where you will subtract 1 year from the value returned by the prompt)

[Former Member]

Hi Nagavaibhav,

The solution suggested by you gives error!

The actual syntax to achieve prompt is

@Measure_PY Where @Select(Time\Fiscal Period) IN @Prompt("Enter Selection year")

And if i try -1 on the year prompt, i will get the error.

[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be prepared.

Regards

Sushant

[Former Member]

Hi Sushanth,

I have given only the psuedo code. Hence you must excuse the syntax.

And - 1 is only an indication that 1 year should be subtracted from the UserResponse's output.

You will need to make use of some functions in Universe to do that operation.

Let me know if you need ready-made answer. Will write it for you, if you could give all the data types and the object names.

Rgds

Nagavaibhav

[Former Member]

Hi Nagavaibhav,

I know -1 is to subract one year from the year parameter, Pma Year is a integer field and parameter created in numeric.

@Select(Pmayears\Pma Year) IN @Prompt(Enter Year)

Personally, i think functions are required when i have a date prompt, in this case simple subraction should do.

I would appriciate if you could provide the solution!

Thanks for the help!

Regards

Sushant

[Former Member]

Hi Ram, For hiding blank rows you have to use show rows where measure values, and for hiding "0" you have to use show rows where measure values ="0" . Thers is a big difference in these two options, hope you will get it with this. Also conditions used will overwrite the default settings. Thanks, Avinash

[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