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
Solved

Retrieving tables from reports

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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

Suggested Solutions

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

839 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