Group Filter

• Carol Adams

  SSCrazy

  Points: 2320

  More actions

  December 1, 2009 at 12:54 pm

  #228281

  I have a report that contains 1 group (CUSTOMER).

  My detail section contains an ID field and CUSNUM.

  The footer of my group contains 1 field =count(Fields!ID).

  What I want to do is only print the top 20 customers who have the most "ID"'s.

  I tried adding a filter to the group =count(fields!id,fields!cusnum) TOP N 20 and it errors.

  Any ideas?

• ezytime

  SSCommitted

  Points: 1846

  More actions

  December 1, 2009 at 4:47 pm

  #1086190

  Hi,

  If the error is something like "failed to evaluate filter expression" then it's likely due to the fact that SSRS apparently does not implicitly cast numeric values. Try using the expression =CInt(20) in your Top N Value, rather than just 20.

  Furthermore, your group filter expression may be generating an error - you've written =count(fields!id,fields!cusnum) but I believe SSRS would interpret a second argument in such an expression as a Scope. Try changing it to a single field: =Count(Fields!ID.Value)

• Carol Adams

  SSCrazy

  Points: 2320

  More actions

  December 2, 2009 at 6:07 am

  #1086412

  The CInt(20) worked. I also took off the 2nd parameter on the Count function. I thought the 2nd parameter was for grouping, but I didn't really need it anyway as I have a group on the report and that is where I placed the filter.

  Thanks so much for your help...I am still learning SSRS.

  Any recommendations on a book that I could get. I want something that gives me more than the basics of setting up a report...I want to know the extra tips and tricks like the one you showed me.

• ezytime

  SSCommitted

  Points: 1846

  More actions

  December 2, 2009 at 5:37 pm

  #1086834

  Any recommendations on a book that I could get.

  Maybe someone else can help you there - I am learning SSRS through doing it.

  Glad to help though!