If statement in Reporting Services

Posted on 2011-10-05
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

    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

    Very helpful!  I got it working.  Thanks

    Featured Post

    PRTG Network Monitor: Intuitive Network Monitoring

    Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

    Join & Write a Comment

    Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…
    In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor ( If you're interested in additional methods for monitoring bandwidt…

    734 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

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now