Tracking Changes in Crystal Reports
It can be both a positive and negative when a vendor continually updates reports for you: on one hand, you are getting updated reports to ensure data accuracy - but on the other hand: do you really know what's going on under the hood?
Looking at the Amtelco ecosystem, I noticed there was a mechanism to update reporting inside the Intelligent Series Supervisor System section. Well, I've seen (and used) it before, but someone recently commented that it would be nice to understand what the updates are when they click the button.
"The Crystal Reports business intelligence application is incorporated in IS reports as the report generation engine. " - Amtelco.com
Technically this information is straight-forward enough to access - but it is exactly that: technical. To get a look into a particular Crystal Report, you have to be able to edit the .rpt file itself using something like SAP Crystal Reports 2020.
So with Amtelco, it looks a little-bit like this:
- Open Intelligent Series Supervisor
- Head over to the Reporting section
- Find the particular Crystal Report you care about
- Click the Pencil icon to export the report as
.rpt - Save and open the Crystal Report
- Review the Crystal Report Database Commands
And that's for just one report - not an easy process to follow when the current report total is around 300 last I checked (MDR, CTE, Genesis, and IS.)
But it's likely that since Amtelco is doing ongoing report upgrades that those reports are saved into the Intelligent database. This information might allow us to do a mass export, saving some man-hours from manually pulling close to 300 reports form IS Supervisor (realistically you would do only the ones you specifically care about.)
Turns out that with a little PowerShell, we can connect to the SQL server, jump into the rptReports table, and save the binary data into a file which opens nicely in SAP Crystal Reports 2020. It's the same thing IS Supervisor is doing when we click the Pencil icon. We do need to ignore the .URA files since we only care about the Crystal Report reports (side note: you should stop using Unified Reports.)
This is still too much work though - opening each of the reports to copy/paste the SQL Database Command being used - and then we also have to keep track of the ongoing changes being made between report updates.
This is suddenly a project.
Too bad there isn't a pragmatic method from SAP Crystal Reports that would allow us grab that via a script of some sort - or is there? Doesn't Amtelco use these Crystal Reports in their Reporting section - which means there has to be some method of embedding them, right? Look no further than the SAP Crystal Reports Runtime and SDK.
By referencing the correct assemblies (i.e., CrystalDecisions.CrystalReports.Engine) we can make a simple C# console application that iterates through our .rpt files in a directory, opens them using the Crystal Reports SDK, grabs the SQL Database Command, and saves it to a .sql file.
At this point we have every Crystal Report exported into a folder and the associated SQL commands saved into a readable file alongside.
We still don't have an easy way to track changes between report updates, so we still have a black-box of changes to unravel. But luckily we can use our normal development tools - namely, git diff, to dig into the Crystal Reports SQL Database Command changes between each update. This could allow us to establish formulas and understand exactly the changes being made to the reports.
Then, the process looks a little more approachable:
- Run the PowerShell script to export all of the Crystal Reports to disk
- Run the C# application to export all of the SQL Database Commands from each Crystal Report
- Commit the
.sqland.rptfiles into git - Review the
git diffto see what's changed
Each time there is a report upgrade, we can follow this process and use normal git comparison tools to see exactly what changes we should expect even if they aren't well documented outside of the reports themselves.
Git is ostensibly a developer tool, but it's great for analysis of anything you can save into a text file including changes over time. For our purposes, we'll be able to compare the changes in the SQL command (and even parameters) exported from the Crystal Report to determine exactly how reports have changed from update to update.
All 300 reports and their SQL commands are coming to the Call Theory Script Library later this week allowing easy reference without having to manually export each report yourselves.
You'll also find the PowerShell Crystal Report Export script to pull your reports from SQL server and the CrystalReportSQLExtracter .NET console app source so you can extract the SQL commands yourself (i.e, for reports and more.)
