Friday, September 30, 2005

Creative Use of the IQY Export Format

This is a question that crops up often enough - can I call a Discoverer worksheet export function as part of another program. Say a user has setup a batch operation where one of the steps in the operation requires that the results of a Discoverer worksheet be fed into a subsequent part of the operation. Whereas in earlier versions it was possible to use the documented URL parameters (see Section 11.8, "Discoverer Viewer URL parameters" from Chapter 11 of the Oracle Application Server Discoverer Configuration Guide, 10g (9.0.4), Part Number B10273-01) to call a Discoverer worksheet and use the &fm URL parameter to export the worksheet in a desired format (HTML, TXT, XLS, etc...), this is no longer possible in the 10.1.2 release, and is documented as such (see section 1.2.2.1 - 'URL parameters removed' of the Oracle® Business Intelligence Discoverer Configuration Guide, 10g Release 2 (10.1.2.1) for Microsoft Windows and Solaris Operating System (SPARC), Part No B13918-03.)
But, unknown to many, there is still a way available to get the same functionality, albeit only for the Excel export format.
Here's how:
Assume that I have the following workbook and worksheet:
Workbook name: SalesReports
Workbook identifier: SALESREPORTS
Worksheet name: Margins
Worksheet identifier: 1
Connection id: cf_a102 (I am using a public connection here).
You can view the information below in the workbook screenshot from Discoverer Plus (as you know you can get the worksheet identifier by right clicking the worksheet tab and clicking 'Worksheet Properties')

First let's run this worksheet through a regular IQY export (you can use either Plus or Viewer, doesn't really matter). After the export operation has completed you can open the generated file (in my case Margins.iqy) in Excel.The query is run, and the data retrieved and displayed as shown below:

Now comes the interesting part. You would have saved the IQY file on your computer (if not, re-run the export, and this time save it to some folder on your computer). Open this file in a text editor like Notepad, TextPad, JEdit, etc... This is how the file will look like:
WEB
1
http://myhostname.com/discoverer/viewer?
&connectionKey=cf_a102&worksheetName=SALESREPORTS/1&webQueryExport=yes&&password=["password","Password :"]
You can see that apart from a small amount of IQY specific stuff like the first line with 'WEB' or the last parts of the URL with the '[' braces, etc... it should become apparent that you can extract the URL portion of it and past it into your browser.
The URL that I extract is http://myhostname.com/discoverer/viewer?
&connectionKey=cf_a102&worksheetName=SALESREPORTS/1&webQueryExport=yes

As you would expect, the query is run and the data displayed in your browser.
To abstract from the above example, if you do want to generate an XLS export of a Discoverer worksheet using URL parameters, you can do that, and the relevant URL would be:
http://[your host name]:[port number]/discoverer/viewer?
&connectionKey=[connection id of the public connection]&worksheetName=[workbook identifier]/[worksheet identifier]&webQueryExport=yes
So, the URL export functionality is still present, though in a limited form.