question

Kev Riley avatar image
Kev Riley asked

Filtering and sorting in SSRS report not working together

I have a simple, one-table report in SSRS 2005 (**EDIT this originally said SSRS 2205, hence the ribbing below**) with 2 basic requirements, and I can do both separately, but combining them I get into a whole heap of bother..... The report is listing all the payments made for the given dates, these are passed as params to a proc, and that returns payment date, payment type, amount, blah, blah, ... First up I have a filter on payment type (credit card, amex, debit card etc). This is implemented as a multi-value parameter and applied to the detail row of the table (although it forces me to create a grouping first - don't quite get that but hey-ho). So using this I can select all or some of the payment types, and the data is magically filtered! Second, the user asked for sorting capabilities (coincidentally on the payment type column), so I thought 'Aha! Interactive Sorting!'. Right-clicked the 'Payment Type' column header, choose the 'Interactive Sort', specify the expression as the data field. All happy. Run the report, and I get the up/down arrows on the column header, but alas they do nothing! If I remove the filter (and grouping), then the sort works fine, but together something is treading on something else's toes, and they're not playing happy..... Anybody had similar experiences? [I find a lot of this in SSRS, there seems to be easy ways to do things, but when they don't quite work, you've got no idea why.......] Also whilst I'm here, why do I have to put the filter on the detail row (and subsequently create a grouping) when there is a filter tab on the table properties, but that seemed to fail me too :(
sql-server-2005ssrsreportingbids
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Fatherjack avatar image
Fatherjack answered
Damn you are picky! ;) Is there a scope for the column sort? If so then that might need expanding to cover the whole report dataset. I dont use interactive sort much so cant talk from experience I'm afraid. PS, is this an early access version of SSRS? - 2205 ?? - you must know the right people!
3 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

2205 is funny:-) I kind of tried to picture myself working on SQL Server on 2205...and its not pleasant:-)
0 Likes 0 ·
I'm so ahead of my time, I scare myself :)
0 Likes 0 ·
@Fatherjack - yeh it is something to do with scope I'm sure. Have put it on the back burner for a while, gave the user the option of either or none, and they where happy with that for the mo. Cheers!
0 Likes 0 ·
Mivoat avatar image
Mivoat answered
Re: [I find a lot of this in SSRS, there seems to be easy ways to do things, but when they don't quite work, you've got no idea why.......] Yep - I'm finding much the same. It seems they'd rather you sat there scratching your head for hours instead of admitting: "Sorry guys, we haven't got that bit working properly yet." Or maybe they haven't noticed. The good news is it seems most of the problems are in BIDS rather than the Report Server. Mind you - I can't get a group sort to work interactively and can't tell what's at fault in that case.
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Re: I can't get a group sort to work interactively and can't tell what's at fault in that case. I've just realised my Textboxes that defined interactive sort were not outside the group scope trying to be sorted. Now I've moved them outside and interactive group sort works fine. Mind you, it would have been nice to see an error msg. Miv
0 Likes 0 ·
Ariski avatar image
Ariski answered
I have solved a similar problem like this by making a hidden Chart outside the group, that helps the Interactive Sorting to think that the data are not grouped. http://stackoverflow.com/questions/1003106/interactive-sort-over-multiple-groups-in-ssrs/30961287#30961287
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.