Report Form Problem - What can it be? 1

[wld1957]

I have used Crystal reports for over 15 years to make forms and reports. We are currently using CRX 11 on windows 7. Version 11.0.0.2495.

I have a report that is more of a form. It has worked well for the past 4 years and now one of the formulas within the report has a problem where it doubles the amount (Total Court costs). It does have a running total (SheriffFee) that is the one that is doubling the amount. Then when it is added to the other formulas (Total Court Costs)it is not correct but should not be the source since it did not change. It sums the fees and evaluates for each record. I did try to remove the SheriffFee running total but then the (Total Court Cost) formulas still doubled. Nothing I have looked at is giving me an indication of what is causing it.

I have tried about everything I know to trouble shoot what may be the cause. First off I do not believe it is a problem with the report. When something does not work after working I look at what has changed. The report has not changed.

The report draws basic information such as name, address etc along with figures from the database. The formulas do the math which has worked well and allows me to check figures and then email the form through visual cut along with an attachment. It is for checking figures and information.

I tried the report on other computers and it did the same thing. I tried it through both visual cut and crystal itself with the same problem. I then went back to the original report of several years ago and again the same problem.

Since the report has subreports I saved a copy and started to remove all of the subreports and eliminate as much information as I could to find the problem. The problem still exists.

I then checked the options and report options under crystal and everything appears to be in order.

This leave me to believe that it has to do with the way the information is being pulled from the SQL database?

I need some guidance on where to look or what else to try and troubleshoot the issue. Not sure if the sql statement will help but will include it along with a picture of the form for visual reference.

Any ideas would be greatly appreciated. Thanks in advance. I just do not know what else to try.


SELECT "CAS_Action"."CourtCaseNumber", "CAS_Case"."PlaintiffTitle", "CAS_Case"."DefendantTitle", "CAS_Action"."ActionId", "CAS_Action"."HearingDate", "CAS_Case"."SheriffNumber", "CAS_ActionFee"."FeeAmount", "MNT_Fee"."FeeDesc", "CAS_ActionProperty"."PropertyDesc", "CAS_Action"."EnteredById", "CAS_ActionFee"."FeeId", "CAS_ActionProperty"."ParcelNumber", "CAS_Case"."CaseId", "CAS_Action"."ActionLabel", "CAS_Action"."CaseId", "CAS_ActionCustom"."CustomValue", "CAS_Action"."ActionStatusId", "CAS_Judgment"."JudgmentAmount"
FROM "Civil4"."dbo"."CAS_ActionProperty" "CAS_ActionProperty" INNER JOIN ((("Civil4"."dbo"."CAS_Judgment" "CAS_Judgment" INNER JOIN (("Civil4"."dbo"."MNT_Fee" "MNT_Fee" INNER JOIN "Civil4"."dbo"."CAS_ActionFee" "CAS_ActionFee" ON "MNT_Fee"."FeeId"="CAS_ActionFee"."FeeId") INNER JOIN "Civil4"."dbo"."CAS_Action" "CAS_Action" ON "CAS_ActionFee"."ActionId"="CAS_Action"."ActionId") ON "CAS_Judgment"."CaseId"="CAS_Action"."CaseId") INNER JOIN "Civil4"."dbo"."CAS_ActionCustom" "CAS_ActionCustom" ON "CAS_Action"."ActionId"="CAS_ActionCustom"."ActionId") INNER JOIN "Civil4"."dbo"."CAS_Case" "CAS_Case" ON "CAS_Action"."CaseId"="CAS_Case"."CaseId") ON "CAS_ActionProperty"."ActionId"="CAS_Action"."ActionId"
WHERE "CAS_Action"."ActionStatusId"=1 AND "CAS_Action"."ActionLabel"='Order of Sale' AND "CAS_Case"."SheriffNumber"='20002098'
ORDER BY "CAS_Action"."CourtCaseNumber"

I have another report that this report is based off of that still works. This is the sql statement for that one.

SELECT "CAS_Action"."CourtCaseNumber", "CAS_Case"."PlaintiffTitle", "CAS_Case"."DefendantTitle", "CAS_Action"."ActionId", "CAS_Action"."HearingDate", "CAS_Case"."SheriffNumber", "CAS_ActionFee"."FeeAmount", "MNT_Fee"."FeeDesc", "CAS_ActionProperty"."AppraisedValue", "CAS_ActionProperty"."MinimumBid", "CAS_ActionProperty"."PropertyDesc", "CAS_Action"."EnteredById", "CAS_ActionFee"."FeeId", "CAS_ActionFee"."FeeNote", "CAS_ActionProperty"."ParcelNumber"
FROM ("Civil4"."dbo"."CAS_ActionProperty" "CAS_ActionProperty" INNER JOIN (("Civil4"."dbo"."MNT_Fee" "MNT_Fee" INNER JOIN "Civil4"."dbo"."CAS_ActionFee" "CAS_ActionFee" ON "MNT_Fee"."FeeId"="CAS_ActionFee"."FeeId") INNER JOIN "Civil4"."dbo"."CAS_Action" "CAS_Action" ON "CAS_ActionFee"."ActionId"="CAS_Action"."ActionId") ON "CAS_ActionProperty"."ActionId"="CAS_Action"."ActionId") INNER JOIN "Civil4"."dbo"."CAS_Case" "CAS_Case" ON "CAS_Action"."CaseId"="CAS_Case"."CaseId"
WHERE "CAS_Case"."SheriffNumber"='16000601'
ORDER BY "CAS_Action"."CourtCaseNumber"

 

[lbass]

Something has likely changed in one of the tables in your database.

You have some table that is causing the fee to appear more than once. The easiest way to check this is to save your report under another name and then do some testing. Lay out your fields in the detail section by adding fields from your tables in the other the tables are joined in your SQL statement. Sort by your court case number, and then observe where records are duplicating. Note especially the fields that contribute to your running total. Once you can see where the duplication is occurring, take a look at the layout of your joins in the linking expert. Is there a table that would should be joined by more than one field to eliminate the duplication? Is there another selection criterion you should add to the report? Depends upon what you uncover.

-LB

 

[wld1957]

Thanks and I will give this a try tomorrow. It makes sense and will give me a place to start.

 

[wld1957]

Thanks "LBass", with your recommendations I was able to track down the culprit even though it took me about a day to do so. It was a custom value that was temporarily created and then removed from the database by the software provider. Even though the field was removed it still remained on some of the records.

It really should not have any impact on the report but it did. I was able to correct the problem and the report in the process.

I wanted to say thank you as over the years you and others with suggestions have helped in so many ways. The reports and knowledge that I have gained over that time would not have been possible without the help.