[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Problem with Crystal Report

Posted on 2011-09-13
10
Medium Priority
?
355 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
Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

 
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 2000 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

Tech or Treat! - Giveaway

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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. …
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…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

650 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