1 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Filtering reports on a calculated field

I don't know if this is even possible, but it would be really useful if it were.

I am trying to produce a report which excludes taxon occurrences where the sample spatial ref is a 10km square (i.e. Len(SAMPLE.SPATIAL_REF)=4).

Using the notes on the Wiki I have succeded in adding records to the REPORT_ATTRIBUTE and REPORT_FIELD tables, reusing an existing record in REPORT_JOIN. So I can produce a report with a field called Sample Spatial Ref Length.

However, when I tried to modify the USEABLE_FIELD table (as I have successfully with another such attribute), I can make my new attribute appear in the additional filters list and I can set the filter to only allow records where the field is >4. Unfortunately when I run the report it causes an exception.

Inspecting the error message, I find that at the end of the WHERE clause the following (SAMPLE.[Sample Spatial Ref Length] > 4). Of course the new calculated field is not within the sample table, so this is why it fails (it will be within #REPORT_OUTPUT, but I can't work out how to make the query work.

So two questions:
1. is it possible to persuade recorder to filter on a calculated attribute
2. is there any chance I could see the promised instructions on adding items to USEABLE_FIELD and USEABLE_TABLE.

Thanks in anticipation

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

2 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Filtering reports on a calculated field

No response to this one so far.

It would be really useful to see some guidance on USEABLE_FIELD if anyone has anything.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

3 Reply by charlesroper

  • charlesroper
  • charlesroper
  • Digital Development Manager
  • Offline
  • From: Field Studies Council
  • Registered: 26-03-2006
  • Posts: 906

Re: Filtering reports on a calculated field

I'd like to know the answer to this one too.

Charles Roper
Digital Development Manager | Field Studies Council
http://www.field-studies-council.org | https://twitter.com/charlesroper | https://twitter.com/fsc_digital

charlesroper's Website

4 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Filtering reports on a calculated field

Charles, do you think if we keep bumping this one to the top someone will answer it eventually?

I'm not even worried about the calculated field thing anymore. Now all I want to do is add Taxon Group to the USEABLE_FIELD table.

You would think that the ability to produce a filter which provides all occurrences of (say) vascular plants would be something we would want to do often wouldn't you? We want to synchronise our data holding with the county recorders.

And before anyone points me towards the Vascular plants preferred checklist as an alternative, I should point out that we have a significant number of records (mostly of hybrids, subspecies and varieties) which do not appear in the checklist.

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website

5 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Filtering reports on a calculated field

Hi Rob
Coming back to this, here is a script to create a taxon group additional filter in the Report Wizard:

IF NOT EXISTS(SELECT 1 FROM Usable_Table WHERE Usable_Table_Key='JNCCDEV100000019')
    INSERT INTO Usable_Table VALUES (
        'JNCCDEV100000019',
        'Taxon_Group',
        'Taxon_Version',
        'Taxon_Group.Taxon_Group_Key = Taxon_Version.Output_Group_Key',
        NULL,
        'T',
        5) -- must be higher than the join_order for Taxon_Version

IF NOT EXISTS(SELECT 1 FROM Usable_Field WHERE Usable_Field_Key='JNCCDEV100000049')
    INSERT INTO Usable_Field VALUES (
        'JNCCDEV100000049',
        'Taxon_Group',
        'Taxon_Group_Name',
        'Taxon Group',
        'TEXT',
        'T',
        0,
        0,
        1,
        'Taxon_Group.Taxon_Group_Name'
    )

I will ask Mary to include this in the next update. Please bear in mind the keys I have used are consecutive with the ones used by 6.14.5, so we may need to check they don't clash with any in the next update which JNCC have already added. I will check...

You are right though, Usable_Field can only operate against the "inputs" not the "outputs", so if you have a calculated field in #REPORT_OUTPUT you need to set up a filter against the calculation itself, not the field name of the calculated output. Does that make sense?

John van Breda
Biodiverse IT

johnvanbreda's Website

6 Reply by RobLarge

  • RobLarge
  • RobLarge
  • Veteran
  • Offline
  • From: Wiltshire
  • Registered: 15-07-2008
  • Posts: 812

Re: Filtering reports on a calculated field

Thanks John

Looks very useful, I will look more closely when I have time to digest it properly

Rob Large
Wildlife Sites Officer
Wiltshire & Swindon Biological Records Centre

RobLarge's Website