Skip to Content
author's profile photo Former Member
Former Member

Array/Formula Help

I have crystal reports XI. I found a formula online and modified it to fit my companys holidays each year. This is the formula:

numberVar yr := {?Year}; // The year for which you need Holidays

dateVar array holidays;

redim holidays [10];

holidays [1] := CDate (yr, 1, 1); // New Years day

holidays [3] := CDate (DateAdd ("d", 1 - DayOfWeek (CDate (yr, 5, 31), crMonday), CDate (yr, 5, 31) ) );

// Memorial Day (last Mon in May)

holidays [4] := CDate (yr, 7, 4); // July 4 (Independence Day)

holidays [5] := CDate (DateAdd ("d", 7 - DayOfWeek (CDate (yr, 9, 1) , crTuesday), CDate (yr, 9, 1) ) );

// Labor Day (first Mon in Sept)

holidays [6] := Date ( yr, 11 , 3) - DayOfWeek ( Date ( yr , 11 , 3) ) + 26;

// Thanksgiving (4th Thurs. in Nov)

holidays [7] := CDate (yr, 12, 25); // Merry Christmas

holidays [8] := Date ( yr, 01 , 3) - DayOfWeek ( Date ( yr, 01, 3) ) + 23;//MLK Day

holidays [9] := Date ( yr, 11 , 3) - DayOfWeek ( Date ( yr, 11, 3) ) + 27; //day after thanksgiving

holidays [10] := cdate (yr, 12, 26); //day after christmas

I am trying to count the number of tickets we had open when the ticket was opened on the dates indicated in the array above. I have another formula that converts a field to a string: cstr (cdate({TASKS.OPENDATE}), "MM/dd/yyyy"). What I want to do is create a formula that says "If @dtconv = @holidays then "Holidays" and group by that forumla so all holidays are grouped together so I can count them based on the grouping. What is happening is that it is partial working. but it is only looking at the last row of the holidas formula bove and comparing the dates based on that, the rest are left ungrouped and added to the report. I just want Holidays group using the formula above so I can count them. Is there a different want of comparing them? Thanks

Ralph

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 13, 2009 at 04:48 PM

    1. Why is element 2 not populated?

    2. Every element in the array is of type date, then why are you converting {TASKS.OPENDATE} to cstr?

    3. Do not convert {TASKS.OPENDATE} to cstr, rather leave it as a date field.

    cdate({TASKS.OPENDATE})

    4. Change your second formula to this:

    dateVar array holidays;

    If @dtconv IN holidays then 'Holidays' else ' Working Days';

    5. Group on this second formula.

    6. Do a count on ticket number by the above group and you should get the number of tickets opened during holidays.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 13, 2009 at 08:23 PM

    I did what you suggested, but it is now putting everydate under Working Days so it does not appear it is comparing to the holiday arrary anything I can do to troubleshoot what it is doing? Thanks

    Ralph

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 14, 2009 at 07:16 PM

    It was probably mis misinterpretation. I added the formula into the holiday array formula and it worked like a champ.

    Ralph

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.