Solved

Retrieving tables from reports

Posted on 2013-01-30
4
293 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
  • 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

A recent questions about how to add SSRS named instances, couldn't find any that talks about SQL server 2008, anyway I decided to help by creating some screen shots. The installation is straightforward, you just pop the SQL server 2008 installati…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now