We help IT Professionals succeed at work.

Adding a Parameter choice or everything

tgfo4927 asked
I have created a report that has a parameter for Vendor ID - but how do I let the user choose a vendor or display all the vendors.  I've tried using * in the query filter but no luck.  The parameter is requiring me to enter a vendor number and returns an error if I leave it blank.
Watch Question

Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Without looking at your report, couple of options:
(1)  Create a new report that has as it's recordsource the same SQL but without the parameter, so it returns all rows
(2)  Create a 'dummy' parameter that menas return all rows (-1, 0 are typical), then change the report's RecordSource so that it does something like this:

    SELECT Everyone
    FROM Vendors
    WHERE (VendorID = @VendorID OR @VendorID = -1)


I don't want to have 2 different reports - The users should have the option to choose a vendor or not.

What do you mean create a dummy parameter?  do I do that in the query?
If you want to let users use * as a signal to return all vendors then your SQL code is like this:

select * from Vendors where (VendorID = @VendorID or @VendorID = '*')

Open in new window

If you want to return all vendors if users just leave the Vendor ID parameter blank then the code is:

select * from Vendors where (VendorID = @VendorID or @VendorID = '')

Open in new window

If you do this, then don't forget to change the @VendorID parameters properties to "Allow blank values", otherwise you'll get and error message if you don't put anything in.


Perfect solution!