OTBI - No option to output format in excel 2007+ .xlsx

Kevin M Storrie

Hello!

From my online research online (I'm not an MS history expert) - "XLS and XLSX extensions represent popular Excel file formats that were introduced by Microsoft as part of its Office suite over a period of time. XLS being the oldest and widely used file type is also known to be the Excel97-2003 file format. The XLSX file format was introduced as a replacement of XLS file type with the launch of Excel 2007."

The issue is, the older format has been blocked for use now on our systems, we can't open the files, although don't see any option to export in .xlsx only .xls, anyone else experienced this, is it on the roadmap?

Cheers

Kevin

Nathan CCC

Hi Kevin,

1.

The Oracle Business Intelligence (suite Enterprise Edition) OBI (suite EE) platform is the product inside Oracle Transactional Business Intelligence OTBI in Oracle Fusion Cloud Applications (FA). The EE is in brackets because Standard Edition (SE) of the suite was retired many many years ago.

As a platform OBIEE is the older more "legacy" product deprecated by "cloud first" Oracle Analytics Cloud (OAC) which uses Data Visualization (DV) as the "modern" BI tool. So dont hold your breath waiting for big feature enhancements to OBIEE in OTBI. Here are some of the statements of direction for some of the products in BI (but not actually for OTBI). Oracle Business Intelligence Statement of Direction Index (Doc ID 2582420.1)

Oracle designates Oracle Analytics Cloud as the strategic product for future enhancements for business user analytics needs...

In Oracle Fusion Cloud Applications the "older" product OBIEE was used for real time analytics and reporting rather than OAC in OTBI. BTW OAC is used in the sister "data warehouse" product with your "decks" of performance indicator "cards" user interface built using DV in Oracle Fusion Data Intelligence (formerly known as Fusion Analytics Warehouse (FAW)). But this is an add-on extra cost option that not all customers buy along with their FA.

2. As to your use case export to XLSX - actually you can do this! Please explain why you think you cant?

On an analysis on a dashboard select link Export, select Formatted, select Excel.

System download in format Microsoft Excel 2007 *.xlsx (not older format *.xls) Happy days!

For more information see user guide

Oracle® Fusion Middleware
User's Guide for Oracle Business Intelligence Enterprise Edition
12.2.1.3.0 E80922-02 November 2017
Appendix A Basic Information to Tell Your Users
About Exporting Results
https://docs.oracle.com/middleware/12213/biee/BIEUG/GUID-EAC48E89-8FEA-47BA-A24B-2088A538DA86.htm#GUID-ECB58D48-A42B-4C6C-A04F-0D8AA5B5D414

Hassan El Bouihi-Oracle

Hello Kevin,

Could you be more specific on where you are trying to export to Excel.
Please provide screenshots for further review.

Thank you

Hassan

Kevin M Storrie

Thank you @Nathan CCC for the details response, yes sorry - internal communication error our side, I thought it was both products but learnt it is BIP only that can't export to .xlsx

Cheers

Kevin

Nathan CCC

@Kevin M Storrie Hi Kevin, No you can export as XLSX for both analysis AND reports in OTBI.

Sorry but I think another internal communication issue on your side.

" it is BIP only that can't export to .xlsx" I think no that is not true either.

With a publisher report in OTBI too you can export as XLSX. Here on our example dashboard I have the same data again but this time as a report. For selected layout select Actions, select Export, select Excel (*.xlsx)

Again read the manual

Oracle® Fusion Middleware
Report Designer's Guide for Oracle Business Intelligence Publisher
12c (12.2.1.3.0) E80602-02 September 2017

https://docs.oracle.com/middleware/12213/bip/BIPRD/GUID-AD8B4E8B-B187-4A66-B6CC-82A382989CE0.htm#BIPRD3013

Kevin M Storrie

Thanks Nathan, I have asked for a screenshot from our engineering teams of the system which doesn't allow .xlsx output… hopefully that will clear this up… will post when in comes through..

Kevin M Storrie

Excel Template Issue.docx

Hi @Nathan CCC

As promised please find attached the steps and screenshots of where we have no option to export to .xlsx

Cheers

Kevin

Nathan CCC

Hi Kevin,

Thanks.

So your document does NOT quite conclude what you assert in your question (at least not all cases)

"don't see any option to export in .xlsx only .xls the older format has been blocked for use now on our systems, we can't open the files" - this is not true for all layouts in all reports (only some of them using excel templates)

The input format of your layout template is NOT the same things as the output format when you run the report.

You have 3 + options when creating a layout template at design time

you can create one or more templates using one or more of the following types

1. use online editor (XPT) - if that does not meet your needs
2. use RTF template (most people use word for this because it has a great add in to add fields etc)
3. use XLS template - it is this template that you upload into your report must be XLS (not XLSX) (but later on when you view your data using this XLS template when you run report you CAN open it in XLSX)
4. use a PDF template
5. or use some of the other template types for specific use cases like etext flash etc

So as we have discussed if you build your template as XPT or RTF at design time then later on your users CAN view/export the output in Excel as XLSX at runtime. So many of the reports are ok. So most all requirements can be met by a XPT or RTF template but then viewed ok by your users in Excel no problem.

As for any reports which MUST HAVE for some reason layouts with Excel templates (because XPT or RTF templates for some reason cant meet some requirement you have at design time)

So this bit in the document is true

While uploading it [XLSX], it stops me saying that template file extension doesn’t match Template type

Yes when you download/upload an excel template from/to a report it MUST be format XLS (not XSLX)

This is your problem - your Trust Center settings block you open an XLS file type

with new laptops in place, it does not allow us to open xls files

A solution may be to ask your system administrator to configure your laptop settings to make it so you can edit/view excel documents of type XLS - I suspect this is a faster solution that putting in an idea for an enhancement request to ask oracle to support XLSX for excel templates in legacy product BI Publisher.

On my laptop I am able to use a later version of Excel to edit XLS in compatibility mode - I just have to save as XLS (not XLSX) before upload back to my Report.

Regards the size of the problem. I would not think it is perhaps that big an issue if you can not get your company to allow XLS documents on your laptops. In 90+% of use cases the output will look pretty much exactly the same to the user at runtime whether I build the template in XPT or RTF or XLS. There are some settings in the report properties you can change to avoid row/column merging etc in excel outputs. And some good practice tips to use in your template design if the template was not XLS. for example, page breaks become sheets in Excel output etc. So I think you will be able to meet most requirements using only template types XPT RTF. I can only think of some very extreme edge cases that mandated that I must use an XLS template rather than XPT or RTF. Depends on your particular requirements obviously.