How to get the SQL for a Report Builder report
There are two ways to get the generated SQL for a Report Builder report (or for a model-based query in any RS report): 1) use SQL Profiler to capture the incoming SQL commands, or 2) enable query logging in the report server.
To enable query logging on the report server (option #2), make the following change to the web.cofig file in your ReportServer install directory:
Before:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
...
<RStrace>
...
<add name="Components" value="all,RunningJobs:3,SemanticQueryEngine:2,SemanticModelGenerator:2" />
</RStrace>
After:
<?xml version="1.0" encoding="utf-8" ?>
<configuration>
...
<RStrace>
...
<add name="Components" value="all,RunningJobs:3,SemanticQueryEngine:4,SemanticModelGenerator:2" />
</RStrace>
You can then run a report and you will find the corresponding SQL statement in the log file at:
...\Reporting Services\LogFiles\ReportServer_<datetime>.log
