2

Dynamic Top Count Filters Using Variables and Parameters

By Jason Picker , Sales Engineer


Top Count filters are easy to implement using the Quick Filter menu available off the right-click menu.  However, there are times you need to create something more flexible.  In this blog, you will see how to use a variable to alter the count and a parameter to change the measure that the filter is based on.
 

Step 1:  Create Top Count Filter

Let’s start by creating our Top Count filter.  In my example, I will create a Top 10 filter on Product Subcategories based on Sales.

Creating Top Count Filter

 

Step 2:  Convert Count to use a Variable

Next, we will modify the count to use a variable to prompt the user for the number of items.  This is accomplished by clicking on the Row Filter button on the Query tab and then clicking on the purple x icon to build the variable.

 

Creating Top Count Variable

 

Once you save the variable settings, you will notice that the Top count is replaced with the variable name.

 Filter Dialog After Creating Variable

 

Step 3:  Create Measure Parameter

Now comes the fun!  We are going to create a parameter of the measures we want to use as our filter options.  On the Elements tab of the Selector, you can click on the gear icon in the upper left corner and choose the New Parameter menu.

 

Create Parameter Menu

 

Give the parameter a name, select the measures you want, select the default value and then press the OK button to save it.

 

Create Measure Parameter

 

Step 4:  Change Filter to Use Parameter

Let’s put it all together now.  We will edit the Row Filter and change the measure selection to use the parameter.

 Modify the Row Filter

 

To wrap up my report, I changed my slicers to use buttons so it looks more consistent.  We now have a fully dynamic report that gives our users flexibility in what they want to see.  Enjoy!

 Final Results

1reply Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
Like2 Follow
  • 2 Likes
  • 2 yrs agoLast active
  • 1Replies
  • 963Views
  • 3 Following