Wednesday, March 15, 2006

How To Customize The Export Formats List in Viewer

In my previous post (link to post) I described how to add a quick export link to the main Viewer worksheet page that would let you export the currently opened Discoverer worksheet to a supported format.

This is kind of a followup post to the earlier one. Here I show how it is possible to construct a Viewer export page with only certain export formats available.
To begin with, it is important to know which export format corresponds to which 'resource key'. Not all formats are supported for both relational and OLAP worksheets, as you would have seen. Some, like the HTML, CSV, PDF, etc... are supported on both relational and OLAP connections, while some others like 'Excel as Pivot Table' are supported on only relational connections, while there are some export formats that work for both relational and OLAP worksheets.
This is the list of supported Export ids and resource Keys For Discoverer 10.1.2.45.46C (this also works for 10.1.2.48.18):

ID Resource Key Extension Supported Connection
0 export.type.csv (*.csv) Relational\OLAP
1 export.type.excel (*.xls) Relational
2 export.type.excel.pivot (*.xls) Relational
3 export.type.html (*.htm) Relational
5 export.type.reports (*.xml) Relational
6 export.type.text (*.txt) Relational\OLAP
7 export.type.pdf (*.pdf) Relational\OLAP
8 export.type.dif (*.dif) Relational
9 export.type.slk (*.slk) Relational
10 export.type.wks (*.wks) Relational
11 export.type.prn (*.prn) Relational
12 export.type.gif (*.gif) Relational\OLAP
13 export.type.png (*.png) Relational\OLAP
14 export.type.iqy (*.iqy) Relational Non SSO
16 export.type.html.excel (*.zip) Relational\OLAP
17 export.type.excel (*.zip) OLAP


Getting down to the nitty-grtty, this is how a regular Viewer worksheet export page looks like. I have expanded the dropdown so you can see the differences between relational and OLAP connections.
This is the list of export formats you get to see for relational worksheets:
This list is missing the "Excel as Pivot Table" because the worksheet is a table and not a crosstab, in case you were wondering.

And this below is the export formats list you get to see for OLAP worksheets:
This then is the Viewre page that you want to modify. Actually, it is only the dropdown that you would be interested in modiying, removing certain export formats that may not be required andpresent a shorter, cleaner list to end users.

To do that (and you should be familiar with this by now), you have to locate where the UIX file for this export page resides. If you are unfamiliar with UIX then see some of my previous UIX Hack posts. Saves me time, saves you unwanted grief. After all, I am not a UIX expert by any stretch of the imagination, nor is it my intent to start a discourse on UIX here. Jonas Jacobi has a very good blog (link to blog) on UIX technologies, aptly called "Jonas Jacobi's JSF, ADF Faces and ADF UIX blog".

So, using Windows as my installation platform, I have Oracle Application Server 10g (10.1.2) installed on c:\ias, and that is where I go to.

i.e. "c:\ias\j2ee\OC4J_BI_Forms\applications\discoverer\discoverer"

Here you shall find a lot of files that have the extension "UIX". Yippee... so far so good. Be careful now... the intent is not to muck around with every .uix file here; rather, it is only one file that we are interested in mucking up (I think I shall have to explain in some other post why I have this fascination for the word 'muck'). Therefore, as is suggested before people embark on mucking expeditions, make a backup of the "ExportTypes.uix" file first and foremost. Ah yes, also read the disclaimers at the end of this post.

Now, here is where the lobotomy starts. Open the "ExportTypes.uix" file using a text editor like Notepad, TextPad, JEdit, et.... and scroll down to the place where you find lines like these:
Find the contents inside the "contents" tag:
<contents childData="${uix.eventResult.exportTypeList}">
<option value="${uix.current.exportTypeValue}" text="${uix.data.nls[uix.current.resourceKey]}" />
</contents>

And replace them with the following lines:
<contents>
<option value="1" text="${uix.data.nls['export.type.excel']}" />
<option value="2" text="${uix.data.nls['export.type.excel.pivot']}" />
<option value="7" text="${uix.data.nls['export.type.pdf']}" />
</contents>

If the lines above do not display properly, you can also use these screenshots for reference.
The highlighted lines below are what you have to search for in the ExportTypes.uix file:

And replace them with the highlighted lines below:

Having done these changes, you know what to do:
- Save and close this file.
- Restart your OC4J_BI_Forms instance. To do this you haveto login to your Oracle Application Server Control.
- Close and restart your browser.
- Connect to Discoverer Viewer, open up a worksheet, and navigate to the 'Export' page.

See the dropdown now. The dropdown now shows you only three export formats. You can now go back and edit the UIX file to add or remove as many export formats as you want. You have all the information you need available in this post now.
Ok... that's about all I have for this post. I do have a couple of things before I end this post.
If you specify a relational only export format while trying to export an OLAP worksheet (and vice-versa) you will get an error. What error? An error like the one shown below:

This is because when you let Discoverer construct the export formats dropdown list it know which formats to include for relational and which ones to include for OLAP. But here you have explicitly specified the export formats to show in the dropdown, and it is the same options that will be displayed, irrespective of the worksheet type, whether relational or OLAP. Therefore when you select a relational worksheet and specify that it be exported in an OLAP available format, then you are aksing for problems. Now, one could argue that the product should handle this instead of spitting out an error. But, firstly, it is handling matters by throwing out an error instead of doing something weird like not responding or churning out some garbage. Secondly, the whole notion of hacking UIX files is not encouraged or supported... you are basically hacking the product to do something that it was not built to do in the first place. That UIX technologies make so many things possible is an advantage, but needs to be used judiciously.

Oh, another thing... there is one more place in Discoverer Viewer where you can select the export type. Guess where? The email export type selection page! Remember? When you specify that a worksheet be emailed, you have to first select a format in which the worksheet should be emailed as. And here also you can customize the export formats dropdown to exclude certain export types. To do that you have to follow the same steps as above, but the UIX file that needs to be modified is "EmailExportTypes.uix": see the screenshot below. The file is in the same folder as where the "ExportTypes.uix" file is located. And the changes are the same, so I am not going to repeat them here.


Related UIX Hacks posts:
UIX Hack 1 - Removing the 'Connect Directly' section
UIX Hack 2 – Remove the Last Refresh Date from Discoverer Portlets
UIX Hack 3 - Removing the 'type' column from a portlet
UIX Hack 4 - Removing the worksheet breadcrumb
UIX Hack 5 - Add A Quick Export Link to the Worksheet Page

The Disclaimer:
  • I am NOT suggesting that you go around and muck around with these UIX files.
  • Any changes you make to UIX files are unsupported.
  • Oracle Support will not support your installation if you run into problems as a result of making changes to these UIX files.
  • You will have to reproduce any errors on an instance without these UIX changes for Oracle Support to help you.
  • Any upgrades or patches you apply may overwrite custom changes you make to your UIX files.