Incorrect data in scheduled report

[jmg002]

We are currently on Crystal Enterprise X. We have one particular scheduled report that for the last two months has displayed the wrong customer data. However, when we run the report manually, it displays the correct customer data. Is there any way to determine why the scheduled report has suddenly started pulling wrong data.

 

[Madawc]

Please define 'wrong'. The wrong period?

If it is data-based and uses parameters, you might be having a problem with parameter dates not updating.

You can define a date-range in terms of the current date, removing the need for date parameters.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[jmg002]

The scheduled report is pulling another customer's data. The date range and data parameters are all correct but it pulls numbers from another account. However, if I create a new scheduled task, it pulls the correct customer information (and also if I run it manually). It has only posted the incorrect customer info on the original monthy scheduled item. This item did work the first couple of months but suddenly starting pulling someone else's data.

 

[synapsevampire]

If it suddenly started to pull other data, I'd be suspicious of a database change.

The key is to copy the Database->Show SQL Query to see the criteria being passed, and post this and the Report->selection formula->Record.

You should always post your database and the connectivity you're using, especially when asking about data issues.

-k

 

[jmg002]

There have been no database changes. We have an Oracle database using an ODBC connection where the customer information is kept separate by unique "account" codes.

At one time it looks like we did have a verson of this report published in CE using the wrong criteria. But it was later corrected with a newer version of the report, but the scheduled item somehow seems to be using the previous version.

Below is our sql query from the report:
SELECT "MFTHIST"."TXN_CODE", "MFTHIST"."IND_REV", "MFTHIST"."NBR_COMP", "MFTHIST"."CD_TXN_LIT", "MFTHIST"."DTE_PROC", "MFTHIST"."QTY_ORD", "MFTHIST"."AMT_GROSS", "MFTFUND"."NME", "MFTCOMP"."NME", "MFTFUND"."DTE_PROC_CURR"
FROM ("PUBLIC"."MFTHIST" "MFTHIST" INNER JOIN ("PUBLIC"."MFTCOMP" "MFTCOMP" INNER JOIN "PUBLIC"."MFTFUND" "MFTFUND" ON "MFTCOMP"."NBR_COMP"="MFTFUND"."NBR_COMP") ON ("MFTHIST"."NBR_COMP"="MFTFUND"."NBR_COMP") AND ("MFTHIST"."NBR_FUND"="MFTFUND"."NBR_FUND")) LEFT OUTER JOIN "PUBLIC"."MFTRFEE" "MFTRFEE" ON ((((((("MFTHIST"."NBR_COMP"="MFTRFEE"."NBR_COMP") AND ("MFTHIST"."NBR_FUND"="MFTRFEE"."NBR_FUND")) AND ("MFTHIST"."NBR_ACCT"="MFTRFEE"."NBR_ACCT")) AND ("MFTHIST"."DTE_PROC"="MFTRFEE"."DTE_PROC")) AND ("MFTHIST"."TYP_ACCT"="MFTRFEE"."TYP_ACCT")) AND ("MFTHIST"."DTE_ESTAB_TRAC"="MFTRFEE"."DTE_ESTAB_TRAC")) AND ("MFTHIST"."NBR_BTCH_TRAC"="MFTRFEE"."NBR_BTCH_TRAC")) AND ("MFTHIST"."NBR_SEQ_TRAC"="MFTRFEE"."NBR_SEQ_TRAC")
WHERE NOT ("MFTHIST"."CD_TXN_LIT"='07' OR "MFTHIST"."CD_TXN_LIT"='90' OR "MFTHIST"."CD_TXN_LIT"='91') AND "MFTHIST"."NBR_COMP"=807 AND ("MFTHIST"."TXN_CODE"='EXI' OR "MFTHIST"."TXN_CODE"='EXO' OR "MFTHIST"."TXN_CODE"='LIQ' OR "MFTHIST"."TXN_CODE"='PUR' OR "MFTHIST"."TXN_CODE"='WOF' OR "MFTHIST"."TXN_CODE"='WOR') AND ("MFTHIST"."DTE_PROC">={ts '2006-04-01 00:00:00'} AND "MFTHIST"."DTE_PROC"<{ts '2006-05-01 00:00:00'})
ORDER BY "MFTFUND"."NME"


Below is our selection criteria we use to select the data used in the report we had issues with. And as I stated above, we use An Oracle database for our data.

not ({MFTHIST.CD_TXN_LIT} in ["07", "90", "91"]) and
{MFTHIST.NBR_COMP} = 807.00 and
{MFTHIST.TXN_CODE} in ["EXI", "EXO", "LIQ", "PUR", "WOF", "WOR"] and
{MFTHIST.DTE_PROC} in LastFullMonth

 

[Turkbear]

Hi,
If you did not delete the recurring instance of the 'bad' one, it will still be the one that runs even though you published a newer version..Scheduled Report instances are static and use the report as it existed at the time it was scheduled.





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[synapsevampire]

Delete the instance and create a new one based on the new report.

-k

 

[jmg002]

Based on others that we have updated, that doesn't appear to be the case. I checked a few reports that recently had changes and looked at the report instances since we implemented the changes. The report instances reflect the changes made and we did not recreate the scheduled item for them.

Also, I just ran a very simple test. I created a report and moved into our test Crystal Enterprise environment. I then scheduled the report for 5 minutes later. In the meantime, I made a change to the report and moved it again into test CE. When the scheduled occurrence ran, the report contained the change I made. If schedule report instances are static, I would not have seen the change I made.

I am being told by Business Objects that this particular scheduled instance must be corrupt. I can delete it and create a new one but my concern is how do I know if others are or will become corrupted. I can't be publishing wrong client data. Any other thoughts?

 

[hilfy]

Do you have "Save Data with Report" turned on in the published report? If so, that is probably your culprit.

-Dell

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