I’m trying to create a simple grid where I can drill down when you click the numbers but I can’t seem to make it work. Will someone please be able to help? Unsure what I’m doing wrong.
If you want to start from a summary view and then drill-down then select “MDX” as the list type and not “Element Drill”. An example MDX to work with might be …
{[FIN Chart of Account].[Repairs and Maintenance],[FIN Chart of Account].[Repairs and Maintenance].Children}
Then just make sure that the “Show Hierarchy” option is set to ON. (The name is confusing but this option controls whether the rowset is drillable and the +/- icons display to the left of the names of the consolidated elements.
This is a slightly advanced technique, in this demo dashboard what you see is a dynamic MDX statement being used for the rows that will show the children of the product group that has been selected in the filter. This allows for interactivity when using multiple widgets. When you for instance click on “Bikes” in the KPI widgets you will see that the filter up the top changes to “Bikes”. The table widget has an MDX configured for the rows that uses a placeholder to refer to the selected product:
{[Product].[Product].[<<UX_Demo.Product.Product>>].Children}
The important part is <<UX_Demo.Product.Product>> which is the syntax that we use to refer to page selections, this part of the MDX will be replaced before the query is send to the TM1 server. The general syntax for this is $<<Instance.Dimension.Hierachy>>, in this example the instance being "UX_Demo, dimension Product and hierachy also Product (if you are not using an alternate hierachy just repeat the dimension name to refer to the “default” hierarchy).
The drill in the table itself works in the same way in this example, you will see that the option “Hyperlink to change filters” has been activated as well, which means that when you click on a product category in the grid widget it will change the filter selection for product in the page and that will change the MDX statement that is using the placeholder.