Troubleshooting Report Output

If you are receiving report output that you believe to be incorrect, you have the ability to verify that the SQL query being generated by the Report returns the results that are expected. You can find the SQL query generated by the report in a few different ways:

1. Report Designer Show SQL Dialog
2. By viewing the SQL statement in the log file
3. Implementing a Server Event to show the SQL instead of the report output

Report Designer Show Generated SQL Dialog

Use theShow Generated SQL/Show SQL option in the Advanced Report Designer to show the constructed SQL queries that will be executed with the report. See Show SQL v2021.1+

SQL Statement in Log File

The SQL statement that is generated with every report execution can be viewed within the log file, allowing you to take the query and run it against the data to validate its correctness.

Within the log file, the SQL statement will be formatted as such:

2016-01-27 10:08:31,297 INFO [founspz1bwdhpiixlvelew34] [Api.Data.DbConnect.Execute] SQL Stmt: SELECT dbo.[Categories].[CategoryName] as c0,dbo.[Products].[ProductID] as c1,dbo.[OrderDetails].[OrderID] as c2,dbo.[OrderDetails].[ProductID] as c3,dbo.[Categories].[CategoryID] as c4,dbo.[Products].[CategoryID] as c5
FROM dbo.[Products] inner join dbo.[OrderDetails] on (dbo.[Products].[ProductID] = dbo.[OrderDetails].[ProductID]) inner join dbo.[Categories] on (dbo.[Products].[CategoryID] = dbo.[Categories].[CategoryID]) 

Show SQL Server Event

In addition to viewing the SQL statement within the log file, you can implement a Server Event to display the SQL query at runtime either globally or on a per report basis. Below you can find the code you will need to get started with the Server Event.

This event should be set to run on 'OnExecuteSQLStatementConstructed'
throw new WebReports.Api.Common.WrUserMessage(args[0].ToString(), WebReports.Api.Common.WrUserMessageType.Text);
return null; 

Global Event

To set the Server Event to run globally, the event will need to be implemented OnExecuteSQLStatementConstructed. Every report execution that occurs will then be halted, and the SQL statement will show instead of the execution as such.