In this article, we explored how to create dynamic asynchronous views using MDX. This approach enhances data visibility by allowing unique element combinations in each row and column. Now, we’ll expand on this by demonstrating a practical example.

MDX Forms, available through the Slice add-on in Excel, provide a powerful way to customize and maintain reports efficiently. They offer flexibility, enabling on-the-fly dimension changes, dynamic row and column swaps, and the creation of calculated members using MDX. These forms are maintained by a single Excel function, simplifying report upkeep.

Let’s create a dynamic asynchronous view for analyzing sales data across different years and versions. Follow these steps:

Create a cube called “System Information” or “System Info” or you may already have a similar cube which stores global variables for your TM1 model, such as Financial Year, Budget Year and so on.

Use the MDX lab in either Arc or Slice to write and test your query. Remember to use the MDX function “StrToMember”. This function returns the member specified by an MDX–formatted string, which in this case, we will dynamically fetch string values from the “System Info” cube.

The “StrToMember” function in your MDX query will look something like below, remember to read our blog “Create Dynamic Asynchronous Views with MDX” for a complete and detailed explanation.

Column 1: Actuals for the Current Year

({[Version].[Actual]} *

 {StrToMember(“[Year].[Year].[” + [System Info].([System Info Parameter].[Current Year],[System Info Measures].[String]) + “]”)} *

 {[Period].[Year]})

Column 2: Budget for the Budget Year

({[Version].[Budget]} *

 {StrToMember(“[Year].[Year].[” + [System Info].([System Info Parameter].[Budget Year],[System Info Measures].[String]) + “]”)} *

 {[Period].[Year]})

Insert your MDX query in the Slice Cube Viewer to visualize the dynamic asynchronous view.

or insert the MDX query in your Slice MDX Form

Edit the “System Info” cube values to dynamically update your report. This will ensure consistency with the latest parameters of your TM1 model.

Dynamic asynchronous views with MDX, combined with the power of Slice MDX Forms, offer a robust solution for customizing a budget template in IBM Planning Analytics/TM1. This approach enhances report flexibility and maintenance, allowing for a tailored and efficient user experience.

Ready to take your Budget Planning Templatesto the next level?

You can download and try Slice for free