Sqlize Crystal Reports

Sqlize Crystal Reports

Atlas BI Library ETL | Crystal Reports Supplimentary ETL

Website β€’ Demo β€’ Documentation β€’ Chat

maintainabilitydiscord chatlatest release

Make a wild swing at converting Crystal Reports into SQL and extracting useful metadata.

∞ πŸ”§ How Does it Work?

Sqlize Crystal Reports runs Aidan Ryan’s RptToXml converter to convert a directory of SAP Crystal Reports into XLM files, and then makes a strong attempt at parsing that XML out into a somewhat readable and potentially runnable t-sql statement. The results are saved into a database table along with the reports:

  • FileName
  • Title
  • Description
  • Query

If mutliple queries are found in the report, there will be a db entry for each query.

πŸ‘· Please chip in if you see a way to make the sql more runnable or code more readable.

good luck from here 😏

∞ πŸƒ Getting Started

∞ First, install SAP’s Crystal Reports, Developer for Visual Studio, SP 28

Here are a few links to try -

We are on a 64bit Windows Machine and built the executable with the 64 drivers. If you are on a 32bit machine you might as well rebuild from the source.
Install:

  • SAP Crystal Reports for Visual Studio (SP28) runtime engine for .NET framework MSI (64-bit)
  • SAP Crystal Reports for Visual Studio (SP28) runtime (64-bit)

Maybe the 2nd install is redundant?

∞ Next, install a few Python packages

pip install pyodbc lxml sqlparse requests xmltodict

∞ Create Database

There are a few tables to create -

USE [CrystalSQL]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TABLE [dbo].[Reports](
[Name] [nvarchar](max) NULL,
[Reference] [nvarchar](max) NULL,
[ReportId] [nvarchar](max) NULL,
[DocumentId] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Templates](
[ReportName] [nvarchar](max) NULL,
[Query] [text] NULL,
[Title] [nvarchar](max) NULL,
[Description] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Attachments](
[HRX] [nvarchar](max) NULL,
[PDF] [nvarchar](max) NULL,
[CreationDate] [datetime] NULL,
[Name] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Documents](
[Name] [nvarchar](max) NULL,
[Description] [nvarchar](max) NULL,
[FolderId] [nvarchar](max) NULL,
[Cuid] [nvarchar](max) NULL,
[DocumentId] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

CREATE TABLE [dbo].[Objects](
[Title] [nvarchar](max) NULL,
[Cuid] [nvarchar](max) NULL,
[StatusType] [nvarchar](max) NULL,
[Type] [nvarchar](max) NULL,
[LastRun] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

Don’t forget to add a user account that can delete and insert.

∞ Create .env file

(or, pass the variables as environment variables)

database = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;DATABASE=database_name;UID=username;PWD=password'

# get report sql settings
rpt_src = '\\\\network\\c$\\path\\to\\.rpt\\files\\'

# get report data settings
sap_api_username = "BOE_REPORT"
sap_api_password = "password"
sap_api_url = "http://server.example.net"

# get report files settings
crystal_boe_output_drive = "\\\\server\\Output"

∞ Running

There are three parts to this ETL that can be run separately.

python get_report_data.py # loads BOE report links
python get_sql.py # gets report sql code
python get_report_files.py # gets report output links. passed to Atlas as run links

∞ πŸ† Credits

Special thanks to Aidan Ryan for creating the RptToXml converter.