SSRS 2005 question

Matt_Hirst · 23 Aug 2011 at 19:32

I have set a filter in Data, the filter is:

=@ItemNo

This works fine in that I can specify an item number and it prints that number out, question is it possible to include a wildcard in the filter so that if the @ItemNo - nothing then use * to extract all records.

Have tried using:

=IIF((@ItemNo)="",*,@ItemNo)
=IIF(isnull(@Itemo),*,@ItemNo)

neither of these work.

Am currently using:

Like N'%' + @ItemNo + N '%'

and this offers some flexibility in the output, just not exactly what I am after.

Anyone help?, Am very new to SSRS.

regards,

Matt

HTMLHugo · 23 Aug 2011 at 23:40

AND (ItemNo LIKE '%'+ ? + '%') or similar

sorry my ssrs skills are very rusty

cotton31 · 25 Aug 2011 at 10:25

I'd tend to do this in your SQL:

Code:

WHERE ((ItemNoField = @ItemNo) OR (@ItemNo IS NULL))

which basically says return item numbers matching @ItemNo or everything if @ItemNo is left blank.

fretted · 25 Aug 2011 at 21:55

+1 but cut out the some (or even all) of the brackets
eg.
WHERE (ItemNoField = @ItemNo or @ItemNo is NULL)

Matt_Hirst · 26 Aug 2011 at 12:44

I ended up adding a little bit of code behind the report.

Thanks for everyones help.

Matt