need help - newbie questions

[Former Member]

Hello,

I am working on an existing report using Crystal Report XI R2. The report pulls several tables/views data from SQL 2005. All business rules are defined in either formated and/or calculated fields. The tables are linked inside the report when it was created long back. I need a new field to the final dataset returned, so it can be used for additional grouping purpose. Since that additional field is determined based the user input (Year) to lookup (quite complex), I created a Stored Procedure taking two parameters from report and returns a list of AccountID and that additonal field.

I am able to add this Stored Procedure to current database tables access in the report and link it with other table using the key. The only warning I got is "More than one datasource or a stored procedure has been used in this report. Please make sure that no SQL Expression is added and no server-side group by is performed", regardless if I reset the Data Source Location from History tree or New Connection from the Database Expert.

When I run the report and provide values to the parameters auto-generated by adding the Stored Procedure, I got "Fail to retrieve data from Database" followed by "Invalid Argument Provided". When I click "Show SQL Query", it shows two separate SQL SELECT statement.

Because the existing report contains all the business rules in several hundred calculated fields and formatted fields, putting all of them back to single Stored Procedure would be a huge task. I thought Stored Procedure returning dataset like regular table access can be a quick fix. Can I link the fields returned from Stored Procedure with Tables? If so, what I might have missed here? What went wrong with the Stored Procedure argument? Is this something to do with the connection or the separate SELECT statement displayed in the "Show SQL Query" dialogbox?

Thanks,

Michael

[Former Member · Accepted solution]

Hi Michael,

Have you applied any service pack for Crystal Reports?

Does the issue is with single Report or all the Report?

Could you please try to create a new sample report using same database for testing purpose.

Regards,

Shweta

[Former Member · Accepted solution]

Shweta,

I using remote desktop to access the server and I have no idea if SP been applied or not. How to tell from the Crystal Report?

The report I am revising is the first only report. Thus, I have no idea other reports.

Just curious, what's the purpose of recreating a dummy simple report for? I can try recreate a dummy simple one. However, recreating whole report would take a long time, as there are hundreds of formulated fields which implements all the business rules before been used in report. Let's put it this way - the whole report design screen, Details, footers are all packed with fields. Don't ask me why as I know nothing of this report history. :=)

Thx,

Michael

[Former Member · Accepted solution]

Hi Michael,

For testing purpose create the sample report with same database just place few feilds.

No need to create exactly same report.

The purpose of creating sample report is to narrow down the issue .

Please check that issue is with single Report or all the Reports?

Have you tried to update database from Database->Set Datasource location?

Do a small test to test your connection.

-Right Click on desktop ->

Create new text document->

Save as test.UDL->

double click and test connection by selecting driver name,datasource name,user id and password.

Click on Test Connection.

It should be succeed.

Regards,

Shweta

[Former Member · Accepted solution]

Hello Shweta,

The UDL testing works fine.

Also, I did try to recreate a simple report as you suggested. I included a couple fo tables and the Stored Procedure (SPROC) and link them in Crystal report. However, it shows blank in test-run. I guess it may be because I am not familiar with creating report by linking tables and Stored Procedure and fields directly from CR. At least, the "Invalid Argument Provided" error is gone. I don't know if this answers to what you suggested.

CR crashed:

Based on above experience, I made a copy of original report and added SPROC to it by "creating a new connection" from Database Expert wizard, then add linked the SPROC to one of existing tables. However, after I add a new group based on the field returned by Stored Procedure, I test-run the report and the whole CR crashed. In the event log, it shows "Faulting application crw32.exe, version 11.0.0.895, faulting module crqe.dll, version 11.0.0.1282, fault address 0x000a4d5c". The SQL Profiler shows the stored procedure was executed successfully. I haven't had luck from googling it.

FYI, the report without the new changes is still working fine though.

Any suggestion? I may post another thread for the CR crashed issue.

Thx,

Michael