Solved

Retrieving tables from reports

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
From time to time, for debugging and troubleshooting your flow at run time you’ll need to check if the variable has the correct value or not, there’re several ways to check for the value of the variables inside the flow, You can add Break Points, a …
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

27 Experts available now in Live!

Get 1:1 Help Now