If statement in Reporting Services

Posted on 2011-10-05
Medium Priority
Last Modified: 2012-05-12
I'm trying to add the attached code to a Reporting Services Dataset.  
When I debug the code in SQL Server the error I get is...
   The isnull function requires 2 arguments..
Would you please show me what I'm doing wrong?  
SELECT  IIF (IsNull(YEAR(tblClientInExit.ReIntakeDate)),YEAR(tblClients.OriginalStartDate),YEAR(tblClientInExit.ReIntakeDate)) AS PlaceYear,
DATEPART(quarter, tblClients.OriginalStartDate) AS PlacePeriod, tblClients.OriginalStartDate, 
                      tblAddresses.CountyLkUpID, tblClientInExit.ReIntakeDate
FROM         tblClients INNER JOIN
                      tblAddresses ON tblClients.ClientFileNo = tblAddresses.ClientFileNo INNER JOIN
                      tblClientInExit ON tblClients.ClientFileNo = tblClientInExit.ClientFileNo
ORDER BY PlacePeriod, tblClientInExit.ReIntakeDate DESC

Open in new window

Question by:BobRosas
LVL 21

Accepted Solution

Jason Yousef, MS earned 500 total points
ID: 36919886
IIF is a SSRS function and not a T-SQL one...
the T-sql equivalent is CASE

Also ISNULL is a T-sql function that uses 2 arguments, example:  Isnull('Year', 2011)  so here if the Year column is null I'm replacing with 2011

Read here about CASE

Author Closing Comment

ID: 36919993
Very helpful!  I got it working.  Thanks

Featured Post

Upgrade your Question Security!

Add Premium security features to your question to ensure its privacy or anonymity. Learn more about your ability to control Question Security today.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Integration Management Part 2
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question