Chart "Top N"

With 2k5 I am trying to make the "Top N" operator, used in a chart "category," return only N categories. But it almost always returns more than N categories. Can someone explain the nuances of the Top N operator, so I can get past this?

Is N fixed number, If yes then you can try filtering on rownumber.

One more option is to create a different dataset and then use "select top 10" syntax in query itself.

HTH1

Priyank

|||

Whether N is fixed or not, "rownumber" does not correlate to chart "category" grouping. I want the top 10 categories, not the top ten rows.

I don't want to use "select top N" in my data set, because it would require refactoring my query to use a "group by" expression. What I want is for the grouping to be handled by the chart.

So this brings me back to the beginning. Can someone explain the highly erratic behavior of the "top N" operator? It sometimes returns more than N, sometimes less, and it often screws up the sort order. Is this something fixed in a SSRS patch?

|||

Ok, I figured it out. First of all, assuming the perspective of "order ascending," "Top N" actually means "Bottom N," and "Bottom N" actually means "Top N." Go figure. Also, the "Top N" operator, at least when used with a chart "category" filter, does the following:

1) Uses the category field expression to select the first row of each category

2) Sorts the selection of "first rows" that came from the category, based on the field expression that was given to the "Top N" operator itself

3) Performs a dense_rank() function on that output

4) Selects the "Top N" of the dense rank

5) Afterward, the "Sorting" spec of the category is applied

This is why I sometimes get more than "N" categories returned.