Showing results for 
Search instead for 
Do you mean 
Community Home Request Access Read Blogs Share Your Ideas Search Community View My Settings
Reply
Copper Contributor
Posts: 33
Registered: ‎08-19-2009

Weird issues with a crystal report..

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

Bronze Elite Contributor
Posts: 514
Registered: ‎03-24-2009

Re: Weird issues with a crystal report..

[ Edited ]

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
Regards, Adam Travers
empath-e Services Limited
Copper Contributor
Posts: 33
Registered: ‎08-19-2009

Re: Weird issues with a crystal report..

The save data with report option is unchecked.

 

 

Highlighted
Copper Elite Contributor
Posts: 33
Registered: ‎02-10-2009

Re: Weird issues with a crystal report..

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

 

Martin Rudnick
Wells Fargo
SalesLogix Product Manager

"When in doubt, log out...and reboot"
Astute Commentator
Posts: 26
Registered: ‎07-22-2009

Re: Weird issues with a crystal report..

I was having a problem that my subreports were not refreshing.   Using the DiscardSavedData solved the problem!  thanks -
Copper Contributor
Posts: 7
Registered: ‎02-05-2010

Re: Weird issues with a crystal report..

 

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
Copper Contributor
Posts: 33
Registered: ‎08-19-2009

Re: Weird issues with a crystal report..

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.
Copper Contributor
Posts: 7
Registered: ‎02-05-2010

Re: Weird issues with a crystal report..

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

New Member
Posts: 15
Registered: ‎08-18-2009

Re: Weird issues with a crystal report..

A little late in the game but after experiencing a similar issue I believe I have a workaround.

Like all the exmaples I could find I used to limit the rows using the objRDC.RecordSelectionFormula.

I went ahead and also changed the objRDC.SQLQueryString and now all is working.

 

Roy Helmer.