Standard Report to CSV File via BI Publisher

Update: Added screenshots, and info on Internet Explorer 7 / Excel not opening .xls file when trying to view concurrent request output.

Gee - what a long post! WAIT! Its quick work and worth it. If you're not there already, get to a recent version of BI Publisher e.g. 5.6.3 or ATG RUP5.

We are going to:

• Take the "Active Users" standard report, copy the program definition.
• Setup and create a BI Publisher (BIP) Template a.k.a XML Publisher (XMLP)
• Produce a CSV (comma separated values) output from the BIP Excel output format

All this without affecting the standard program and only a teeny tiny bit of technie stuff. This technique applies to any concurrent request that executes via Oracle Reports. The only major difference in the process is the content of the (RTF) Template, plus changing a few names for your report.

1. Copy concurrent program you want funky output from
System Administrator > Concurrent > Program > Define
Query Program "Active Users"
Click the "Copy To" Button
Program: Active Users - BIP
Short Name: XXXX_FNDSCURS
Application: Application Object Library
Check Include Incompatible Programs
Check Include Parameters
Click OK
Change the Output Format to XML
Save

2. Add your new concurrent program to your required request group
System Administrator > Security > Responsibility > Request
Query:
Group: System Administrator Reports
Application: Application Object Library
Add new Request:
Program: Active Users - BIP
Save

3. Run the request
System Administrator > Requests > Run
Name: Active Users - BIP
Submit
If you view the output, you can see the XML Source

4. In Microsoft Word create a file with the following contents save as RTF file, say ActiveUsersBIP.rtf.
Note: Nice to use BIP Template Builder, and form fields, but here I display the real tags for clarity. Software for (BI Template Builder - Patch 5887917 & Microsoft .Net 2.0 Framework).

Username	Resp	Start	End
<?for-each: G_RESPS?><?../../USER_NAME?>	<?RESPONSIBILITY_NAME?>	<?START_DATE?>	<?END_DATE?> <?end for-each?>

5. Define XML Publisher Data Definition and Template
XML Publisher Administrator > Data Definitions
Click Create Data Definition
Name: Active Users - BIP
Code: XXXX_FNDSCURS
Note: the code must match the concurrent program short name
Application: Application Object Library
Note: match the Application to the concurrent program application
Leave defaults for remaining fields
Click Apply

XML Publisher Administrator > Templates
Click Create Template
Name: Active Users - BIP
Code: XXXX_FNDSCURS
Note: the code must match the concurrent program short name
Application: Application Object Library
Note: match the Application to the concurrent program application
Data Definition: Active Users - BIP
Type: RTF
Click File, Browse and upload your RTF template file ActiveUsersBIP.rtf created earlier
Language: English
Click Apply

6. Run the request
System Administrator > Requests > Run
Name: Active Users - BIP
Notice the Layout has been set by default to Active Users - BIP
Click the "Layout" button and change the Format to Excel
Add, OK.
Submit

7. View request output and save as CSV
View > Requests
Click "View Output" from your concurrent request
At the Open or Save, choose Open
Note: Excel should open with the output from your file. If your Internet Explorer window pops up then disappears, you need to add your eBusiness Server to your Trusted hosts. In IE 6 this is under Tools, Internet Options, Security, click the "Sites" button under to Trusted Sites, uncheck "Require server verification (https)...", put your fully qualified hostname under "add this web site" then click OK. For Internet Explorer 7 or 6 add your site to Local Intranet Zone. Tools, Internet Options, Security, click Local Intranet, Sites, Advanced, Add your fully qualified hostname, e.g. http://myappserver.mydomain.com

Click File (or Page) > Save As and choose Save as type: "CSV (Comma delimited) *.csv"

All done! Well, some screenshots might be nice... UPDATE: Added screenshots!

Now just waiting for Excel Analyzer to arrive with the eBusiness Suite