Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

SQL Server - SSRS - Where to find query generating the report

The guy who created all the reporting using SSRS has left, and I am trying to troubleshoot one of the reports being generated. How do I get access to look at the query that is generating the report.

like image 970
Chris Avatar asked Feb 18 '11 17:02

Chris


People also ask

How do I see queries in report Builder?

In Report Builder, open the report that you created. On the report results page, near the upper right, click Edit. Click Format results, and expand the Advanced section. You can see the generated query for the report that you created.

How do I edit a query in SSRS report?

To edit a query field or a dataset field In the Report Data pane, right-click the field, and then click Field Properties. In the Fields page of the Dataset Properties dialog box, click an existing field to select the row. Change the name of the field or the value of the field. Select OK.

Where is the RDL files location?

rdl files are stored in the Content column of ReportServer. dbo. Catalog.


1 Answers

This will give a listing of the reports with their xml report definition:

SELECT Name,
CAST(CAST(Content AS VARBINARY(MAX)) AS XML) AS ReportXML
FROM ReportServer.dbo.Catalog
where Type = 2
order by Name

The info you want is in the DataSets section, example below. There may be more than one query / stored procedure in the report.

<DataSets>
    <DataSet Name="SalesSummary">
        <Query>
            <CommandType>StoredProcedure</CommandType>
            <CommandText>rptSalesSummary</CommandText>
            <QueryParameters>
                <QueryParameter Name="@STARTDATE">
                    <Value>=Parameters!STARTDATE.Value</Value>
                </QueryParameter>
                <QueryParameter Name="@ENDDATE">
                    <Value>=Parameters!ENDDATE.Value</Value>
                </QueryParameter>
            </QueryParameters>
            <DataSourceName>SampleDB</DataSourceName>
        </Query>
        .
        .
        .
   </DataSet>
    <DataSet Name="CompanyLocation">
        <Query>
            <DataSourceName>SampleDB</DataSourceName>
            <CommandText>
                SELECT CompanyLocationID AS ID, Name 
                                    FROM tblCompanyLocation
                WHERE WareHouseOnlyInd = 0
                ORDER BY Description
            </CommandText>
            <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
        </Query>
    </DataSet>
    .
    .
    .

See http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition/ReportDefinition.xsd

like image 123
jim31415 Avatar answered Sep 28 '22 07:09

jim31415