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.
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.
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.
rdl files are stored in the Content column of ReportServer. dbo. Catalog.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With