Can I automatically see which VIEWS are used for each REPORT?

Is there a way to see which database views (or tables) were used for each report?

(For example, I have a report called "Customer Oriented" and it uses 2 views: Customers, and Products. Can I automaticaly pull out this information?

Thanks,

The dataset information is just stored as text, unless you code your own logic for parsing that information will will have no chance of getting that information that you wanted to have.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Hi Jens,

Where can I see that text? (the one in which you can see the name of dataset). Although I am more looking for seeing database VIEWS or TABLES used to create a report rather than a DATASET, but that might give me some insight.

Thank you.

Alexan

|||

You can call GetReportDefinition() on the SOAP API for a published report and retrieve the dataset elements from the report. You would then have to parse the contents of the dataset manually to determine the names of the tables or views which are referenced.

Out of curiosity, why do you have this requirement?

|||The dataset definition is presented as a node in the XML data (WHat John ment with Report Definition), just can just retrieve that with e.g. a method like SelectSingleNode.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Thanks for your answers. Your answers are still very higher than my knowledge and I don't know how to do them.

John: The reason that I need to do this is because we need to change the Views from time to time (to satisfy newer reports needs) so we want to see what reports that we have already made from a particular view might be affected by these changes. I think this is a very lame way, and it's better to create a new view for each specific report, but my boss says maintaining a lot of views is pain for him. I am not sure what he meant.

|||

He meant that it is better for you to have pain than for him to have it.
(There are lots of times I am all shades of wrong.)

R

|||

In all honesty you shouldn't try to do this from Reporting Services. The data entered in the query box is any valid statement that will return a result set. Considering just SQL server as a data source, valid statements include stored procedure and table valued function calls and trying to parse the SQL to extract table names is crazy.

You should perform an audit and store and maintain that information in some spreadsheet.

That's my 2 cents.

|||

Think you're right. Thank you all.