What I wanted to do is to limit the results of a table to a TOP 10, in other words, the highest 10 values. I didn't want to do it on the server side, because it would have meant running again the same query. I had the detailed resultset already available in my report.
I was surprised to see how simple it is. Reporting Services is full of good surprises indeed.
My table was a table of payed fines. I had to make the TOP 10 fines per reason. So I made a table, Grouped by Reason. In the group properties, tab Sorting, I did a descending sort on this expression
to have the highest values first. Then, in the Filter tab, added this filter:
Expression : =Fields!Reason.Value Operator : TOP N Value : =10
the = (equal) before 10 in the value is important, as it requires an expression in this box.
Then, in the columns of the detail section of the table, I added a Reason Name, Count of cases and percentage of cases, with the following expressions :
Reason : =Fields!Reason.Value Cases : =Count(Fields!FineId.Value) % : =Count(Fields!FineId.Value) / ReportItems!txtReasonCountTotal.Value
To format your labels in percentage, use the format “P0”, the 0 stands for the number of decimals. Obviously… “P1” would allow you to show one decimal.
For unknown reasons to me, certainly depending of the percentage formatting, As soon as you format the label of an axis as percentage, the figure seems to be automatically multiplicated by 100. So, I was using a formula like
=(count(smth) / SomeTotal)*100
to calculate the percentage as data value. So, I removed the *100, and entered in the axis property, a major gridline interval at 0.1, otherwize Reporting Services didn't seem to make a good job as automatically putting the gridlines. Probably it doesn't want to go below 1.
To nicely format a pie chart, you can show the labels in the chart, linked to the slice by a line. To do so, uncheck the “Show Legend” check box in the Legend tab, and in the Data tab, edit the Data Value. Then, in the Point Labels tabe, check “Show Point Labels”. Uncheck the “auto” position, otherwize the label will appear inside the slice, and select a border.
Here is an example of a nice label, showing the value name, and the percentage value of the slice
=Fields!MyField.Value & VbCrLf & Format(Count(Fields!MyField.Value) / ReportItems!SomeTotal.Value), "P2")