Top 20 filter on a group

[dianemarie]

Hello, I have a report with two groups. I am summing a detail field on Group 2 (calculated field), and only displaying the Group 2 row, with Group 2 sorted in descending volume by the summed field. The summed field is =sum(Fields!Qty.value). I want to filter the top 20 by this row and field. It seems like it should be easy enough. I Edit Group 2, click on the Filter tab, do an expression for the field sum, select Top N for the operator and 20 for the Value. I tried selecting the detail field first for the expression, then I tried an expression of =Sum(Fields!Qty.value) in the Expression field, and I also tried pulling in the field from the Datasets so that it looks like this: =Sum(Fields!Qty.Value, "DMIReport") I get this error when I run the report: "An error occurred during local report processing. An error occurred during report processing. Failed to evaluate the FilterValue." Does anyone see anything obviously wrong that I'm doing? Is there a way to filter on the Group 2 expression for Top 20 in the SQL query maybe? Thanks much.

 

[RiverGuy]

It works for me. Are you sure you haven't selected the Tablix/Table itself and set the filter for the whole thing rather than just the group?

 

[dianemarie]

Hi RG, I'm definitely filtering the group. Did you just select the field name for the expression (=fields!qty.value) or do an expression of =sum(fields!qty.value)? I'm still messing around with it. So what I'm getting from your reply is that yes, it should work.

 

[RiverGuy]

I did the aggregate expression, without the dataset context. So in my case it was

=SUM(Fields!SalesAmt.Value)

 

[dianemarie]

Got it to work. The piece I was missing was in my Values box, I was just keying 20. It has to be =20. Once I put in the equal sign, it worked.