locked
Report Builder 3.0 IIF Expression RRS feed

  • Question

  • I would like my expression to read: IF the value of Finalsign = 1 and CONT_ACTION =7 then calculate the date (time) difference between the ID (start date) and CONT_TIME (end date).  I would like to only display the count of the documents that were 24 hours from start to finish.

    This is the first part of what I have.

    IIF (Fields!FINALSIGN.Value = 1 then DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value))

    I get the error message:  The Value expression for the textrun 'FINALSIGN1.Paragraphs[0]' contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIF (Expression  As Boolean, TruePart As Object, False Part As Object) As Object'.


    • Edited by dustin5378 Thursday, February 12, 2015 9:21 PM
    Monday, February 9, 2015 5:26 PM

Answers

  • IIf requires 3 parts to be defined:
     Expression to be evaluated
     Return value if expression is true
     Return value if expression is false

    So the error is just saying that you have not told it what to return if Fields!FINALSIGN.value is something other than 1. You can get rid of the error, at least for now by returning empty string ("") or whatever value you would like.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, February 9, 2015 11:19 PM
  • You are not using the correct Syntax for SSRS.

    Refer to https://msdn.microsoft.com/en-us/library/ms157328.aspx

    Try something like the following instead:

    IIF (Fields!FINALSIGN.Value = 1, DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value), "")

    This specifies to return an empty string if FinalSign != 1.

    Tuesday, February 10, 2015 12:18 AM
  • Basically in the SELECT statement, you can use COUNT(DOCUMENT.DID) and GROUP BY your columns and in your WHERE clause you can add the date range.


    A Fan of SSIS, SSRS and SSAS

    Thursday, February 12, 2015 9:34 PM

