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.