Results 1 to 6 of 6
  1. #1
    Join Date
    Oct 2011
    Posts
    3

    Post Unanswered: Forecast Report - Crystal Reports

    Help!!

    I have attached an example forecast report that I have produced in Crystal Reports XI - release 2.

    Based on the Estimated Close Date and Bill Frequency there are certain calculations (Mainly adding Months to the Estimated Close Date). I have included my code.

    The {Sheet1_.Monthstart} is an Excel spreadsheet listing the dates.

    Select {OPPORTUNITY.TVL_BILLING_FREQUENCY}

    Case "Quarterly in advance":
    If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",1,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ({OPPORTUNITY.TVL_PS_FEE} /2) + ({OPPORTUNITY.TVL_PILOT_VALUE} /2) Else
    If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",3,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ({OPPORTUNITY.TVL_PS_FEE} /2) + ({OPPORTUNITY.TVL_PILOT_VALUE} /2) + ((({OPPORTUNITY.TVL_LICENCEVALUE} /12)* 3)) + ((({OPPORTUNITY.TVL_ANNUAL_HOSTING} /12) * 3)) + ({OPPORTUNITY.TVL_OTHER_FEES}) Else
    If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",6,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ((({OPPORTUNITY.TVL_LICENCEVALUE} /12)* 3)) + ((({OPPORTUNITY.TVL_ANNUAL_HOSTING} /12) * 3)) Else
    If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",9,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ((({OPPORTUNITY.TVL_LICENCEVALUE} /12)* 3)) + ((({OPPORTUNITY.TVL_ANNUAL_HOSTING} /12) * 3)) Else
    If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",12,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ((({OPPORTUNITY.TVL_LICENCEVALUE} /12)* 3)) + ((({OPPORTUNITY.TVL_ANNUAL_HOSTING} /12) * 3))


    Case "One Off":
    If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",1,{OPPORTUNITY.ESTIMATEDCLOSE}))) and ({Sheet1_.Monthstart} > Datetime("Sept 12")) Then {@First_Year_Fees}

    As you can see from the example report I have highlighted two figures, these figures are actually for Dates not specified on the report > Sept 12. They seen to wrap.

    How do I ensure that these dates don't wrap.

    If someone can point me the right direction I would be grateful...

    Thank you, please ask further questions if requried.
    Attached Thumbnails Attached Thumbnails Example Forecast.jpg  

  2. #2
    Join Date
    Oct 2010
    Posts
    46
    The simplest and correct method is to filter off the Sept 12 dates in the record selection.

    Example:

    You set up a parameter where the user can enter the cut off date, for example, Sept 12, 2011. Your record selection would filter off the dates by the following code:

    {table.datetimefield} < {?dateparm}

    Those records that are being read into the report will not have dates greater and equal to Sept 12, 2011.


    If, for some reason, you need records > Sept 12, 2011 in the report but not for that figure you highlighted, you can do the following:

    Assume the formula that prints the highlighted 1,875 figure is @col_1.

    Your @col1 formula should use a IF statement to check if the date is less than Sept 12, 2011. If yes, you do the calculation for printing else do nothing (unless you have something else to do here).

    This would prevent the formula from calculating those records that is > Sept 12, 2011.

    Example:

    Code:
    if {table.datetimefield} < {?dateparm} then (
         Select {OPPORTUNITY.TVL_BILLING_FREQUENCY}
         Case "Quarterly in advance":
         If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",1,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ({OPPORTUNITY.TVL_PS_FEE} /2) + ({OPPORTUNITY.TVL_PILOT_VALUE} /2) Else
         If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",3,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ({OPPORTUNITY.TVL_PS_FEE} /2) + ({OPPORTUNITY.TVL_PILOT_VALUE} /2) + ((({OPPORTUNITY.TVL_LICENCEVALUE} /12)* 3)) + ((({OPPORTUNITY.TVL_ANNUAL_HOSTING} /12) * 3)) + ({OPPORTUNITY.TVL_OTHER_FEES}) Else
         If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",6,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ((({OPPORTUNITY.TVL_LICENCEVALUE} /12)* 3)) + ((({OPPORTUNITY.TVL_ANNUAL_HOSTING} /12) * 3)) Else
         If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",9,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ((({OPPORTUNITY.TVL_LICENCEVALUE} /12)* 3)) + ((({OPPORTUNITY.TVL_ANNUAL_HOSTING} /12) * 3)) Else
         If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",12,{OPPORTUNITY.ESTIMATEDCLOSE}))) Then ((({OPPORTUNITY.TVL_LICENCEVALUE} /12)* 3)) + ((({OPPORTUNITY.TVL_ANNUAL_HOSTING} /12) * 3))
         Case "One Off":
         If (Month ({Sheet1_.Monthstart})=Month (DateAdd("m",1,{OPPORTUNITY.ESTIMATEDCLOSE}))) and ({Sheet1_.Monthstart} > Datetime("Sept 12")) Then {@First_Year_Fees}
    )

  3. #3
    Join Date
    Oct 2011
    Posts
    3

    Forecast report - Crystal Reports

    Thank you

    I will try your suggestion. I will let you know how I get on.

  4. #4
    Join Date
    Oct 2011
    Posts
    3

    Forecast Report - Crystal Reports

    Dear Mission

    Unfortuantely that did not work. I think the problem might relate to the date I'm brining in. its just a spreadsheet with dates listed. (type DateTime)

    Monthstart
    01/10/2011
    01/11/2011
    01/12/2011
    01/01/2012
    01/02/2012
    01/03/2012
    01/04/2012
    01/05/2012
    01/06/2012
    01/07/2012
    01/08/2012
    01/09/2012

    and the field that is displaying on the spreadsheet is a formula field (Cashflow Calcs) which is defined as a number not a date.

    Is there any other information I can provide you with to try and resolve this.

    Thank you for your help so far.

  5. #5
    Join Date
    Oct 2010
    Posts
    46
    If the date you are bringing in looks like that:

    01/10/2011
    01/11/2011
    01/12/2011
    01/01/2012

    then you would convert this field to a date field by using the cdate function.

    Code:
     
    CDate
     
    Convert to Date. 
     
    CDate(string)
    CDate(year, month, day)
    CDate(DateTime)
     
    Example:
    CDate("08/28/11")   // this returns August 28, 2011
    You also said the date you are bringing in is a number, in order to format the date to this format "08/28/11" so that you can use CDate, you need to convert the number field to a text field or convert to text and then extract the relevant day, month, year parts.

    To convert the number field to text, use ToText function:

    Code:
     
    ToText 
     
    The ToText function converts Boolean, Numbers, Currency, Date, Time, and DateTime values to text strings.
     
    ToText (x, y) 
    ToText (x, y, z) 
     
    x - The number to convert to text 
    y - The number of decimal places to include in result (optional).  
    z  - The character to use as the thousands separator. If you don’t specify one, it will use your application default. (Optional.) 
     
    Example:
    ToText(12345.678,0)  // this returns "12345"
    If the converted text do not have the appropriate date format, you need to extract parts of the text field to get the day, month, year part. You can do this with the Mid function.

    Code:
     
    Mid
     
    The Mid function returns a specified number of characters from a string.
     
    Mid (str, start)
    Mid (str, start, length)
     
    str- the text string from which one or more characters is being extracted
    start  - a whole number indicating the position of the first character to extract
    length  - a whole number indicating the number of characters to be extracted from the text string
     
    Example:
    Mid("abcdef", 3, 2)  // this returns  "cd"
    Once you have converted the date you are reading into a date field, you can then filter out the dates you do not want from the record selection.

  6. #6
    Join Date
    Oct 2011
    Posts
    3
    I just started to play the game,The future Exchange





    _________________
    silkroad gold
    sro gold
    silkroad online gold

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •