July 7, 2011 at 8:30 am
Hi
I have a report which has 3 parameters.
Each of them users can select multiple values. A microsoft problem is when creating a subscription on the reporting site, when selecting the parameters there is not option to select all. There users have to select each and individual field in that parameter.
So I have created the parameters in BIDS 2008 and have the below query for each of them.
select 'ALL' as fieldname
union
select fieldname from tablename
Then in the dataset query I have the below
WHERE (K04.WORDNO_PPS IN (@wordno)) OR
('*' IN (@wordno)) OR
(K04.RESOURCENO_PPS IN (@resource)) OR
('*' IN (@wordno)) OR
('*' IN (@resource)) OR
(dbo.MBF010.PARTNO_WOR IN (@partno)) OR
('*' IN (@partno))
My issue is if I have one parameter it works, but with me having 3 parameters they dont work, everything gets selected no matter which field you select.
Can any please advise.
Thanks
July 8, 2011 at 5:25 am
ok ive managed to get this working with CASE statements, my only issue is I can not select multiple values. I used the statement below, then thought I need to use IN statement rather than like but this still error'd when selecting more than one value....any ideas??
WHERE
case
when @wordno = '*' then 1
when K04.WORDNO_PPS like '%' + @wordno +'%' then 1
else 0
end
= 1
and
case when @partno = '*' then 1
when MBF010.PARTNO_WOR like '%' + @partno +'%' then 1
else 0
end
= 1
and
case when @Resource = '*' then 1
when K04.RESOURCENO_PPS like '%' + @Resource +'%' then 1
else 0
end
= 1
Thanks in advanced
July 8, 2011 at 7:49 am
If I am understanding you correctly, all three parameters are independent, so if a user selects "blah" for @resource and "all" for @partno, you only want the report to return all records where @resource = "blah" and not all records for all values of @resource?
If so, I think you can change your where clause to this
WHERE
(K04.WORDNO_PPS IN (@wordno) OR @wordno = "All")
and
(K04.RESOURCENO_PPS IN (@resource) OR @resource = "All")
and
(dbo.MBF010.PARTNO_WOR IN (@partno) OR partno = "All")
If you want all records to be displayed if a user selects "All" for any of the three parameters then this should work
WHERE
(K04.WORDNO_PPS IN (@wordno) OR @wordno = "All")
or
(K04.RESOURCENO_PPS IN (@resource) OR @resource = "All")
or
(dbo.MBF010.PARTNO_WOR IN (@partno) OR partno = "All")
July 18, 2011 at 2:16 am
Thanks but I already tried this and again it only work with one of the parameters, it doesnt take note of the other 2.
So I got it working with case statements.
My issue is now I can not select multiple values. I can only select one value at a time. for example I can only select Works order 0001 I could not select 0002 and 0003.
I found this issue with your suggestion as well even though I am using IN(@parameter)
Any advise??
Thanks
R
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply