Most end-users use Excel as a very generic tool, because of their familiarity and the control in analysing data using the power of MS Excel.
Customers request converting lot of reports to excel.
As you can imagine, converting all reports to excel is a mammoth task using BI (XML) Publisher.
There is a trick to capture Oracle Reports output (text output) into a excel sheet.
This trick uses the power of XML and MS Excel to format the data. This is trick is a simple and effective work around.
Basic Steps :
1) Set the output format for the report to XML
2) Run the report to generate output.
3) Save the output file locally as a XML file.
4) Open the file using MS Excel.
5) To make it more beautiful, you may use a MS Excel Template.
The Excel Templates can be used to do complex data analysis and formatting. Displayed example below is a simple excel template, to make the point.
Example :
Requirements :
Report to convert to Excel : Active Users Report
Current Output :
Expected Output :
Steps :
1) Set the output to "XML".
(In this case, to keep the sanctity of the system, I have created a new concurrent request with a different output type)
Standard Report :
New Report with Output as XML :
2) Run the Report and Display the output
XML Report Output :
3) Lets save the XML output file in local PC. ( File->Save As).
Make sure the file is saved as a <file name>.xml file.
4) If we open the file using MS Excel, the output would be :
5) To make it more to look like the Requirement, I first created a MS Excel Template ( *.xlt file) like below.
6) Apply the template to the excel output. Data->XML-> Import. This will give us output as below :
The Excel Template can be used to do comprehensive data analysis and formatting. Displayed above is a simple excel format to make the point.
Author : SUDEEPT MAHARANA
Linkedin Profile : www.linkedin.com/in/sudeept
Comments
(16)
Subscribe to this comment's feed
Error when creating New Report
written by Sabeeh , April 08, 2009
written by Sabeeh , April 08, 2009
I tried creating a XML output version of the GL report "Account Analysis - Payable Details". When trying to create a similar report in XML format I get the following error:
The parameters of concurrent program "GLACTANP180XML" of application "General Ledger" use features which are only supported in Oracle Forms. This concurrent program may not be submitted from other environments such as OA Framework or PL/SQL APIs.
The number of warning messages: 2.
--------------------------------------------------------------
The parameter "Accounting Flexfield From" uses value set "GL_SRS_LEDGER_FLEXFIELD" with validation type "Pair".
This type of value set is only supported in Oracle Forms.
--------------------------------------------------------------
The parameter "Accounting Flexfield To" uses value set "GL_SRS_LEDGER_FLEXFIELD" with validation type "Pair".
This type of value set is only supported in Oracle Forms.
---
Any idea how this can be resolved?
Votes: -1
The parameters of concurrent program "GLACTANP180XML" of application "General Ledger" use features which are only supported in Oracle Forms. This concurrent program may not be submitted from other environments such as OA Framework or PL/SQL APIs.
The number of warning messages: 2.
--------------------------------------------------------------
The parameter "Accounting Flexfield From" uses value set "GL_SRS_LEDGER_FLEXFIELD" with validation type "Pair".
This type of value set is only supported in Oracle Forms.
--------------------------------------------------------------
The parameter "Accounting Flexfield To" uses value set "GL_SRS_LEDGER_FLEXFIELD" with validation type "Pair".
This type of value set is only supported in Oracle Forms.
---
Any idea how this can be resolved?
report abuse
vote down
vote up
Article is good but lacks something
written by Jithendra , April 09, 2009
written by Jithendra , April 09, 2009
Hi Sudeept,
The article is good. Thanks for sharing. But this article is missing an important thing which everyone is struggling to get to - An Excel output based on Microsoft Excel via XMLP.
Any ideas on that?
Regards,
Jithendra
Votes: -1
The article is good. Thanks for sharing. But this article is missing an important thing which everyone is struggling to get to - An Excel output based on Microsoft Excel via XMLP.
Any ideas on that?
Regards,
Jithendra
report abuse
vote down
vote up
Need Help
written by Jagadish , July 09, 2009
written by Jagadish , July 09, 2009
Hi, i have problem after converting the report to excel, if the report have numeric columns its getting repeated. Can you please help.
Votes: +0
report abuse
vote down
vote up
Display Parameters
written by Vara , July 28, 2009
written by Vara , July 28, 2009
I want to display report parameters on the EXCEL output. Can you please share your thoughts.
Votes: +0
report abuse
vote down
vote up
Re: Article is good but lacks something
written by Grau , August 05, 2009
written by Grau , August 05, 2009
An Excel output based on Microsoft Excel via XMLP.
Any ideas on that?
Try to use native Excel templates
Welcome to http://xlspe.com
report abuse
vote down
vote up
Use this project. code.google.com/xmlparaplanilha
written by Geraldo Lopes , October 15, 2009
written by Geraldo Lopes , October 15, 2009
Open source Java program i've built for this purpose. It's in english , but all you need to do is click at Converter (Conversion) and point to the xml file you want to output as xls. It generates tabs as it find differents paths in the xml.
Hope this helps
Votes: +0
Hope this helps
report abuse
vote down
vote up
Error when creating New Report
written by Jogyhe , January 29, 2010
written by Jogyhe , January 29, 2010
Hi, Sabeeh
Have you solved the question?
i also have this problem ,would you share the solution?
thanks
Votes: +0
Have you solved the question?
i also have this problem ,would you share the solution?
thanks
report abuse
vote down
vote up
...
written by kartik_becs , February 02, 2010
written by kartik_becs , February 02, 2010
The article is good but the problem is that Concurrent program takes long time to generate the output compare to the original output.Can we resolve that as well.
Votes: +0
report abuse
vote down
vote up
Excel Output
written by kriss , June 09, 2010
written by kriss , June 09, 2010
Is there any chance,to generate the particular report output in Excel? With out changing the options at running the concurrent program.
Votes: +0
report abuse
vote down
vote up
There is even a simpler way
written by vjman , October 10, 2010
written by vjman , October 10, 2010
This is a good article. Still to do that you have to create n Oracle Report, upload it to the server, create concurrent request.
This takes more time the larger the organization is, sometimes even weeks.
Yet sometimes the business needs the report "right away".
I came up with a way to generate Excel output by creating the query and pasting it int the concurrent request definition.
No messy files, registrations, etc. It opens right in Excel and it takes as long to build as it takes to come up with the query.
Watch it at:
http://www.more4apps.com/demos?DemoName=EO&ProductName=Excel-Out
for more details see
http://piotrbelter.blogspot.com/
Votes: +0
This takes more time the larger the organization is, sometimes even weeks.
Yet sometimes the business needs the report "right away".
I came up with a way to generate Excel output by creating the query and pasting it int the concurrent request definition.
No messy files, registrations, etc. It opens right in Excel and it takes as long to build as it takes to come up with the query.
Watch it at:
http://www.more4apps.com/demos?DemoName=EO&ProductName=Excel-Out
for more details see
http://piotrbelter.blogspot.com/
report abuse
vote down
vote up
Issue with Bursting the excel formatted output of larger size
written by unknown , February 15, 2011
written by unknown , February 15, 2011
Hi,
I have a issue with bursting the Excel formatted output of BI publisher report. If the size is less than everything works fine. If the output file size is 33 MB, i am not able to view the output in the request form and also bursting is not working. Can you please let me know if there is any restriction in the size of excel output for bursting.
Thanks in Advance
Votes: -1
I have a issue with bursting the Excel formatted output of BI publisher report. If the size is less than everything works fine. If the output file size is 33 MB, i am not able to view the output in the request form and also bursting is not working. Can you please let me know if there is any restriction in the size of excel output for bursting.
Thanks in Advance
report abuse
vote down
vote up
Try Transform
written by TB , July 08, 2011
written by TB , July 08, 2011
A product called "Transform" can receive any text report from any system using a virtual printer driver on MS Windows and can parse out the data and drop it onto a pre-defined .XLS sheet in any format desired. http://www.sprolinux.com/Transform.aspx
Votes: +0
report abuse
vote down
vote up
Excel Reports
written by Joseph Becket , July 29, 2011
written by Joseph Becket , July 29, 2011
The article is very helpful! Thank you, I'll be sure to use your advice. Personally, I had some problems converting excel files over to PDF. I got so annoyed from manually moving the information (such as commission reports, invoices, etc.) over to another document, but then I found this program at my work that quickly and painlessly converts my excel documents into PDF and ready to go templates and reports, saving me aggravating hours upon hours. It provides my business a simple way to make individualized statements for our business associates, taking our invoices and seamlessly placing the information in the awesome templates. Check it out here.
Votes: +0
report abuse
vote down
vote up
Excel Output
written by AK , October 24, 2011
written by AK , October 24, 2011
Could you please let me know how you got to display the Column Headers in excel file.
I have an rdf file. I created a concurrent request with output as XML.
I save the the output file as XXX.xml and open with excel. I get the data but I dont get the column headers.
Thanks,
AK
Votes: +1
I have an rdf file. I created a concurrent request with output as XML.
I save the the output file as XXX.xml and open with excel. I get the data but I dont get the column headers.
Thanks,
AK
report abuse
vote down
vote up
Thanks for the article. I have got a doubt, I'm clear till point #4, where we open the .xml file using excel. After this using excel template..I'm a little confused. And also won't it be cumbersome if each and every report we need to do this manual procedure of opening with excel. I'm not aware of generating reports with excel output. Please guide me.
Thanks
Ashwini