×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Run Crystal Report designed for Pervasive on a MSSQL database.

Run Crystal Report designed for Pervasive on a MSSQL database.

Run Crystal Report designed for Pervasive on a MSSQL database.

(OP)
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.

RE: Run Crystal Report designed for Pervasive on a MSSQL database.

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

RE: Run Crystal Report designed for Pervasive on a MSSQL database.

(OP)
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.

RE: Run Crystal Report designed for Pervasive on a MSSQL database.

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.

RE: Run Crystal Report designed for Pervasive on a MSSQL database.

(OP)
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.

RE: Run Crystal Report designed for Pervasive on a MSSQL database.

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.

RE: Run Crystal Report designed for Pervasive on a MSSQL database.

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

Thanks again for your help.

RE: Run Crystal Report designed for Pervasive on a MSSQL database.

(OP)
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close