BIP - SQL to Excel in one click

Geraud Andreux

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