Reports randomly "miss" records

[Former Member]

Background:

I've made a report that I send to customers. From this report, I've made a summary report that's literally a copy of the first report, but most everything is suppressed except for one section of the original report. Again, from the main report, I've made another report that is basically the summary report, except it outputs all the information in a specific format so I can upload it into my financial system as a flat file. Other than all the suppressed sections there are a few layout changes and such, but nothing that should affect the data, they way it's sorted, etc...

The Problem:

The 3 report's summations don't match up all the time. I'd say they are 90% correct. I did some expanding to find out that the reports randomly drop a record of data here and there. They are terribly small differences too because a record is usually only a matter of cents, however this makes it hell on us because we can't rely on the summary to match what the customer sees which may not match the upload into our financial system.

More bad news:

It appears to be random and it isn't just one report that does it. Sometimes the customer receipt misses a record and the others don't, sometimes the summary misses one and the others don't and sometimes the upload misses. You get the point.

I've gone through the SQL queries that are created and they are literally word for word. They match 100%, even the order of everything (which was to be expected by using a copy of the receipt for the others). Also, at first glance, all the options are the same as well. They pull from the same DBs using the same links. All filtering is in place and all match. Finally, the records read always match up after the reports finish, so they are obviously all pulling the data.

I'm stumped and have been working on this for toooooo long. I think it's time for some fresh minds. Anyone?

Darren

[Former Member · Accepted solution]

I think we'll need a whole lot more information in order to get to the bottom of this...

First-glance, gut feel suspects are:

- A null value somewhere causing a calcualted field to not return a value.

- Something in the record selection or calculations that is causing rounding or other similar types of issues. (You say that the amounts dropped are small...)

The following would be helpful to know:

- Is the dropping of records repeatable? If you run a report with the same input records and the same parameters (if any), do you always get the same dropped records?

- What is your data source, specifically. If you are using a database command, please post the SQL (as unmodified as possible, but I realize parts may be considered confidential).

- Please post the filters being used, including the logic of any formula fields that are used within the filters.

Carl

[Former Member · Accepted solution]

some answers:

Yes, there are occasional null values in reported fields, but all 3 reports get the same values and I believe all reports have the same fields shown. Will have to take a closer look to make sure.

Yes, I do rounding here and there, but again every report does the same thing at the same places. The rounding is done after the summations of the records occur. Theoretically, it shouldn't affect the records themselves.

Yes, the dropping of records is repeatable. I can refresh the report and the same records are dropped every time.

Here's the SQL from the main receipt report, but I warn you, it gets pretty hairy. There are many tables that need to be joined across multiple databases. Nothing should be confidential.

LOCAL

SELECT "MC_Call_Infos"."Extension", "MC_Call_Infos"."Cost_0", "MC_Call_Infos"."Date_Time", "MC_Call_Infos"."Dialed_Number", "MC_Call_Infos"."Dest_Description", "MC_Call_Infos"."Wait_Time", "MC_Call_Infos"."Ring_Time", "MC_Call_Infos"."Talk_Time", "MC_Call_Infos"."Hold_Time", "MC_Call_Infos"."Source", "MC_Call_Infos"."Account_Code"

FROM "MicroTelCall"."MICROTEL"."MC_Call_Infos" "MC_Call_Infos"

WHERE ("MC_Call_Infos"."Date_Time">={ts '2008-12-01 00:00:00'} AND "MC_Call_Infos"."Date_Time"<{ts '2009-01-01 00:00:01'})

ORDER BY "MC_Call_Infos"."Extension", "MC_Call_Infos"."Date_Time"

EXTERNAL JOIN MC_Call_Infos.Extension={?LOCAL: MC_Extensions.Extension_Number}

LOCAL

SELECT "MT_Divisions"."Name", "MC_Extensions"."Note_0", "MT_Departments"."Name", "MC_Extensions"."Note_1", "MC_Extensions"."Extension_Number"

FROM ((("MicroTel"."MICROTEL"."MC_Extensions" "MC_Extensions" INNER JOIN "MicroTel"."MICROTEL"."MC_Employee_Extension_Tags" "MC_Employee_Extension_Tags" ON "MC_Extensions"."Sequence"="MC_Employee_Extension_Tags"."Extension_Sequence") INNER JOIN "MicroTel"."MICROTEL"."MT_Employees" "MT_Employees" ON "MC_Employee_Extension_Tags"."Employee_Sequence"="MT_Employees"."Sequence") INNER JOIN "MicroTel"."MICROTEL"."MT_Departments" "MT_Departments" ON "MT_Employees"."Department_Sequence"="MT_Departments"."Sequence") INNER JOIN "MicroTel"."MICROTEL"."MT_Divisions" "MT_Divisions" ON "MT_Departments"."Division_Sequence"="MT_Divisions"."Sequence"

WHERE "MC_Extensions"."Extension_Number"={?LOCAL: MC_Call_Infos.Extension}

ORDER BY "MT_Divisions"."Name", "MT_Departments"."Name", "MC_Extensions"."Note_0"

I filter by date. Only other filters are for the subreports that filter based on the department that the main report is currently compiling. I do in line filtering of any records that aren't valid at run time. So any records that are $0.00 or below and any records that have "false" numbers.

[Former Member · Accepted solution]

The fact that it is repeatable is very good news. You can then take a very systematic approach to debugging. Here's how I would approach it:

- Determine one record that you know is being dropped from one report but not one of the others. (The first one dropped would speed things up, but that's not an absolute requirement.)

- If you have a complex filter (more than 1 condition), move it to a formula field, and change the filter to that field. (Have it return true/false based on the complex condition.)

- Set a breakpoint in the filter formula field (right before returning the result), and any other calculation fields that have any chance of being relavent. Here's how I set a breakpoint (basic syntax):

dim debug as number
debug = debug / debug

- The breakpoint will cause a divide by zero error, and when you press the OK button, you will be taken to the formula editor for that field. The editor will show you the current values of all of your fields (enormously helpful!). See if anything looks strange. You may need to put a condition around the code above so that it stops on the record in question. Wost case, you can use a counter like this:

global dbgcnt as number
dim debug
dbgcnt = dbgcnt + 1
if dbgcnt > 1832 then
  debug = debug / debug
end if

- Replace the "1832" with whatever number will cause the division by zero on your desired record. (Using a customer number and time stamp or some other uniquely identifying values from the data record would be easier, obviously.)

See where that gets you.

Some other suggestions:

- Don't assume anything! Rounding may be working differently than you think, for example. Verify the results of all calculation steps are what you think they should be.

- Compare every value and every step in the logic between the two reports to see where they differ.

- I'm not sure what you mean by "I do in line filtering of any records that aren't valid at run time," but that would be a good place to look, too.

- If a report is indeed "dropping" records, it will be in the filters, or conditional formatting, usually.

- Just out of curiosity, is it the last record for a group that is dropped? There might be a logic error that misses the last record of a group. (I've fallen into that trap before...)

HTH,

Carl