How to Get the Most out of MDX Forms with Slice

MDX forms are an incredibly powerful add-on to the existing Excel reports in IBM Planning Analytics (TM1) and they offer a tremendous level of flexibility and customization to get you the results that you need. In this article, we’re going to go deeper into some of the more advanced use cases, so if you’d like to familiarize yourself with the basics first, you might want to read this blog post before continuing.

You don’t need to start from scratch when you are creating MDX Forms. There are two very simple ways where you can have Slice generate the MDX Forms for you to get you up and running as quickly and efficiently as possible.

1.       The first option is to right-click on any cell in an Excel workbook à Go to Slice à Insert Form à MDX Form

2.       The second option is to export the MDX Form to Excel from a cube view in Slice. You can export any view as an MDX Form. Go to the Excel icon à From the drop-down select à Export MDX Form.

You can apply or create a new formatting style to the Cube Viewer and use it with your MDX Forms with the View Style Editor plugin (Find out more about this plugin here).

MDX Forms are driven by a single Excel function wherein you can use different MDX queries. This allows you to shape your report in many different ways, for example:

• Change the dimension on-the-fly and/or the hierarchy on rows and columns.

• Dynamically swap rows and columns.

• Stack dimensions and/or hierarchies on both rows and columns.

• Create on-the-fly calculations by using calculated members in MDX.

If you’re highly proficient with MDX, you can also create on-the-fly calculations by using calculated members in MDX. Remember you can test your queries in the MDX Lab (Read more about that here) with features such as the easy-to-use Code Formatter for MDX and quick access to snippets and functions by hitting CTRL+SPACE. Writing MDX queries has never been easier.

MDX Forms are a powerful tool that has many advantages, such as:

• Easier to maintain reports because everything is driven by one excel function.

• Expand and collapse rows and columns.

• Stack dimensions on rows or columns.

• Create on-the-fly calculations by using calculated members in MDX.

• Have many MDX forms in a single spreadsheet.

Try the Slice MDX Forms Hands-On Tutorial today and see for yourself how easy it is to create advanced MDX Forms.

• Unpacking 3 new features of the DBRW in Slice

• Getting started with Slice in one minute

• Building Active Forms with Hierarchies