Incorrect Syntax near '=' SSRS Report Builder 3.0

1
votes

sorry if this has been asked and answered before but I have not been able to find an answer that solves me particular problem.

I get the following message when trying to run an ssrs query and I am not sure how to correct it. Normally the error refers to an extra space or whatever that can be picked up from copy and pasting but I can't find it if there is one and I'm not sure what else the problem could be. Here is the error message followed by the query. Any help/comments would be much appreciated

TITLE: Microsoft SQL Server Report Builder

An error occurred while executing the query. Incorrect syntax near '='.

ADDITIONAL INFORMATION:

Incorrect syntax near '='. (Microsoft SQL Server, Error: 102)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=09.00.4060&EvtSrc=MSSQLServer&EvtID=102&LinkId=20476

------------------------------"

SELECT 
company.cmp_name
,commodity.cmd_code
,commodity.cmd_name
,IIF(vTTSTMW_ChargeDetails.[Delivery Year]=@DeliveryYear, SUM(vTTSTMW_ChargeDetails.Charge),0) AS [Revenue Current Year]
,IIF(vTTSTMW_ChargeDetails.[Delivery Year]=@DeliveryYear-1, SUM(vTTSTMW_ChargeDetails.Charge),0) AS [Revenue Prior Year]
FROM
company
LEFT OUTER JOIN vTTSTMW_ChargeDetails
ON company.cmp_id = vTTSTMW_ChargeDetails.[Bill To ID]
LEFT OUTER JOIN commodity
ON vTTSTMW_ChargeDetails.[Order CMD Code] = commodity.cmd_code
GROUP BY
company.cmp_name
,commodity.cmd_code
,commodity.cmd_name
2
sql-serverreporting-servicesssrs-2012reportbuilder3.0
Can you run the query in SSMS? – DeanOC
IIF became available in SQL 2012. It is not valid in 2008. – Tab Alleman
No, unfortunately it gives me the same error. – Tom
Sorry I missed tagged this it is 2012 I will get that changed. – Tom

2 Answers

1
votes

If you are using an earlier-than-2012 version of SQL, try changing this:

,IIF(vTTSTMW_ChargeDetails.[Delivery Year]=@DeliveryYear, SUM(vTTSTMW_ChargeDetails.Charge),0) AS [Revenue Current Year]
,IIF(vTTSTMW_ChargeDetails.[Delivery Year]=@DeliveryYear-1, SUM(vTTSTMW_ChargeDetails.Charge),0) AS [Revenue Prior Year]

to this:

,SUM(CASE WHEN vTTSTMW_ChargeDetails.[Delivery Year]=@DeliveryYear THEN vTTSTMW_ChargeDetails.Charge ELSE 0 END) AS [Revenue Current Year]
,SUM(CASE WHEN vTTSTMW_ChargeDetails.[Delivery Year]=@DeliveryYear-1 THEN vTTSTMW_ChargeDetails.Charge ELSE 0 END) AS [Revenue Prior Year]

EDIT

And try changing this:

@DeliveryYear-1

To this:

CONVERT(nvarchar(7), CAST(@DeliveryYear AS int)-1)
0
votes

You might need to put the aggregation outside the IIF statement, like this:

SELECT 
      company.cmp_name
    , commodity.cmd_code
    , commodity.cmd_name
    , SUM(
        IIF(
            vTTSTMW_ChargeDetails.[Delivery Year] = @DeliveryYear, 
            vTTSTMW_ChargeDetails.Charge, 
            0
            )
        ) AS [Revenue Current Year]
    , SUM(
        IIF(
            vTTSTMW_ChargeDetails.[Delivery Year] = @DeliveryYear - 1, 
            vTTSTMW_ChargeDetails.Charge, 
            0
            ) 
        ) AS [Revenue Prior Year]
FROM
    company
    LEFT OUTER JOIN vTTSTMW_ChargeDetails ON company.cmp_id = vTTSTMW_ChargeDetails.[Bill To ID]
    LEFT OUTER JOIN commodity ON vTTSTMW_ChargeDetails.[Order CMD Code] = commodity.cmd_code
GROUP BY
      company.cmp_name
    , commodity.cmd_code
    , commodity.cmd_name