Crosstab default field values [#2849798]

Hi,
I am stuck at the default values for crosstab tables.
I got nice crosstab table with summed dimension values. There are also where is cells with no values (null/empty)
I want to see zeros in places of null/empty.
I tried to set default value for the field:
<frx:field id="vol_free" format="number" format-string="9 999.999">0</frx:field>
But without success yet.

Comments

Comment #1

4 February 2017 at 22:40

janeks created an issue. See original summary.

Comment #2

janeks CreditAttribution: janeks as a volunteer commented 4 February 2017 at 22:41

Issue summary:

View changes

Comment #3

metzlerd CreditAttribution: metzlerd commented 7 February 2017 at 19:47

Yes, for the data that is to be graphed I cannot pass it through the formatting functions that take care of this defaulting as the commas and other formatting cause the graphing engine to fail. Instead you should handle this in the SQL, but using COALESCE(vol_free,0) as vol_free in the select column.

Does that make sense?

Comment #4

janeks CreditAttribution: janeks as a volunteer commented 8 February 2017 at 07:58

The problem is that in query result there are only few (f.ex. 3) result rows for a grouping fields, while possible values for dimension field is f.ex. 10, that comes with another groups. So in this case Coalesce will no work, because Forena will any way get some grouping and dimension pairs with no values / nulls.

In my case I can use so called master data in query, to get full number of records per grouping fields, but:
-> it costs something (perfomance / increased data flow between db and Forena)
and
-> there could be cases where no masterdata is available and then it need some additional grouping/summary subquery to create and join - i.e sql gets more complicated

There is also another problem, that I faced (I guess the reason is the same as for first problem) - getting totall column as the last one i.e sorting of dimension columns.
I tried different ways of sorting in sql but no success (allwasy getting Totall somewhere in the middle) :
Reg Agr 114 115 511 512 952 997 Total 611 622
3002 132476016 0.481 2.000 1.000 2.000 4.110 11.000 20.591
3002 132572010 -1.990 0.010 0.010 1.000 15.000 14.030
3002 153113023 -2.990 -0.010 0.010 -2.990
3002 163183009 0.772 -44.530 45.000 10.000 25.000 36.242

Comment #5

janeks CreditAttribution: janeks as a volunteer commented 8 February 2017 at 07:53

Note - in this case I do not care about graphs. I need only simple and nice crosstab table. ;-)

Comment #6

metzlerd CreditAttribution: metzlerd commented 8 February 2017 at 16:51

I don't understand what you are saying here, but forena crosstabs do not "invent" data for combinations that don't exist in the query results. No token replacement is performed for combinations that don't exist in the crosstab, so there is no defaulting because of that.

Currently, if you want to make sure you have values, then you need to make sure you have data rows that represent the combinations.

It's possible that this is a duplicate of #2356151: Crosstab Sorting.

IF you are just worried about getting totals for a grouped expression you should look at #2551203: Crosstab Row Total