Conditional filtering on Cube based report

[PRIT AMRIT]

Hello Guys,

I have a report which is based on a TM1 Cube. The report has several dimensions with 2 measures. The report has a Time Dimension. The time dimension hierarchy looks like the attached print screen.

Requirement- The report has filter on time dimension, meaning user have to select a value whilst running the report. Instead the user do not want to do any selection manually when running the report. The report should determine the Month based on the system date and filter the report with respective YTD values.
E.g. If the user runs the report in July-2014 (doesn't matter what date) the report should be filtered by YTD 2014M06, similarly If the user runs the report in Aug-2014 (doesn't matter what date) the report should be filter by YTD 2014M07

Can anybody suggest if this can be done using any MDX functions or any other logic to be put-in to achieve this requirement? Many thanks!

Regards,
Prit

[MFGF]

Hello Guys,

I have a report which is based on a TM1 Cube. The report has several dimensions with 2 measures. The report has a Time Dimension. The time dimension hierarchy looks like the attached print screen.

Requirement- The report has filter on time dimension, meaning user have to select a value whilst running the report. Instead the user do not want to do any selection manually when running the report. The report should determine the Month based on the system date and filter the report with respective YTD values.
E.g. If the user runs the report in July-2014 (doesn't matter what date) the report should be filtered by YTD 2014M06, similarly If the user runs the report in Aug-2014 (doesn't matter what date) the report should be filter by YTD 2014M07

Can anybody suggest if this can be done using any MDX functions or any other logic to be put-in to achieve this requirement? Many thanks!

Regards,
Prit

Hi,

In theory this should be possible. Do you want to retain the prompt and have it default to returning the monthly YTD members, or get rid of it and just have the report show the YTD monthly members?

Two other questions:

1. Can you copy and paste the MUN of a YTD month member into this thread so we can see its structure?
2. I can see you have a named level called Year. What names do the levels below this have?

MF.

[PRIT AMRIT]

In theory this should be possible. Do you want to retain the prompt and have it default to returning the monthly YTD members, or get rid of it and just have the report show the YTD monthly members?

This report doesn't have a Prompt page. It has a context filter. User manually change the value and run the report. Instead of manual selection, as you said user wants to get rid of it and just have the report show the YTD monthly members based on the current month.

E.g. If the user runs the report in July-2014 (doesn't matter what date) the report should be filtered by YTD 2014M06, similarly If the user runs the report in Aug-2014 (doesn't matter what date) the report should be filter by YTD 2014M07

Two other questions:

1. Can you copy and paste the MUN of a YTD month member into this thread so we can see its structure?

Code Select Expand

[PL_Reporting].[PL_Time].[PL_Time].[Month]->:[TM].[PL_Time].[PL_Time].[@MEMBER].[YTD 2014M07]

2. I can see you have a named level called Year. What names do the levels below this have? -- Please find attached pring screen of Year level. It doesn't have the YTD.

Should you require more details pls let me know. Thank you for your help in advance.

Regards,
Prit

[MFGF]

This report doesn't have a Prompt page. It has a context filter. User manually change the value and run the report. Instead of manual selection, as you said user wants to get rid of it and just have the report show the YTD monthly members based on the current month.

E.g. If the user runs the report in July-2014 (doesn't matter what date) the report should be filtered by YTD 2014M06, similarly If the user runs the report in Aug-2014 (doesn't matter what date) the report should be filter by YTD 2014M07

Two other questions:

1. Can you copy and paste the MUN of a YTD month member into this thread so we can see its structure?

Code Select Expand

[PL_Reporting].[PL_Time].[PL_Time].[Month]->:[TM].[PL_Time].[PL_Time].[@MEMBER].[YTD 2014M07]

2. I can see you have a named level called Year. What names do the levels below this have? -- Please find attached pring screen of Year level. It doesn't have the YTD.

Should you require more details pls let me know. Thank you for your help in advance.

Regards,
Prit

Hi,

Ok - ditch the context item, and go to the query. Drag a Slicer member set from the toolbox into the Slicer area, and code the expression as:

#'prevMember([PL_Reporting].[PL_Time].[PL_Time].[Month]->:[TM].[PL_Time].[PL_Time].[@MEMBER].[YTD ' + timestampMask($current_timestamp,'yyyy') + 'M' + timestampMask($current_timestamp,'mm') + '])'#

Does this give you what you need?

Cheers!

MF.

[PRIT AMRIT]

Hi MF,

It gives me a parsing error, attached find the printscreen pls.

Thanks for your help indeed.

Best regards,
Prit

[MFGF]

Hi MF,

It gives me a parsing error, attached find the printscreen pls.

Thanks for your help indeed.

Best regards,
Prit

Ummm. Nope. That parsing error isn't from this expression. When you validate, it checks all the expressions used, not just the one you are working on. You have another expression elsewhere in the report that is triggering this error.

I can see why you are getting the error too - you're trying to use the _add_months() function on a member somewhere. The two are not compatible - _add_months() expects a date or datetime argument, not a member...

Cheers!

MF.

[PRIT AMRIT]

Hi MF,

Sorry, it was my bad. The expression given by you works in my dummy report with few columns. But not in the original report.

It gives the following error. Please find the attached screenshot too for more details.

Element 'slicerMemberSet' is not valid for content model: '((slicerMemberSet,)|(contextSlice,))'RSV-SRV-0042 Trace back:RSReportService.cpp(752): RSException: CCL_CAUGHT: RSReportService::process()RSReportServiceMethod.cpp(263): RSException: CCL_RETHROW: RSReportServiceMethod::process(): asynchRunSpecification_RequestRSASyncExecutionThread.cpp(808): RSException: RSASyncExecutionThread::checkExceptionRSASyncExecutionThread.cpp(260): RSException: CCL_CAUGHT: RSASyncExecutionThread::runImpl(): asynchRunSpecification_RequestRSReportDOMBuilder.cpp(238): RSException: CCL_THROW: RSReportDOMBuilder::doParse

waiting to hear from you soon. Thank you for your help.

Best regards,
Prit

[PRIT AMRIT]

I figured out that this error was caused by the context filters on the report page. Hence I put them in the slicer and now it works. Did a quick data check too and seems all right.

The next challage now would be, the value selected in the context filter ( in the current report version put it in Slicer) was displayed on the report layout. Since i put it in slicer now the values in slicer not displays on the report layout. Need to fix this now.

However, A BIG THANKS to MF for his quick help.. i mean it....

Cheers,
Prit

[MFGF]

I figured out that this error was caused by the context filters on the report page. Hence I put them in the slicer and now it works. Did a quick data check too and seems all right.

The next challage now would be, the value selected in the context filter ( in the current report version put it in Slicer) was displayed on the report layout. Since i put it in slicer now the values in slicer not displays on the report layout. Need to fix this now.

However, A BIG THANKS to MF for his quick help.. i mean it....

Cheers,
Prit

Ah! Yep - that's why I said to ditch the context item. It wasn't clear you had others too, or I would have specified that slicers replace context filters entirely. You can have one or the other but not both. :)

To display the current context from the slicer, probably the easiest approach is to drag in a singleton for each slicer member set. Base each singleton on the same query your main report object uses, and add a query calculation to each singleton using the same expression used in the slicer member set.

Cheers!

MF.

[PRIT AMRIT]

To display the current context from the slicer, probably the easiest approach is to drag in a singleton for each slicer member set. Base each singleton on the same query your main report object uses, and add a query calculation to each singleton using the same expression used in the slicer member set.

This is exactly what I did. But the problem I could forsee is every time they would change the slicer (like they do selection in Context filter) they have to manually update the query calculation items. But it's not the case when you have a context filter. The values would be displayed based on the selection.

Anyway if no way to do this, then i have the only option to sell this work around to the users. Can ask them them to save few copies of the report with different slicers :) :D

Thanks MF...

Best regards,
Prit

[MFGF]

This is exactly what I did. But the problem I could forsee is every time they would change the slicer (like they do selection in Context filter) they have to manually update the query calculation items. But it's not the case when you have a context filter. The values would be displayed based on the selection.

Anyway if no way to do this, then i have the only option to sell this work around to the users. Can ask them them to save few copies of the report with different slicers :) :D

Thanks MF...

Best regards,
Prit

Hi,

So it's not a static slicer requirement? I can see two options:

1. Change the slicers to use prompt macros with the currently defined member as the default. You could then use a layout calculation to show the parameter value for each prompt, and add inline prompts onto the report page to allow the users to select other members.

2. If this (the month) is the only hard-coded context requirement, you could put all the other context items back into the context filter, delete the slicer member set for month, and instead change your measure to be a query calculation using a tuple of this expression and your measure. That would allow your users to change the context as desired.

Cheers!

MF.

[PRIT AMRIT]

1. Change the slicers to use prompt macros with the currently defined member as the default. You could then use a layout calculation to show the parameter value for each prompt, and add inline prompts onto the report page to allow the users to select other members.

This is what exactly I did. Hope the user would be happy with this.

For option 2 - there are a lot of measures, hence I preferred #1  :D

Once again, thank you so much MF to bring this issue to the closure...

Cheers,
Prit