Report Server Diagnostic Reports

  • Thanks Michael. Not to be a pest, but I am getting the following error:

    Msg 9414, Level 16, State 1, Line 2

    XML parsing: line 1, character 17, equal expected

  • Try using this as a substitute for the query in ReportQueries.rdl to retrieve SQL queries from reports.

    ;WITH ReportItems (ItemID, Name, Path, reportXml) AS

    (

    SELECT ItemID

    , Name

    , Path

    , CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXml

    FROM Catalog

    WHERE (Type = 2)

    AND (@reportID = CONVERT(varchar(500), ItemID)

    OR @reportID = 'NOID')

    )

    SELECT ItemID

    , Name

    , Path

    , report.commandText.value('.', 'nvarchar(MAX)') AS commandText

    FROM reportItems

    CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; //CommandText/text()') AS report(commandText)

    UNION ALL

    SELECT ItemID

    , Name

    , Path

    , report.commandText.value('.', 'nvarchar(MAX)') AS commandText

    FROM reportItems

    CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; //CommandText/text()') AS report(commandText)

  • OUTSTANDING! Thanks so much. This will help a great deal in our migration. Kudos to you!

    This is a real keeper in my bag of tricks!

    :-D:-D:-D:-D:-D:-D:-D

  • Thanks for the collaboration Jcoe - I put that query in the report - works great.

  • Thanks a lot Mike! I was just trying to figure out how to write a report server permissions report and these reports worked great for me.

    Thanks!!

  • Hi Mike,

    I was modifying the reports to provide data to one of our users on only specific reports. While I was going through the queries, it occurred to me that there is a way to more efficiently generate the inactive reports portion of the statistics report by eliminating a join:

    SELECT c.Name

    , c.Path

    , l.lastRunDate

    FROM Catalog AS c

    LEFT OUTER JOIN (SELECT ReportID, MAX(TimeStart) AS lastRunDate

    FROM ExecutionLog

    GROUP BY ReportID) AS l

    ON c.ItemID = l.ReportID

    WHERE (c.Type = 2)

    AND (

    (l.lastRunDate IS NULL)

    OR (l.lastRunDate <= DATEADD(mm, - 6, GETDATE()))

    )

    ORDER BY l.lastRunDate DESC

    , c.Name

  • Hi Mike,

    I've also come up with a slight restatement of my prior query that allows the datasource that the query runs against to be identified as well:

    ;WITH ReportItems (ItemID, Name, Path, reportXml) AS

    (

    SELECT ItemID

    , Name

    , Path, CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXml

    FROM Catalog

    WHERE (Type = 2)

    AND (

    (@reportID = CONVERT(varchar(500), ItemID))

    OR (@reportID = 'NOID')

    )

    )

    SELECT ItemID

    , Name

    , Path

    , report.query.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSourceName/text())[1]', 'nvarchar(MAX)') AS dataSourceName

    , report.query.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (CommandText/text())[1]', 'nvarchar(MAX)') AS commandText

    FROM reportItems

    CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; //Query') AS report(query)

    UNION ALL

    SELECT ItemID

    , Name

    , Path

    , report.query.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSourceName/text())[1]', 'nvarchar(MAX)') AS dataSourceName

    , report.query.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (CommandText/text())[1]', 'nvarchar(MAX)') AS commandText

    FROM reportItems

    CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; //Query') AS report(query)

  • thanks jcoe - i'll have to take a look at the data source as that is something in particular we're looking to include. the problem we have encountered is that the name (which we would report on) isn't always the true data source being used by the report. see below:

    <DataSource Name="ds_source1">

    <rd:DataSourceID>f065cf10-e585-4c14-9fe2-48e8ace9cb4e</rd:DataSourceID>

    <DataSourceReference>\Data Sources\ds_source2</DataSourceReference>

    </DataSource>

    in the above example, the code we previously instituted would display the "ds_source1" value for the data source name...while in fact the actual data source is "ds_source2". we have seen this happen as the data source name often gets populated by default during development -- this name is also not always consistent with the prod configuration. as of yet my attempts to get to the actual data source name (and connection string) haven't worked as those are encrypted.

    i will check your code to see if i get different results. i wouldn't expect the connection string to come from this...but getting the true data source name would be a win.

    thanks again

  • Great reports and very easy to set up... Great job and thanks!

  • Great one! I know how much time it would save for one using these analysis reports.

    Thanks

  • Many thanks for sharing this excellent package!

    I'm running SQL Server 2008 R2 and works great.

  • michael_davis2 (11/9/2010)


    thanks jcoe - i'll have to take a look at the data source as that is something in particular we're looking to include. the problem we have encountered is that the name (which we would report on) isn't always the true data source being used by the report. see below:

    <DataSource Name="ds_source1">

    <rd:DataSourceID>f065cf10-e585-4c14-9fe2-48e8ace9cb4e</rd:DataSourceID>

    <DataSourceReference>\Data Sources\ds_source2</DataSourceReference>

    </DataSource>

    in the above example, the code we previously instituted would display the "ds_source1" value for the data source name...while in fact the actual data source is "ds_source2". we have seen this happen as the data source name often gets populated by default during development -- this name is also not always consistent with the prod configuration. as of yet my attempts to get to the actual data source name (and connection string) haven't worked as those are encrypted.

    i will check your code to see if i get different results. i wouldn't expect the connection string to come from this...but getting the true data source name would be a win.

    thanks again

    I've done some more tinkering on this today. You can combine my prior query with this one to further resolve data sources:

    ;WITH ReportItems (ItemID, Name, Path, reportXml) AS

    (

    SELECT ItemID

    , Name

    , Path

    , CAST(CAST([Content] AS VARBINARY(MAX)) AS XML) AS reportXml

    FROM Catalog

    WHERE (Type = 2)

    --AND (

    -- (@reportID = CONVERT(varchar(500), ItemID))

    -- OR (@reportID = 'NOID')

    -- )

    )

    SELECT ItemID

    , Name

    , Path

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/@Name)[1]', 'nvarchar(MAX)') AS dataSourceName

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/DataSourceReference/text())[1]', 'nvarchar(MAX)') AS dataSourceReference

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/ConnectionProperties/DataProvider/text())[1]', 'nvarchar(MAX)') AS embeddedDataProvider

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/ConnectionProperties/ConnectString/text())[1]', 'nvarchar(MAX)') AS embeddedConnectString

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; (DataSource/ConnectionProperties/IntegratedSecurity/text())[1]', 'nvarchar(MAX)') AS embeddedIntegratedSecurity

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"; (DataSource/rd:SecurityType/text())[1]', 'nvarchar(MAX)') AS designerSecurityType

    FROM ReportItems

    CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"; //DataSources') AS report(datasource)

    UNION ALL

    SELECT ItemID

    , Name

    , Path

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/@Name)[1]', 'nvarchar(MAX)') AS dataSourceName

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/DataSourceReference/text())[1]', 'nvarchar(MAX)') AS dataSourceReference

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/ConnectionProperties/DataProvider/text())[1]', 'nvarchar(MAX)') AS embeddedDataProvider

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/ConnectionProperties/ConnectString/text())[1]', 'nvarchar(MAX)') AS embeddedConnectString

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; (DataSource/ConnectionProperties/IntegratedSecurity/text())[1]', 'nvarchar(MAX)') AS embeddedIntegratedSecurity

    , report.datasource.value(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; declare namespace rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"; (DataSource/rd:SecurityType/text())[1]', 'nvarchar(MAX)') AS designerSecurityType

    FROM ReportItems

    CROSS APPLY reportXml.nodes(N'declare default element namespace "http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition"; //DataSources') AS report(datasource)

    Unfortunately, the connection strings in the DataSource table are encrypted, so joining to that table does no good. It should, however, at least let you cross-reference the DataSourceName used by the query to the DataSourceReference and then look it up on the server manually through the UI. It will also provide connection strings that have been embedded in the report.

  • Just deployed this and it worked perfectly. It was exactly what we needed. Thanks so much for sharing!!:-D

  • Mike. Here is a couple more to add to this package. I find myself dealing with the GUID's a lot for the ReportCaching that we are using. I've included the view and report.

  • Hi Mike,

    I've done some further tinkering (trying to find a way to get those Data Sources) and figured out something really cool! You can access the Reporting Service Web Services from a report in Reporting Services. (How's that for a tongue-twister?)

    Check the bit of fun below out for a Reports project in BIDS 2008 R2. If you're using plain 2008, shared datasets are not supported. You will need to follow a very similar process to create the Datasets directly in the reports.

    Create an XML Data Source for the Report Service Web Service

    Create a Query to the List Children Method

    • Right click on the Shared Datasets folder.
    • Select Add New Dataset.
    • Set the Name to ReportService2005_ListChildren.
    • Set the Data source to ReportService2005WebServiceAPI.
    • Set the Query type to Text.
    • Place the following code into the Query:

      <Query>

      <Method Name="ListChildren" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">

      <Parameters>

      <Parameter Name="Item"/>

      <Parameter Name="Recursive"/>

      </Parameters>

      </Method>

      <ElementPath xmlns="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">

      ListChildrenResponse/CatalogItems/CatalogItem

      {

      ID(string),

      Name(string),

      Path(string),

      VirtualPath(string),

      Type(string),

      Size(integer),

      Description(string),

      Hidden(boolean),

      CreationDate(date),

      ModifiedDate(date),

      CreatedBy(string),

      ModifiedBy(string),

      MimeType(string),

      ExecutionDate(date)

      }

      </ElementPath>

      </Query>

    • Go to the Parameters page.
    • Click OK on the warning that about "Could not create a list of fields for the query."
    • Click Add.
    • Set the Name to Item.
    • Set the Default Value to /.
    • Set the Data Type to Text.
    • Click Add.
    • Set the Name to Recursive.
    • Set the Default Value to true. (Case must be exact.)
    • Set the Data Type to Boolean.
    • Go back to the Query page.
    • Click Refresh Fields.
    • Enter / in the Parameter Value for Item.
    • Enter true in the Parameter Value for Item.
    • Click OK.

    Create a Query to the GetItemDataSources Method

    • Right click on the Shared Datasets folder.
    • Select Add New Dataset.
    • Set the Name to ReportService2005_GetItemDataSources.
    • Set the Data source to ReportService2005WebServiceAPI.
    • Set the Query type to Text.
    • Place the following code into the Query:

      <Query>

      <Method Name="GetItemDataSources" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">

      <Parameters>

      <Parameter Name="Item" Type="string"/>

      </Parameters>

      </Method>

      <ElementPath xmlns="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">

      GetItemDataSourcesResponse/DataSources/DataSource

      {

      Name(string)

      }

      /DataSourceReference

      {

      Reference(String)

      }

      </ElementPath>

      </Query>

    • Go to the Parameters page.
    • Click OK on the warning that about "Could not create a list of fields for the query."
    • Click Add.
    • Set the Name to Item.
    • Set the Data Type to Text.
    • Go back to the Query page.
    • Click Refresh Fields.
    • Enter the path to a report or model, i.e. /MyReportFolder/MyReport, in the Parameter Value for Item.
    • Enter true in the Parameter Value for Item.
    • Click OK.

    Create a Query to the GetDataSourceContents Method

    • Right click on the Shared Datasets folder.
    • Select Add New Dataset.
    • Set the Name to ReportService2005_GetDataSourceContents.
    • Set the Data source to ReportService2005WebServiceAPI.
    • Set the Query type to Text.
    • Place the following code into the Query:

      <Query>

      <Method Name="GetDataSourceContents" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">

      <Parameters>

      <Parameter Name="DataSource"/>

      </Parameters>

      </Method>

      <ElementPath xmlns="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">

      GetDataSourceContentsResponse/Definition

      {

      Extension(string),

      ConnectString(string),

      UseOriginalConnectString(boolean),

      OriginalConnectStringExpressionBased(boolean),

      OriginalConnectString(string),

      CredentialRetrieval(string),

      WindowsCredentials(boolean),

      ImpersonateUser(boolean),

      Prompt(string),

      UserName(string),

      Enabled(boolean)

      }

      </ElementPath>

      </Query>

    • Go to the Parameters page.
    • Click OK on the warning that about "Could not create a list of fields for the query."
    • Click Add.
    • Set the Name to DataSource.
    • Set the Data Type to Text.
    • Go back to the Query page.
    • Click Refresh Fields.
    • Enter the path to a data source, i.e. /MyDataSourceFolder/MyDataSource, in the Parameter Value for DataSource.
    • Enter true in the Parameter Value for Item.
    • Click OK.

    Create a Query to Get the Report Data Sources Using the GetReportDefintion Method

    • Right click on the Shared Datasets folder.
    • Select Add New Dataset.
    • Set the Name to ReportService2005_GetReportDefinition_ReportDataSources.
    • Set the Data source to ReportService2005WebServiceAPI.
    • Set the Query type to Text.
    • Place the following code into the Query:

      <Query>

      <Method Name="GetReportDefinition" Namespace="http://schemas.microsoft.com/sqlserver/2005/06/30/reporting/reportingservices">

      <Parameters>

      <Parameter Name="Report"/>

      </Parameters>

      </Method>

      <ElementPath IgnoreNamespaces="true">

      GetReportDefinitionResponse/Definition(Base64Encoded)/Report{}/DataSources/DataSource

      {

      @Name(string),

      DataSourceReference(string),

      DataSourceID(string),

      SecurityType(string)

      }

      /ConnectionProperties

      {

      IntegratedSecurity(string),

      ConnectString(string),

      DataProvider(string)

      }

      </ElementPath>

      </Query>

    • Go to the Parameters page.
    • Click OK on the warning that about "Could not create a list of fields for the query."
    • Click Add.
    • Set the Name to Report.
    • Set the Data Type to Text.
    • Go back to the Query page.
    • Click Refresh Fields.
    • Enter the path to a data source, i.e. /MyReportsFolder/MyReport, in the Parameter Value for DataSource.
    • Enter true in the Parameter Value for Item.
    • Click OK.

    You can then create appropriate reports and subreports to fully resolve datasource information.

    There are very few things in the report server that cannot be accessed this way. See the following documents on MSDN for more information on:

    ReportingService2005 Class

    Reporting Services: Using XML and Web Service Data Sources

    XML Query Syntax for Specifying XML Report Data (SSRS)

    Element Path Syntax for Specifying XML Report Data (SSRS)

    SQL Server RDL Specification

    Hopefully this is as entertaining for you as it was for me. I cheered when I figured this out!

    James.

Viewing 15 posts - 76 through 90 (of 117 total)

You must be logged in to reply to this topic. Login to reply