Hello all,
I am working with a series of Crystal Reports that were created many years ago (before I arrived at this company) to show the sales performance of our company's sales staff. These reports gave sales figures for each dealer managed by a given sales person for YTD, a cumulative total, and as a percent of the grand total for all dealers managed by that sales person. Back then, our company's ERP software (MAX for windows) ran on a Pervasive SQL database. These reports have laid unused for several years. Since then, our company has upgraded to MAX 5.0, which runs on MS SQL. There were some tables I couldn't match up between the databases, but none appeared to contribute to the reports. When I tried running the reports, I kept getting a message that "a date is required here". After asking around, I found that the parameter fields used to select the start and end dates for the reports were looking for dates, whereas the data was in strings (data is gathered from Invoice_Detail.INVDTE_31). When I changed the "Convert Date-Time Field To:" Report Option to "To Date", the error disappeared. However, a new problem emerged. The total numbers for some of the dealers were grossly incorrect. As one example, one dealer/customer showed total sales of -$27000 (yes, negative), when my manual observation and calculation showed it should have been over $140,000.
Here are the formulas and selection criteria for the report (all reports are similar except for the customer IDs specified in the select criteria):
Counter:
numbervar counter;
IF
recordnumber = 1
THEN
counter:= 1
ELSE
counter:=counter + 1
EIGHT:
if month({Invoice_Detail.INVDTE_32}) = 8 then {@ExtSalesDol} else 0
ELEVEN:
if month({Invoice_Detail.INVDTE_32}) = 11 then {@ExtSalesDol} else 0
ExtSalesDol:
if {Invoice_Master.STYPE_31} = 'CR' THEN {Invoice_Detail.INVQTY_32} * -1 * {Invoice_Detail.PRICE_32} else
{Invoice_Detail.INVQTY_32} * {Invoice_Detail.PRICE_32}
FIVE:
if month({Invoice_Detail.INVDTE_32}) = 5 then {@ExtSalesDol} else 0
FOUR:
if month({Invoice_Detail.INVDTE_32}) = 4 then {@ExtSalesDol} else 0
NINE:
if month({Invoice_Detail.INVDTE_32}) = 9 then {@ExtSalesDol} else 0
ONE:
if month({Invoice_Detail.INVDTE_32}) = 1 then {@ExtSalesDol} else 0
PercentOfTotal:
({@runtotal} / Sum ({@ExtSalesDol}) * 100)
RunTotal:
numbervar runtotal;
IF
recordnumber = 1
THEN
runtotal:= Sum ({@ExtSalesDol}, {Customer_Master.CUSTID_23})
ELSE
runtotal:=runtotal + Sum ({@ExtSalesDol}, {Customer_Master.CUSTID_23})
SalesCode:
IF
{Customer_Master.SLSREP_23} <> " "
THEN
{Customer_Master.SLSREP_23}
ELSE
{@WorkTer}
SEVEN:
if month({Invoice_Detail.INVDTE_32}) = 7 then {@ExtSalesDol} else 0
SIX:
if month({Invoice_Detail.INVDTE_32}) = 6 then {@ExtSalesDol} else 0
TEN:
if month({Invoice_Detail.INVDTE_32}) = 10 then {@ExtSalesDol} else 0
THREE:
if month({Invoice_Detail.INVDTE_32}) = 3 then {@ExtSalesDol} else 0
TWELVE:
if month({Invoice_Detail.INVDTE_32}) = 12 then {@ExtSalesDol} else 0
TWO:
if month({Invoice_Detail.INVDTE_32}) = 2 then {@ExtSalesDol} else 0
WorkTer:
IF
{Customer_Master.CUSTID_23} = "RAY425"
THEN
{Invoice_Master.REP1_31}
ELSE
{Customer_Master.SLSTER_23}
SELECT CRITERIA:
{Invoice_Master.INVDTE_31} in {?Start} to {?Stop} and
{Part_Master.ACTTYP_01} = "F" and
{Customer_Master.CUSTID_23} in ["CON380", "DIR148", "DIT114", "DIT128", "DIT271", "DIT402", "ENC066", "FAE023", "FEM142", "GCD118", "MUN945", "RAY601", "WES039"]
I'll be the first to admit my Crystal Report skills have been rusty. and I've been having trouble making heads or tails of this. My initial thought is that one of those report formulas is off, or that the change in how the data is formatted in pervasive vs. ms sql.
Would anyone have any idea where I can begin to salvage these reports, or am I better off starting from scratch?
IP Logged
