What is the best way to for me to troubleshoot my report?

[ceddins]

Good afternoon,

I have two reports that are exactly the same (the same parameters, same fields, same record selection, etc) except for one detail: Report 1 uses three tables, DPSP, FHP, and FDP (connected in that sequence) and Report 2 only uses 2 tables, DPSP and FDP.

The reports give me different results sometimes. Even though I'm not actually using any fields from the FHP table, merely connecting the other 2 tables through the FHP table changes the results. I need to get the results that both the reports are giving me into a single report.

My question is, what can I do to find out what is causing each report to give me different data? If I can understand why I'm getting different results, I'll know how to get all the results in a single report.

Thanks in advance for your help.

I've pasted the SQL queries in case that is useful:
Report 1:
SELECT "FDP"."RVCTFD", "FDP"."AMNTFD", "FDP"."RCDTFD", "DPSP"."TORGDP", "DPSP"."CONVDP", "DPSP"."PKGBCDP", "DPSP"."RTEPDP", "DPSP"."PKGDP", "DPSP"."EFDTDP", "DPSP"."SEQDP", "DPSP"."LNAMDP", "DPSP"."FNAMDP", "DPSP"."RMNODP", "DPSP"."TTYPDP", "FDP"."RKY1FD", "FDP"."TAX1FD", "FDP"."TAX2FD", "FDP"."DATEFD", "FDP"."TIP$FD", "DPSP"."PROGDP"
FROM ("TEXASA"."LMDTA"."FDP" "FDP" INNER JOIN "TEXASA"."LMDTA"."FHP" "FHP" ON ("FDP"."FKYDFD"="FHP"."FKYDFH") AND ("FDP"."FSEQFD"="FHP"."FSEQFH")) INNER JOIN "TEXASA"."LMDTA"."DPSP" "DPSP" ON ("FHP"."KYDTFH"="DPSP"."KYDTDP") AND ("FHP"."SEQFH"="DPSP"."SEQDP")
WHERE "FDP"."RCDTFD"="DPSP"."EFDTDP" AND "FDP"."RVCTFD"<>' ' AND "FDP"."RKY1FD"=0 AND "DPSP"."PROGDP"='PSTRTXG'

Report 2:
SELECT DISTINCT "FDP"."RVCTFD", "FDP"."AMNTFD", "FDP"."RCDTFD", "DPSP"."TORGDP", "DPSP"."CONVDP", "DPSP"."PKGBCDP", "DPSP"."RTEPDP", "DPSP"."PKGDP", "DPSP"."EFDTDP", "DPSP"."LNAMDP", "DPSP"."FNAMDP", "DPSP"."RMNODP", "DPSP"."TTYPDP", "FDP"."RKY1FD", "FDP"."TAX1FD", "FDP"."TAX2FD", "DPSP"."PROGDP", "FDP"."DATEFD", "DPSP"."SEQDP", "FDP"."TIP$FD"
FROM "TEXASA"."LMDTA"."FDP" "FDP" INNER JOIN "TEXASA"."LMDTA"."DPSP" "DPSP" ON ("FDP"."FKYDFD"="DPSP"."FKYDDP") AND ("FDP"."FSEQFD"="DPSP"."FSEQDP")
WHERE "FDP"."RKY1FD"=0 AND "FDP"."RCDTFD"="DPSP"."EFDTDP" AND "FDP"."RVCTFD"<>' ' AND "DPSP"."PROGDP"='PSTRTXG'

 

[hilfy]

The difference probably occurs because one query is selecting distinct records and the other one isn't.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[lbass]

Two issues. First, by placing the FHP table between the other two tables, you are requiring that there be a record in the FHP table; if there isn't, the number of records will be reduced.

Second, you have checked "select distinct" in database->select distinct, so the second query might be returning fewer records.

I guess the question becomes what the logic is for using the FHP table in the first report. No fields are being used, but it IS acting as a filter because of the inner join. This may be for a good reason, but you haven't shared the logic.

-LB

 

[ceddins]

hilfy and lbass,

Thank you both for your responses.

lbass,

I unchecked the "select distinct records" option in Report 2 and still have the issue of report 1 and report 2 returning different data.

The reason for the FHP table is sort of accidental. I originally built the report with 3 tables. I checked the invoice results the report was giving me against the actual customer invoices in our Property Management System and noticed that a few of them were incomplete. I tinkered with the report to try and get the other half of those customer invoices. Removing the FHP table gave me the half of the invoice data that I was missing, but it also took away the half of the invoice data that I had when the FHP table was connected. All other data was unchanged from Report 1 to Report 2. So, that's how I got Report 2.

I can't figure out why the data is behaving this way. What can I do to figure it out?

 

[lbass]

It sounds like you might be missing data in both additional tables. Try changing your links to left outer joins, and join both directly to the first table (not to each other). Remove the selection criteria on the DPSP table since it will undo the left join. Then place the linking fields from each table in the detail section and notice whether they are sometimes null. This is just a test to understand how the data relates to each other.

Your explanation described the effects without really addressing the logic of why the tables are being used. What does each one add to the mix?

-LB