Dynamic Parameters and using an "All" Value

I have a report that I need to create a parameter of Territories which I can
do with the following Data set...
Territories>>
Select Name
From dbo.territory
and
Contacts>>
SELECT TER.Name AS Territory, CAST(CON.ContactId AS varchar(36)) AS
ContactID, CON.FirstName, CON.LastName, CON.JobTitle, CAD.Line1, CAD.City,
CAD.StateOrProvince, CAD.PostalCode, CON.Telephone1 AS
businessphone, CON.MobilePhone, CON.Telephone3 AS rangephone,
CAST(ACT.AccountId AS varchar(36)) AS AccountID,
ACT.Name AS companyname, CAD.StateOrProvince AS company_stateorprovince
FROM ContactBase CON LEFT OUTER JOIN
CustomerAddressBase CAD ON CON.ContactId = CAD.ParentId AND CAD.AddressNumber = 1 LEFT OUTER JOIN
Account ACT ON CON.AccountId = ACT.AccountId LEFT
OUTER JOIN
CustomerAddressBase CAA ON ACT.AccountId = CAD.ParentId AND CAD.AddressNumber = 2 LEFT OUTER JOIN
TerritoryBase TER ON ACT.TerritoryId = TER.TerritoryId
WHERE (TER.Name IN (@.Territories))
ORDER BY TER.Name, CAD.StateOrProvince, ACT.Name, CON.LastName
I cannot create an All value and use it in the Parameters, for my parameters
values I was doing non Query and setting them to the name, but for All I
tried Northeast,Southest,West. That does not error out, it just doesn't
return any data. The other values work. Very Bizarre.
Any ideas of what I can do? The territory table uses GUIDs as IDs, So I
cannot easily create an integer which could solve the issue.Hi!
We had the same problem but did this.
In your territories select case do the following:
Select '%' as 'Name', 'All' as 'Label'
UNION
Select Name, Name as 'Label'
From dbo.territory and Contacts
This would give you a table looking like
Name Label
% All
Ottawa Ottawa
etc etc
The you would change the dataset statement to :
WHERE (TER.Name LIKE (@.Territories))
Hope this helps,
Erik
"Jack Bender" wrote:
> I have a report that I need to create a parameter of Territories which I can
> do with the following Data set...
> Territories>>
> Select Name
> From dbo.territory
> and
> Contacts>>
> SELECT TER.Name AS Territory, CAST(CON.ContactId AS varchar(36)) AS
> ContactID, CON.FirstName, CON.LastName, CON.JobTitle, CAD.Line1, CAD.City,
> CAD.StateOrProvince, CAD.PostalCode, CON.Telephone1 AS
> businessphone, CON.MobilePhone, CON.Telephone3 AS rangephone,
> CAST(ACT.AccountId AS varchar(36)) AS AccountID,
> ACT.Name AS companyname, CAD.StateOrProvince AS company_stateorprovince
> FROM ContactBase CON LEFT OUTER JOIN
> CustomerAddressBase CAD ON CON.ContactId => CAD.ParentId AND CAD.AddressNumber = 1 LEFT OUTER JOIN
> Account ACT ON CON.AccountId = ACT.AccountId LEFT
> OUTER JOIN
> CustomerAddressBase CAA ON ACT.AccountId => CAD.ParentId AND CAD.AddressNumber = 2 LEFT OUTER JOIN
> TerritoryBase TER ON ACT.TerritoryId = TER.TerritoryId
> WHERE (TER.Name IN (@.Territories))
> ORDER BY TER.Name, CAD.StateOrProvince, ACT.Name, CON.LastName
> I cannot create an All value and use it in the Parameters, for my parameters
> values I was doing non Query and setting them to the name, but for All I
> tried Northeast,Southest,West. That does not error out, it just doesn't
> return any data. The other values work. Very Bizarre.
> Any ideas of what I can do? The territory table uses GUIDs as IDs, So I
> cannot easily create an integer which could solve the issue.
>sql