postgresql - How to dynamically remove crosstab columns -

postgresql - How to dynamically remove crosstab columns -

preamble: i've got crosstab following:

row groups: month, jan december; column groups: name; column total , row total.

it shows total amount of money spent in parametric year, each person in db. datasource postgres database, i'm retrieving info query.

on db i'm using "blank" records, partial extractions: need show months, , i'm running illustration on may, need show anyway months jan december.

my db looks this:

id | name | month | year | money 1 | marco | jan | 2014 | 150.0 2 | marco | feb | 2014 | 80.0 3 | marco | march | 2014 | 135.5 4 | marco | apr | 2014 | 50.0 5 | marco | may | 2014 | 200.0 6 | | june | 2014 | 0.0 7 | | july | 2014 | 0.0 8 | | august | 2014 | 0.0 9 | | september | 2014 | 0.0 10 | | oct | 2014 | 0.0 11 | | nov | 2014 | 0.0 12 | | dec | 2014 | 0.0

the first 5 rows, illustration of "regular" record, others "hacking" ones need 12 month crosstab table on report.

problem: solution brings me crosstab table has empty column if run study jan november. if dec filled in, doesn't show blank column.

as is:

| | marco | mario | luigi | total jan | 0.0 | 150.0 | 150.0 | 150.0 | 450.0 feb | 0.0 | 80.0 | 80.0 | 80.0 | 240.0 march | 0.0 | 135.5 | 135.5 | 135.5 | 406.5 apr | 0.0 | 50.0 | 50.0 | 50.0 | 150.0 may | 0.0 | 200.0 | 200.0 | 200.0 | 600.0 june | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 july | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 august | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 september | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 oct | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 nov | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 dec | 0.0 | 0.0 | 0.0 | 0.0 | 0.0 total | 0.0 | 615.5 | 615.5 | 615.5 | 1846.5

expectation:

| marco | mario | luigi | total jan | 150.0 | 150.0 | 150.0 | 450.0 feb | 80.0 | 80.0 | 80.0 | 240.0 march | 135.5 | 135.5 | 135.5 | 406.5 apr | 50.0 | 50.0 | 50.0 | 150.0 may | 200.0 | 200.0 | 200.0 | 600.0 june | 0.0 | 0.0 | 0.0 | 0.0 july | 0.0 | 0.0 | 0.0 | 0.0 august | 0.0 | 0.0 | 0.0 | 0.0 september | 0.0 | 0.0 | 0.0 | 0.0 oct | 0.0 | 0.0 | 0.0 | 0.0 nov | 0.0 | 0.0 | 0.0 | 0.0 dec | 0.0 | 0.0 | 0.0 | 0.0 total | 615.5 | 615.5 | 615.5 | 1846.5

question: how can hide "blank" column? there way create collapse?

edit: tried create "blank" column totally blank, editing look of measures show "blank" if name column grouping "blank" too. both in column, measure , total cell i've checked "remove if blank" alternative no result. there still space between months , rest of crosstab. here i've got now:

| | marco | mario | luigi | total jan | | 150.0 | 150.0 | 150.0 | 450.0 feb | | 80.0 | 80.0 | 80.0 | 240.0 march | | 135.5 | 135.5 | 135.5 | 406.5 apr | | 50.0 | 50.0 | 50.0 | 150.0 may | | 200.0 | 200.0 | 200.0 | 600.0 june | | 0.0 | 0.0 | 0.0 | 0.0 july | | 0.0 | 0.0 | 0.0 | 0.0 august | | 0.0 | 0.0 | 0.0 | 0.0 september | | 0.0 | 0.0 | 0.0 | 0.0 oct | | 0.0 | 0.0 | 0.0 | 0.0 nov | | 0.0 | 0.0 | 0.0 | 0.0 dec | | 0.0 | 0.0 | 0.0 | 0.0 total | | 615.5 | 615.5 | 615.5 | 1846.5

it's not possible remove columns on crosstab using ireport.

postgresql jasper-reports collapse crosstab