1

Re: Increase Available fieldsin 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

Re: Increase Available fieldsin Wizard's Conditions

I documented how to add attributes into the Report Wizard in Recorder 2002 in an e-mail to the old e-group - see message 122, 3/12/2003, http://www.smartgroups.com/vault/Recorder. I presume it will be much the same in Recorder 6.

Sally

Sally Rankin
JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

Sally Rankin, JNCC Recorder Approved Expert
E-mail: s.rankin@btinternet.com
Telephone: 01491 578633
Mobile: 07941 207687

3 (edited by brianmiller 03-11-2006 09:54:59)

Re: Increase Available fieldsin Wizard's Conditions

Hi Sally,

The link does not work unfortunately. This is the document I think:
http://snipurl.com/1142v

Brian

Brian Miller
(Conservation Officer (Buckinghamshire), BBOWT)

4

Re: Increase Available fieldsin Wizard's Conditions

Thanks, Brian. I think that's what I'm after. Sally's link takes you to the SmartGroup's downloads page (if you remove the full stop at the end of Sally's link.)
I'm out of the habit of looking there for tips and tricks, there's perhaps a case for some selective shifting of useful stuff from the SmartGroup era to this site (Sarah?)

5

Re: Increase Available fieldsin Wizard's Conditions

Hi Darwyn

We just received word (the day before yesterday) that the SmartGroups website is closing - something which we are not happy about to say the least!!

The site is due to close on the 30th November.

I emailed their customer services department yesterday, as the downloading of supporting files is relatively straight forward, however as yet we have no satisfactory method for downloading the messages themselves. Their first line on this was to cut and paste the messages into word - not really feasible for the entire thing!

Either way, I think it would be worthwhile to go through these and pick out, as you say, the most useful threads.

Kind regards,

Sarah

Sarah Shaw
Biodiversity Information Assistant
JNCC

Sarah Shaw
Biodiversity Information Assistant
JNCC

6

Re: Increase Available fieldsin Wizard's Conditions

Be careful - as I understand it, adding attributes to Recorder 6 report wizard is different from Recorder 2002. Mike Weideli has more details, but I don't know whether he reads this forum.

Charles

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

7

Re: Increase Available fieldsin Wizard's Conditions

Well that seems to make reporting through the Wizard out of the question until we learn more about adding USABLE fields in Recorder 6.
Here's my attempt through the other route (N.B. your Location hierarchy needs to be structured to do this):

<?xml version="1.0" ?> 
<CustomReport menupath="Location Reports" title="Quick Species List for a location hierarchy" description="Generates a list of species.  Enter values for the following parameters to filter the report."> 
<SQL>
--was SppListToSingleSiteInclSubSitesTest by Mike Weideli
CREATE TABLE #Locations (
  Location_Key CHAR(16) COLLATE Database_Default PRIMARY KEY,
  Parent_Key CHAR(16)  COLLATE Database_Default
  --Entry_Date SMALLDATETIME(4)  COLLATE Database_Default
)

CREATE TABLE #Parent (
  Location_Key CHAR(16) COLLATE Database_Default PRIMARY KEY,
  Parent_Key CHAR(16) COLLATE Database_Default
  --Entry_Date SMALLDATETIME(4)  COLLATE Database_Default
)

<Where keytype="Location">

INSERT INTO #Parent VALUES ('%s', 'B')

INSERT INTO #Locations Select #Parent.Location_Key, 'B' FROM #Parent

WHILE 1=1 BEGIN
    INSERT INTO #Locations
        SELECT L.Location_Key, 'B'
        FROM Location L
        INNER JOIN #Locations Tinc on L.Parent_Key=TInc.Location_Key
        LEFT JOIN #Locations Texc ON Texc.Location_Key=L.Location_Key
        WHERE Texc.Location_Key IS NULL
    IF @@ROWCOUNT=0
        BREAK
END


