Re: A simple guide to XML reports #2
This second example is only slightly more ambitious. What I'm trying to achieve here is a list of landowners for each location designations (e.g. a "Wildlife Site").
The kit I've used for this is a little better than in my previous posting.
Now I've obtained Access 2003 and linked it to the underlying Recorder database in order to construct a query.
Here's the query I've designed in Access 2003:
SELECT TOP 100 PERCENT dbo.LOCATION.LOCATION_KEY, dbo.SITE_STATUS.SHORT_NAME AS Status, dbo.LOCATION.FILE_CODE AS Object,
dbo.LOCATION_NAME.ITEM_NAME AS Location, dbo.LOCATION.SPATIAL_REF AS Grid, dbo.TENURE_TYPE.SHORT_NAME AS Tenure,
dbo.INDIVIDUAL.FORENAME AS Initials, dbo.INDIVIDUAL.SURNAME AS Name, dbo.ADDRESS.ADDRESS_1 AS Address,
dbo.CONTACT_NUMBER.NUMBER AS Contact
FROM dbo.TENURE INNER JOIN
dbo.INDIVIDUAL ON dbo.TENURE.OWNED_BY = dbo.INDIVIDUAL.NAME_KEY INNER JOIN
dbo.LOCATION_NAME ON dbo.TENURE.LOCATION_KEY = dbo.LOCATION_NAME.LOCATION_KEY INNER JOIN
dbo.LOCATION ON dbo.LOCATION_NAME.LOCATION_KEY = dbo.LOCATION.LOCATION_KEY INNER JOIN
dbo.TENURE_TYPE ON dbo.TENURE.TENURE_TYPE_KEY = dbo.TENURE_TYPE.TENURE_TYPE_KEY INNER JOIN
dbo.ADDRESS ON dbo.INDIVIDUAL.NAME_KEY = dbo.ADDRESS.NAME_KEY INNER JOIN
dbo.CONTACT_NUMBER ON dbo.INDIVIDUAL.NAME_KEY = dbo.CONTACT_NUMBER.NAME_KEY INNER JOIN
dbo.LOCATION_DESIGNATION ON dbo.LOCATION.LOCATION_KEY = dbo.LOCATION_DESIGNATION.LOCATION_KEY INNER JOIN
dbo.SITE_STATUS ON dbo.LOCATION_DESIGNATION.SITE_STATUS_KEY = dbo.SITE_STATUS.SITE_STATUS_KEY
WHERE (dbo.LOCATION_NAME.PREFERRED = 1) AND (dbo.TENURE_TYPE.SHORT_NAME = 'Owner') AND (dbo.SITE_STATUS.SHORT_NAME = 'Wildlife Site')
ORDER BY dbo.LOCATION_NAME.ITEM_NAMEA little tidying up in a text editor (removing the "Top 100 percent" and the "dbo."s) and I've got something that will insert into my xml document.
SELECT
LOCATION.LOCATION_KEY,
SITE_STATUS.SHORT_NAME AS Status,
LOCATION.FILE_CODE AS Object,
LOCATION_NAME.ITEM_NAME AS Location,
LOCATION.SPATIAL_REF AS Grid,
TENURE_TYPE.SHORT_NAME AS Tenure,
INDIVIDUAL.FORENAME AS Initials,
INDIVIDUAL.SURNAME AS Name,
ADDRESS.ADDRESS_1 AS Address,
CONTACT_NUMBER.NUMBER AS Contact
FROM
TENURE INNER JOIN
INDIVIDUAL ON TENURE.OWNED_BY = INDIVIDUAL.NAME_KEY INNER JOIN
LOCATION_NAME ON TENURE.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY INNER JOIN
LOCATION ON LOCATION_NAME.LOCATION_KEY = LOCATION.LOCATION_KEY INNER JOIN
TENURE_TYPE ON TENURE.TENURE_TYPE_KEY = TENURE_TYPE.TENURE_TYPE_KEY INNER JOIN
ADDRESS ON INDIVIDUAL.NAME_KEY = ADDRESS.NAME_KEY INNER JOIN
CONTACT_NUMBER ON INDIVIDUAL.NAME_KEY = CONTACT_NUMBER.NAME_KEY INNER JOIN
LOCATION_DESIGNATION ON LOCATION.LOCATION_KEY = LOCATION_DESIGNATION.LOCATION_KEY INNER JOIN
SITE_STATUS ON LOCATION_DESIGNATION.SITE_STATUS_KEY = SITE_STATUS.SITE_STATUS_KEY
WHERE
(LOCATION_NAME.PREFERRED = 1) AND (TENURE_TYPE.SHORT_NAME = 'Owner') AND (SITE_STATUS.SHORT_NAME = 'Wildlife Site')
ORDER BY
LOCATION_NAME.ITEM_NAMEHere's the final xml document after I've dropped the above code in:
<?xml version="1.0" ?>
<CustomReport menupath="Location Reports" title="Location ownership (Individuals)" description="Generates a list of location owners for specified location status.">
<SQL>
<Where keytype="Default">
SELECT
LOCATION.LOCATION_KEY,
TENURE_TYPE.SHORT_NAME AS Tenure,
LOCATION_NAME.PREFERRED as Preferred,
LOCATION_NAME.ITEM_NAME AS Location,
INDIVIDUAL.TITLE AS Title,
INDIVIDUAL.FORENAME as Forename,
INDIVIDUAL.INITIALS as Initials,
INDIVIDUAL.SURNAME as Surname,
SITE_STATUS.SHORT_NAME AS Status
FROM
NAME INNER JOIN
INDIVIDUAL ON NAME.NAME_KEY = INDIVIDUAL.NAME_KEY INNER JOIN
TENURE ON NAME.NAME_KEY = TENURE.OWNED_BY INNER JOIN
LOCATION ON LOCATION.LOCATION_KEY = TENURE.LOCATION_KEY INNER JOIN
TENURE_TYPE ON TENURE.TENURE_TYPE_KEY = TENURE_TYPE.TENURE_TYPE_KEY INNER JOIN
LOCATION_NAME ON LOCATION.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY INNER JOIN
LOCATION_DESIGNATION ON LOCATION.LOCATION_KEY = LOCATION_DESIGNATION.LOCATION_KEY INNER JOIN
SITE_STATUS ON LOCATION_DESIGNATION.SITE_STATUS_KEY = SITE_STATUS.SITE_STATUS_KEY
WHERE
(TENURE_TYPE.SHORT_NAME = 'Owner')
AND
(LOCATION_NAME.PREFERRED = 1)
AND
<Condition operator="equal" field="Site_Status.Short_name" name="Designation " type="Text" />
--(SITE_STATUS.SHORT_NAME = 'pWildlife site')
ORDER BY
Location
</Where>
</SQL>
<Columns>
<Column name="LOCATION_KEY" tablename="Location key" visible="False" />
<Column name="Location_key" position="1" caption="Location_key" width="120"/>
<Column name="Status" position="2" caption="Status" width="100"/>
<Column name="Location" position="3" caption="Location" width="200"/>
<Column name="Preferred" position="4" caption="Preferred" visible="False" />
<Column name="Tenure" position="5" caption="Tenure" keycolumn="Tenure" width="80"/>
<Column name="Title" position="6" caption="Title" width="50"/>
<Column name="Initials" position="7" caption="Initials" width="50"/>
<Column name="Forename" position="8" caption="Forename" width="100"/>
<Column name="Surname" position="9" caption="Surname" width="100"/>
</Columns>
</CustomReport>Note the new features I've incorporated into this (as compared with my first guide)
1. The <CustomReport line gives me the text for an opening dialog box
2. I've changed one of my "WHERE" statements so that it responds to the question asked in the opening dialog box, remming out the fixed statement "SITE_STATUS.SHORT_NAME = 'pWildlife site'" to help keep track of my changes.
3. The <Columns section works better now, the method for specifying column widths is correct.
Give the above one a try, my next effort will be to combine this with a similar statement for Organisations through the UNION statement so that I get a single list regardless of whether the land owner is an Individual or an Organisation. I can do it in Access but the Transact SQL methodology evades me for the moment.
