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
     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">

DECLARE @Level int

<Where keytype="Default">


<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'" />




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" />




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,
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 




TDET.Preferred = 1


<Condition field= "S.Location_Name" operator="like" type="text"  name="Location Name (Sample)"  />

<Condition field= "TOCC.Verified" operator="not equal" type="OptionSet"  name="Failed/Pending verification" >

        <Option name="Include" value = "3" />
        <Option name="Exclude" value = "1" />
<Condition field= "TOCC.Confidential" operator="less than" type="OptionSet"  name="Confidential" >
 <Option name="Include" value = "2" />
        <Option name="Exclude" value = "1" />


<Condition field= "TOCC.Checked" operator="greater than" type="OptionSet"  name="Unchecked Records" >
 <Option name="Include" value = "-1" />
        <Option name="Exclude" value = "0" />

<Condition field= "TOCC.Zero_Abundance" operator="less than" type="OptionSet"  name="Zero Abundance" >
 <Option name="Include" value = "2" />
        <Option name="Exclude" value = "1" />



ITN.System_Supplied_data = 1 


<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 "/>

  <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"  />



Created by Atop CHM to web converter,© 2009 all right reserved.