Help with formula...

[mona]

Hi all,

I'm trying to calculate a "beginning balance" for my report of which i would like to calculate based on a date parameter (string - like "NOVEMBER&quot from user but the glitch is that i'm trying to sum up amounts for the previous month to arrive at this "beginning balance"

example:

Dim x As currency

While (Date = DateParameter - 1) <-----looking for syntax
x = Sum({Amount}) or other suggestions
Wend
formula = x

Any suggestions appreciated, and thanks in advance,
Mona

 

[kenhamady]

First, make sure that the previous months records make it into the report. Otherwise they aren't going to be evaluated by the formula. Your selection formula will have to include them. Of course, you may have to suppress the details so that they don't print as part of the output.

Next make a formula:

if {Datefield} < DateParameter
then {Amount}
else 0

Third, add a subtotal or grand total of this field to the report (Insert - Summary/Grand Total).


If you don't want these records junking up the primary report, you could create a subreport to total the previous records, and then pass the total to the main report. Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[mona]

Ken,
The portion that I'm having trouble with is getting the previous months' records into my report. I am trying to figure out the syntax for specifying the previous month (not ALL months previous). For example, the user may specify via a parameter that they want their report generated for &quot;NOVEMBER&quot;, I want to sum up all the amounts for &quot;OCTOBER&quot; and present this sum as the beginning balance. Below is my selection formula where I attempt to select the previous months' records (last conditional). As I stated previously, the parameter field is a string. This formula returns an error stating a number, currency, date, or date-time field is required here. I've played around with cdate, tonumber functions to no avail. Any further suggestions would be appreciated.

Selection formula:
{adjustment.person_number} = {?Customer Number} and
UpperCase (MonthName (Month ({adjustment.adjustment_date}) )) = {?ReportDate} and
UpperCase (MonthName (Month ({adjustment.adjustment_date}) )) = {?ReportDate} - 1

 

[kenhamady]

Check the common formulas FAQ for how to add/subtract one month from any date value (easier in V8 than V7)

If that doesn't help:
What version of Crystal are you using?
What does the report date parameter represent?

Ken Hamady

http://www.kenhamady.com/

Crystal Reports Training and a
Quick Reference Guide to VB/Crystal

 

[flaubert]

You're using a MonthName command that I don't have (V7?). However, given a month in the form of a string, the following:

instr('FEBMARAPRMAYJUNJULAUGSEPOCTNOVDECJAN',left({?ReportDate},3))+2)/3

will return the NUMBER for the previous month. ie if {?ReportDate} is 'NOVEMBER', then it will return 10 (for OCTOBER).
I am assuming that you can convert this easily with your MonthName function.

Note that if {?ReportDate} is 'JANUARY', it will return 12. You will obviously still need to account for the fact that in this case, it is also the previous year.

Does this help?

 

[mona]

flaubert (and kenhamady),

Thanks for the suggestions, I ended up using a Select/Case to arrive at the previous month for example:

shared prevmonth as number

Select Case {?ReportDate}
Case &quot;JANUARY&quot;
formula = 0
prevmonth = formula
..
..
..
and so on

My next dilemna that I'm trying to resolve is that I've created an additional subreport that will pull all relevant records from the database irregardless of the {?ReportDate}. I want to use the 'prevmonth' variable from the main report to sum up only those records in this new subreport that has a particular date field that matches the 'prevmonth' variable. I created the following formula in the report footer:

shared numbervar prevmonth;
global currencyvar x;

If ((month({adjustment.adjustment_date}) - 1) = round(prevmonth,2)) then
x = Sum ({adjustment.amount});

x

But I am returned back a value of 0.00. Any suggestions?

 

[flaubert]

Is the formula you've shown the one in the main report?

If so, you need to initialise your variable - ie. you need to do something like

shared numbervar prevmonth:= 0;

rather than just

shared numbervar prevmonth;

If you don't initialise it like this in the main report, you will get a zero back.