Show Top Values in Form

Author: Kepion Product Team

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 is ready, and with some formatting you have: