Question on Cube Reporting - Compare MUN Values between 2 dimensions.

[a_sarkar1024]

I have a report requirement where I need to create a dynamic filter for a report. The report is built on a cube. Here is the requirement in detail.

(1) The report has a prompt on Year - Month (E.g. Jan 2014) which obviously is a level within the Time Dimension. This is a single select prompt.

(2) Once the user makes the selection and clicks submit, based on the name of the month the report is supposed to filter the contents of a chart with another value from a different dimension as well, which the user would not want to select.
  E.g. If the user selects Jan 2014 or Jan 2013, then the month value is January and then the chart in report should get filtered on Scenario = forecast_jan. Scenario is a separate dimension and so the value 'forecast_jan' is an MUN at a level in the Scenario dimension.

(3) I wanted to know if this kind of a operation is possible using an OLAP source. I think we need to be able to map or basically compare two MUN values/captions from two different dimensions within the same cube using some logical operators. Is such an operation possible in OLAP?

Regards
Anirban

[MFGF]

I have a report requirement where I need to create a dynamic filter for a report. The report is built on a cube. Here is the requirement in detail.

(1) The report has a prompt on Year - Month (E.g. Jan 2014) which obviously is a level within the Time Dimension. This is a single select prompt.

(2) Once the user makes the selection and clicks submit, based on the name of the month the report is supposed to filter the contents of a chart with another value from a different dimension as well, which the user would not want to select.
  E.g. If the user selects Jan 2014 or Jan 2013, then the month value is January and then the chart in report should get filtered on Scenario = forecast_jan. Scenario is a separate dimension and so the value 'forecast_jan' is an MUN at a level in the Scenario dimension.

(3) I wanted to know if this kind of a operation is possible using an OLAP source. I think we need to be able to map or basically compare two MUN values/captions from two different dimensions within the same cube using some logical operators. Is such an operation possible in OLAP?

Regards
Anirban

It might be possible to use a macro to construct the two MUNs from the prompt, but to know that we'd need to see how the MUNs of your month level members and your scenario members are structured. Can you post up an example of each?

MF.

[Lynn]

Your query that feeds the prompt control should have an item to display the members of your time dimension level. Specify this as your display value in the prompt control. Then create a query item to translate each display value to the corresponding MUN of your other dimension.

Then create a slicer for the query using the expression: #prompt('YourDateParam','token')#

The tricky part is the query item that translates the selected value to the MUN in your other dimension. As the muppet suggested, you could get some help with that if you post an example of what a MUN looks like for your scenario dimension. I think a sample of the caption, business key and MUN of the dimension that feeds your prompt would also be handy so we know what we have to work with.

In the meantime, attached is a report from the sample data that illustrates the concept. Not sure my expression for building the MUN is the most elegant but it should give you an idea.