Product Tip of the Month – Prolog®
Comparing and Analyzing Report Manager or Query Manager Results
By Steve Van Dyke, Meridian’s Prolog Product Lead
Have you ever run a report or query that does not return the results expected according to the filters, sort order, or formulas provided? This Product Tip of the Month is intended for users with an intermediate to advanced knowledge of Microsoft SQL.
Using this process, you will be able to see the SQL script Report Manager or Query Manager is using to pull data from your database for the content of your report or query. This information can be used to perform a comparison analysis on the returned results verses the expected results. To view and analyze the SQL script, please perform the following steps:
Step 1: Open Prolog’s Report Manager.
Step 2: Run and print the report.
Step 3: Close the report.
Step 4: Click on the Designer tab and click once in the Record Filters box on the left side.
Step 5: Using your keyboard press CTRL + C to copy the SQL script to your clipboard.
Note: Right click will not work.
Step 6: Open Notepad and press CTRL + V to paste the SQL script into Notepad. Compare the script with your Prolog Report and analyze what is being returned verses what should be returned according to the SQL script. If you are using a full version of Microsoft SQL you can also paste and run the SQL script in SQL Query Analyzer or SQL Enterprise Manager to compare the results between SQL and Prolog.
Note: This example uses Report Manager, but can also be applied to Query Manager.
Quick Tip – Run the Verify Database Integrity Utility and if a Costing Report, run the Recalculate Budget Utility, then run the report again. These utilities fix common Report or Query result problems. These utilities are found under the Tools Menu – Database Utilities.
