Friday, September 14, 2007

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).

UsernameRespStartEnd
<?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

35 comments:

Anonymous said...

Hey Gareth
thats was not a long post - you wanna see some of my monster posts. Very useful thou. As for Excel Analyzer coming to EBS? some day, maybe ...
Tim

Unknown said...

Well Gareth Roberts
Regards
Habib
http://mhabib.wordpress.com

Praburam said...

Hi,

I have done all the steps whatever you mentioned to view the output in EXCEL format after registered the RTF file to run as a concurrent program.

When i preview the output in the options before run the conc. program, i got error msg with HTML tags and also when i view the output,it shows the output in some htm tags format not in EXCEL Format.

please post your comments to view the output in EXCEL format.

Regards
Prabu

Gareth said...

Hi Prabu,
Either keep going all the way to the end and see if it works, or post your message here (or even better in Oracle Forums under eBusiness Suite or BI).
Gareth

Gareth said...

Oops, that previous comment should have said error message...

Unknown said...

Gareth,

Very interesting post indeed. I was wondering if we could have used Report Manager to accomplish the same.

Have you had any exposure to Report Manager? From the userguide of the product, it sounds promising.

Manish

Gareth said...

Hi Manish,

I haven't used Report Manager but my understanding is that its about storing and sharing reports, rather than converting formats, so you could use it to store your Excel/CSV XML Publisher output, but it ain't gonna help you to get the Excel/CSV output in the first place.

Gareth

Dmitry Volodin said...

Hi Gareth!

Could you explain how to submit a new XML(BI) Publisher report via Concurrent Manager, not based on Oracle Reports. This report based on Template and Data Definition (SQL Query) only. Thanks a lot.

Best regards,
Dmitry

Gareth said...

Hi Dmitry,
In XML Publisher Administrator, setup your Data Definition and Template, note the code will be your concurrent program short name.
In System Administrator create a concurrent program with short name exactly as per code.
For executable choose XDODTEXE.
Add required parameters.
Save.
Add the program to required Request Group.
Run it.

Gareth

Dmitry Volodin said...

Hi Gareth.

The Data Definition and Template records have a CODE parameters. When I setup this codes the same, the result of the defined concurrent program was the XML-file, generated by Data Definition template.
What I was doing wrong?

Thanks a lot,
Dmitry

Gareth said...

Hi Dmitry,
You are 1/2 way there, make sure the CODE on your template and data definition match EXACTLY, case for case. When you submit the concurrent program, the layout should default to your named template.
Gareth

Dmitry Volodin said...

Hi Gareth.

I've checked Data Definition, Template and Concurrent Programs define. The CODE's are the same in all places. But I could not to access to the layout, then concurrent request submitted.

Thanks as lot,
Dmitry

Unknown said...

Hi Gareth,

We made a concurrent program that builds our xml. When the xml is finished we submit a request with XDOREPPB and the output format parameter is 'EXCEL'. When we run the request we see a nice xml output after that the second request runs. When we view that output it opens up a browser and shows the result but it does not open excel nor does it show a window to open or save the file.

Do you have an idea?

Thnx in advanced!

Gareth said...

Hi Matt,

Make sure your concurrent program short name, data definition code, and template code match exactly.

The second request should have the layout visible if you view the concurrent request details.

Regards,
Gareth

Unknown said...

I added the following to my code :
fnd_request.add_layout
After that I do :
fnd_request.submit_request

But then i get a warning in my request result.

If I debug the add_layout code it gives me a succes but there is no layout visible in the request details.

Gareth said...

Hi Matt,

Have you run the Layout in BI Publisher Desktop with your XML file to make sure you do actually get output?

Gareth

Quentin said...

Hi Gareth! Nice tutorial.

Do you know how can i amke the layout to be default to EXCEL and not PDF ? Cause it's really annoying to change the layout every time you run the report.

Thanks !

Gareth said...

Hi Quentin,

Still awaiting patch from Oracle, will chase Tim. In the meantime the solution is at my changing default layout from PDF post:
http://garethroberts.blogspot.com/2008/01/changing-default-layout-format-from-pdf.html

Regards,
Gareth

Gareth said...

Hi Quentin,

Okay, I notice patch 5612820 is available via controlled release for changing the default format from PDF to EXCEL or something else.

Regards,
Gareht

Quentin said...

Hey Gareth,

Thank for the tip.

From the patch notes there is something that says :
" Expect to get default output set to same type as in the data template definition form. "

This means that i have a template in .RTF format and i want to be outputed in Excel, the format of the template must be in XSL format.

With Bi Publisher plugin from word i have used Tools>Export>XSL-FO Style Sheet and tried to uplad it to the template manager. But i get this error :

The uploaded file XXROR_TEST_TM.xml is invalid. The file should be in XSL format.

Do you know what's wrong ?

Thanks

Gareth said...

Hi Quentin,

No, RTF template can produce RTF, PDF, Excel, HTML ....

Just save your word doc as RTF and upload, set Default Output Type to Excel, see my latest blog post.

Gareth

Quentin said...

Hi Gareth,

Thanks for all the tips and updates.

I have another question. In this mode of constructing XLS files, is it possible to insert images ?

CAuse i have built an RTF template with a picture in it and it's not showing on the XLS output. For PDF output is ok, but unfortyunatelly i need XLS.

Are you aware of any workarounds? Or it's just imposible to have images in XLS outputs ?

Thanks

Gareth said...

Hi Quentin,

For XLS in BI Publisher I think you need to use external reference to image ...

Haven't tried pictures in SYLK, but perhaps again you could use a Image URL.

Regards,
Gareth

Fusion said...

Hi Gareth,

I have modified the XSL-FO template and when i try to upload using XML Publisher Admin, i get the following error

The uploaded file frm_xslfo_p3AWm717.fo is invalid. The file should be in XSL format

Please let me know.

Thanks
Rakesh

Gareth said...

Hi Rakesh,
I guess you must have a syntax error in your XSL-FO. Drop me an email with it if you like. Email address on my profile.
Regards,
Gareth

eoracleapps said...

Very good Post. I was not able to understand how to print the output in Excel, but your post made it very Clear.
But is that means every time user need to run the Report , he will go to option and change the Template from PDF to Excel.

Gareth said...

Hi eoracleapps,
For setting the default, check out my post on changing the default layout here.

Regards,
Gareth

Unknown said...

Thank you for sharing this information! I have been wondering about xsl-fo. I feel that you really brought it into the light for me with what you have said here! I can't wait to read more!

Rene Knip said...

Hi Gareth,
This is a very clear explanation, thanks. However I get an error message when I try to preview in the template area:Caused by: oracle.xdo.parser.v2.XPathException: Namespace prefix 'ref' used but not declared.

However when I preview the rtf template in Word after importing the XML Data, there is no problem and I get a correct output file.
Do you have any idea, what went wrong here?

Regards,
Rene

Gareth said...

Hi Rene,

I would guess perhaps the sample XML or RTF needs uploading (or maybe some other issue). Check those 2 items and if it doesn't solve it either don't worry about it since you can view in Desktop, or log an SR on My Oracle Support.

Regards,
Gareth

Unknown said...

i have doubt? how can declare a variable in rtf section in xml publisher

Gareth said...

Hi Unknow,

Declare variable like this:


Regards,
Gareth

Unknown said...

Hi All,

My requirement is that we need excel output in BI publisher but data is more than 1 lack.

Please let me know how to handle this situation

Gareth said...

Hi Dheeraj,

I suggest you use CSV file, via BI Publisher ETEXT template.

Regards,
Gareth

Unknown said...

Thanks Gareth