Solved

Problem with Crystal Report

Posted on 2011-09-13
10
341 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 35

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
 
LVL 35

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 35

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
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 …
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

733 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