Solved

Problem with Crystal Report

Posted on 2011-09-13
10
332 Views
Last Modified: 2012-05-12
I have an invoice and order confirmation in Crystal which is duplicating some of the items on the order but only sometimes. When the same invoice is reprinted then there is no duplication.

The application is Sage BusinessVision with Crystal run-time engine version 10

There is a long history behind this issue going back to 2007. The issue started to appear sometime in 2007 and was reported by some customers. BusinessVision QA at the time and later on attempted to replicate the issue but so far to this day no one has ever be able to replicate this. I have attempted to replicate the issue for countless hours with no success.

I'm not sure why but someone from QA discovered that by adding the following formula to the report for suppression in the detail section the problem went away.
previous({SALES_HISTORY_DETAIL.NUMBER}) = {SALES_HISTORY_DETAIL.NUMBER}
and previous({SALES_HISTORY_DETAIL.RECNO}) = {SALES_HISTORY_DETAIL.RECNO}

Open in new window


Now the problem is that as soon as I start customizing the report the problem comes back. in my case I have a custom report for one of my customers where I'm grouping the items on an invoice. (attached). Sage is telling me that they can't help me because of the customization I have.    

I've been insisting with the Sage QA team to get the issue resolve but they claim that it is a Crystal bug but are not sure because no one has ever been able to replicate it.

Has someone else ever come across this? I have attached my custom report and also the standard report for you to look at.
 Invoice---Plain-Paper.rpt
 Invoice.rpt
0
Comment
Question by:Gerhardpet
  • 4
  • 3
  • 3
10 Comments
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36533492
btw the database is a Pervasive SQL version 10
0
 

Expert Comment

by:ajaytemp
ID: 36533708
Please list database information.

I had similar problem in CR2007 where II started a new report and linked to the Access 2007 data table.  I restarted the report completely, and clicked finish in the UI screen for picking database and data access layer technology (ADO.NET 2) before I clicked next. if I clicked next, it adds an option for data table that caused data view problems.      the problem went away.
0
 

Expert Comment

by:ajaytemp
ID: 36533713
I saw the database version and I was using CR 2007 Full with Service Packs.
0
 
LVL 34

Expert Comment

by:James0628
ID: 36538960
Disclaimer: I know nothing about Sage, so I'm speaking in generalities here.

 The reason for the duplicate items is quite simple.  You're getting more than one record for those items.  That's why that suppression formula fixes (hides) the problem.  It says if this record has the same NUMBER and RECNO as the previous record, suppress this record.  The extra records are probably coming from one of the secondary tables, like a product table that has two records for the same product, so any line item for that product shows up twice.

 You said that if the invoice is reprinted, you don't get the duplication.  Is the invoice reprinted immediately, or some time later?  If it's reprinted immediately, then it may be that the printing process itself is eliminating the extra records, like there's a temporary record that is removed when the invoice is printed.  If it's reprinted some time later, then something is removing the extra records in between the two printings.

 FWIW, if you go to Database > "Show SQL Query", CR should show you the query that it's using.  If you have the capability to run a query in Sage manually, you could try running that query and looking at the "raw" data.  If nothing else, you could post the query here and we could take a look at it, assuming that there is nothing "sensitive" in it.

 James
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36539148
The strange thing is that this only happens once in a while. My customer calls me about every 2 to 3 weeks with one invoice and they generate 150 to 200 invoices a day.

When this happens the reprints are done a day later or after the customer calls back complaining about items being duplicated. No one handle these invoice manually, they are automatically emailed and faxed without user intervention.

I have a PSQL Btrieve connection and I don't have the option to show the SQL query
0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 34

Accepted Solution

by:
James0628 earned 500 total points
ID: 36539276
Another possibility might be to make a copy of the report and change it to show more fields in the detail section - Basically, every field that's currently being used on the report.  What I call a "data dump".  The idea is to look for something different in the duplicate records that might tell you where they're coming from.  The first trick there is finding some duplicate records to check.  And it's possible that all of the fields that you're using, or even all of the fields in the records, are duplicated, in which case that may not tell you anything, but it's a place to start.

 Oh, and as for the problem coming back when you customize the report, I would think that the Previous suppression formula would still work, unless you're changing the order so that the records aren't sorted by NUMBER and RECNO.  Or, of course, if you're changing that suppression formula, that might be the problem.

 James
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36556959
In my case I think it is because I'm grouping the details in the report and that is why the suggested formula does not work.

I will try you suggestions to see if I can find out where the problem is.
0
 

Expert Comment

by:ajaytemp
ID: 36558061
Upon report creation, when you attach the report to a database is where the problem occurs.

On a UI, there if you press Next button, then CR 2008 Enterprise will AUTOMATICALLY,   JOIN DATA TABLES after FINISH button.

So there are two screens,
1)  Pick Data Source and Data Adapter (ADO for example)
2)  JOIN DATA TABLES

The second one cause problems.
0
 
LVL 34

Expert Comment

by:James0628
ID: 36566016
FWIW, my basic assumption has been that the "duplicate" records are not really duplicates, per se.  For example, we had some software that stored the quantity for each item on an invoice in a separate table.  Some items were sold in pounds and some were sold in other units.  The software would create one quantity record in the sold units, and one in pounds.  So, if you simply linked to that quantity table, you'd get some items "duplicated", once for each quantity record.  I've been assuming that you're running into something like that.

 However, it's also possible that you have some problem that is actually causing duplicate records to be created for some items.  Hopefully it's not that, because that would probably be much harder to track down and fix.

 Anyway, good luck with it.

 James
0
 
LVL 1

Author Comment

by:Gerhardpet
ID: 36566043
Yes my scenario is your last comment

Apparently not even the developers have been able to replicate the issue
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now