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 your Report returns the results that are expected. You can find the SQL query generated by the report in two different ways:
- Report Designer Show SQL Dialog
- By viewing the SQL statement in the log file
- Implementing a Server Event to show the SQL instead of the report output
Report Designer Show Generated SQL Dialog
Use the Show Generated SQL/Show SQL option in the Advanced Report Designer to show the constructed SQL queries that will be executed with the report. See the Show SQL section of the Advanced Options article for more information.
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 and run it against your 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.
throw new WebReports.Api.Common.WrUserMessage(args[0].ToString(), WebReports.Api.Common.WrUserMessageType.Text);
return null; //This event should be set to run on 'OnExecuteSQLStatementConstructed'
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.
