I attempted to solve this problem, or work around it, by having all relevant computation performed in the data source (which required dynamic sql with nested selects and window functions). Even with the data perfectly arranged and sorted in advance, the "TopN" feature of reporting services STILL managed to screw up the results. I've concluded that "TopN" is broken.
However, I found a useable work around. I added still another 'select' layer on my datasource with a dense_rank() function, and then used its result in the filter expressions of the relevant 'category.' Problem solved.
|||I ran into the same problem and I found that this could be resolved though Reporting Services by adding the following to the group's visibility expression:
=IIF(RUNNINGVALUE(Fields!User.Value,COUNTDISTINCT,"table1_Domain")<11,False,True)
This report is counting top users of a web site that stores hits in a database table which is group by domain, then user.
In the report, I created a field: COUNT(Fields!User.Value). The inner group is then is sorted by this field (descending).
So, the logic in this expression is: Every time the user name changes it keeps that in the running total. The running total is reset when the group above it (the user's domain) changes. IIF the running total is less than 11, then Hidden = False.
So, while all values may still be processed, it only shows the top 10.
Hope this helps others
BTW, there is one drawback - since I'm using an expression for the visibility, I can't make this a drill-down field (since drill-down is also a function of visibility). If I select the "Visibility can be toggled by another report item", the report still displays as expected when first rendered, but if it's collapsed then expanded, ALL values for the group will appear, not just the top 10. I guess the visibility expression is only process at initial report rendering time, and not each time the group is collapsed/expanded.
|||Okay, I'm a but dense today - there is another way to do this. The above will guarantee that ONLY 10 values are returned, but if you remove the sort, and use only the filter for TopN, you'll get the top 10 VALUES (some may duplicate). So, the above solution may return
DOMAIN Logon Count
~~~~~~~~~~~~~~~~~~~~~~~~
Domain1
UserA 10
UserB 10
UserC 9
........etc...upto
UserJ 2
Domain2
etc
However, using only TopN (no sort), may return
Domain1
UserA 10
....
UserJ 2
UserK 2
UserL 2
Domain2
etc
So, TopN by itself (without Sort) can return more than 10 values because there are multiples of the last entry with the same value. My solution in the previous posting depends on Sort, and simply hides everything after the 10th entry.
If someone else finds a way to list just the first 10 entries in a sorted list, please post. I don't like my solution too much because I can't further drill-down into the entries.
sql