In Web Intelligence 3.1 SP2 (summer 2009), we released a new feature called Input Controls. Coy’s column did a good job of describing the general use of these controls Web Intelligence SP2: Input Controls and Web Intelligence Input Controls - Part 2. What I like about them, and we use them internally on our Web Intelligence content, is that in 30 seconds you can expose the most common and likely filters that enable consumers to quickly personalize their BI content. (Actually, it takes less than 30 seconds if you use default parameters.)
We’ve received a number of questions about possible use cases and output that Input Controls can support. To start, I’ll contribute one about ranking. This was a sample proposed by one of Web Intelligence’s fantastic program managers, in fact, the one who designed the feature.
To start, ranking of Top n is one of the most common report outputs. Everyone benefits from views of large lists ranked by the products with the largest sales, customers with the longest overdue payments, employees with the most years experience, etc. But "hardcoded" outputs, such as Top 10 products by revenue, can be frustrating for a consumer when the value they want to view is in the top 20 or top 30, and not the top 10. I'm going to describe how report authors can enable consumers of their content to control their own definition of top n, without having to do anything more than move a slider.
First, consider customer satisfaction survey data with dimension [Customer Name] and two measures: [Satisfaction] (as in “satisfaction rating”) and [Net Sales]. If you build a table, sort descending by [Satisfaction], you get potentially 100s of rows.
For the sake of the report, let’s put a rank on the top n Customers by satisfaction rating but build an input control to enable consumers to control the number of top customers visible.
Next, create a variable called “Rank Customers by Satisfaction”, set as a measure and use the following formula: =Rank([Satisfaction];([Customer Name]);Top)
This uses Web Intelligence Rank function. You define the dimensional context for [Satisfaction] as the [Customer Name] to which is appears. Note this would have been the default even if you didn’t put the [Customer Name] in the context, but it is sometimes useful to lock in the context when there are multiple dimensions in table rather than let the tables column layout define the context.
Now, create a value for a rank which will serve to define the starting value for the Input control. Let’s set this variable as a dimension and label it Top n and simply make the formula =10. Note you can set this to 0 through 20, it doesn’t matter what number you set because it will be dynamically overwritten by the slider in the input control.Pretty simple so far? The 3rd variable is simply an “If” flag to read the status of the Top n variable you just created. Call this variable “Flag rank” and set as a measure. The calculation is also simple:=If([Rank customers by Satisfaction]>[Top n];0;1)
This has taken about 2 minutes so far. The last step is to use the Flag to create a FILTER on the table you’ve defined above. Make sure the filter pane is open and the table is selected. Drag the “Flag rank” variable into the filter pane, and set the filter “Equal to constant” and type “1” in the text entry field like below.
"
Now that the variables are set up, the only thing left to do is to define the input control. Reveal the input controls in the right-hand pane and click new. From the Input Control wizard select the “TOP n” variable as the dimension to filter on, then click NEXT. Fill in the next step like this:
Then you have an input control slider that can change the numbers of customers appearing in the ranking table.
Moving the slider dynamically changes the number of customers displayed in the table.
So, here’s a summary of what was added to the report:
Step | Output | Details |
| 1 Create a ranking calculation | Variable named: “Rank Customers by Satisfaction” | =Rank([Satisfaction];([Customer Name]);Top) |
| 2. Default value for slider | Variable named: “Top N” | =10 |
| 3. Detection of Rank value | Variable named: “Flag rank” | =If([Rank customers by Satisfaction]>[Top n];0;1) |
| 4. Set a filter on the table using the “Flag rank” variable | Constant value | =1 |
| 5. Define your input control on top of Top n | Top n | Min value = 0, Max = 30 |
With a few more calculations, you not only enable end-users to rank, but to see the impact of customer satisfaction on revenue. The table below is completely dynamic as well, all text changes according the value selected in the input control
When you add up the time to define the variables, filter and parameters for this input control, it was perhaps 3 minutes. That’s a worthwhile investment for report authors to spend when you think of the many different questions that now can be answered by consumers of this report.
6 Comments
