June 3, 2010 at 8:59 am
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
June 10, 2010 at 9:59 am
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)
June 10, 2010 at 4:29 pm
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
June 11, 2010 at 6:04 am
Thanks for the collaboration Jcoe - I put that query in the report - works great.
November 8, 2010 at 8:21 am
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!!
November 8, 2010 at 2:24 pm
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
November 8, 2010 at 2:39 pm
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)
November 9, 2010 at 5:46 am
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
December 20, 2010 at 9:54 am
Great reports and very easy to set up... Great job and thanks!
March 1, 2011 at 6:31 am
Great one! I know how much time it would save for one using these analysis reports.
Thanks
April 12, 2011 at 7:19 am
Many thanks for sharing this excellent package!
I'm running SQL Server 2008 R2 and works great.
April 12, 2011 at 10:34 am
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.
April 28, 2011 at 11:14 am
Just deployed this and it worked perfectly. It was exactly what we needed. Thanks so much for sharing!!:-D
September 14, 2011 at 4:49 pm
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.
January 11, 2012 at 5:27 pm
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
Go to the Credentials page.
Create a Query to the List Children Method
<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>
Create a Query to the GetItemDataSources Method
<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>
Create a Query to the GetDataSourceContents Method
<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>
Create a Query to Get the Report Data Sources Using the GetReportDefintion Method
<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>
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:
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)
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