Saiku (Pentaho plugin) - excel export data truncated and empty extra columns

[mas...@gmail.com]

Hello all,
I'm testing Saiku-3.14-SNAPSHOT build from github and i found some bugs related to data format.

First:
- datasource : Quadrant Analysis
- Measures: Actual and Budget
- Rows: Departament and Position

1. I added Rows -> Totals -> All -> Sum and after data render two new empty columns apears:
Actual Grand Total
Budget Grand Total

On Export XLS the generated file contains this two empty columns and last cells truncated and covered by some info about totals and Bla :)
See attach:
saiku-export - grandTotals.xlsx
saiku - grandTotals.png

2. Same configuration with "Hide parents" unchecked, result in a corect table and excel but without totals.
See atach:
saiku-export - grandTotals - noHideParents.xlsx
saiku - grandTotals - noHideParents.png

A good Day,
Geo

[John Kilin]

In The Saiku Plugin 3.14 release do not work export to Excel.
The same problem with Bla, Bla ...
Example query:
WITH
SET [~ROWS_Region_Region] AS
    {[Region].[Region].Members}
SET [~ROWS_Department_Department] AS
    {[Department].[Finance], [Department].[Human Resource]}
SET [~ROWS_Positions_Positions] AS
    {[Positions].[Positions].Members}
SELECT
NON EMPTY {[Measures].[Actual]} ON COLUMNS,
NON EMPTY NonEmptyCrossJoin([~ROWS_Region_Region], NonEmptyCrossJoin([~ROWS_Department_Department], [~ROWS_Positions_Positions])) ON ROWS
FROM [Quadrant Analysis]

[Gosvami]

I just tested Saiku plugin with last week source code from github, and:

1. Now it hides the empty columns.

2. Export XLS. It shows all data, but we still see the "Bla" cells. About this, i think i figure it out why this is happening. This "Bla" is a substring from "Blank", but it looks weird because it was never meant to appear without an aggregated function selected. This is still a bug.

[Blender Alevert]

I managed to not render the box with aggregate by commented the lines who generate this in /saiku-core/saiku-service/src/main/java/org/saiku/service/util/export/excel/ExcelWorksheetBuilder.java (function addTotalsSummary) and now the data is not truncated and that box not rendered, but i saw another bug (with or without modification): if i have multilevel subtotals, this data, after first group, overlap other cells. I think somewher is a -1. I'll test tommorow.

[Gosvami]

Indeed, but i was looking for a more "graceful" solution. I was trying to find when colScanTotals and rowScanTotals  are > 0 (function addTotalsSummary), and why without selecting an aggregate function, it's still showing up the measure type cells at the end of the spreadsheet.

And yes, meanwhile, i'll comment those lines :)

Thanks,

Ps. Perhaps you can do an example with this overlapping cells so more people can replicate it.

[George Alexandru Dudău]

I attach a file with grand total for dimensions: Department and Positiions. Look from row 47

[George Alexandru Dudău]

In addTotalsSummary rowIndex do not care about subtotals;
In that exceel file we have (without totals) 148 rows + 1 header rows = 149 and

int rowIndex = startingRow + rowsetBody.length + 2; // Lines offset after data, in order to add summary

startingRow = 1 in this case
rowsetBody.length = 149
rowsetBody.length + 2 = 151

If i add subtotals the rowsetBody must be biger, but remains the same, so
addTotalsSummary box will be over data.

Temporary i disabled addTotalsSummary by commenting addTotalsSummary(lastHeaderRow); where in this case
lastHeaderRow = 1

[Gosvami]

For now i decided to disable subtotals and totals in the excel export function (like it was in Saiku 3.8).

To accomplish this, i comment only this line in ExcelWorksheetBuilder.java

comment line 117

//scanRowAndColumnAggregations(table.getRowTotalsLists(), rowScanTotals, table.getColTotalsLists(), colScanTotals);

In this way, my data won't get truncated or overlap, and i get rid of the "Bla" rows. Expect no aggregations. Any.

I don't like this, and it isn't a solution, but for now, my users will get all of their data in their excels.

I still don't know where to look to fix this, if i do, i'll let you know.

Regards,

[John Kilin]

As a more light solution to the problem i can offer to add lines to the TotalsListsBuilder.java:
...
  public TotalsListsBuilder(Measure[] selectedMeasures, TotalAggregator[] aggrTempl, CellSet cellSet,
                            AxisInfo totalsAxisInfo, AxisInfo dataAxisInfo, ThinQuery thinQuery) throws Exception {

    for (int i = 0; i < aggrTempl.length - 1; i++)
      if (aggrTempl[i] instanceof BlankAggregator) aggrTempl[i]= null;

    this.thinQuery = thinQuery;
...

среда, 10 мая 2017 г., 20:05:08 UTC+4 пользователь Gosvami Zuniga написал:

[Gosvami]

Hi John,

I test your solution. 

I used the same query that George Alexandru Dudău proposed as a test for this.

Quadrant Analysis "...grand total for dimensions: Department and Positions"

In fact it's better, because it keeps subtotals, totals, don't overlap data and it hides the aggregation rows when they're blank.

But it keeps truncating some info at the end.

I add rowScanTotals to calculate the rowIndex in addTotalsSummary(). ExcelWorksheetBuilder.java

Now it won't truncate data.

-----------------------------------------------

In Summary, we need to modified 2 files. 

TotalsListsBuilder.java

+ import org.saiku.service.olap.totals.aggregators.BlankAggregator;

...

public TotalsListsBuilder(Measure[] selectedMeasures, TotalAggregator[] aggrTempl, CellSet cellSet,
                            AxisInfo totalsAxisInfo, AxisInfo dataAxisInfo, ThinQuery thinQuery) throws Exception {

+    for (int i = 0; i < aggrTempl.length - 1; i++)
+     if (aggrTempl[i] instanceof BlankAggregator) aggrTempl[i]= null;

this.thinQuery = thinQuery;

...

ExcelWorksheetBuilder.java

...

private void addTotalsSummary(int startingRow) {

-     int rowIndex = startingRow + rowsetBody.length + 2; // Lines offset after data, in order to add summary

+     int rowIndex = startingRow + rowsetBody.length + rowScanTotals.size() + 2; // Lines offset after data, in order to add summary

     checkRowLimit(rowIndex);

...

-----------------------------------------------

I hope this changes are enough to solve this thread. They do as far as i test them.

[srikan...@gmail.com]

Hi John,

 Is there a way to fix this issue without modifying the java code.

I am using the saiku plugin in pentaho 7,1, where all the java codes are bundled as jar.