There are many reasons why extracting a SalesLogix Report from the SalesLogix database might be useful. There are possibilities of integration, automated running of reports, as well as the need to generate a PDF from some code on a server using a SalesLogix Crystal report.
SalesLogix stores reports in the DATA field, which is a binary (BLOB) field in the PLUGIN table. Not just the report, but the SalesLogix report plugin, which contains not only the report itself but also the plugin info for the report. All together in a single BLOB field. This BLOB field is the serialized Delphi class named TCRWReportWrapper with the report file bytes appended to the end of the serialized class bytes.
Luckily, a serialized Delphi class will always end in two consecutive NULL bytes (0x00, 0x00) and will never contain two consecutive NULL bytes internally. This makes out job a bit easier in knowing where the serialized Delphi class ends and the bytes making up the report file begin. We can read out the bytes from PLUGIN.DATA for the report record and then read through them until we find the two consecutive NULL bytes, when write out everything following to a file and we'll have the report itself.
Here is some C# code to do just that:
using System;
using System.Data;
using System.Data.OleDb;
using System.IO;
namespace FX.SalesLogix.Utility
{
public class ReportExtractor
{
public ReportExtractor(string ConnectionString)
{
this.ConnectionString = ConnectionString;
}
public string ConnectionString { get; set; }
public void GetReport(string PluginID, string DestinationFile)
{
using (OleDbConnection conn = new OleDbConnection(this.ConnectionString))
{
conn.Open();
using (OleDbCommand cmd = new OleDbCommand("SELECT DATA FROM PLUGIN WHERE PLUGINID = ?", conn))
{
cmd.Parameters.Add(new OleDbParameter("PLUGINID", PluginID));
// Get the plugin byte array
byte[] data = (byte[])cmd.ExecuteScalar();
// Look for the two consecutive null bytes
int idx = 0;
for (int i = 0; i < data.Length; i++)
{
if ((int)data[ i ] == 0 && (int)data[i + 1] == 0)
{
idx = i + 2;
break;
}
}
// Get everything after the two consecutive bytes
byte[] rpt = new byte[data.Length];
Array.Copy(data, idx, rpt, 0, data.Length - idx);
// Write out to an RPT file
using (FileStream f = new FileStream(DestinationFile, FileMode.CreateNew))
{
f.Write(rpt, 0, rpt.Length);
f.Close();
}
}
}
}
}
} |
To use the code you'd do something like the following:
// Extract report to C:\MyReportFile.rpt
ReportExtractor rptextractor = new ReportExtractor(MyConnectionString);
rptextractor.GetReport("pDEMOA0000HQ", @"C:\MyReportFile.rpt"); |
Now you can do whatever you'd like with the report such as open it in an RDC object and generate a PDF from it.