For the longest time I’ve been looking at the filtering options within Reporting Services with disgust. The general rule is you only bring back the data that is needed for a report to the report, so there should never be any reason to apply filtering to the data.
Well, I finally found a use for the filtering options on one of the groupings I have in a report I’ve developed for a client. And… my prejudice against the filtering options has been broken.
The report has a dataset that is returned using GROUP BY and WITH ROLLUP. The data has locations which are rolled up to 3 higher levels. Each of these levels breaks down into months and any location without data still needs to appear in the summary report. The initial view of the report has the months rolled up and the user can then drill-down into any location or level to see the monthly breakdown of the data.
To get all the locations to appear I ended up using LEFT OUTER JOINs but then I ended up with blank rows that would show up in the drill-downs; which of course didn’t make the report look very pleasing to the eye.
Set the hidden property of the monthly break down rows to TRUE when there isn’t monthly data. Reporting Services adjusts the drill-down controls so that the high level of the drill-down appears in the report without the plus/minus icon.