SSRS 2008 Query Parameters

Question

I have a report that needs to display accounting data from different locations or all locations.

Table_sales:

PartId    Rev     LocId
1         $10     1
2         $2      1
3         $5      2

Lets say the query is something basic like this to get all revenue:

SELECT SUM(rev) FROM Table_sales

If I want to be able to limit the report to a particular Location I would have to change it to:

SELECT SUM(Rev) FROM Table_sales WHERE LocId = @param

But if I did that how would I get all locations without having multiple queries?

FYI the SQL 2008 Filter option doesn't seem work on the actual query I am using since the actual query uses group by functions.

Also SSRS 2008 DataSet queries can be expressions, but I can't seem to get that to work.

Answer 1

I'm not sure about the filter options or dataset queries you mentioned in SSRS 2008, but in some of the older versions of SSRS, I've seen stuff like this:

SELECT SUM(Rev) FROM Table_sales WHERE LocId = @param or 'All' = @param

I'm doubtful that it's the most efficient way to do this, but it allows you to have 1 query where you can select either all results (by passing the term 'All' as the parameter) or just the LocId's you want (by passing the appropriate LocId as the parameter).

Answer 2

Maybe you can use that:

SELECT SUM(Rev) FROM Table_sales WHERE LocId IN (@param)