Oracle Transactional Business Intelligence

Welcome to the Oracle Analytics Community: Please complete your User Profile and upload your Profile Picture

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

Accepted answer
62
Views
7
Comments

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

Tagged:

Best Answer

  • Nathan CCC
    Nathan CCC Rank 7 - Analytics Coach
    edited August 13 Answer ✓

    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

Answers

  • Hassan El Bouihi-Oracle
    Hassan El Bouihi-Oracle Rank 5 - Community Champion

    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
    Kevin M Storrie Rank 3 - Community Apprentice

    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
    Nathan CCC Rank 7 - Analytics Coach

    @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
    Kevin M Storrie Rank 3 - Community Apprentice

    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
    Kevin M Storrie Rank 3 - Community Apprentice

    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
    Nathan CCC Rank 7 - Analytics Coach
    edited August 15

    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.