90 Minds Community

 View Only

  • 1.  Crystal Formula on employee birth date

    Posted 06-24-2020 18:44
      |   view attached
    I have a custom report that I'm trying to filter off the month of the employee's birth date. The customer would like to be able to enter in the month and it only pull up the employees that have a birthday in that month. I tried to use the specified order sort by it is looking for the entire date when I added the month January. I also tried a grouping option but that didn't work. Any suggestions on what I could else I could try? Attached sample of my report. TIA.

    ------------------------------
    Shannon Stanley
    Sage 100 Consultant
    Ardent Consulting LLC
    704-839-6697
    ------------------------------


  • 2.  RE: Crystal Formula on employee birth date

    Posted 06-24-2020 18:47
    Month(datefield) should result in a number between 1-12, for use in your filter / suppression formulas.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------



  • 3.  RE: Crystal Formula on employee birth date

    Posted 06-24-2020 19:10
    Edited by Dan Burleson 06-24-2020 22:07
    Record selection criteria for employee birthdays in the current month:
    Month({PR_Employee.BirthDate}) = Month(CurrentDate)​
    For employee birthdays in the next month:
    Month({PR_Employee.BirthDate}) = Month(DateSerial(Year(CurrentDate), Month(CurrentDate)+1,1))​


    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------



  • 4.  RE: Crystal Formula on employee birth date

    Posted 06-24-2020 19:16
    The "next month" formula won't work over year end (resulting in error about month 13 in December). 
    This should work better... = Month(DateAdd("m",1,CurrentDate))

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 5.  RE: Crystal Formula on employee birth date

    Posted 06-24-2020 22:13
    Edited by Dan Burleson 06-24-2020 22:13
    I mis-typed 'DateValue' instead of 'DateSerial'. Thanks for catching that. I corrected it above. That's what I like about 'DateSerial', one doesn't have to think about boundaries in either the year, month or day arguments.

    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------

    Original Message


  • 6.  RE: Crystal Formula on employee birth date

    Posted 06-25-2020 09:38
    Nice!  I've never noticed DateSerial before, and it looks like the DateAdd functionality is built in.  Very useful.  (Another tool for the report design tool-kit).


    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    Toronto ON
    ------------------------------

    Original Message


  • 7.  RE: Crystal Formula on employee birth date
    Best Answer

    Posted 06-25-2020 12:03
    Everyone should know about determining the last day of the current month with:
    DateSerial(Year(CurrentDate), Month(CurrentDate)+1,0)​


    ------------------------------
    Dan Burleson
    Software Consultant
    Connex Software
    Corvallis OR
    541-224-6642
    ------------------------------

    Original Message


Related Content