thoughts on coding

May 23, 2008

Crystal Reports hell…

Filed under: Uncategorized — Tags: , — Frantisek @ 4:12 pm

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:

  1. use the data reader as custom data access object
  2. 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:

  1. setup ODBC connection named i.e. coras with integrated windows security and default DB – coras_dev.
  2. 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.
  3.  

  4. Our reports have several parameters and when the user wants to generate the report he is asked for the parameters via autogenerated web page.
  5. 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;
 
if (!string.IsNullOrEmpty(this.ConnectionSettings.Server))
    crystalTableLogOnInfo.ConnectionInfo.ServerName =
this.ConnectionSettings.Server;
 
if (!string.IsNullOrEmpty(this.ConnectionSettings.Catalog))
    crystalTableLogOnInfo.ConnectionInfo.DatabaseName =
this.ConnectionSettings.Catalog;
  if (!string.IsNullOrEmpty(this.ConnectionSettings.UserID))
    crystalTableLogOnInfo.ConnectionInfo.UserID =
this.ConnectionSettings.UserID;
  if (!string.IsNullOrEmpty(this.ConnectionSettings.Password))
    crystalTableLogOnInfo.ConnectionInfo.Password =
this.ConnectionSettings.Password;
  crystalTableLogOnInfo.TableName = crTable.Name;
  crTable.ApplyLogOnInfo(crystalTableLogOnInfo);
  crTable.Location = crTable.Name;
}
this.Report.VerifyDatabase();

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. ?  

Peace!

Advertisements

1 Comment »

  1. rezzing an old comment, but man i’m pullin my hair out that it constantly has to validate/verify the tables/procs are there at runtime with these reports! it is insane to think that I should want, let alone REQUIRE Crystal reports to verify that all my tables/views/sprocs have the columns whenever I run a report in runtime.

    Comment by sean — July 17, 2013 @ 7:58 pm


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Blog at WordPress.com.

%d bloggers like this: