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:
- By viewing the SQL statement in the log file
- Implementing a Server Event to show the SQL instead of the report output
SQL Statement in Log File
The SQL statement that is generated with every report execution can be viewed within your log file, allowing you to take the query and and run it against your data to validate its correctness.
Note: If you do not know how to locate your log file, please read this guide on Accessing the Log File
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.ToString(), WebReports.Api.Common.WrUserMessageType.Text);
return null; //This event should be set to run on 'OnExecuteSQLStatementConstructed'
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.
Below you can find the code you will need to get started with the Server Event