Share via

How to get the SQL for a Report Builder report

  • Article

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

Comments

  • Anonymous
    August 07, 2006
    Bob, I spoke with you several months ago with regards to passing parameters to a report model in a URL.  We are back on the project again and I was wondering if this is a possible solution.

    We are an asp application with multiple facilities and users accessing a single database.  The primary indicator for each user is "docid" which restricts the data the user can access in the database.

    We want to build a report model that when opened in Report Builder only pulls data based on the user's "docid".

    Can what is discussed in this post help with that.

    Thanks
  • Anonymous
    October 30, 2006
    The SQL is very complex for even quite simple reports, if someone I was working with wrote a report with the SQL generated I wouldnt be happy. Do you have plans to optimise this?
  • Anonymous
    December 22, 2006
    I enabled that setting and still don't get queries in the log. Where should I expect them? And why don't I get them?Here is the log:<Header> <Product>Microsoft SQL Server Reporting Services Version 9.00.2047.00</Product> <Locale>en-US</Locale> <TimeZone>Russian Standard Time</TimeZone> <Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServer__12_22_2006_07_02_28.log</Path> <SystemName>GOLF2</SystemName> <OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName> <OSVersion>5.2.3790.65536</OSVersion></Header>w3wp!webserver!5!22.12.2006-07:02:29:: i INFO: Reporting Web Server startedw3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing ConnectionType to '0'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing IsSchedulingService to 'True'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing IsNotificationService to 'True'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing IsEventService to 'True'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing IsWebServiceEnabled to 'True'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing ProcessRecycleOptions to '0'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WatsonFlags to '1064'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing SecureConnectionLevel to '0'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing DisplayErrorLink to 'True'  as specified in Configuration file.w3wp!library!5!22.12.2006-07:02:29:: i INFO: Initializing WebServiceUseFileShareStorage to 'False'  as specified in Configuration file.w3wp!resourceutilities!5!22.12.2006-07:02:29:: i INFO: Reporting Services starting SKU: Enterprisew3wp!resourceutilities!5!22.12.2006-07:02:29:: i INFO: Evaluation copy: 0 days leftw3wp!runningjobs!5!22.12.2006-07:02:29:: i INFO: Database Cleanup (Web Service) timer enabled: Next Event: 600 seconds.  Cycle: 600 secondsw3wp!runningjobs!5!22.12.2006-07:02:29:: i INFO: Running Requests Scavenger timer enabled: Next Event: 60 seconds.  Cycle: 60 secondsw3wp!runningjobs!5!22.12.2006-07:02:29:: i INFO: Running Requests DB timer enabled: Next Event: 60 seconds.  Cycle: 60 secondsw3wp!runningjobs!5!22.12.2006-07:02:29:: i INFO: Memory stats update timer enabled: Next Event: 60 seconds.  Cycle: 60 secondsw3wp!library!5!12/22/2006-07:02:31:: i INFO: Catalog SQL Server Edition = Enterprisew3wp!library!5!12/22/2006-07:10:23:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activityw3wp!library!5!12/22/2006-07:10:24:: i INFO: Call to GetSystemPermissionsw3wp!library!5!12/22/2006-07:10:28:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activityw3wp!library!5!12/22/2006-07:10:28:: i INFO: Call to GetSystemPermissionsw3wp!library!1!12/22/2006-07:10:31:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activityw3wp!library!1!12/22/2006-07:10:31:: i INFO: Call to GetSystemPermissionsw3wp!library!1!12/22/2006-07:10:33:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activityw3wp!library!1!12/22/2006-07:10:33:: i INFO: Call to GetSystemPermissionsw3wp!library!1!12/22/2006-07:10:35:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activityw3wp!library!1!12/22/2006-07:10:35:: i INFO: Call to GetSystemPermissionsw3wp!library!5!12/22/2006-07:10:38:: i INFO: Call to GetPermissions:/SharePoint reports/Users/User activityw3wp!library!5!12/22/2006-07:10:39:: i INFO: Call to GetSystemPermissions
  • Anonymous
    March 11, 2008
    Hi bwalkerI am cming very late on this page.I am having the same problem as you do, did you get a chance how to handle it.regardsDD
  • Anonymous
    January 21, 2009
    PingBack from http://www.keyongtech.com/2193971-report-builder-field-calculate-problem