Run Crystal Report designed for Pervasive on a MSSQL database.

[mbrayc]

Hello all,

I have several Crystal Reports that were created many years ago to gather data on the sales performance of our company's sales staff. At the time, the data for these reports was housed in a Pervasive v8 database (with DDFs) and entered using MAX 3.7 and later 4.0. These reports fell into disuse, and since they were last used (some four years ago), our company has switched to an MSSQL database, with MAX 5.0. Whenever these reports are run now, Crystal Reports keeps trying to install the Crystal XI RDC software, even though it is already installed. A reboot is then requested. This happense with anyone who tries to run these reports, regardless of operating system, and all company users have access to the correct database. I've opened one report and updated the table names from the old Pervasive database with the DDF tables to the new MSSQL databse. However, there were some DDF tables in the older database for which I was unable to find links to the new database. The only problem still remaining, at least that I can find to date, is that there are two custom date parameters, where the user running the report must enter starting and ending dates for the time frame over which they want to evaluate the salesperson's performance. When I go to run the report, after entering the desired start and end dates, I get a message saying "A string is required here."
Here is the text of the parameters in question (with the problem parameters in bold type):

{Invoice_Master.INVDTE_31} in {?Start} to {?Stop} and
{Part_Master.ACTTYP_01} = "F" and
{Customer_Master.CUSTID_23} in ["WES728", "MUE050", "LCW164", "HIG127", "HAR615", "FPE035", "FOL340", "ESS580", "DOR146", "CLE230", "CHE035", "BRO010"]

I'm not sure if the problem is with those parameters or if the report is still looking for data from the old database, or something else entirely. Any thoughts would be greatly appreciated.

Thanks in advance.

 

[kray4660]

Two questions come to mind. What is the data type of {Invoice_Master.INVDTE_31}. What data type are the parameters set for ({?Start}, {?Stop}).

 

[mbrayc]

Thanks for your reply, kray4660,

{Invoice_Maser.INVDATE_31} shows as a String. The parameters ({?Start}, {?Stop}) show as Date type in the Parameter editor. Maybe that's the problem - different data types? Perhaps change the parameters to String types? If so, how would that affect entering desired start and stop dates?

Thanks again.

 

[kray4660]

The data types have to match. I have had date parameters as strings. The user who enters the 'date' has to be careful to use the right format or they will not get the right results. It might work better to change the {Invoice_Master.INVDTE_31} to a date. I know that you should be able to use CAST or CONVERT SQL functions to convert the field.

I hope this helps.

 

[mbrayc]

Thanks again for the reply.

I switched the parameters that request the start and end dates to strings. I don't expect issues with formatting since the user is prompted to enter the dates in the proper format when the report is run. Another message came up afterward, though (text follows below):

(Popup Window) "A date is required here"
(Error Text) if month ({Invoice_Detail.INVDTE_32}) = 1 then(@ExtSalesDol) else 0

The ExtSalesDol mentioned above is a custom parameter as follows:
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}

Other than converting the Invoice_Master.INVDTE_31 and possibly the Invoice_Detail.INVDTE_32 fields to dates, are there any other possible options?

Thanks again.

 

[kray4660]

If you using the month function you have to have a date, thus {Invoice_Detail.INVDTE_32} would have to be a date. Otherwise if the {Invoice_Detail.INVDTE_32} field has a fixed length for the date. You could use the string functions to 'pull out' the date (i.e., {Invoice_Detail.INVDTE_32} = '01/01/2010', left({Invoice_Detail.INVDTE_32},2) would yield a '01' thus your formula would be if left( {Invoice_Detail.INVDTE_32}) = "01" then(@ExtSalesDol) else 0 ).

I hope this helps.

 

[mbrayc]

I'll try that and let you know how it goes.

Thanks again for your help.

 

[mbrayc]

Hello again,

Sorry about the lack of a reply, but I had to put this project on the back burner due to several more pressing concerns, plus the intended recipient of the reports couldn't review them. I found one key was in the Report Options menu, namely switching the "Convert Date-Time Field:" option from "To String" to "To Date". The report then ran supposedly normally. When presenting my findings to the sales staff, I found my numbers were way off (one customer/dealer's numbers were negative, which made no sense).

I went back to look at the formulas and was left scratching my head. So here's a rundown on all of them (note these are from a report for a different salesman):

COUNTER:
numbervar counter;
IF
recordnumber = 1
THEN
counter:= 1
ELSE
counter:=counter + 1

ONE:
if month({Invoice_Detail.INVDTE_32}) = 1 then {@ExtSalesDol} else 0
(Note: As I said before, there are eleven other similar formulas (TWO through
TWELVE) that are identical except for the month number (2 for TWO, etc.))

ExtSales Dollar:
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}

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}

WorkTer:
IF
{Customer_Master.CUSTID_23} = "RAY425"
THEN
{Invoice_Master.REP1_31}
ELSE
{Customer_Master.SLSTER_23}

The select criteria for this report is as follows:
{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"]

Any thoughts?

Thanks again for everyone's help.