Show Top Values in Form

Author: Kepion Product Team

Introduction

You can use Kepion to create advanced reports such as showing the top values in a Form by a certain criteria. You can leverage the powerful NATIVE MDX tab in the Form editor to create these dynamic reports. Here, we will create a report where the user can choose the Account criteria dynamically on a filter and show the top Geography on the rows.

Example

Let’s see how we created this report. First we define the following report layout:

Rows:

• Geography - This is where we want to show the list of geography in our report. Its definition will not be configured by the ROW tab, but rather will be done in the NATIVE MDX tab.

Columns:

• Account - With the Filter Variable checked, we can define the column of the report to be driven by the Filter Variable.

Filters:

• Scenario
• FiscalYear

Next, we define the Actual COLUMN definition. Notice that we are using a variable called ${[Account].[Account]}. This will allow the selected filter variable, in this case being the Account dimension, to have its selected member dynamically injected into the column definition:

Tip: If the ${[Account].[Account]} variable isn't an option in the Variable selection window, ensure that you checked the Filter Variable check-box in the previous step.

Let’s now look at the FILTER definition. Notice that the first two filters are just regular filters, but the third one is a Filter Variable. This means that any selection in this filter will be dynamically injected into any member placeholder that is ${[Account].[Account]}

Finally, to make the report show the top Geography by the selected Account filter, we need to inject some MDX into the row definition. First, navigate to the NATIVE MDX pane and make sure you have the Row check-box checked in the CURRENT tab:

Then, we'll define a dynamic ROW definition directly 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 give us our dynamic definition:

TOPCOUNT
(
    DESCENDANTS([Geography].[Geography].[All], 10000, LEAVES)
    ,1000
    ,([Measures].[Measures].[Value],${[Account].[Account]})
)

Now your report is ready, and with some formatting you have: