This is a complicated one, however google doesn’t seem to want to give me any answers.
So…
I have a crystal formula, a lot of them actually, a table with 4 columns and 9 rows. Each has a variation of this rather complexe formula (ill post formula at end).
When I print this form, there is almost a 50 second processing time. Which is unacceptable, even worse, I’ve only programmed a little less than half the coded forumlas into this report, There will be 18 rows PLUS another full page worth of various other formulas.
Specifically, if there is a way to make crystal recognize forumlas within forumlas dynamically, this would probably help alot (less formulas referencing other formulas). Or if there is a way to make Crystal skip formulas if they are not needed. Example, only 3 rows of data are available, so no need to calculate all 18 rows…
I have already tried making a formula that basically counts how many rows will actually get printed, and this formula is checked inside each formula in the table. With an If statement like this
If {@NumberOfRows} >= nCurrentRows THEN
(
Huge amount of code
)
ELSE
(
0;
)
However this did not seem to help performance, it seems like crystal is compiling the huge formula regardless of if the majority of the code is skipped.
Here is the code: I explain some key points of the code at the end of this message.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
//This function figures out which fee amount to print based on which is APR or not.
local NumberVar nRowNumber := 1; //Row number, this must be changed per use of this formula
local NumberVar nFeeAmt := 0; //fee amt, returned out of function
local NumberVar i; //loop counter, counts from 1 to 20
local StringVar sMatrixName; //contains the COC matrix name
local StringVar sNameOfC32Money; //Contains C32Money field
local NumberVar nValueOfC32Money; //Contains value from C32Money field
local NumberVar nNumOfFinds := 0; //Used to count up to the row number, to figure out how many finds are needed
local StringVar sAPRFlagName; //Name of the APR flag
local StringVar sAPRValue; //APR Flag Value
local StringVar sAPRAffect; //Contains if the APR should be affected, if an A, then variable is checked, if Y, asumed yes
//Master If, to determine if formula should just end earily cause not possible to have anymore
//This is for performance purposes
If {@NumberOfGoodFees} >= nRowNumber THEN //good to go
(
IF LEFT(UCASE({@LN_Purpose}),5) = “RCREN” THEN
(
sMatrixName := “COC9XX”;
)
ELSE
(
sMatrixName := “COC” + LEFT({@LN_purpose},3);
);
for i := 1 to 20 step 1 do
(
//Reset to 0
nValueofC32Money := 0;
//Get the name of the c32 money field
sNameOfC32Money := CRIGetMatrixData(sMatrixName, “Line” + totext(i,”0″) , “C32FieldName” , “\cfw32\matrix\”);
//check if the money field is blank, if so, skip the row, otherwise, get the value
IF trim(sNameOfC32Money) <> “” THEN
(
SELECT UCASE(sNameOfC32Money)
Case “LN_MONEY85”: nValueofC32Money := {@LN_Money85}
Case “LN_MONEY86”: nValueOfC32Money := {@LN_Money86}
Case “LN_MONEY3”: nValueofC32Money := {@LN_Money3}
Case “LN_MONEY4”: nValueofC32Money := {@LN_Money4}
Case “LN_MONEY5”: nValueofC32Money := {@LN_Money5}
Case “LN_MONEY117”: nValueofC32Money := {@LN_Money117}
Case “LN_MONEY6”: nValueofC32Money := {@LN_Money6}
Case “LN_MONEY90”: nValueofC32Money := {@LN_Money90}
Case “LN_MONEY7”: nValueofC32Money := {@LN_Money7}
Case “LN_MONEY89”: nValueofC32Money := {@LN_Money89}
Case “LN_MONEY91”: nValueofC32Money := {@LN_Money91}
Case “LN_MONEY10”: nValueofC32Money := {@LN_Money10}
Case “LN_MONEY87”: nValueofC32Money := {@LN_Money87}
Case “LN_MONEY113”: nValueofC32Money := {@LN_Money113}
Case “LN_MONEY114”: nValueofC32Money := {@LN_Money114}
Case “LN_MONEY88”: nValueofC32Money := {@LN_Money88}
Case “LN_MONEY112”: nValueofC32Money := {@LN_Money112}
;;
);
IF nValueofC32Money <> 0 THEN
(
//One was found, so add to counter
nNumOfFinds := nNumOfFinds + 1;
If nNumOfFinds = nRowNumber THEN
(
//With the value now known, need to determine if is an APR, was APR checked off?
//First, check if it affects the APR, if its Y, then print. If its A, then check, if else, then ditch
sAPRAffect := CRIGetMatrixData(sMatrixName, “Line” + totext(i,”0″) , “APR” , “\cfw32\matrix\”);
IF TRIM(sAPRAffect) <> “” THEN
(
IF UCASE(TRIM(sAPRAffect)) = “Y” THEN
(
nFeeAmt := nValueOfC32Money;
);
IF UCASE(TRIM(sAPRAffect)) = “A” THEN
(
//need to ask the Matrix for the name of the variable
sAPRFlagName := CRIGetMatrixData(sMatrixName, “Line” + totext(i,”0″) , “APRField” , “\cfw32\matrix\”);
IF trim(sAPRFlagName) <> “” THEN
(
SELECT UCASE(sAPRFlagName)
Case “LN_FLAG47”: sAPRValue := {@LN_FLAG47}
Case “LN_FLAG48”: sAPRValue := {@LN_FLAG48}
Case “LN_FLAG53”: sAPRValue := {@LN_FLAG53}
Case “LN_FLAG55”: sAPRValue := {@LN_FLAG55}
Case “LN_FLAG66”: sAPRValue := {@LN_FLAG66}
Case “LN_FLAG52”: sAPRValue := {@LN_FLAG52}
Case “LN_FLAG49”: sAPRValue := {@LN_FLAG49}
;;
//Now check if the value is Y, if so, needs to be printed
IF ucase(sAPRValue) = “Y” THEN
(
nFeeAmt := nValueOfC32Money;
)
ELSE
(
nFeeAmt:= 0;
);
);
);
);
exit for ;
);
);
);
);
nFeeAmt;
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
CRIGetMatrixData is a custom function that reads matrix files, Matrixes basically have text in them.
The huge case statements are because the Matrix describes the database field used, however, even though the Crystal equivelent has the same name, Crystal doesn’t allow such dynamic access.
Every forumla that is like LN_Something, is at run time, just a single value, it is a forumla that has no processing, just a single value. These values are injected at run time, there is a negligiable performance hit from this. It takes about 1 second per 1000 formulas of this nature :).
Because I am sure TR will destroy the tabbing, I have provided a txt file for viewing.
http://trevorsarchives.selfip.net/temp/CrystalFormula.txt
