I would write a separate CTE that would count the most common areas and return those areas in descending order and put a limit of 7.
Now, I want this to be a top n value where n can be 1, 2, 3, ..., n such that the chart will breakdown and show the travel_time for n areas.
How do I do this?
My initial plan was to write a query the same way, however I would not be able to vary the n value from the dashboard without going to the query. I know that I can make a field filter but I am struggling to do so.
This is how the aforementioned CTE looks like:
top_7_area AS (
ISNULL(Area,'') <> ' ' AND -- some area values are blank
Area IS NOT NULL
GROUP BY Area
ORDER BY COUNT(*) DESC