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_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