next »

[Keshen P.]

Hi guys,

We are constantly being asked to produce BI Reports with pivot tables and have it output into Excel. Has anyone developed a BI tool to work with Genero BDL ?

[Alex G.]

Hi,

the report writer supports the creation of pivot tables in several ways:
- There is the PIVOTABLE report object that has many options. It supports for example n-dimensions, n-measures, sorting after grouping, top-n, multi variable expressions to both build dimension keys as well as values and many more options such as output as business graphs or lists. There are two demonstrations of in the sample reports. The "dynamic" pivot table demonstration demonstrates how a flat stream can be dynamically pivotized based on user input.
- There are sample reports and templates that pivotize data with 2-dimensions and n-measures.
- The data transformation facility offers pivotizing capabilities with a similar features as the PIVOTABLE report object but since the operation is done at the data level with transformed schemas, the subsequent output options (e.g. drawing a custom table) are larger.

We do not however at this point create pivottables in the Excel output due to limitations in the Apache POI library which we use to create Excel output.

Regards,
Alex   

[Gary C.]

Hi
We faced the same issue. Most of our analysis reports allow the user to select the dimensions and measures they want and they output the report to Excel and then manage the pivots from there. None of the users will output a pivot report to PDF, they always use Excel. Other reports simply generate a flat Excel report for them to pivot themselves.

We've been using Microsoft's Power BI but of course this sits outside of Genero and our core application.

[Keshen P.]

Thanks for the feedback guys. Much appreciated.

[Reuben B.]

It is possible to use ApachePOI to create Pivot Tables.  I have added it into the repository https://github.com/FourjsGenero/fgl_apache_poi, see the entry at the bottom of the README.  So if you want to give your users an Excel file with a pre-populated pivot table you could investigate a solution using that.  There is a little quirk in that I could not get the columns/row to sort without using a java class that was not in the ApachePOI download, refer to the readme for details of 3 lines you have to uncomment and the Java library you need to find if you want that.

If you are getting into volumes of data there is probably also improvements that could be made to the way the data sheet is populated in the example.

Alex pointed you at Tutorials&Samples->Reports->Order Report->DynamicPivotTable which creates a GRW report on the fly.  That same front-end could potentially give you the parameters to generate Excel PivotTable using the above ApachePOI, as well you could use Dynamic Dialogs to generate a Genero form/dialog on the fly.