multiple query reports with SSRS

Question

Hi, I want my report to display results from two select statements. Basicly, one select statement is active users, and the other is non-active users.

How do I structure the data tab of the report (I am using SSRS-2005)?

Thanks

Frank

Answer 1

Two ways you could approach this:

1. Have two queries in your "Data" tab - one for active users and one for inactive users. Then on your report simply drop two tables and point one of them at the "active users" DataSet and the other at the "inactive users" DataSet.

2. Have the one query which returns all users, active or not, along with a column for their "active" status. Now drop two tables on your report and point them both at this single DataSet. Then, in the properties of each table, set up the filter so that one table only shows active users and the other shows inactive ones.

I don't I'd favour one of these approaches over the other, but I'd probably lean towards the second approach since it's only one round trip to the database. The overhead of filtering during the report rendering might not make it worth it, though - you'd have to try it and see.