Using query parameters in a Case statement -- sql server reporting services

Denise
5/5/2006 5:18:01 PM

Hello

I am using Reporting Services within Visual Studio .Net 2003, connecting to
a SQL Server 2000 database.

I am trying to use a query parameter within a Case statement but am getting
a syntax error. The query is as follows, with the @StartDate parameter in
the second 'Quarter' case statement:-

SELECT a.ACTUALCLOSE, CONVERT(char(25), a.ACTUALCLOSE, 103) AS ukdate,
a.STATUS, a.DESCRIPTION AS Opportunity, b.ACCOUNT AS 'Account Name',
SUM(h.PRICE) AS Value, e.DESCRIPTION AS Source, a.SALESCYCLE,
CASE WHEN a.TYPE = 'Account Development' THEN 'Acc. Dev.' WHEN a.Type <>
'Account Development' THEN a.Type END AS Type,
CASE WHEN month(@StartDate) BETWEEN 1 AND 3 AND month(a.actualclose)
BETWEEN 1 AND 3 THEN ' 1 ' WHEN month(@StartDate) BETWEEN
1 AND 3 AND month(a.actualclose) BETWEEN 4 AND 6 THEN ' 2 ' WHEN
month(@StartDate) BETWEEN 1 AND 3 AND month(a.actualclose) BETWEEN
7 AND 9 THEN ' 3 ' WHEN month(@StartDate) BETWEEN 1 AND 3 AND
month(a.actualclose) BETWEEN 10 AND 12 THEN ' 4 ' WHEN month(@StartDate)
BETWEEN 4 AND 6 AND month(a.actualclose) BETWEEN 1 AND 3 THEN ' 4 ' WHEN
month(@StartDate) BETWEEN 4 AND 6 AND month(a.actualclose)
BETWEEN 4 AND 6 THEN ' 1 ' WHEN month(@StartDate) BETWEEN 4 AND 6 AND
month(a.actualclose) BETWEEN 7 AND
9 THEN ' 2 ' WHEN month(@StartDate) BETWEEN 4 AND 6 AND
month(a.actualclose) BETWEEN 10 AND 12 THEN ' 3 ' WHEN month(@StartDate)
BETWEEN 7 AND 9 AND month(a.actualclose) BETWEEN 1 AND 3 THEN ' 3 ' WHEN
month(@StartDate) BETWEEN 7 AND 9 AND month(a.actualclose)
BETWEEN 4 AND 6 THEN ' 4 ' WHEN month(@StartDate) BETWEEN 7 AND 9 AND
month(a.actualclose) BETWEEN 7 AND
9 THEN ' 1 ' WHEN month(@StartDate) BETWEEN 7 AND 9 AND
month(a.actualclose) BETWEEN 10 AND 12 THEN ' 2 ' WHEN month(@StartDate)
BETWEEN 10 AND 12 AND month(a.actualclose) BETWEEN 1 AND 3 THEN ' 2 ' WHEN
month(@StartDate) BETWEEN 10 AND 12 AND
month(a.actualclose) BETWEEN 4 AND 6 THEN ' 3 ' WHEN month(@StartDate)
BETWEEN 10 AND 12 AND month(a.actualclose) BETWEEN 7 AND
9 THEN ' 4 ' WHEN month(@StartDate) BETWEEN 10 AND 12 AND
month(a.actualclose) BETWEEN 10 AND 12 THEN ' 1 ' END AS Quarter
FROM OPPORTUNITY a LEFT OUTER JOIN
ACCOUNT b ON a.ACCOUNTID = b.ACCOUNTID LEFT OUTER JOIN
LEADSOURCE e ON a.LEADSOURCEID = e.LEADSOURCEID LEFT OUTER JOIN
OPPORTUNITY_PRODUCT h ON a.OPPORTUNITYID = h.OPPORTUNITYID
WHERE (a.STATUS = 'Closed - Lost ') AND (a.ACTUALCLOSE BETWEEN @StartDate
AND @EndDate) AND (a.TYPE <> ' Re - tender ')
GROUP BY a.STATUS, a.ACTUALCLOSE, a.DESCRIPTION, b.ACCOUNT, e.DESCRIPTION,
a.TYPE, a.SALESCYCLE
ORDER BY a.SALESCYCLE, quarter, b.ACCOUNT

When I try to run the query in the Data view I get 'ADO Error: Syntax error
or access violation'. Is this because I'm using the parameter in this way?
Can this not be done?

If anyone could help solve the problem, or suggest an alternative approach,
I'd be grateful.

Many thanks

Amarnath
5/6/2006 2:19:01 AM

Hi,
No problem of giving parameters like this. Check the username and password.
test the connection while creating the data source.
Try running the query in sql query analyzer with the same user.
Lastly put this in a stored procedure and execute it in data tab.
Moreover you can shorten the case when statement with just 4 instead of 16.

Amarnath

[quoted text, click to view]

toolman
5/8/2006 6:27:25 AM

To shorten your case statement you could use the DATEPART() function.
For example:
CASE WHEN DATEPART(quarter,@StartDate) = DATEPART(quarter,ActualClose)
THEN '1'
WHEN DATEPART(quarter,@StartDate) + 1 = DATEPART(quarter,ActualClose)
THEN '2'
WHEN DATEPART(quarter,@StartDate) + 2 = DATEPART(quarter,ActualClose)
THEN '3'
WHEN DATEPART(quarter,@StartDate) + 3 = DATEPART(quarter,ActualClose)
THEN '4'
END

Denise
5/8/2006 12:22:06 PM

Hi Amarnath

Thanks for your reply. The connection details are fine: the report was
running without error until I added the date parameters into the Case
statement. I had also already created a SP. This doesn't produce an error,
but doesn't return any records either (there should be around 52, based on
my database). Also, when I substitute an actual date for the parameter,
Reporting Services doesn't error, but again does not return any records.

You mentioned I could cut the Case statement down. How would I do that?

Thanks

Denise

[quoted text, click to view]

Denise
5/9/2006 4:03:23 PM

Thanks very much for this, toolman. It looks good, but I'm still getting
the syntax error when I try to run it within Reporting Services. Does
anyone have any ideas?

Thanks

Denise

[quoted text, click to view]

Denise
5/16/2006 12:00:00 AM

Hello

I managed to get round this by using a stored proc within RS. I had been
building the query within RS, and was still getting the error. Running the
SP instead works perfectly.

Thanks for al your help.

Denise

[quoted text, click to view]

Hi Amarnath

Thanks for your reply. The connection details are fine: the report was
running without error until I added the date parameters into the Case
statement. I had also already created a SP. This doesn't produce an error,
but doesn't return any records either (there should be around 52, based on
my database). Also, when I substitute an actual date for the parameter,
Reporting Services doesn't error, but again does not return any records.

You mentioned I could cut the Case statement down. How would I do that?

Thanks

Denise

[quoted text, click to view]

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about sql server reporting services

sql server reporting services : Using query parameters in a Case statement