Friday, March 23, 2012

Parameter - Default Value - ALL?

H
I need to have a report parameter that Allows the user to select o
Company name but that has a default value as ALL (i.e. will retur
data for ALL companys
How can I do this
Thanks
DarraDarran,
There are lots of ways to do this but this is how I do it:
Stored Procedure to fill dropdown:
SELECT
"<ALL>" AS CompanyName
, NULL AS CompanyID
UNION
SELECT
CompanyName
, CompanyID
FROM
CompanyTable
--
In RS make sure your parameter "Allows Nulls"
--
Now set the parameter Default to "=Nothing"
--
In your data-select stored procedure use this logic:
...
@.CompanyID INT
...
SELECT (fields)
FROM (data table)
WHERE CompanyID = @.CompanyID OR @.CompanyID IS NULL
--
If the user selects "<ALL>" the stored procedure will ignore the parameter
and select everything.
Hope this helps.
Fred
"Darran" wrote:
> Hi
> I need to have a report parameter that Allows the user to select on
> Company name but that has a default value as ALL (i.e. will return
> data for ALL companys)
> How can I do this?
> Thanks!
> Darran
>sql

No comments:

Post a Comment