Saturday, June 30, 2012

Quick Way to Troubleshoot a Report.

Hi Everyone,

Report is usually connected to one or many data sources, in Acumatica they are represented by Data Access Classes (DAC). Sometimes DAC is not really what we have in SQL database. DAC may have calculated synthetic fields, added during its [long] life. DAC may also have additional fields from other tables.

For example: FABookHistory data access class has field CreatedByID which corresponds to field in its main table from SQL DB, but also it has field CreatedByID_Description, which is coming from joined table with user names.

And all of us, are professionals with SQL of course. :)
If something goes wrong with our report what we can do?

1. Run SQL Profiler and try to catch query our report is running.

Well its ok, except if we trace it on production environment with multiple users printing similar reports or simply loading the server with their requests. I am not saying Profiler is a bad choice, it is OK, but sometimes it may be difficult to trace.

2. We just added a much simpler way to get SQL query report runs. And this query will include all the parameters we added to report. Here is how to see it.

First, run the report with all the parameters

Second, once you saw the report outcome, press Help->Trace

Third, find on the trace block that responsible for our query

Copy that query to clipboard. Then you can either paste it into your Query Analyser or use one useful tool to format it before. Magic Tool

Be Happy,


No comments:

Post a Comment