Dynamic Measure in a tuple issue

[RyanDonohue]

I have a report with a series of crosstabs and graphs that works fine with a static measure of Current Amount or YTD Amount. I created a prompt in the report so the user can switch between the 2 measures. I created a calculated measure using case when else.

I added this calculated measure to my crosstab and it works as expected. I then went to try to change my graph which requires editing the tuples that make up the series. I pointed to my new calculated measure, but I am getting the error when I validate RSV-VAL-0010 Failed to load the report specification. After this, it will either say {0} or Out of Memory.

As stated, it works fine with both static measures and my calculated measure works in the crosstab. Why am I unable to do this with my tuple?

 

[RyanDonohue]

I figured out a solution that required using Tokens. This allows my tuples to be flexible but avoid using case when statements.

 

[MrTy]

Ryan,

I am something of a newbie to dimensional reporting, although in the last few weeks, I've gained a LOT of ground.

You describe something in your post I'd like to know more about: calculated measures with case statements. I have been unable to do that in my crosstabs, but I'm trying to use dimensional objects. For instance, in trying to build a waterfall report:

CASE
WHEN [RowMonth] <= [ColumnMonth] THEN [Actual]
ELSE [Forecast]
END

This should build a waterfall report. However, the construct I outlined above does not work with a validation error of "the operands cannot be converted to consistent dimensional object."
In fact, I've had lot of trouble with not being able to do this. Do you have a quick tip or can this not be done?

Thanks.

Ty

 

[RyanDonohue]

Hey Ty,

Please forgive me as I am rather new myself.

Would you mind explaining what you are trying to do in more detail?

I have a report similar to what you are trying to do that provides Actual up to the selected month, and then Forecast for the remainder of the year. My months only go across the top, which may be different from your requirement.

To do what I have done, I have 2 sets.

The first goes from Jan to the selected month by using the expression: periodsToDate ( [Year], [Month]) where [Month] is a data item for my Month variable ?Month? and [Year] is from my calendar hierarchy.

The second set is all of the months of the selected year except the months in the first set. This is found using the expression: except(descendants(ancestor([Month],[Year]),[Month]),[PTD]) where [PTD] is my first set described above.

Then I have a calculated member that identifies which set is being used and picks [Actual] or [Forecast] respectively.

I'm not sure if that helps, but once I have more details about the data you have and the desired end result, we might be able to come up with a workable solution.

Ryan

 

[MrTy]

I just finished up an identical report as you described and pretty much the same way. This one is different however. Here is a small example:

_________Jul 10______Aug 10_____Sep 10_____Oct 10
Jul Actual Forecast Forecast Forecast TOTAL
Aug Actual Actual Forecast Forecast Total
Sep Actual Actual Actual Forecst Total

Where Sep 10 is the current period.

Now one thing you mentioned above that is something I've not been able to get:

"Then I have a calculated member that identifies which set is being used and picks [Actual] or [Forecast] respectively."

I think knowing that technique would help great deal.

 

[MrTy]

Hey, Ray, when you get a chance, I'd like to know more about that conditional you use in the calculated measure to determine which set is being used.

I have a report like yours also, but simply used a seperate calc under each set.

 

[MrTy]

Ryan,

How did you do the conditional measure? I've done some, but I'm needing to do it within the framework of a tuple. The dynamic argument is currency.

 

[RyanDonohue]

Ty,

I am sorry for leaving you hanging like this!

After reviewing my report, I realized I defined the ledger ([Actual]/[Forecast]) as a hidden column and thus used a standard measure.

I tried to create the waterfall that you are asking for in a blank template. I am able to waterfall where RowMonth=ColMonth as that's the only place my data structure has information.

Try this: Create a calculated measure with this expression
if ( roleValue('_memberCaption',[RowMonth]) < roleValue('_memberCaption',[ColMonth]) ) then ([Actual]) else ([Forecast])

Please note my Month structure is YYYY-MM so the caption works for me. This may not work for you and you can change '_memberCaption' with one of these '_businessKey', '_memberCaption', '_memberDescription', '_memberUniqueName'.

 

[MrTy]

No problem, thanks for responding!

I have had a time with the rolevalue. I've tried to just put it in a data item and cannot get an expected result. I'll play with it some more.

I'm thinking the only way to achieve this is through dual columns and hide as needed.

Thanks again and chat with you later. If I figure it out, I'll let you know.

Ty

 

[RyanDonohue]

Good Luck! Hope you are able to figure it out easily.