OBIEE: Retrieve report SQL

Wow, it's been almost 2 months since I asked how to retrieve the SQL from all the Answers reports. Like I said in my previous post, it would be nice to have an Object Dependency button or something like Application Express has.

I started to think this afternoon, the reports are stored in XML files, wouldn't it be easy to just search through them and extract the SQL? Better yet, maybe Catalog Manager has a tool like that.

It does! As I've gotten more comfortable with the environment things seem easier to find.


Once you select Create Report, you'll be prompted for fields you want to view:


Select Request SQL, fill in the path and name of the file you want to write it to:


You'll then see a window that indicates the report is running:


When the report is complete, you'll see the following:


Now just open up the file and browse the SQL:


Of course this won't be easy as there are 250 reports, but it certainly beats going through each individual report, opening it up, viewing the XML and copying the SELECT statements out. Plus, I really only need the Direct SQL Requests so that makes life a little bit easier.