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

Fiscal month as input parameter

Hi I am new to crystal reports, can u guys help me.I need to Create a report in that the data should apapear for the given fiscal month at a report run.Thanks in advance

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 Dec 05, 2014 at 01:07 AM

    Hi Abhilash,

    thanks for the quick reply.Our Fiscal year stars from July to June.we usually do fiscal months 1,2,3,4..

    Thanks.

    Add a comment
    10|10000 characters needed characters exceeded

    • Assuming you have a prompt for 'Fiscal Month' that accepts numbers 1 to 12, create a formula called (@FiscalMonth) with this code:

      DateVar StartDate;
      DateVar EndDate;
      Select {?Fiscal_Month}
           Case 1: (
                          If Month(currentdate) < 7 then
                          (
                              StartDate := cdate(Year(currentdate-1), 07, 01);
                              EndDate := cdate(Year(currentdate-1), 07, 31);
                          )
        Else
        (
        StartDate := cdate(Year(currentdate), 07, 01);
                              EndDate := cdate(Year(currentdate), 07, 31);
        )
                   )
      
        Case 2: (
        If Month(currentdate) < 7 then
                          (
                              StartDate := cdate(Year(currentdate-1), 08, 01);
                              EndDate := cdate(Year(currentdate-1), 08, 31);
                          )
        Else
        (
        StartDate := cdate(Year(currentdate), 08, 01);
                              EndDate := cdate(Year(currentdate), 08, 31);
        )
        )
        Case 3: (
        If Month(currentdate) < 7 then
                          (
                              StartDate := cdate(Year(currentdate-1), 09, 01);
                              EndDate := cdate(Year(currentdate-1), 09, 30);
                          )
        Else
        (
        StartDate := cdate(Year(currentdate), 09, 01);
                              EndDate := cdate(Year(currentdate), 09, 30);
        )
        )
        Case 4: (
        If Month(currentdate) < 7 then
                          (
                              StartDate := cdate(Year(currentdate-1), 10, 01);
                              EndDate := cdate(Year(currentdate-1), 10, 31);
                          )
        Else
        (
        StartDate := cdate(Year(currentdate), 10, 01);
                              EndDate := cdate(Year(currentdate), 10, 31);
        )
        )
        Case 5: (
        If Month(currentdate) < 7 then
                          (
                              StartDate := cdate(Year(currentdate-1), 11, 01);
                              EndDate := cdate(Year(currentdate-1), 11, 30);
                          )
        Else
        (
        StartDate := cdate(Year(currentdate), 11, 01);
                              EndDate := cdate(Year(currentdate), 11, 30);
        )
        )
        Case 6: (
        If Month(currentdate) < 7 then
                          (
                              StartDate := cdate(Year(currentdate-1), 12, 01);
                              EndDate := cdate(Year(currentdate-1), 12, 31);
                          )
        Else
        (
        StartDate := cdate(Year(currentdate), 12, 01);
                              EndDate := cdate(Year(currentdate), 12, 31);
        )
        )
        Case 7: (
           StartDate := cdate(Year(currentdate), 01, 01);
                              EndDate := cdate(Year(currentdate), 01, 31);                    
        )
        Case 8: (
           StartDate := cdate(Year(currentdate), 02, 01);
                              EndDate := cdate(Year(currentdate), 03, 01)-1;                    
        )
        Case 9: (
           StartDate := cdate(Year(currentdate), 03, 01);
                              EndDate := cdate(Year(currentdate), 03, 31);                    
        )
        Case 10:(
           StartDate := cdate(Year(currentdate), 04, 01);
                              EndDate := cdate(Year(currentdate), 04, 30);                    
        )
        Case 11:(
           StartDate := cdate(Year(currentdate), 05, 01);
                              EndDate := cdate(Year(currentdate), 05, 31);                    
        )
        Case 12:(
           StartDate := cdate(Year(currentdate), 06, 01);
                              EndDate := cdate(Year(currentdate), 06, 30);                    
        )
          Default: cdate(0,0,0);
      

      You can then add a Record Selection Formula with this code:

      EvaluateAfter({@FiscalMonth});
      DateVar StartDate;
      DateVar EndDate;
      {Date_Field} IN [StartDate TO EndDate];
      

      An even better solution is to use the logic from the first formula and create two SQL Expression Fields - one for StartDate and one for EndDate. The only change you'd need to make is use your database specific functions in the code.

      You can then use the SQL Expression field in the Record Selection Formula instead of the variables. This will make sure everything is filtered at the database side.

      Hope this helps.

      -Abhilash

  • Posted on Dec 04, 2014 at 03:05 PM

    Hi Vijay,

    How are your Fiscal Months defined? How many days in Fiscal Month 1, 2 etc and which calendar month is your Fiscal Month 1?

    -Abhilash

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 08, 2014 at 08:18 PM

    Thanks

    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.