I was recently tasked with trying to speed up a very complex report that was based on a Stored Procedure. We wanted to see if any of the formula logic could be moved from the report to the SQL. Since there were close to 600 formulas I was hoping to skip over anything that used specific parameters that won’t easily convert. The challenge was that a formula might not mention these parameters, but might refer to other formulas that do.

To quickly rule out these formulas I needed a tool that could show me all the ‘ancestors’ of each formula and flag when these ancestors used one of the target parameters. No such tool existed, so I decided to create one using my limited programming experience and some significant AI assistance.

The source data comes from Crystal’s “Report Definition” file. This export option creates a text file that includes most of the settings in your report, including a list of all the formulas and their contents.

I used AI to create a program that would parse all the formulas in this text file and generate a dynamic tree showing them in alphabetical order. Each formula that contained other formulas would show an expandable node that would list all the formulas that it used (it’s ‘ancestors’). These formulas would in turn have nodes to show the formulas that they used, and so on. Some of the formulas in this report went down 12 or more levels.

I then asked the AI to read a separate (optional) text file containing the names of the parameters I was trying to skip. The program would highlight any formula that contained any text string contained in this file, or contained in one of it’s ancestors. Now I could go down the complete list of formulas and quickly skip the highlighted ones. knowing that these would not easily convert to SQL.

Once I had this working, I decided to add the ability to flip a switch and go in the opposite direction. This would make the nodes expand to show the formulas that use this formula (descendants) rather than the ancestors. The app met my needs for this project. It will also help me to get an overview of new reports I am asked to fix.

If anyone else has a use for this or is interested in playing around with this, let me know.

And, if you have an odd requirement where a simple application might make your life easier, let me know. I might be able to help you create something useful.