Hey guys, I'm trying to find a way to only show the first five rows of a query in a list without having to make a new query.
I have a report studio report with a list. The list has a store, 30+ metrics, and a rank for each group of metrics. For example, a row will have Store, measuresA, rankA, measuresB, rankB....rankG. The list is populated by a query called QueryStore. Because there's so much going on in this query/list, I'm trying to keep everything simple so when it needs to be maintained down the road it doesn't confuse the next developer.
Now here's the kicker. There is now a request to show in addition to the big list, smaller lists that show the top/bottom 5 stores by each rank. The additional lists will be simple enough: just store, the relevant rank, and 1-2 metrics that help determine the rank. In a simple report, I would make a sub-query of QueryStore, set a filter for rank{x}<=5, and move on. But this report is so big, and there are so many ranks, that the report is going to be flooded behind-the-scenes if I do this for each rank.
What I'd like is to create a 2-3 column list that is still based on QueryStore, but only shows the first five rows. Then I could have each list sorted by its relevant rank. If that's not possible, I want to find a way to show all of these small lists without having to create seprate queries.
Thank you for your help.
In this case you can use style variable & hide the List body.
Thanks Sekhar@22. That did the trick.
For anyone else with this problem, here's what I did:
- Create one list for rankA, metricA, and store. Set the list to sort ascending by rankA
- Select a column in the list, and go to the properties for List Columns Body Style
- Under List Columns Body Style->Style Variable, create a new Boolean Variable called "OnlyTop5". The expression for the variable is RowNumber()<6
- Use Condition Explorer to OnlyTop5 value being NO
- Back on the List Columns Body Style, set the Box Type to "None"
- For each additional top 5 list, repeat the steps above on a new list, or just copy and paste the list. Each list sorts by a different rank, and they all display only the first five rows.