need help - newbie questions
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?