Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
want to use a variable in my IN clause that that holds the different
values (strings) but I can't seem to get it to work. I've posted the
SQL statement as it currently is and what I tried to do to get it to
work.
Thanks for any help in advance.
Mike
Current Statemenet:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotos
What I tried:
SELECT NumOfPhotos.NumberOfPhotos
FROM NumOfPhotos
INNER JOIN vewStatsInnerSummary ON
(vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
WHERE vewStatsInnerSummary.Batch=@.Batch
AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
AND vewStatsInnerSummary.Photoset<>'99'
AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
GROUP BY NumberOfPhotosMike,
The string variable is treated as a single data element, not a list or array
of elements. So, if:
SET @.PhotoSet = '1,23,56,78'
Your query would only return a row with the value '1,23,56,78' in
vewStatsInnerSummary.Batch.
You can slice up the string into a temporary table and then use that table
in your query, or use dynamic SQL, etc.
Vyas has a write up on your issue.
http://vyaskn.tripod.com/passing_arrays_to_stored_procedures.htm
RLF
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>|||The type of (Reporting Services) parameter you are using is for a
multivalued parameter. It would show up as a drop down with several values,
each value having a checkbox next to it. Then, whatever values are checked
will be in your "IN" clause. The top checkbox is used for "Select All".
--
Alain Quesnel
alainsansspam@.logiquel.com
www.logiquel.com
"Mike" <m_gorgone@.hotmail.com> wrote in message
news:1194024918.596959.227710@.y42g2000hsy.googlegroups.com...
> Hi everyone I'm using Reporting Services and in my Dataset's Query I'm
> want to use a variable in my IN clause that that holds the different
> values (strings) but I can't seem to get it to work. I've posted the
> SQL statement as it currently is and what I tried to do to get it to
> work.
> Thanks for any help in advance.
> Mike
> Current Statemenet:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet=@.PhotoSet
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
> What I tried:
> SELECT NumOfPhotos.NumberOfPhotos
> FROM NumOfPhotos
> INNER JOIN vewStatsInnerSummary ON
> (vewStatsInnerSummary.Batch=NumOfPhotos.Batch AND
> vewStatsInnerSummary.PhotoSet=NumOfPhotos.PhotoSet)
> WHERE vewStatsInnerSummary.Batch=@.Batch
> AND vewStatsInnerSummary.PhotoSet IN (@.PhotoSet)
> AND vewStatsInnerSummary.Photoset<>'99'
> AND DATEDIFF(day,@.ReportBeginDate ,statsdate) >=0
> AND DATEDIFF(day,@.ReportEndDate,statsdate) <=0
> GROUP BY NumberOfPhotos
>
