OK. I've got a tough one here. I am attempting to create a parameter .aspx
page that will pass in start date, end date and multiple storeIDs to the
report. A section of my query in the report dataset looks like this:
WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
@.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
The problem is at the end with the @.paramStore. It works if you pass it
just one StoreID. The syntax becomes ...StoreID IN ('1')
When you try to pass it more than one storeid, it blows up. The syntax
becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
convert '1,2' to datatype int. Is there a way to take these leading and
trailing apostrophes off or can you think of a workaround? Thanks.You have to do a dynamically generated SQL statement, like this:
= "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
@.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
(" & @.paramStore & "))"
It builds the SQL statement on the fly, so you won't be able to use the
query designer after this. I may not have put the quotes in properly, but I
hope you get the idea.
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> OK. I've got a tough one here. I am attempting to create a parameter
> .aspx
> page that will pass in start date, end date and multiple storeIDs to the
> report. A section of my query in the report dataset looks like this:
> WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> The problem is at the end with the @.paramStore. It works if you pass it
> just one StoreID. The syntax becomes ...StoreID IN ('1')
> When you try to pass it more than one storeid, it blows up. The syntax
> becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> convert '1,2' to datatype int. Is there a way to take these leading and
> trailing apostrophes off or can you think of a workaround? Thanks.|||Thanks Jeff but I can't seem to get this to work. It changes my quotation
marks to brackets and comes up with error "Identifier expected."
"Jeff A. Stucker" wrote:
> You have to do a dynamically generated SQL statement, like this:
> = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN
> (" & @.paramStore & "))"
> It builds the SQL statement on the fly, so you won't be able to use the
> query designer after this. I may not have put the quotes in properly, but I
> hope you get the idea.
> Cheers,
> '(' Jeff A. Stucker
> \
> Business Intelligence
> www.criadvantage.com
> ---
> "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > OK. I've got a tough one here. I am attempting to create a parameter
> > .aspx
> > page that will pass in start date, end date and multiple storeIDs to the
> > report. A section of my query in the report dataset looks like this:
> >
> > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> >
> > The problem is at the end with the @.paramStore. It works if you pass it
> > just one StoreID. The syntax becomes ...StoreID IN ('1')
> >
> > When you try to pass it more than one storeid, it blows up. The syntax
> > becomes ...StoreID IN ('1,2') and an error comes up saying that it cannot
> > convert '1,2' to datatype int. Is there a way to take these leading and
> > trailing apostrophes off or can you think of a workaround? Thanks.
>
>|||My advice in this situation is to back up and make sure you can create the
appropriate string.
Create a report that has the report parameters and a textbox and nothing
else. In the textbox put in the expression. Now you should be able to copy
and paste the result into query analyzer and it should work. Sometimes just
seeing the result will let you know what you are doing wrong.
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"BrianW" <BrianW@.discussions.microsoft.com> wrote in message
news:7236F404-0AB1-44C6-97A1-A601A1E2B738@.microsoft.com...
> Thanks Jeff but I can't seem to get this to work. It changes my quotation
> marks to brackets and comes up with error "Identifier expected."
> "Jeff A. Stucker" wrote:
> > You have to do a dynamically generated SQL statement, like this:
> >
> > = "SELECT ... WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN
> > @.paramStartDate AND > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID
IN
> > (" & @.paramStore & "))"
> >
> > It builds the SQL statement on the fly, so you won't be able to use the
> > query designer after this. I may not have put the quotes in properly,
but I
> > hope you get the idea.
> >
> > Cheers,
> >
> > '(' Jeff A. Stucker
> > \
> >
> > Business Intelligence
> > www.criadvantage.com
> > ---
> > "BrianW" <BrianW@.discussions.microsoft.com> wrote in message
> > news:9FF1DC3A-463D-41BB-9130-631A5D0600FF@.microsoft.com...
> > > OK. I've got a tough one here. I am attempting to create a parameter
> > > .aspx
> > > page that will pass in start date, end date and multiple storeIDs to
the
> > > report. A section of my query in the report dataset looks like this:
> > >
> > > WHERE (dbo.SalesCheckDetails.SalesDate BETWEEN @.paramStartDate AND
> > > @.paramEndDate) AND (dbo.SalesCheckDetails.StoreID IN (@.paramStore))
> > >
> > > The problem is at the end with the @.paramStore. It works if you pass
it
> > > just one StoreID. The syntax becomes ...StoreID IN ('1')
> > >
> > > When you try to pass it more than one storeid, it blows up. The
syntax
> > > becomes ...StoreID IN ('1,2') and an error comes up saying that it
cannot
> > > convert '1,2' to datatype int. Is there a way to take these leading
and
> > > trailing apostrophes off or can you think of a workaround? Thanks.
> >
> >
> >
