Report Server Diagnostic Reports

Question

Post reply

Report Server Diagnostic Reports

Viewing 15 posts - 91 through 105 (of 117 total)

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

michael_davis2 Mr or Mrs. 500 Points: 521 More actions · Answer 1

Awesome stuff James!!!

This is a really great find and is in many ways a holy-grail find for what we've been trying to do!!!

jcoe SSC Enthusiast Points: 112 More actions · Answer 2

michael_davis2 (1/12/2012)
Awesome stuff James!!!
This is a really great find and is in many ways a holy-grail find for what we've been trying to do!!!

Thanks! FYI, I've updated the prior post to include one more query set that can be used to fetch the Report Data Sources that have a connection string embedded in the report as well. I've managed to work it out in such a way that the whole thing can be resolved without ever touching the database directly.

jcoe SSC Enthusiast Points: 112 More actions · Answer 3

Another fun XML query. This one will get the command text for Datasets in the report:

Create a Query to Get the Report Datasets 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{}/DataSets/DataSet
{
@Name(string),
}
/Query
{
CommandText(string),
DataSourceName(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.

Rob-350472 SSCertifiable Points: 7197 More actions · Answer 4

I was looking manually in some of the report server tables just yesterday and I noticed what a wealth of useful information was contained within them. I was going to create my own reports but these are far more comprehensive!

As an additional bonus I can learn some tricks from the formatting and features in these reports to apply to my own reports.

Thanks for this most useful collection of reports and interesting article.

matt.olson Newbie Points: 3 More actions · Answer 5

matt.olson

Newbie

Points: 3

May 24, 2012 at 9:53 am

#1492656

Any idea if this works with SSRS 2012?

michael_davis2 Mr or Mrs. 500 Points: 521 More actions · Answer 6

I don't know - we're just getting ready to go to 2012 soon (and when I do, you can bet these will be one of the first things I check out).

In the reading I have done, it sounds like SSRS pretty much will be in Sharepoint Integration mode from the start (I may be wrong here but that was the impression I got). Due to that, it may be possible that the repository could be structured differently. Microsoft never blessed accessing the database directly -- so I wouldn't be surprised if there were changes.

If you come across the answer before I do, I'd appreciate hearing what it is.

Thanks!!!

MaricopaJoe Ten Centuries Points: 1190 More actions · Answer 7

Yes, these reports will work fine in 2012. With sharepoint intergration the ExecutionlogView3 is on the farm where the reportserverdb lives.

This is the primary view for these reports.

michael_davis2 Mr or Mrs. 500 Points: 521 More actions · Answer 8

michael_davis2

Mr or Mrs. 500

Points: 521

May 25, 2012 at 6:14 am

#1493089

Great - thanks for the info!!!

andres.jordan Valued Member Points: 67 More actions · Answer 9

Hopefully you guys can help me, we have been using the diagnostics reports for a long time. But recently we started deploying reports directly from VS2010 to SSRS 2008 R2 and for some reason any of the reports deployed this way do not show up at all when we run the "ReportQueries" if we run any of the other diagnostic reports all of the reports show up.

MaricopaJoe Ten Centuries Points: 1190 More actions · Answer 10

MaricopaJoe

Ten Centuries

Points: 1190

September 18, 2012 at 4:17 pm

#1539321

Are you no longer deploying them through bids?

andres.jordan Valued Member Points: 67 More actions · Answer 11

no, the person that used to handle the report building used VS2010 with TFS source control. We just kept following that process.

MaricopaJoe Ten Centuries Points: 1190 More actions · Answer 12

I can't advise you in deploying in such a manner, unless you are using some sort of custom .net application with the report viewer control.

Those reports are designed to be deployed via BIDS and only BIDS.

Assuming you are running in native mode, have you attempted to upload the reports manually through the report manager as described below.

1.Go to Report Manager

2.Too keep all clean, you can create a folder in which you will keep all your reports

3.Create the data source, if not yet existing, by selecting the New Data Source link. ?Specify Microsoft SQL Server as the Connection Type.

?Specify the database Connection String as follows:

data source=[Name of database server SQL Instance];initial catalog=[Name of database]

?Connect Using the Credentials stored securely in the report server and specify the user name and password used to connect to SQL Server instance installed in the database server.

4.Upload the report by selecting the Upload File link and navigate to the RDL file stored in the system.

5.Using Show Details view, edit the uploaded reports by selecting the Edit link.

6.Under the Data source link, assign the previously created shared datasource as the source of data for the report.

Note: The “Apply” button needs to be selected to apply the changes made for the report.

rpriesing SSC Veteran Points: 217 More actions · Answer 13

Love the reports and for the most part they have been easy to add additional columns (Linked Report, Linked To, Hidden). One thing that we are trying to get working is the showing of each datasource within a report. Currently the report shows multiple lines for the reports that have multiple datasources but the datasource name shows the same one for each because of the subquery selecting Top 1. If this has been addressed I appologize, I haven't seen it in the posts.

Thanks

MaricopaJoe Ten Centuries Points: 1190 More actions · Answer 14

You can start by running this query.

SELECT Dsc.name DataSourceName,

Cat.path,

Cat.name AS Reportname

FROM catalog AS Cat WITH(nolock)

INNER JOIN datasource AS Dsc WITH(nolock)

ON Cat.itemid = Dsc.itemid

rpriesing SSC Veteran Points: 217 More actions · Answer 15

Thanks for the info but to be more specific, it's this section of the ReportInventory report query that's giving us the headache:

(select top 1 cat1.Name

from Catalog

join DataSource on Catalog.ItemID = DataSource.ItemID

join Catalog cat1 on DataSource.Link = cat1.ItemID

where Catalog.Type = 2 and Catalog.ItemID = c.ItemID ) as dataSourceName,

because it's a subquery it is limited to return one row even if multiple data sources are in place. We have tried to break it out and use a temp table to hold the data initially and then query from there but have not been successful.