Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Retrieving tables from reports

Posted on 2013-01-30
4
Medium Priority
?
328 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 1000 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 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

My client sends data in an Excel file to me to load them into Staging database. The file contains many sheets that they have same structure. In this article, I would like to share the simple way to load data of multiple sheets by using SSIS.
A couple of weeks ago, my client requested me to implement a SSIS package that allows them to download their files from a FTP server and archives them. Microsoft SSIS is the powerful tool which allows us to proceed multiple files at same time even w…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

688 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