Sql Reporting Services Parameter Question

Question

I'm new with SQL Reporting Services 2008 and cannot find out how to do something that should be simple.

What I have is a single select parameter that is populated with a list of salesman from a query. This works great. And I have the dataset query responding to this parameter working well. So my reports for a single salesman are working fine.

My problem is that I would also like to have an option that is ALL Salesman so that the manager can print out a complete list.

Any suggestions on how to do this.

Answer 1

I usually UNION ALL a custom value to the top of my query with a special value that would indicate to my later query that it should not filter.

I usually use NULL, 0, or '%' for this.

Something like:

SELECT 0 [UserId], '(All Users)' [Username]
UNION ALL
SELECT
    [UserId],
    [Username]
FROM
    dbo.[Users]

And then, in the later query:

SELECT * FROM Data WHERE (UserID = @UserID OR @UserID = 0)

Answer 2

Your sales person query probably looks like this:

SELECT SalesPersonName FROM SalesPerson ORDER BY SalesPersonName ASC

You should change it to this:

SELECT 1 as SortOrder, SalesPersonName FROM SalesPerson
UNION SELECT 0 as SortOrder, 'All' ORDER BY SortOrder, SalesPersonName

Now your query will return:

All
Adam
Barry
Steve
...

Now when you pull your data for the report you can do:

WHERE (SalesPersonName = @SalesPersonName OR @SalesPersonName='All')

And make sure you set the default to 'All' if that is what your manager wants.