generating an SSRS report without hard-coding a query

Question

Hello,

I've just started working with SSRS 2008 and SQL Server 2008. I am trying to create a report that involves querying about certain sectors and their sub sectors (for example a sector would be something like 'financial sector' and its sub sectors would 'bank', 'transfer'...etc ). However, the sectors and sub sectors are changing somewhat rapidly, and I don't know how to generate a report without hard-coding the sectors and sub sectors. What I would like to do is something like a for loop maybe where I can first query the sectors and then for each sector, query its sub sectors. Does anyone have an idea how to do it?

Answer 1

Don't use a loop, use a join instead. For example, say you want to find the number of companies in the 'financial' sector, but you only record subsector against a company - try the following:

select count(*)
from dbo.company c, dbo.sector s
where c.subsector = s.subsector and s.sector = 'financial'

To extend this to a report, try using something like the following query as the basis of a report:

select c.name company_name, s.subsector, s.sector
from dbo.company c, dbo.sector s
where c.subsector = s.subsector and 
s.sector like @sector and 
s.subsector like @subsector

If sector and subsector are always populated, then entering % against each of the parameters will return all companies; entering financial against sector and % against subsector will return all companies in the financial sector; and so on.