QGIS Print Composer – Using Table Fields in Expression for Custom Table

layoutsprint-composerqgistable

I am looking for a way to – well, kind of use a custom table in the print composer like an excel sheet. I've used a custom table in my Print Layout to calculate values based on Atlas features. So far, so good. But now I want to do calculations in the table by accessing fields directly. In this case: Calculating B7 by adding B2 + B4. There seems to be no way as far as I tried in the expression dialogue.

Anybody knows a trick?

enter image description here

Best Answer

I guess it bears repeating that expecting GIS data structures to behave like spreadsheets - especially in terms of a 'totals' row - is usually going to result in disappointment. More so with the Attribute Table layout item which is just a derivative of source data - I don't think QGIS stores the location of each row.

You could use a convoluted expression - or Python, or generate a virtual layer maybe - to:

  1. generate unique aggregates based on map layout visibility
  2. sort the results in the specific order required and extract the 2nd and 4th rows from #1 - OR reference the specific rows by an identifying value (e.g. "item type" = 'point')
  3. extract the required aggregate values from #2
  4. Sum the results from #3

...but you would still need the "totals" row to be a separate table or text box rather than within the attribute table.

I am not sure how you generated an attribute table that has both sums and counts as separate rows rather than columns.

But to prove that the concept is technically possible, here is an example where I have counted the heights of trees visible in an atlas feature grouped by genus, ordered by genus.

In a separate table I calculated the combined heights of relevant trees; the first column just had static text (e.g. 'Sum of blah') and the second text had an expression. I also filtered this table to show only 1 row, otherwise it would iterate the same value over every feature in the source layer.

For this example I created two such tables, (a) the 2nd and 4th rows (much more convoluted), and (b) the total number of trees that are genus Acacia or Eucalyptus.

enter image description here

But I don't think trying to sum 'the second and fourth rows' is ever a good approach - that's just not good data structuring, if you can instead reference particular values that would be more robust.

Here is the code for table (a)

with_variable('results',
            array_distinct(
                  array_agg(sum("height_m",
                                group_by:="genus",
                                filter:=intersects($geometry,@atlas_geometry)),
                  filter:=intersects($geometry,@atlas_geometry),
                  order_by:="genus")),
            @results[1]+@results[3])

And the code for table (b)

array_sum(
          array_distinct(
                         array_agg(sum("height_m",
                                       group_by:="genus",
                                       filter:=intersects($geometry,@atlas_geometry)),
                         filter:=intersects($geometry,@atlas_geometry) 
                         and "genus" in ('Acacia','Eucalyptus'))))

Replace "genus" with the attribute you want to group by,"height_m" for the attribute you want to aggregate, and 'Acacia' and 'Eucalyptus' with any specific values you want to reference for table (b)

Related Question