Solved

Retrieving tables from reports

Posted on 2013-01-30
4
325 Views
Last Modified: 2016-02-11
Hi All,

I have about 25 old reports sitting on the server and I would like to know if it is possible to retrieve the tables for each report that the datasets are pointing to. Rather than going trhough each report and getting the table names.

Please let me know if this is possible.

Thanks in advance.
0
Comment
Question by:jose11au
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
4 Comments
 
LVL 6

Assisted Solution

by:liija
liija earned 250 total points
ID: 38838450
Basically .rdl-files are just xml documents. So they are readable.
Is it possible, it depends on your reports and how they are built.
If there is a common way to name the main dataset, it might be easy to check and copy the SQL from XML.

But if there are several data sets in one report, and there are no naming conventions, it might be a very hard and risky job without checking the report structure properly in Visual Studio.

25 reports is not that many IMO. Personally I would go through the logics and SQL's in Visual Studio, it shouldn't take that long.
0
 
LVL 37

Accepted Solution

by:
ValentinoV earned 250 total points
ID: 38838476
If you're familiar with SSIS, you could use that to get the data out of the XML (a report consists of RDL, which is a type of XML).

In fact, I wrote an article about just that.  In that article I'm retrieving a list of datasets and their fields.  Not exactly what you want, but you could use the same technique.

Table names however are a bit more difficult because they are not located in a separate XML attribute or node.  They can only be found in the actual query, which is located in the CommandText node.  Here's a quick example as you would more or less retrieve it in the XML:

<DataSet Name="YourDataset">
  <Query>
	<DataSourceName>SomeDataSource</DataSourceName>
	<CommandText>Select yourfields from yourtable</CommandText>
  </Query>
  <Fields>...</Fields>
</DataSet>

Open in new window

Check out the following article to get an understanding of how you can get data out of the XML using SSIS and XSLT: Loading Complex XML Using SSIS

So yes, something is possible but it won't be easy.

Another option you could look into is the ReportServer.dbo.Catalog table.  That one also stores the reports.  More details in this article: Retrieving data from the ReportServer system tables

Note: to get the queries from the previous article to work on 2008, you need to replace 2005 with 2008 in that URL: http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition
0
 

Author Comment

by:jose11au
ID: 38838766
Thanks so much guys. Fantastic info.

Many Thanks.
0
 
LVL 37

Expert Comment

by:ValentinoV
ID: 38838989
We thank you for asking an interesting question! :)
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Over the last 2 years, I have been working on SSIS 2008. Really the tough tasks in SSIS are to deploy packages and pass parameters (Values from outside package). The latter is certainly a headache for developers, particularly for me. We had to ma…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question