All replies

  • IIf requires 3 parts to be defined:
     Expression to be evaluated
     Return value if expression is true
     Return value if expression is false

    So the error is just saying that you have not told it what to return if Fields!FINALSIGN.value is something other than 1. You can get rid of the error, at least for now by returning empty string ("") or whatever value you would like.


    "You will find a fortune, though it will not be the one you seek." - Blind Seer, O Brother Where Art Thou
    Please Mark posts as answers or helpful so that others may find the fortune they seek.

    Monday, February 9, 2015 11:19 PM
  • You are not using the correct Syntax for SSRS.

    Refer to https://msdn.microsoft.com/en-us/library/ms157328.aspx

    Try something like the following instead:

    IIF (Fields!FINALSIGN.Value = 1, DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value), "")

    This specifies to return an empty string if FinalSign != 1.

    Tuesday, February 10, 2015 12:18 AM
  • Yosedesh,

    This gets me headed in the right direction, but I guess I am not understaning the numbers that this is returning.

    Is this the sum of all the hours from all documents with FINALSIGN.VALUE=1?

    I would like to only display the count of the values that were 24 hours from start to finish.

    Thanks.

    Tuesday, February 10, 2015 4:06 PM
  • Can you post sample data for your dataset? Tell us what is the results to be displayed.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, February 10, 2015 4:49 PM
  • SELECT
       dbo.Convert_Id_to_Date(DOCUMENT.CLINICALDATE) ,DOCUMENT.FINALSIGN
      ,DOCUMENT.STATUS,DoctorFacility.ListName
      ,DOCTYPES.DESCRIPTION,DOCUMENT.JOBTITLE
      ,PatientProfile.PatientId,DOCUMENT.DID
      ,DOCUMENT.VISDOCID,PatientProfile.searchname
      ,DOCUMENT.SUMMARY,DOCCONTB.CONTB_ACTION
      ,DOCCONTB.CONTB_TIME,DoctorFacility.Ledger
    FROM
      DOCUMENT
      INNER JOIN DOCTYPES
        ON DOCUMENT.DOCTYPE = DOCTYPES.DTID
      INNER JOIN DoctorFacility
        ON DOCUMENT.USRID = DoctorFacility.PVId
      INNER JOIN PatientProfile
        ON DOCUMENT.PID = PatientProfile.PId
      INNER JOIN DOCCONTB
        ON DOCUMENT.SDID = DOCCONTB.SDID
    WHERE
      dbo.Convert_Id_to_Date(DOCUMENT.CLINICALDATE) BETWEEN (@StartDate) AND (@EndDate)
      AND (PatientProfile.searchname <> 'Test')
      AND (DOCTYPES.DESCRIPTION = 'Office Visit'
       OR DOCTYPES.DESCRIPTION = 'Gyn Office Visit'
       OR DOCTYPES.DESCRIPTION = 'OB Office Visit')
      AND (DOCUMENT.JOBTITLE = 'FacultyPhysician'
       OR DOCUMENT.JOBTITLE = 'FellowPhysician'
       OR DOCUMENT.JOBTITLE = 'LicensedMidlevel'
       OR DOCUMENT.JOBTITLE = 'R1_Resident'
       OR DOCUMENT.JOBTITLE = 'R2_Resident'
       OR DOCUMENT.JOBTITLE = 'R3_Resident')
      AND (DOCCONTB.CONTB_ACTION = 0     =Create
       OR DOCCONTB.CONTB_ACTION = 7)      =Final Signature
      AND DoctorFacility.ListName<>'abc'
      AND DoctorFacility.ListName<>'123'

    ORDER BY
      DOCTORFACILITY.LEDGER,DOCTORFACILITY.LISTNAME,PATIENTPROFILE.PATIENTID,DOCCONTB.CONTB_ACTION DESC;

    This is the query.  The provider name will be in the first column, the total of all office visits for a provider in column2, the total of all office visits signed within 24 hours in the 3rd column, and the % of all office visits signed within 24 hours in the 4th column.

    Tuesday, February 10, 2015 5:49 PM
  • Sample data from your dataset is helpful to understand your question. And also tell which data should be displayed and which should be not.

    A Fan of SSIS, SSRS and SSAS

    Tuesday, February 10, 2015 6:37 PM
  • You actually do not need to post the real data. You can make up some. Do you want to display data when it meets the conditions of FINALSIGN = 1 and hour difference between ID and CONTB_TIME < 24?  

    A Fan of SSIS, SSRS and SSAS

    Tuesday, February 10, 2015 8:51 PM
  • Sorry, still very new to this.

    Yes, display data when it meets the conditions of FINALSIGN = 1 and hour difference between ID and CONTB_TIME < 24.  The COUNT of the documents that meet these conditions.

    Thanks. 


    • Edited by dustin5378 Tuesday, February 10, 2015 9:03 PM
    Tuesday, February 10, 2015 8:55 PM
  • IIF (
    	Fields!FINALSIGN.Value = 1 , 
    	IIF (
    		DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value) < 24,
    		DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value),
    		""
    	)
    	 
    	""
    )


    A Fan of SSIS, SSRS and SSAS

    Tuesday, February 10, 2015 9:04 PM
  • I still get the same error as before when I run this.

    The Value expression for the textrun 'FINALSIGN1.Paragraphs[0]' contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIF (Expression  As Boolean, TruePart As Object, False Part As Object) As Object'


    Do we need a comma before the second set of empty paranthese near the end of the expression?
    • Edited by dustin5378 Tuesday, February 10, 2015 9:32 PM
    Tuesday, February 10, 2015 9:25 PM
  • Yes, Guoxiong is almost correct but you do need the comma:

    IIF (Fields!FINALSIGN.Value = 1, IIF (DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value) < 24,
    DateDiff
    ("h",Fields!ID.Value, Fields!CONTB_TIME.Value), ""), "")

    Another way to do it is to just combine it:

    IIF ((Fields!FINALSIGN.Value = 1) AND (DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value) < 24), DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value), "")

    Hopefully that is what you are looking for.  You may need to modify it if you want it to be <= 24 instead, etc.

      

    Wednesday, February 11, 2015 1:27 AM
  • Sorry, I missed a comma after the second IIF:

    IIF (
    	Fields!FINALSIGN.Value = 1 , 
    	IIF (
    		DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value) < 24,
    		DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value),
    		""
    	),
    	""
    )


    A Fan of SSIS, SSRS and SSAS

    Wednesday, February 11, 2015 2:12 PM
  • That works when I run it, but the results are incorrect.

    I think I needs to read  <24 and CONTB_ACTION = 7.

    How do I add the and statement to this?

    Wednesday, February 11, 2015 7:19 PM
  • If you want to get data when three conditions are met, you can do this

    IIF (
    	Fields!FINALSIGN.Value = 1 AND 
    	DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value) < 24 AND 
    	Fields!CONTB_ACTION.Value = 7 
    	DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value),
    	""
    )


    A Fan of SSIS, SSRS and SSAS

    Wednesday, February 11, 2015 7:53 PM
  • Back to the error message:

    The Value expression for the textrun 'FINALSIGN1.Paragraphs[0]' contains an error: [BC30455] Argument not specified for parameter 'FalsePart' of 'Public Function IIF (Expression  As Boolean, TruePart As Object, False Part As Object) As Object'


    Do we need a comma after the 7?
    • Edited by dustin5378 Wednesday, February 11, 2015 9:19 PM
    Wednesday, February 11, 2015 9:13 PM
  • Yes. Sorry forgot it again
    IIF (
    	Fields!FINALSIGN.Value = 1 AND 
    	DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value) < 24 AND 
    	Fields!CONTB_ACTION.Value = 7, 
    	DateDiff ("h",Fields!ID.Value, Fields!CONTB_TIME.Value),
    	""
    )


    A Fan of SSIS, SSRS and SSAS

    Wednesday, February 11, 2015 10:30 PM
  • This seems to be pulling unusual numbers.  For instance, one of the providers has 15 total documents but this last query piece is reporting that 23 of the 15 documents were signed within 24 hours?

    Sorry for the many questions, I would really like to learn more.

    Thanks

    Wednesday, February 11, 2015 11:19 PM
  • Did you run the query on SSMS and see if the output is correct? Are there duplicates in the output?


    A Fan of SSIS, SSRS and SSAS

    Thursday, February 12, 2015 1:55 PM
  • The query is correct, it is the output that has a couple issues. 

    The dates showing are only the ones between the start date and end date, not including the start date and end date themselves. 

    And the documents signed within 24 hours is still incorrect. Do I need a count function to count the number of documents signed within 24 hours?


    • Edited by dustin5378 Thursday, February 12, 2015 6:10 PM
    Thursday, February 12, 2015 4:38 PM
  • The dates showing are only the ones between the start date and end date, not including the start date and end date themselves. 


    If dbo.Convert_Id_to_Date(DOCUMENT.CLINICALDATE) returns date (no time) it should include data on @StartDate and on @EndDate.

     

    A Fan of SSIS, SSRS and SSAS

    Thursday, February 12, 2015 6:39 PM
  • And the documents signed within 24 hours is still incorrect. Do I need a count function to count the number of documents signed within 24 hours?


    Which part of your query is used to count the documents?

    A Fan of SSIS, SSRS and SSAS

    Thursday, February 12, 2015 6:41 PM
  • =CountDistinct(Fields!DID.Value)

    is used to count the total documents

    Thursday, February 12, 2015 7:17 PM
  • dbo.Convert_Id_to_Date(DOCUMENT.CLINICALDATE) returns date and time
    Thursday, February 12, 2015 7:19 PM
  • =CountDistinct(Fields!DID.Value)

    is used to count the total documents

    Did you try CountDistinct(Fields!DID.Value, scope)? scope = the name of your dataset.

    See https://technet.microsoft.com/en-us/library/ms155923(v=sql.100).aspx


    A Fan of SSIS, SSRS and SSAS

    Thursday, February 12, 2015 7:44 PM
  • dbo.Convert_Id_to_Date(DOCUMENT.CLINICALDATE) returns date and time

    If you want to include data on the end date, it is better to use

    dbo.Convert_Id_to_Date(DOCUMENT.CLINICALDATE) >= @StartDate and dbo.Convert_Id_to_Date(DOCUMENT.CLINICALDATE) < DATEADD(DAY, 1, @EndDate)


    A Fan of SSIS, SSRS and SSAS

    Thursday, February 12, 2015 7:49 PM
  • The data returned from the count of the total documents is coming up correct, but not the documents signed within 24 hours. 

    CountDistinct(Fields!DID.Value, scope)? scope = the name of your dataset. -Is this formula for the number of documents signed within 24 hours.

    The Crystal Formulas are:

    hours:  if {DOCUMENT.FINALSIGN} = 1 then DateDiff("h",{@ID},{DOCCONTB.CONTB_TIME})

    SignedWithin:  if {DOCUMENT.FINALSIGN} = 1 and {@hours} < 25  and {DOCCONTB.CONTB_ACTION} = 7 then "Signed within 24 hours"
    else "Not Signed within 24 hours";

    I just can't get this to work in Report Builder.

    • Edited by dustin5378 Thursday, February 12, 2015 9:17 PM
    Thursday, February 12, 2015 8:55 PM
  • Okay I see your point. Your query returns all documents between start and end date, but you also need the documents within 24 hours. If your report displays the counts, why do not you use the query to return the counts instead of individual documents?

    A Fan of SSIS, SSRS and SSAS

    Thursday, February 12, 2015 9:21 PM
  • Yes this is correct.

    However, I'm not sure what you mean about using the query to return the counts instead of individual documents.

    This is how the finished product should look:

    • Edited by dustin5378 Thursday, February 12, 2015 9:37 PM
    Thursday, February 12, 2015 9:29 PM
  • Basically in the SELECT statement, you can use COUNT(DOCUMENT.DID) and GROUP BY your columns and in your WHERE clause you can add the date range.


    A Fan of SSIS, SSRS and SSAS

    Thursday, February 12, 2015 9:34 PM