UPDATE #Locations Set Parent_Key = #Parent.Location_Key
FROM #Parent
    INNER JOIN #Locations ON #Locations.Parent_Key=#Parent.Parent_Key

SELECT 
    LOCATION_NAME.ITEM_NAME, 
    Count(TAXON_OCCURRENCE.TAXON_OCCURRENCE_KEY) AS NumberSpp
FROM 
    LOCATION_NAME INNER JOIN ((([#Locations] INNER JOIN 
    LOCATION ON [#Locations].Location_Key = LOCATION.LOCATION_KEY) INNER JOIN 
    SAMPLE ON LOCATION.LOCATION_KEY = SAMPLE.LOCATION_KEY) INNER JOIN 
    TAXON_OCCURRENCE ON SAMPLE.SAMPLE_KEY = TAXON_OCCURRENCE.SAMPLE_KEY) ON LOCATION_NAME.LOCATION_KEY = [#Locations].Parent_key
WHERE 
    (((LOCATION_NAME.PREFERRED)=1))
    --AND
    --(ENTRY_DATE BETWEEN CONVERT(DATETIME, '2006-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2006-09-30 00:00:00', 102))
GROUP BY 
    LOCATION_NAME.ITEM_NAME;


</Where>
                                        
</SQL>
    <Columns>

    </Columns>
</CustomReport>

This is almost pure Mike Weideli (he was here on Monday and demonstrated it), as it stands it just gives the number of Taxon_occurences.
This is how to use it:
Place it into your [F:\LRC\Recorder6Server\Reports] folder (same place as the system-supplied "Detailed species list.xml") with the title "Number of species records.xml"
Restart Recorder
Select a Location (one which contains subsites)
Right click | Quick report - note you've now got another folder at the bottom called "Location reports" - (that's pulled from that "menupath" statement on the second line)
Within that folder is the "Number of species records" query - this should return the name of the Location you selected and the total number of species records within all Locations under that selection.

So far nothing really different from Mike's original.

Here's where I tried and failed to make something more out of it:
The remmed items (prefixed by -- ) are an attempt to add the Entry_date to those temporary tables and towards the end to add a further condition to only count those between the two dates shown. Remove these rems and it doesn't work.
Does anyone know how to fix that - it seems so close.

8

Re: Increase Available fieldsin Wizard's Conditions

Darwyn

The remmed out lines should be:

    AND
    (TAXON_OCCURRENCE.ENTRY_DATE BETWEEN CONVERT(DATETIME, '2006-04-01 00:00:00', 102) AND CONVERT(DATETIME, '2006-09-30 00:00:00', 102))

Notice the extra TAXON_OCCURRENCE. before ENTRY_DATE - in your example there are multiple tables with an entry date and the SQL parser doesn't know which one to use.

Cheers,

John van Breda
Biodiverse IT

9

Re: Increase Available fieldsin Wizard's Conditions

Thanks, John.:lol: That works perfectly. It's also very satisfying to find an answer very close to my "beer mat" calculation of yesterday.
For other users wanting to make use of this, don't forget that you have to change the dates specified in that ENTRY_DATE line for each reporting period (e.g. for 2005/6 financial year they would be 2005-04-01 and 2006-03-31) and you might want to make a bunch of them and give them different names.

10

Re: Increase Available fieldsin Wizard's Conditions

You could try to use conditions of type="Date" and with the SubstituteFor attribute set to enable the date range used to be specified on the parameters entry screen. This will make the report a lot more flexible. Of the top of my head, something like:

...
AND
    (TAXON_OCCURRENCE.ENTRY_DATE BETWEEN {StartDate} AND {EndDate})
...

<Condition name="Start Date" SubstituteFor="{StartDate}" type="Date" operator="equal" field=""/>
<Condition name="End Date" SubstituteFor="{EndDate}" type="Date" operator="equal" field=""/>

John van Breda
Biodiverse IT