1 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: Increase Available fields in Wizard's Conditions

I simply need to provide the number of species records entered during the first 6 months of this year for a particular District.
Two ways to do this, firstly through the Location hierarchy or secondly through a Wizard based on a polygon.
For the former I'll have to study Mike Weideli's XMLs very carefully, for the latter I got stuck at the condition filters because:
1. I need to be able to access the Entry_date field in Taxon_occurrence and it's not there.
2. I'm sure I recall being able to add to that list of "Available fields" in a previous incarnation but seem to have "lost it" (in both senses of the phrase)

Can anyone provide directions?

2 Reply by johnvanbreda

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

Re: Increase Available fields in Wizard's Conditions

Hi Darwyn,
Just to clarify, I gather you are trying to get a report of the number of taxon occurrences entered in the first 6 months of this year for a particular district (filtered by location or polygon).

I'm not sure what the problem is with accessing Entry_Date in the Taxon_Occurrence table, this field is and always has been present. You could write an XML report based on the following query, converting LN.Item_Name into a condition so that you can select a location by name in the parameters box (you can filter on location_Key if you have version 6.9.2 or later):

SELECT Count(Taxon_Occurrence_Key)
FROM Taxon_Occurrence XO
INNER JOIN Sample S ON S.Sample_Key=XO.Sample_Key
INNER JOIN Location_Name LN ON LN.Location_Key=S.Location_Key
WHERE XO.Entry_Date>='2006-01-01' AND XO.Entry_Date<='2006-06-30'
AND LN.Item_Name = 'My location'

The other way to do this would be to add to the list of fields available for filtering on using the Report Wizard. This is done through the Usable_Field table.

I hope this helps!

John van Breda
Biodiverse IT

johnvanbreda's Website