Introduction
Kepion's Form Editor has the power to create advanced, dynamic reports through the Native MDX tab. In this article, we will create a report where the Entity Members are ordered from top to lowest value, based on the Account Filter selection.
Configure Form Layout
Go to Layout and configure the Form as follows:
Rows:
- Entity - The Dimension containing our countries, its definition will not be configured by the Row tab, but rather via the Advanced tab.
Columns:
- Account - With Filter Variable checked, we can define the column of the report to be driven by the Filter Variable.
Filters:
- Scenario
- FiscalYear
Configure Columns
1. Go to Column and select Variable.
2. Select your target variable and then Add.
Notice that we are using a variable called ${[Account].[Account]}. This will allow the selected Filter Variable (in this case, the Account Dimension) to have its selected member dynamically injected into the column definition.
Tip: If the ${[Account].[Account]} variable isn't an option, ensure that you have selected the Filter Variable checkbox in Layout tab.
Your should have ${[Account].[Account]} in the right pane:
Define Filters
Go to Filter and review your current configuration. You will notice the first two Filters are just regular Filters, but the third one is a Filter Variable (indicated by the (x) symbol. Any selection in this Filter will be dynamically injected into any member placeholder that is ${[Account].[Account]}
You can change the default Member by clicking the blue text or . When you open the Form, each Filter will be set to the default Member you have set (if you have).
The following is our final Filter configuration:
Configure Rows
Finally, to order the rows by highest to lowest value, we need to inject some MDX into the row definition.
1. Go to Advanced > Native MDX.
2. Select the Row checkbox.
3. Define a dynamic definition in MDX.
Notice that the ${[Account].[Account]} variable is used in the definition. We want to inject the selected Member from the Filter Variable dynamically into the row MDX to generate our definition:
TOPCOUNT
(
DESCENDANTS([Geography].[Geography].[All], 10000, LEAVES)
,1000
,([Measures].[Measures].[Value],${[Account].[Account]})
)
Review and Style Form
Now your report (with some formatting) should look something like this: