Hi Ric,
I have built table functions (in MS SQL) in order to deal with the parsing of the strings passed to the Stored Procedure, I have one for parsing integers (Ids) separated by a comma.
As the string (containing the Ids) can also contain “(none)” or “All” or “(All)” depending on the selection in Dundas, you’ll have to deal with those values in the query.
Here is the function:
Create function [dbo].[ExtractIdTable](@ChaineCsv nvarchar(MAX), @Separateur varchar(2) = ‘,’)
returns @tElement table (Element int)
as
begin
declare @Position int, @Element nvarchar(MAX)
If (@ChaineCsv = ‘Null’ or @ChaineCsv = ‘(none)’ or @ChaineCsv = ‘All’ or @ChaineCsv = ‘(All)’)
Begin
return
End
while len(@ChaineCsv) > 0
begin
set @Position = charindex(@Separateur, @ChaineCsv, 1)
if @Position = 0
begin
set @Element = @ChaineCsv
set @Element = Ltrim(RTrim(@Element))
set @ChaineCsv = ‘’
end
else
begin
set @Element = left(@ChaineCsv, @Position - 1)
set @Element = Ltrim(RTrim(@Element))
set @ChaineCsv = substring(@ChaineCsv, @Position + 1, Len(@ChaineCsv) - @Position)
end
insert into @tElement (Element) values (CAST(@Element AS int))
end
return
end
This will return you a table that you can use for filtering.
SELECT Table1.* FROM Table1
WHERE IIF(@TheStringContainingTheIds = ‘All’, 1=1,IIF(@TheStringContainingTheIds = ‘(All)’, 1=1, IIF(@TheStringContainingTheIds = ‘(none)’, Table1.TheForeignIdYouNeedToFilterOn = NULL, Table1.TheForeignIdYouNeedToFilterOn IN (SELECT Temp.[Element] FROM [dbo].[ExtractIdTable(@TheStringContainingTheIds, ‘,’) AS Temp))))
In Dundas, you assign a Hierarchy on the Bridge Parameter that corresponds to the table your Foreign Key is pointing to, and set this code on the Bridge Parameter:
CollectionMemberValue memberValue = (CollectionMemberValue)$input$.Resolve();
var commaDelimitedString = string.Empty;
//Pass “All” string if user selected “All” checkbox or “All” token
if (memberValue.Token != null && memberValue.Token == PredefinedToken.All)
{
commaDelimitedString = “All”;
}
else
{
for (int i = 0; i < memberValue.Values.Count; i++)
{
if (commaDelimitedString.Length == 0)
{
commaDelimitedString = memberValue.Values[i].UniqueName.Replace(".A", “”);
}
else
{
//concatenate all selected values to a comma-delimited string
commaDelimitedString = string.Format("{0},{1}", commaDelimitedString, memberValue.Values[i].UniqueName.Replace(".A", “”));
}
}
}
return new SingleStringValue($id$, commaDelimitedString);
I hope this helps,
Olivier
