none
SQL: Update or ISNULL function or CASE or SSRS Expression RRS feed

  • Question

  • Hi guys - I have a scenario below. 

    Select letterid, patientid,applicationid, mcpsreceived, letterrequestdatetime from letterrequesthistory

    where patientid = 77203
    and letterid = 139


    I have an expression in SSRS report like this 
    =iif(Fields!McpsReceived.Value = "N","x","") -- This means if the MCPSreceived value is N, then it will create cross 'x' symbol. I am using this 'x' for a check box. 
    Below is the check box 


    When I executed the report for this patient, the report is pickup the patient with value 'X' (applicationID 75397). 
    My question is if the same patient has value "N" and "X" on the same Letterrequestdatetime, I want to to show 'x' symbol. Is it something we can create or update an expression ? Or some thing like ISNULL function or case statement in the select query ? 

    Thank you

    Friday, August 16, 2019 6:57 PM

Answers

  • Hi kkran,

    From my understanding about your scene. The case is, there are data in your database, that have same patient ID ,letter ID and  same Letterrequestdatetime, if one record has value "N" for mcpsreceived, you want "x" mark for all of the record. Right?

    If so, logically, I think, it would be more convenient to use sql query to achieve rather than expressions.

    For example 

    WITH cte AS( SELECT *, DENSE_RANK() OVER (PARTITION BY  letterid,patientid, letterrequestdatetime ORDER BY mcpsreceived) AS Rank FROM YourTable)

    SELECT *, mcpsreceived AS mark FROM cte WHERE cte.RanK=1

    UNION ALL

    SELECT *, mark = 'N' FROM cte WHERE cte.Rank =2

    Then you could slightly change the expression you used for x mark

    =iif(Fields!mark.Value = "N","x","")

    I hope this helps. 

    If it not fit you scenario, please share us more about the design and how you are using data for the x mark sign.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, August 19, 2019 7:38 AM

All replies

  • Hi kkran,

    From my understanding about your scene. The case is, there are data in your database, that have same patient ID ,letter ID and  same Letterrequestdatetime, if one record has value "N" for mcpsreceived, you want "x" mark for all of the record. Right?

    If so, logically, I think, it would be more convenient to use sql query to achieve rather than expressions.

    For example 

    WITH cte AS( SELECT *, DENSE_RANK() OVER (PARTITION BY  letterid,patientid, letterrequestdatetime ORDER BY mcpsreceived) AS Rank FROM YourTable)

    SELECT *, mcpsreceived AS mark FROM cte WHERE cte.RanK=1

    UNION ALL

    SELECT *, mark = 'N' FROM cte WHERE cte.Rank =2

    Then you could slightly change the expression you used for x mark

    =iif(Fields!mark.Value = "N","x","")

    I hope this helps. 

    If it not fit you scenario, please share us more about the design and how you are using data for the x mark sign.

    Regards,

    Lukas


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Monday, August 19, 2019 7:38 AM
  • HI Lukas - This expression I am already using in the report. (

    =iif(Fields!mark.Value = "N","x","")

    My Question is if the same patientid with another applicationID with same letterrequestdate time has X as MCPS received as well then i want the 'x' mark too.


    • Edited by kkran Monday, August 19, 2019 1:32 PM
    Monday, August 19, 2019 1:31 PM
  • Hi Kkran,

    The expression I referred was using a new added column "mark" from the query I gave.

    Have you tried my query to see if it gives new dataset with this Mark field? You could try to see if it works, as I test it works in my environment. If not , you could share us the error.

    Lukas 


    MSDN Community Support Please remember to click Mark as Answer; the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.



    Tuesday, August 20, 2019 2:16 AM
  • Thank you
    Wednesday, August 21, 2019 7:19 PM