SQL Reporting services 2005

wwestendorp

I have a question,

If I have e table where in the filter i can select all or just a single item. How should I do that (NOT using the Multi Value) option in the parameters?

Slawek_Guzek

Hi,

You need to add extra one value to your drop-down parameter values list, called '_all_' for example (whatever, but unique to the rest of values), and in SQL query build your WHERE clause like that:

WHERE (field=parameter) OR (parameter='_all_')

This is only one of many possible solutions.

Regards,
Slawek

wwestendorp

I found on the web another solution which is more or less the same, thanks.

"All" Parameter Value
Question:
How can I enable users to select "All" as the value of a query parameter?

Answer:
The simplest way is to modify your query to accept nulls and map "All" to null.

Step 1: Start with a report that already has a parameterized query and Valid Values queries.

Step 2: Edit your base query to account for nulls.
Whever you have something like this:
... AND Field = @Parameter ...
Replace it with this:
... AND (Field = @Parameter OR @Parameter is NULL) ...

Step 3: Edit your Valid Values queries to insert an explicit "All" and separate ids from labels
For example, for this original query:
SELECT name FROM productcategory
Change it to this:
SELECT name as ID, name as Label FROM productcategory
UNION
SELECT Null as ID, 'All' as Label

Step 4: Update the report parameters
Mark each parameter as nullable
Change the valid values settings to account for the new field names (if needed).

Step 5 (Optional): Update Valid Values queries to account for nulls.
If you have hierarchical parameters, in some cases, you may want the dependent parameters to be independently selectable when the parent parameter's value is "All". In this case, repeat step 2 for each of your Valid Values queries. Note: Don't do this if you have extremely large numbers of possible values, as performance will suffer and your users wouldn't be able to nagivate an excessively long list anyhow.

garak

you can download from the M$ Site a SSRS package for navision. These are basic examples.

Regards