I’m working on the project where we should do some reporting. We choosed Crystal Reports as the reporting technology.
Everything worked fine until the testers started to test the reports on the testers’ DB. We use the testers for testing purposes ;o) We tought that it’ll be enough to change the connection string via CrystalReports DB connection API and it will work. But there were quite big problems and we found quite many people have the same problem how to set the connection in the runtime.
So how to solve the problems:
- use the data reader as custom data access object
- use ODBC connection and use CrystalReports API to do the weird crystal repotrs settings
We choosed option 2 as it was for us more suitable. So what we did:
- setup ODBC connection named i.e. coras with integrated windows security and default DB – coras_dev.
- create report with the input parameters i.e. via Visual Studio and use ODBC DSN name ‘coras’ (previously created) as the connection. Set the integrated security as is depicted on the picture.
- Our reports have several parameters and when the user wants to generate the report he is asked for the parameters via autogenerated web page.
- execute the report in the code. I dont want to write whole code as we use a wrapper around standard crystal reports but the core setuping code is:
TableLogOnInfo crystalTableLogOnInfo = new TableLogOnInfo();
ConnectionInfo crConnectionInfo = new ConnectionInfo();
crystalDatabase = this.Report.Database;
crystalTables = crystalDatabase.Tables;
foreach (Table crTable in crystalTables)
crystalTableLogOnInfo = crTable.LogOnInfo;
crystalTableLogOnInfo.ConnectionInfo.ServerName = this.ConnectionSettings.Server;
crystalTableLogOnInfo.ConnectionInfo.DatabaseName = this.ConnectionSettings.Catalog;
crystalTableLogOnInfo.ConnectionInfo.UserID = this.ConnectionSettings.UserID;
crystalTableLogOnInfo.ConnectionInfo.Password = this.ConnectionSettings.Password;
crystalTableLogOnInfo.TableName = crTable.Name;
crTable.Location = crTable.Name;
How the code works? It’s general code for ADO.NET conected Crystal Reports. Anyway the most important thing is that it’s necessary to iterate over the tables (in our case it’s stored procedure) and resets the table’s logon info, table name, apply the logon info and set the location! The setter causes the connection to the DB and recheck that the table exists (in our case stored procedure) – output from SQL Profiler:
exec [sys].sp_stored_procedures N'usp_GetAllocationsOfMyResources',N'%',NULL,@fUsePattern=1 exec [CORAS_DEV].[sys].sp_sproc_columns_90 N'usp_getAllocationsOfMyResources;1',N'dbo',N'CORAS_DEV',NULL,@ODBCVer=3,@fUsePattern=1 exec [CORAS_DEV].[sys].sp_stored_procedures N'usp_getAllocationsOfMyResources',N'dbo',N'CORAS_DEV',@fUsePattern=1
It worked fine until I have the parameters. The output from SQL profiler:
exec CORAS_DEV.dbo.usp_getAllocationsOfMyResources;1 null,null,null
I found that the parameters (which I set in the other code) are not sent to the stored procedure execution as parameters! I found that there are sent to the report but not to DB access layer. The problem is that there is mapping between parameters passed to Crystal and parameters for stored procedure. When I change the connection in runtime, then the stored procedure has new parameters (their are specified via full name – [server].[schema].[sp]) So it’s necessary to recreate also crystal parameters and remap them to the current stored procedure params. That’s the reason why I had to call VerifyDatabase(). The output from SQL profiler:
exec CORAS_DEV.dbo.usp_getAllocationsOfMyResources;1 ''2008-05-23 18:00:18:000'',''2008-05-23 18:00:18:000'',1720
After this call I set the parameters and the report works fine.
We spent with elaboring Crystal Reports quite much time and I’m now gray haired developer but it works now!!!
Any comments, feedback, etc. ?