Prev
Next
Forums Performance Management Lawson Business Intelligence/Reporting/Crystal

Personal Action Report Question

 3 Replies
 0 Subscribed to this topic
 22 Subscribed to this forum
Sort:
Author
Messages
CHSReportWriter
Basic Member
Posts: 6
Basic Member
2/15/2008 7:50 PM

    I am trying to clean up one of my personal action reports- and I hoped that someone might have some advice for me on how to do this. 

    We have action codes and reason codes in our PERSACTHIS table- when someone transfers departments in our organization we do a PA and mark them as ACTION_CODE  = DATACHG and then EITHER REASON_01 = TRANSDEPT OR REASON_02 = TRANSDEPT.

    When I try to bring in BOTH of those reason codes into ONE crystal report- my results get all messed up.  Currently I have to do two separate reports to get the right results.  Does anyone have any ideas on how to get this to work correctly?

    Here is my SQL-

    {PERSACTHST.ACTION_CODE} = "DATACHG" and
    {?Date Range} = {PERSACTHST.EFFECT_DATE} and
    {PERSACTHST.REASON_02} in ["TRANSDEPT", "TRANSPL"] or

    {PERSACTHST.REASON_01} in ["TRANSDEPT", "TRANSPL"] and
    {EMPLOYEE.COMPANY} = 1

    k-rock
    Veteran Member
    Posts: 142
    Veteran Member
    2/15/2008 7:55 PM
      Just off hand, I would guess you need all of the parameters on both sides of the OR

      {PERSACTHST.ACTION_CODE} = "DATACHG" and
      {?Date Range} = {PERSACTHST.EFFECT_DATE} and
      {PERSACTHST.REASON_02} in ["TRANSDEPT", "TRANSPL"] or

      {PERSACTHST.ACTION_CODE} = "DATACHG" and
      {?Date Range} = {PERSACTHST.EFFECT_DATE} and
      {PERSACTHST.REASON_01} in ["TRANSDEPT", "TRANSPL"] and
      {EMPLOYEE.COMPANY} = 1
      CHSReportWriter
      Basic Member
      Posts: 6
      Basic Member
      2/15/2008 7:59 PM

        I think you are right- that was it!  Thanks!

        Chris Martin
        Veteran Member
        Posts: 277
        Veteran Member
        2/15/2008 8:11 PM

          If you wanted to write a bit more simply... 

          {PERSACTHST.ACTION_CODE} = "DATACHG" and
          {?Date Range} = {PERSACTHST.EFFECT_DATE} and

          ({PERSACTHST.REASON_02} in ["TRANSDEPT", "TRANSPL"] or {PERSACTHST.REASON_01} in ["TRANSDEPT", "TRANSPL"]) and

          {EMPLOYEE.COMPANY} = 1

          This should work also...ji ust added parentheses around your two "or" conditions.