search

glebovpavel / IR_to_MSExcel

Oracle Apex plugin for Interactive Grid or Interactive Report
http://glebovpavel.github.io/Description_IR_TO_XSLX/
Other
39 stars 14 forks source link

Aggregates and Conditional Columns - Errors/Unexpected Output #84

Open RTriplett opened 4 years ago

RTriplett commented 4 years ago

There is a bug resulting in errors or unexpected output with aggregates on conditional columns. I have an example here on the "example report" page https://apex.oracle.com/pls/apex/apex_rtriplet/r/latest-xlsx-download/ If "Show Qty" is off, then downloading as xlsx gives an error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "IR_TO_XLSX", line 1397 If the Average aggregate on the Quantity column is moved to the Inflated Qty column (and Show Qty is still off) then the download "Sum" amount instead is a value from the report, not an aggregate

Changing the population of l_report.[agg]_columns_on_break collections in init_t_report to only be the displayed columns has fixed these issue for me in the testing I have done:

      -- calculate columns count with aggregation separately
      l_report.sum_columns_on_break           := get_cols_as_table(
                                                    l_report.ir_data.sum_columns_on_break,
                                                    l_report.displayed_columns);
      l_report.avg_columns_on_break           := get_cols_as_table(
                                                    l_report.ir_data.avg_columns_on_break,
                                                    l_report.displayed_columns);
      l_report.max_columns_on_break           := get_cols_as_table(
                                                    l_report.ir_data.max_columns_on_break,
                                                    l_report.displayed_columns);
      l_report.min_columns_on_break           := get_cols_as_table(
                                                    l_report.ir_data.min_columns_on_break,
                                                    l_report.displayed_columns);
      l_report.median_columns_on_break        := get_cols_as_table(
                                                    l_report.ir_data.median_columns_on_break,
                                                    l_report.displayed_columns);
      l_report.count_columns_on_break         := get_cols_as_table(
                                                    l_report.ir_data.count_columns_on_break,
                                                    l_report.displayed_columns);
      l_report.count_distnt_col_on_break      := get_cols_as_table(
                                                    l_report.ir_data.count_distnt_col_on_break,
                                                    l_report.displayed_columns);
Johan1959 commented 3 years ago

Hallo Ryan, Yes , this also works for me, hope it will be merged in the package. Thanks! Johan

glebovpavel commented 3 years ago

@RTriplett Thank you, Ryan, the changes are merged to the last version! Better late than never. It turns out that many developers still using 5.1 and have no plans to upgrade to 20.2 immediately. So, i will keep this plugin for at least for bug fixing.