Is it possible to list fields actually used from result sets in SSRS?

Question

I have dozens of SSRS Reports and the corresponding stored procedures that I am slowly cleaning up and optimizing, I am finding a number of data sets that have extra fields that are not used in the actual report, they are usually the result of a SELECT * that is slowing down the SP end of things significantly in a few places.

I am wondering if there is a quicker way to see which fields from the datasets are used/unused to more efficiently clean up the stored procedures. It seems like clicking through to each <<expr>> and checking them off is a ridiculous way to go about this.

Answer 1

I'll tell you, I wish I knew a tool that simplifies this for you. And I don't off the top of my head. But for sure I know you can search the text of the rdl and find these details.

I do this often when troubleshooting problems with existing reports (or SSIS packages).

The .rdl files are human-readable xml. You can open any one file in a text editor and search the text - even Visual Studio if you "Open File" rather than use the Report project.

Given that, of course you can write a routine in your preferred programming language that

• finds the query or proc in the data source of the Report
• runs it (as metadata only) to get all the columns
• search for each one in the text of the rdl
• you can be more specific if you use xml queries to limit the search to more realistic targets like display box Data Sources

Sorry I don't have a more convenient answer like an existing tool.

If I remember, I may look for one because this is a big problem for "corporate coders" like us.

If I can't find one, maybe I'll write the script in .net and come back and post it :)