What's New

Create a Report to Show Top X

By - Updated June 23, 2015

You can use Kepion to create advanced reports such as showing the Top X by a certain criteria. You can leverage the powerful NATIVE MDX tab on the form builder 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.

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


  • Geography (this is where we want to show the list of geography on our report. The definition will not be configured by the ROW tab, but rather will be done in the NATIVE MDX tab)


  • Account (with the Filter Variable checked, we can define the column of the report to be driven by the Filter Variable)


  • 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 onto the column definition:

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

Finally, the make the report show the Top geography by the selected Account filter, we need to inject some MDX to the row definition. Here we use the NATIVE MDX to define a dynamic ROW definition directly in MDX. Notice that the ${[Account].[Account]} 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:

Make sure you have the Row checkbox checked to inject the dynamic MDX:

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