1

Topic: How to add custom attribute to report output

Hi,

I am trying (and failing) to do something simple...

I need to add a custom attribute - Observation type - to a report grid output. I've been referring to this page for guidance: http://indicia-docs.readthedocs.io/en/l … butes.html

The last bit of text on that page says " If you don’t already have any custom attributes configured for one of the surveys accessible to your client website then this would be a good time to configure one. You can then try specifying the custom attribute using the ID or attribute caption in the parameter to check it works." I do have custom attributes configured but I am not sure how to refer to them correctly. There is not an example of "specifying the custom attribute using the ID or attribute caption in the parameter".

This is what I have tried:

I think I need to add a join to the occurrence_attribute_value table like this "LEFT JOIN occurrence_attribute_values v on o.id = v.occurrence_id" and then I can show the occurrence_attribute_id and int_value in the report. That works but there are duplicates. See attached file.

There are several custom attributes for this survey e.g. Observation type [id=7], Age-sex breakdown (Adult males [8], adult females [9]etc). Where do I specify that I only want to see attributes from "Observation type" (occurrence_attribute_id=7)? If I put it in the query (i.e. WHERE #sharing_filter# and v.occurrence_attribute_id=7) then of course it only shows records where the Observation type was recorded, and not those records where the Observation type was left blank. And the next step would be - how do I get it to show the Observation type text (sighting, road kill etc) rather than the number? I can do all of this in an Access query (in attached file) but, firstly, I don't know how to reproduce this in the report query and, secondly, I think I am missing an obvious step somewhere which means I shouldn't have to do this.

Please see the attached file for clarity.

(I am still using cache_occurrences but I don't think this makes any difference here.)

Thanks for any help!

Alice

Post's attachments

Report grid.docx 108.84 kb, 2 downloads since 2018-03-22 

You don't have the permssions to download the attachments of this post.

2

Re: How to add custom attribute to report output

Hi Alice

I've just read the page of documentation you are working with and comparing that with what you have said. Stirring in a bit of experience and maybe a dash of faded memory I'd suggest the following:

- Remove the left join on occurrence attribute values from your report. I expect every join to attributes you need to be added automatically. That includes the joins to get the terms where an attribute is of the termlist type.
- Supply a value for the occattrs parameter of 7. If the report hasn't been prompting you for either smpattrs or occattrs that suggests to me you have them already set in the form configuration under the preset parameter values. Inserting occattrs=7 in that field is all I would expect you to have to do to get something like the results you want.

Other random thoughts:
- Are there unused columns that could be deleted from the report to improve clarity and performance?
- If you enable debug logging you can see the actual query that is executed which can be informative.

I haven't got a handy example to refer to so this is a bit of a shot in the dark. Let us know if it gets you anywhere.

Jim Bacon.

3 (edited by namwebs 28-03-2018 15:12:46)

Re: How to add custom attribute to report output

Hi Jim,

Thanks for your reply.

Yes, I think I was overcomplicating things...

If I take out the join and just change occattrs= to occattrs=7 in the preset report parameters it adds two columns to the report grid output at the end of the grid: "Observation type term id" and Observation type term". Cool!

But I don't want to show them there, and I only want to show the second column. I tried adding the columns to the report xml in the place I  want them, which worked insofar as it moved them to the right place. I tried putting visible="false" on the ID column to hide it but that didn't work. It's also ignoring text I put in to change the colum name i.e.  display='Observation type'

    <column name='attr_occurrence_7' visible="false" datatype="text"/>
    <column name='attr_occurrence_term_7' display='Observation type' visible="true" datatype="text"/>

So, how do I hide the first one and change the name of the second one. I also want to be able to sort on Observation type but even though the datatype is set, I can't.

On this page, in the section "Optional custom attributes," it indicates I should be able to use the equivalent of attr_id_location_4 i.e. attr_id_occurrence_term_7 to refer to the column but this does not seem to be working:  http://indicia-docs.readthedocs.io/en/l … ormat.html

Thanks again.

4

Re: How to add custom attribute to report output

Hi Alice

That's a great first step. We are now getting custom attributes appearing according to how you configure the occattrs and smpattrs report parameters

What you had was a generic report that would work across different surveys with just some changes in configuration. However, if you then add column definitions in to your report that refer to specific attributes it is no longer generic. What we would prefer to do is to organise the columns in the page configuration.

Sure enough, if you look in the Report Settings section, you will see there is a field called Columns Configuration. This allows you to order the columns, alter their visibility and alter their titles amongst other things. It is a bit complicated to get to grips with though. Behind the scenes what you are setting up is a structured array which is described in the API documentation at http://www.biodiverseit.co.uk/indicia/d … eport_grid. Click on the report_grid() function to expand the list of parameters and have a go at reading the description of the columns option.

In the Drupal configuration page there is a kind of visual editor to help you create the structured data. To use this you need to know the column names. According to my reading of the document you linked to they should be attr_occurrence_7 and attr_id_occurrence_7. However, if you use your browser tools and look at the class or id of the <th> element for the column heading I recall it contains the column name that we need perhaps with a prefix. Compare with columns that you know the name of. Perhaps the name does contain the word term like you have suggested. All I can tell you is that we will never get it to work until we have the column name right! Checking the log file to see the SQL executed is probably another way to answer the question.

Here is a rough description of what you need to do to configure the setting. You're good at getting to a solution given a pointer so I'm optimistic you'll get there even if this is not quite right. I can't find any documentation that describes this.

  1. Click Add Column to Columns List{/*]

  2. Click fieldname and enter attr_occurrence_7 in the text box.

  3. Click display and enter Observation type in the text box.

  4. Click Add Column to Columns List{/*]

  5. Click fieldname and enter attr_id_occurrence_7 in the text box.

  6. Click visible and leave the checkbox empty.

Good luck and let us know what you find.
Jim Bacon.

5 (edited by namwebs 03-04-2018 10:20:30)

Re: How to add custom attribute to report output

Thanks Jim.

I remember trying to get to grips with these report settings before. They are not very intuitive!

Some success: I have got it to show the term column and to hide the term id column (the-eis.com/atlas/?q=mammal-records&dynamic-taxon_meaning_list=1368). If I put the term column at the beginning of the grid (in the first column) it will correctly show the column header text I specify in the Display box ("Observation type") but if I put it anywhere else it shows the default ("Observation type term"). I can get the column to show where I want - before the images - by adding the column to the xml file but changing the display setting here doesn't change the column heading either.

In short, it is almost right but I can't get the column heading right.

Thanks for your help.

EDIT - it seems that if I specify occattrs=7 in the  Preset parameter values section of the report settings the map output somehow excludes a large proportion of our records! (The grid is not affected.) I am still trying to work out why but it excludes all records which were not recorded at the resolution of lat-long. So, I have removed this so that the map shows everything. Then I can't show Observation type in the grid though.

6

Re: How to add custom attribute to report output

Hi Alice,

I'm back from Easter hols but not getting email notifications when you post here.

Using the Columns Configuration settings, in order to put the Observation Type column where you want, you may have to add all the preceding columns to the list even if they are not customised in any way. Better to remove specific mention of the custom attribute from the report xml file I think.

Can you use debug logging to find out what queries are being run when you have specified occattrs=7 and view the page? Sounds as if the map report might be doing an inner join rather than a left join. You could do this on a clone of the page if you don't want to change the configuration of the current page. The Node Clone is useful for this if you don't have it already.

Jim Bacon

7

Re: How to add custom attribute to report output

Hi,

Thanks for your reply. I have been trying again to work out why the map is excluding some records, but without much success.

When restricted by occattrs=7 the map excludes many records, but the grid shows everything. If I type in an ID into the grid of a record which is not shown on the map, the map does then show it. This suggests to me that the map 'knows' about the record but is just not displaying it. The records excluded by the map are not all of one attribute type but it does seem to be spatial and I wonder if it is a limit on number of records displayed caused by the "LIMIT 2000"? If so, how do I override this and get the map to display all records?

In the User interface, Form structure part of the Report settings the map report is specified as follows:

[map]
@dataSource=library/occurrences/filterable_explore_list_mapping_nosensitivesp-test

The query being shown in the log for the filterable_explore_list_mapping_nosensitivesp-test.xml map report is below. The bits in between [[[ ]]] are the bits which are added when I change occattrs= to occattrs=7 in the report settings:

SELECT array_to_string(array_agg(o.id), ',') as occurrence_ids,
array_to_string(array_agg(distinct o.taxon_meaning_id), ',') as taxon_meaning_ids,
st_astext(o.public_geom) as geom,
o.sref_precision as sref_precision

[[[, occurrence7.id as attr_id_occurrence_7, occurrence7.int_value as attr_occurrence_7, ltt7.term as attr_occurrence_term_7]]]

  FROM cache_occurrences o
  JOIN websites w on w.id=o.website_id and w.deleted=false
  JOIN users privacyusers ON privacyusers.id=o.created_by_id

  [[[LEFT JOIN occurrence_attribute_values occurrence7 ON occurrence7.occurrence_id=o.id AND occurrence7.occurrence_attribute_id=7 AND occurrence7.deleted=false
LEFT JOIN cache_termlists_terms ltt7 ON ltt7.id=occurrence7.int_value]]]

  WHERE (o.training=false OR o.training IS NULL) AND (o.website_id in (2) OR privacyusers.id=1 OR privacyusers.allow_share_for_reporting=true OR privacyusers.allow_share_for_reporting IS NULL)
AND o.website_id in (2)
  AND o.taxa_taxon_list_id !='3780'
  AND o.taxa_taxon_list_id !='3781'
  AND o.taxa_taxon_list_id !='4118'
  AND o.taxa_taxon_list_id !='4119'
  AND o.taxa_taxon_list_id !='4120'
  AND o.taxa_taxon_list_id !='4121'
 
  AND (o.release_status='R' or o.release_status is null)
AND o.taxon_meaning_id in (1366)
AND o.record_status<>'R'
AND o.survey_id in (9)
AND o.taxon_group_id = 5

GROUP BY st_astext(o.public_geom), o.sref_precision

[[[, occurrence7.id, occurrence7.int_value, ltt7.term]]]

LIMIT 2000.


For comparison, two equivalent reports are:
http://the-eis.com/atlas/?q=carnivore-r … _list=1366
and
http://the-eis.com/atlas/?q=mammal-reco … list=1366.

You can see what I mean about the spatial restriction.

I am in the process of compiling and importing a lot of data to be used for our upcoming national carnivore red data assessment and the species teams really need to be able to display all records, together with record type.

As always, I really appreciate your willingness to help.

Alice

8

Re: How to add custom attribute to report output

Hi Alice,

If I browse those two pages I can see the requests for the reports for the two maps. Ignoring similarities, they are as follows

carnivore-records-temp&dynamic-taxon_meaning_list=1366

report: library/occurrences/filterable_explore_list_mapping_nosensitivesp-test.xml
occattrs: 7
taxon_group_id: 5
taxon_meaning_list: 1366
limit: 2000

mammal-records&dynamic-taxon_meaning_list=1366.

report: library/occurrences/filterable_explore_list_mapping_nosensitivesp.xml
occattrs: 
taxon_meaning_list: 1366.
limit: 2000
sq_size: 10000
bounds: POLYGON((-937736.26226635 -3705400.1532728,5142982.2110295 -3705400.1532728,5142982.2110295 -1748612.2294446,-937736.26226635 -1748612.2294446,-937736.26226635 -3705400.1532728))

They both share the limit 2000 so I would ignore that for now. They are not running the same report with the same parameters. It will be easier to diagnose what is happening if we can compare two identical pages which differ only in the occattrs parameter.

Jim Bacon

9

Re: How to add custom attribute to report output

sorry. Try these:

http://the-eis.com/atlas/?q=carnivore-r … _list=1366
http://the-eis.com/atlas/?q=carnivore-r … _list=1366

They are clones of each other except for that one attribute.

10

Re: How to add custom attribute to report output

That's great.
Now I'm looking at the response to the requests for map data.

Temp1, with occattr=7, returns 2000 results which is equal to the limit clause in the query
Temp2, with no occattr parameter, returns 1818 records.

We can see from the pager below the grid that there are 4753 records.

I understand this now. The query you posted above shows that, without the occattr setting, the records are being grouped by public_geom and sref_precision so that two records at the same location are shown as one dot. This means that all the records can be marked on the map with 1818 dots.
When we add the occattr=7 parameter the grouping clause now includes the occurrence_attribute_value.id and this is different for each record. We will now need 4753 dots to show all the records. This exceeds the 2000 limit and so dots are missing from the map.

One solution would be to change the 2000 limit. You may have to do this at some point but more dots will mean poorer performance. Better would be if we could apply the occattr=7 parameter to the grid but not to the map. We can try this by restoring the global preset parameter value to occattr= (i.e. with no value) and then in the form structure, under the [report_grid] section, add

@extraParams={"occattrs": 7}

I'm not entirely sure I've got this right but it should only take a minute to give it a try.

Jim Bacon.

11

Re: How to add custom attribute to report output

Hi Jim,

Thanks for your reply.

Not quite there yet... I've tried this:

[standard_params]
@allowSave=true
@linkToMapDiv=map
@taxon_list_id=15
@filter-my_records=0
[map]
@dataSource=library/occurrences/filterable_explore_list_mapping_nosensitivesp-test
=Records=
[report_grid]
@dataSource=library/alice/filterable_explore_list_allspecies_mammals
@rowId=occurrence_id
@downloadLink=true
@extraParams={"occattrs": 7}
=Summary=
[report_grid]
@dataSource=library/taxa/filterable_explore_list
@downloadLink=true
@rowId=taxon_meaning_id
@linkFilterToMap=false

and a few variations, based on searching the forum and the readthedocs site, but they all just make that grid tab disappear.

12

Re: How to add custom attribute to report output

Have you got a test page with the above config that I could have a look at?
It might give me a clue about where it goes wrong.

Jim Bacon.

13

Re: How to add custom attribute to report output

I left this one like that:
http://the-eis.com/atlas/?q=carnivore-r … _list=1366

14 (edited by Jim Bacon 17-04-2018 13:12:19)

Re: How to add custom attribute to report output

Okay, I see your problem but it works okay for me on a test system. I don't lose my grid and I see the occattrs=7 parameter appearing in the report request for the grid. However, I reckon there is a bit of clever JavaScript going on matching the grid parameters to the map parameters to keep them in sync. The occattrs=7 appears in the request for the map too and we have already seen that causes you a problem.

This means that, on the one hand, if you updated your IForm module to the latest code it might fix the problem with the missing grid but, on the other, it wouldn't solve your original problem of the map not showing all the records.

Now, it did seem to me that the way your map query is joining in the occattrs parameter seemed a bit unnecessary so I looked at the map query my system was running. For low resolution it is using library/occurrences/filterable_explore_list_mapping_lores.xml. At higher resolution it uses library/occurrences/filterable_explore_list_mapping.xml. The former ignores the occattrs param while the second includes it. This made me wonder if you have <param name='occattrs'> declared in your filterable_explore_list_mapping_nosensitivesp-test report and whether this can actually be safely removed.

An alternative, if I was wanting to try to change the limit of 2000 in the map query, would be trying to use

@extraParams={"limit":10000}

in my form structure under the [map] section. However, the evidence at the moment is that you have a problem with the code interpreting the extraParams setting. Also, as I mentioned, adding more points to the map may impair performance significantly.

Jim Bacon

15

Re: How to add custom attribute to report output

THANK YOU, Jim!

Taking out the <param name='occattrs'> in the map report allows it to show all records. So now I can show everything on the map and the grid and the attribute value.

Phew.. such a long process to do something so simple!

I appreciate your help. (Again.)

Alice

16

Re: How to add custom attribute to report output

Glad it appears to be working. There are still a few things I don't fully understand in this area. It was difficult because we had two issues interacting: adding the attribute to the grid and hitting the limit on map features.

Don't forget that 2000 record limit. Another species with a few more records from a few more locations could tip you over it again and it may not be so obvious next time.

In attempting to overcome this problem, the lores report mentioned above aggregates records by grid square (eg 10km x 10km). You may come to need your own version of this.

The OpenLayers mapping library can also cluster features to maintain performance with a large number of points but I don't believe any work has been done to make use of that with Indicia.

I frequently wish Indicia was easier to use.

Jim Bacon.

17 (edited by namwebs 17-04-2018 16:25:29)

Re: How to add custom attribute to report output

Jim Bacon wrote:

I frequently wish Indicia was easier to use.

Oooooh, are we allowed to say that out loud?  :-)

Aggregating (in my case) to our quarter degree square grid would be great and there are fewer than 2000 in the country. That will be a jolly task to attempt another day...