Best way to perform dynamic subquery in MS Reporting Services?

Question

I'm new to SQL Server Reporting Services, and was wondering the best way to do the following:

• Query to get a list of popular IDs
• Subquery on each item to get properties from another table

Ideally, the final report columns would look like this:

[ID] [property1] [property2] [Select COUNT(*) FROM AnotherTable WHERE ForeignID=ID]

There may be ways to construct a giant SQL query to do this all in one go, but I'd prefer to compartmentalize it. Is the recommended approach to write a VB function to perform the subquery for each row? Thanks for any help.

Answer 1

I would recommend using a SubReport. You would place the SubReport in a table cell.

Answer 2

Simplest method is this:

select *,
 (select count(*) from tbl2 t2 where t2.tbl1ID = t1.tbl1ID) as cnt
from tbl1 t1

here is a workable version (using table variables):

declare @tbl1 table
(
 tbl1ID int,
 prop1 varchar(1),
 prop2 varchar(2)
)

declare @tbl2 table
(
 tbl2ID int,
 tbl1ID int
)

select *,
 (select count(*) from @tbl2 t2 where t2.tbl1ID = t1.tbl1ID) as cnt
from @tbl1 t1

Obviously this is just a raw example - standard rules apply like don't select *, etc ...

Answer 3

Depending on how you want the output to look, a subreport could do, or you could group on ID, property1, property2 and show the items from your other table as detail items (assuming you want to show more than just count).

Something like

select t1.ID, t1.property1, t1.property2, t2.somecol, t2.someothercol
from table t1 left join anothertable t2 on t1.ID = t2.ID

@Carlton Jenke I think you will find an outer join a better performer than the correlated subquery in the example you gave. Remember that the subquery needs to be run for each row.

Answer 4

@AlexCuse - Yes, totally agree on the performance.

I started to write it with the outer join, but then saw in his sample output the count and thought that was what he wanted, and the count would not return correctly if the tables are outer joined. Not to mention that joins can cause your records to be multiplied (1 entry from tbl1 that matches 2 entries in tbl2 = 2 returns) which can be unintended.

So I guess it really boils down to the specifics on what your query needs to return.

Answer 5

Thanks everyone for the fast replies! It looks like the SubReport will work well, I'd prefer to keep the original query flexible in case I need to add more details later. Thanks again.

Answer 6

To answer the other parts of your question - is a VB function the way to go? No. Absolutely not. Not for something this simple.

Functions are very bad on performance, each row in the return set executes the function.

If you want to "compartmentalize" the different parts of the query you have to approach it more like a stored procedure. Build a temp table, do part of the query and insert the results into the table, then do any further queries you need and update the original temp table (or insert into more temp tables).