Forecast Report - Crystal Reports

sarahowen · #1 (**permalink**) 10-12-11, 06:45

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.

mission · #2 (**permalink**) 10-12-11, 13:11

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}
)

sarahowen · #3 (**permalink**) 10-14-11, 04:28

Thank you

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

sarahowen · #4 (**permalink**) 10-14-11, 04:54

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.

mission · #5 (**permalink**) 10-14-11, 12:39

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.

aion12 · #6 (**permalink**) 10-22-11, 22:07

I just started to play the game,The future Exchange

_________________
silkroad gold
sro gold
silkroad online gold

Internet Services

Web Development

Digital Marketing

Consumer Tech