Oracle Business Intelligence

Products Banner

BIP - SQL to Excel in one click

Submitted
34
Views
3
Comments

Description

Generate an Excel report from a data model in one click.

Use Case and Business Need


Hello,

the quasi-totality of the reports we have to create are to be output in Excel.
Since our requirements are typically too complex for OTBI, we have to create them in BIP.
But to go from a working SQL in a data model to a nice excel in a report is a pain, takes time, and brings no real added value.

The issues:
- all the information is in the data model already: columns, sequence, sorting, titles, etc. Conceptually, we could be one click away from excel. Any extra click is a waste of effort.
- creating an excel template in BIP is not simple, you have to refer to the field names from BIP, declare the repeating area and so, again for no benefits. It could be justified for really complex excels with very nice formatting or extra pages, but there could be an option to output a simple grid with a row of titles and the data below.
- using the "guide me" approach in BIP to create the layout is not much better: you have to drag and drop the columns in reverse order on the right panel, and they are not sorted on the left. Then even if you indicate that you don't want a header you have one anyway. The columns are always too narrow for the format, even in landscape. So you have to update the layout once it's created, remove the header manually, enlarge the size of the page (select the white of the page, then enlarge the size in pixels in the bottom left, under properties). Then you have to manually resize each column, and it's particularly tricky (try...).
- if later you modify any column name in your data model you can update the layouts again.

The solution (in my personal perfect world):
- We'd need one button in BIP label along the lines of "create an excel report".
Even keeping the existing BIP technology, that would:
1 - create a linked BIP report with the same name as the data model, in the same folder
2 - create a layout with one column per column of the data model, in the same sequence
3 - each title would come from the "display name" of the structure of the data model
4 - each column's width would be automatically adjusted to the content (or would default to a minimum in a personal parameter somewhere)

No need for anything special for updates: since it's one click, we could simply recreate the report when there is a change in the data model.

That's all! But that would save hours and hours of frustrating work,

thanks in advance!

Géraud

Original Idea Number: dd4762e38f

Tagged:
1 votes

Submitted · Last Updated

Comments

  • There used to be an option of auto-generate layout which would map all columns in same sequence automatically and if some one chooses excel as the default output then one will get report data in excel.

    However, in Release 12 i found that autogenerate option takes a lot of time and doesn't works sometimes

    Kind Regards

    Ashish

     

  • Hi Geraud

    Very much agree to this. It is extremely complicated to make a very simple report, especially when you want it to just exactly mirror the data model. This caused a lot of headache for us since the data model would only show 200 results. Any more and a report had to be created an exported.

    Once workaround that we found for testing purposes (doesn't work well for actually making an interface/report) is to use a direct database query:

    Navigate to BI

    Click on New Analysis

    Choose Direct Database Request

    Enter "HCM_OLTP"."Connection Pool" as the connection pool (include the quotes)

    Enter query, click on Validate, click results

     

    In the results panel, there is a button to export the results directly to Excel without creating a report. We use this to test our query before moving to data model/report. At least you only have to build the report once...

     

    Regards

    Reese

  • Oracle discontinued Direct Database Query, was a good feature...rip