Swapping dimensions using MUN manipulation

When a prompt is based on a dimension, the selected value is passed as a full qualifier (MUN), not just the key. This results in a limitation of the prompt's use. We cannot use the prompt to filter any other dimension. In this recipe, we will see how to override this limitation by performing the string manipulation of MUN.

Consider a business case where a cube has two time dimensions (say Billing date and Transaction date). Users want a choice on the prompt page to select which time dimension to filter on. Also, they will select a Date (Year or Month) on the prompt page, so we need to filter the appropriate time dimension.

Getting ready

Here we will use the GO Data Warehouse (analysis) package where we have two time dimensions that we can use in this recipe: the regular Time dimension and the Time (ship date) dimension.

How to do it...

In this recipe we will use a value prompt to select the required dimension that will be used in our report, as follows...