Weird issues with a crystal report..

[andrewrees]

Hi,

We're currently experiencing some weird issues with launching a crystal report from a button on the SLX account screen.  In the report there is one parameter for an account number, which is being passed over and the report itself is based on a view using custom data we've pulled in from an accounting system.

The issue is that the first time the report is launched it executes the following SQL..

  

 SELECT "v_NewSalesReportView"."C_THEIR_REFERENCE", "v_NewSalesReportView"."ACCOUNT", "v_NewSalesReportView"."ACCOUNT_NUMBER", "v_NewSalesReportView"."ANALYSIS1", "v_NewSalesReportView"."C_ITEM_NUMBER", "v_NewSalesReportView"."C_TRANSACTION_DATE", "v_NewSalesReportView"."C_ORDER_NUMBER", "v_NewSalesReportView"."C_INVOICE_NUM_TEXT", "v_NewSalesReportView"."C_ALTERNATE_PART", "v_NewSalesReportView"."C_EXCHANGE_RATE", "v_NewSalesReportView"."C_PRICE", "v_NewSalesReportView"."C_ITEM_DISCOUNT_PCENT", "v_NewSalesReportView"."C_INVOICE_CREDIT", "v_NewSalesReportView"."C_QUANTITY"
 FROM   "sysdba"."v_NewSalesReportView" "v_NewSalesReportView"
 WHERE  "v_NewSalesReportView"."ACCOUNT_NUMBER"='ASTO08'

Every time after the report is run after it generates the following SQL instead..

SELECT "v_NewSalesReportView"."C_THEIR_REFERENCE", "v_NewSalesReportView"."ACCOUNT", "v_NewSalesReportView"."ACCOUNT_NUMBER", "v_NewSalesReportView"."ANALYSIS1", "v_NewSalesReportView"."C_ITEM_NUMBER", "v_NewSalesReportView"."C_TRANSACTION_DATE", "v_NewSalesReportView"."C_ORDER_NUMBER", "v_NewSalesReportView"."C_INVOICE_NUM_TEXT", "v_NewSalesReportView"."C_ALTERNATE_PART", "v_NewSalesReportView"."C_EXCHANGE_RATE", "v_NewSalesReportView"."C_PRICE", "v_NewSalesReportView"."C_ITEM_DISCOUNT_PCENT", "v_NewSalesReportView"."C_INVOICE_CREDIT", "v_NewSalesReportView"."C_QUANTITY"
 FROM   "sysdba"."v_NewSalesReportView" "v_NewSalesReportView"
 WHERE   "v_NewSalesReportView"."C_INVOICE_NUM_TEXT"<>'' AND ("v_NewSalesReportView"."C_TRANSACTION_DATE">=CONVERT(DATETIME, '2007-09-29 00:00:00', 120) AND "v_NewSalesReportView"."C_TRANSACTION_DATE"<CONVERT(DATETIME, '2009-09-29 00:00:00', 120))

 It will execute the second query every time until we reload the client using ctrl + f5, where it will run the first query once again, and then the second query every time after.

Has anyone ever seen behaviour like this before? Is there something stupid I'm missing?

Thanks

[AdamTravers]

Hi Andrew,

Do you have the 'Save Data with Report' checked within the report itself under the File menu? I know that can cause some oddities when running reports.

Message Edited by AdamTravers on 09-28-2009 03:20 PM

[andrewrees]

The save data with report option is unchecked.

[mrudnick]

In your code that calls the report to you discard saved data?  Like below...

'Get RDC Object
    set Report = Application.BasicFunctions.GetCrystalReport(strReportName)
    Report.DiscardSavedData

[susanrn]

I was having a problem that my subreports were not refreshing.   Using the DiscardSavedData solved the problem!  thanks -

[toddjd]

I'm seeing a similar issue, where every time the RDC object is called after the report plugin has been loaded, it executes the SQL from the report without applying the filter for the record, in the example the "ACCOUNT_NUMBER"='ASTO08'    

So on every subsequent run after the first it is pulling excess row from the database. And the DiscardSavedData does not do the trick.

If you find the solution, please share

thanks,
Todd

[andrewrees]

I cheated in the end, and created my own crystal report viewer in Visual Studio using the standard crystal control and launched it as a .NET extension. This worked fine for me although perhaps a bit of a bodged solution.

[toddjd]

Turns out that the problem is the temp files that are created when the report is first run.  The second time you run the temp files cause the report to run using a query without the selection formula hence getting too much data.  When you reload (ctrl-f5) the temp files are purge and the next run is fine.  I sure it was designed toward efficiency, but ends up being an "undocumented feature"

My business partner worked out a way around it when displaying to the crystal viewer, but we have a need to push reports directly to PDF from buttons on the mainview, and we haven't got that worked out yet.

Writing a new crystal view would be very inconvenient due to deployment issues.

Thanks,

Todd