Ranking and Filtering Based upon Prompt Value

[former_member1161148]

I am working on a report in Web Intelligence that ranks a list of values in a list. Then a prompt value would be applied, be it 5, 10, 25, etc., that will filter the rank based upon the chosen number. So, for example, one of the prompt values is 20, so the report should filter to only show the top 20 records based upon a rank that was derived from a variable when 20 is chosen in the prompt. How can this be accomplished?

[nscheaffer]

I have three options for you. Hopefully you can find something in here that is helpful.

1. Database Ranking

If you are basing your rank on something that is available directly from your universe (no variable calculations, merging with another query, etc.) you could use database ranking.

In order to do that select the object within the universe tree you want to rank and click the "Add a database ranking" in the Query Filters section.

You will have to drag what your ranking is based upon to where I have Quantity sold. Then click the icon in the middle and choose "Prompt".

And there you have it.

Now if your ranking is based on a variable that approach will not work. Keep reading.

2. Input Control

You could filter on your Rank with an input control. My Rank variable is simply this...

=Rank([Query 2].[Quantity sold])

Be sure your filter operator is "Less than or Equal to". I used a control type of "Simple slider", but you could certainly choose a different one if it better suits your needs.

The potential drawback to this approach is that it doesn't actually prompt the user for that Top X value. We'll do that in the next option.

3. Dummy Prompt

A dummy prompt is where you have a query that prompts for a value not to return any data, but just so that you can capture that value. Here is query with a dummy prompt. I added the "Month = -1" so that I do not actually get any data.

To extract that prompt response I created a variable called Top X Prompt with the following formula. Keep in mind that the UserResponse function will always return a string so I need to convert that to a number for use later when comparing to my Rank.

=ToNumber(UserResponse("Enter Top X:"))

We cannot filter comparing objects/variables with each other. So I need another variable I am calling Rank Filter Prompt with this formula...

=If([Rank] <= [Top X Prompt]; 1; 0)

Finally add a filter on Rank Filter Prompt.

With prompt value of 7 I get the top 7 rows.

Let me know which of these (if any) fits your situation.

Noel