asked
SSRS interactive sorting is collapsing my data groups
Using SSRS 2005. I have a report that has a data grouping, and the report starts with the rows collapsed for the grouping. I also have interactive sorting on the column headers. If I expand the group, then click the sorting arrows, the data group collapses, presumably the report is being re-run with my specified sort order, and the initial collapsed state of the grouping is applied. Any quick workarounds?
I think you are seeing the report refresh with you choice applied so the original format is also re-applied. Nearly everything in SSRS is open to coding so you could write a procedure to alter the hidden/visibleness of the groups when you change the sort order. You could just hijack the sort click event too I expect. Do you have a document map on the report? If that is there do you 'have' to have the groups expanded?
Hmm... coding....is that .net coding.....think I'll use my other workaround : start with the rows expanded! No there is no document map.
Yeah, grit your teeth and get coding, just don't tell anyone you wrote something that started with Sub and ended End Sub instead of BEGIN TRAN!!! Oh, bung a document map in there and see what happens, it may expand the group when you select the group from the map. Not sure, just something I'd try..
From reading in a SSRS book I have, I'm thinking that you may be sorting the records with your SQL statement. But according to my book, that should be avoided with "dynamic data reordering" so that your report "isn't working against the database engine." **Edit>** here is another possibility: This [brief article][1] talks about Entity vs Value groups. [1]:
http://blogs.msdn.com/b/bobmeyers/archive/2006/12/20/getting-grouping-right-in-report-builder.aspx
@Mark, thanks for that - no I'm not sorting within the SQL statement, it's purely the interactive sort over the data. Also that link, whilst useful, talks about Report Builder - that's not what I'm using here.
