Variables
XML reports allows variables to be declared and used within the SQL statement. The declaration must be inside the SQL tags and should normally be placed before any other statement. Variables can be used to control loops or to hold data for manipulation in the same way as they are used in User Defined Functions. In many cases it will be easier to write a new UDF rather than include complex calculation in the actual report. Early version of XML reports did appear to have problems with variables so I have tended to use temporary tables However, variables will be able to replace Temporary tables in some cases, especially where the temporary table is being used to hold a parameter. If you wish to explore further the use of variables then a book which explains User Defined Functions may be useful.
The following example shows a how variables can be used to hold parameter instead of temporary tables.
<?xml version="1.0" ?>
<!-- JNCC Records for selected Sample Location Name with
Designations by Set
XML Report by Mike Weideli of
Littlefield Consultancy for JNCC
http://www.lfield.co.uk
Version 4 - 22 March 2009
Uses Taxon
Designation sets
QUICK REPORT - Not
implemented
Confidential - By parameter
Zero Abundance - By
parameter
Validated - By
parameter
Invalid = By
parameter
-->
<CustomReport menupath="JNCC\Location Name Reports"
title="Ln2a Location Name Report "
description="Records for a Location Name
- specified by name. Search will work on the start of the Sample Location Name.
To search anywhere in the Sample Location Name enter * at the start of the
search string.
Includes a summary of designations for a set chosen from
those system supplied">
<SQL>
DECLARE @Set CHAR(16)
DECLARE @Level int
<Where keytype="Default">
SET @SET
<Condition field="" operator="equal"
type="OptionSet" name="DesignationSet"
>
<Option name="All
Designated Species excluding Northern Ireland (All European, W and C, Cons Regs,
Red Lists)" value = "'SYSTEM0100000000'"
/>
<Option name="All
European Designated (Birds Dir, H and S, Berne, Bonn)" value =
"'SYSTEM0100000001'" />
<Option
name="All UK Legally Protected (W and C Act, Badgers and Cons
Regs)" value=
"'SYSTEM0100000002'" />
<Option name="Wildlife and Countryside Act (All Schedules)" value =
"'SYSTEM0100000003'" />
<Option name="IUCN Red Lists
(excl BoCC)" value = "'SYSTEM0100000004"
/>
<Option
name="Biodiversity Action Plan species (2007)" value = "'SYSTEM0100000005'"
/>
<Option name="NI Wildlife
Order" value = "'SYSTEM0100000006'" />
</Condition>
Set @Level
<Condition field="" operator="equal" type="OptionSet"
name="Status output" >
<Option name="Short name" value = "1"
/>
<Option name="Long Name"
value = "2" />
<Option
name="Abbreviation" value = "4"
/>
<Option name="Kind" value
= "3" />
<Option
name="Yes/No" value = "5" />
</Condition>
SELECT TOCC.Taxon_Occurrence_key, S.Spatial_Ref,S.Location_name as
Item_name,s.vague_date_start,s.vague_date_end,s.vague_date_type,dbo.formateventRecorders
(s.sample_key)
as Recorders, dbo.FormatIndividual (I.Title, I.Initials, I.Forename, I.Surname)
as Determiner,
ITN.Preferred_name, ITN.Authority,ITN.Common_name,
ITN2.Actual_name as Recommended_name, ITN2.Common_Name as
Recommended_Common,
TG.Taxon_Group_Name,
dbo.ufn_getdesignations(ITN.taxon_List_Item_Key,@level,@set,null)
as Status
From Sample S
INNER JOIN Taxon_Occurrence TOCC ON TOCC.Sample_key =
S.Sample_key
INNER JOIN Taxon_Determination TDET on TDET.Taxon_Occurrence_key
= TOCC.Taxon_Occurrence_key
INNER JOIN Taxon_List_Item TLI ON TLI.Taxon_List_Item_key =
TDET.Taxon_List_Item_Key
INNER JOIN Taxon_Version
TV ON TV.Taxon_version_Key = TLI.Taxon_Version_Key
INNER JOIN Taxon_Group TG
ON TV.Output_group_key = TG.Taxon_group_Key
INNER JOIN Index_Taxon_Name ITN
ON ITN.Taxon_List_Item_Key = TDET.Taxon_List_Item_Key
INNER JOIN
Index_taxon_Name ITN2 ON ITN2.Taxon_List_Item_key =
ITN.Recommended_Taxon_List_item_key
INNER JOIN Individual I on I.name_key =
TDET.Determiner
where
TDET.Preferred = 1
AND
<Condition field= "S.Location_Name" operator="like" type="text"
name="Location Name (Sample)" />
and
<Condition field= "TOCC.Verified" operator="not equal" type="OptionSet" name="Failed/Pending verification" >
<Option name="Include"
value = "3" />
<Option
name="Exclude" value = "1" />
</Condition>
AND
<Condition field=
"TOCC.Confidential" operator="less than" type="OptionSet"
name="Confidential" >
<Option name="Include" value = "2"
/>
<Option name="Exclude"
value = "1" />
</Condition>
AND
<Condition field= "TOCC.Checked" operator="greater than"
type="OptionSet" name="Unchecked Records" >
<Option
name="Include" value = "-1" />
<Option name="Exclude" value = "0" />
</Condition>
AND
<Condition field= "TOCC.Zero_Abundance" operator="less than"
type="OptionSet" name="Zero Abundance" >
<Option
name="Include" value = "2" />
<Option name="Exclude" value = "1" />
</Condition>
AND
ITN.System_Supplied_data = 1
</Where>
<Orderby name="by Taxon Group" SQL="Order By tg.taxon_group_name,
itn.preferred_name "/>
<Orderby name="by Scientific Name "
SQL="Order By itn.preferred_name drop "/>
<Orderby name="by Location Name" SQL="Order By S.Location_name, tg.taxon_group_name,itn.preferred_name "/>
</SQL>
<Columns>
<Column
name="Taxon_Occurrence_key" visible="false" tablename= "Taxon_Occurrence"
/>
<Column name="Spatial_ref" width="60" position="1"
caption="Spatial Ref" keycolumn ="Taxon_Occurrence_key"/>
<Column name="Item_name" width="200" position="2" caption="Sample
Location Name" />
<Column name="Vague_date_start" width="100"
position="3" caption="Date" />
<Column name="Recorders"
width="200" position="4" caption="Recorders" />
<Column
name="Determiner" width="120" position="5" caption="Determiner" />
<Column name="Taxon_Group_name" width="200" position="6"
caption="Taxon Group" />
<Column name="Preferred_Name"
width="200" position="7" caption="Scientific Name" />
<Column
name="Authority" width="200" position="8" caption="Authority" />
<Column name="Common_Name" width="200" position="9" caption="Common Name"
/>
<Column name="Recommended_name" width="200" position="10"
caption="Recommended Name" />
<Column name="Recommended_Common"
width="200" position="11" caption="Recommended Common Name" />
<Column name="Status" width="400" position="12"
caption="Designations" />
</Columns>
</CustomReport>