SQL Reporting Services - Parameters

Hi,

I am seriously stuck in SQL Reporting Services. And don't know where to go and what to do in order to achieve what I want to achieve.

1. In my report I have the following parameters. SiteID, Document Name, Document Location and Action. These values are picked from the database through queries and filled in the parameters.

2. I want the user to be able to view the report for 'Any', 'All' and/or 'None' of the values in the parameter.

e.g.
2.a. The user can generate the report and return all values.
2.b. The user can generate the report with a particular 'SiteID' but all Document Locations, Document Names and Actions.
2.c. The user can generate the report for 'All' SiteIDs, but one Document Location, 'All' Documents and 'All' Actions.

etc.

I was trying this out with parameters. But it wasn't working. Meaning, first of all I wasn't able to get 'All' from the database.

Can someone please help me out, or at least point me in the direction where I should be looking, since I have run out of options.

Hi AmadUddin,

You can create parameters of your reports in SSRS whose data sources are datasets created in the data tab of the report.

In this dataset, by modifying your sql codes you can add "ALL" by adding an extra record which has a description "ALL" and a value "0" or "-1", etc.

I mean, if you set country parameter by

SELECT CountryId, Code FROM Countries

modify your script like

SELECT CountryId, Code FROM Countries
UNION
SELECT 0, 'ALL'

You can further modify the script by adding an order by clause

And where this parameters are sent, you can check the @CountyId parameter passed and if it is 0 set it to null

The easiest way of using such parameters is like

SELECT ....
WHERE
(@CountryId ISNULL OR CountyId = @CountryId)

I hope these will help you start solving the problem.

Eralper
http://www.kodyaz.com