Re: A simple guide to XML reports
This is a bit of an ambitious title but some of the postings I've made on the LRC Technical Forum provide what I think amounts to a basic guide to some simple XML reporting. As I've gradually got to grips with the issues involved, so these XML reports have gradually become more complex and more useful.
So here's the first one. All you need for this one is a text editor, Recorder 6 and a bit of an understanding of how Queries in Access 97 work.
<?xml version="1.0" ?>
<CustomReport description="Locations designated in Site_Status as Badger setts">
<SQL>
SELECT
LOCATION.LOCATION_KEY as LOCATION_KEY,
LOCATION.FILE_CODE as Object,
LOCATION_NAME.ITEM_NAME as Location,
LOCATION.SPATIAL_REF as [Grid ref],
LOCATION_DESIGNATION.SITE_STATUS_KEY as [StatusKey],
LOCATION_DESIGNATION.REF_CODE as [Reference],
LOCATION_DESIGNATION.DATE_FROM as [From],
LOCATION_DESIGNATION.DATE_TO as [to],
SITE_STATUS.SHORT_NAME as [Status],
SITE_STATUS.LONG_NAME as [StatusLong]
FROM
SITE_STATUS INNER JOIN (LOCATION INNER JOIN (LOCATION_NAME INNER JOIN LOCATION_DESIGNATION ON LOCATION_NAME.LOCATION_KEY = LOCATION_DESIGNATION.LOCATION_KEY) ON LOCATION.LOCATION_KEY = LOCATION_NAME.LOCATION_KEY) ON (SITE_STATUS.SITE_STATUS_KEY = LOCATION_DESIGNATION.SITE_STATUS_KEY) AND (SITE_STATUS.SITE_STATUS_KEY = LOCATION_DESIGNATION.SITE_STATUS_KEY)
<Where keytype="Default">
WHERE
((SITE_STATUS.SHORT_NAME)='Badger Sett')
AND
LOCATION_NAME.PREFERRED = 1
ORDER BY
Reference
</Where>
</SQL>
<Columns>
<Column name="LOCATION_KEY" position ="1" caption="LOCATION_KEY" visible = "true" column-width="0.5in" />
<Column name="Location" position ="2" caption="Locations" visible = "true" column-width="0.5in" />
<Column name="StatusKey" position ="3" caption="StatusKey" visible = "false" />
<Column name="StatusLong" position ="4" caption="StatusLong" visible = "false" />
<Column name="Grid ref" position ="5" caption="Grid ref" visible = "true" localwidth="180" />
<Column name="Object" position ="6" caption="Object" visible = "true" column-width="0.5in" />
<Column name="Reference" position ="7" caption="Reference" visible = "true" />
<Column name="Status" position ="8" caption="Status" visible = "true" />
</Columns>
</CustomReport>The function of this is to pick out all your Locations that you have designated as something or other ('Badger Sett' in this case) on the Designation tab of the Locations
The SELECT statement is a fairly simple one that you could readily design in the Query facility within Access.
If you do it this way then just copy and paste the SQL text into your text editor, add the extra text as shown above and tidy everything up into separate lines so that you can understand it.
A useful text editor is UltraEdit which you can find at IDM Computer Solutions Inc.
Note that I've given some sort of alias name to most of the fields in the first part by using "as xxxxx", the use of square brackets is a good habit since words like "to" and "from" are reserved words and will be interpreted as a command if you don't do it that way. Don't forget that every one of those fields in the first SELECT statement ends in a comma except for the last one.
Next thing to do is save it with a .xml extension (not .txt) in the appropriate folder, mine is C:\Program Files\Recorder 6 Server\User Files\Reports (write another folder below this level to store temporary xmls, a faulty one in this folder can mess things up)
You should now have something that works now; use Reports - Run - select your file from the drop-down box and you have your report.
Later posts I made on the LRC Technical Forum refined some of the above, (e.g. I solved the column width problem in the <columns> section) I shall deal with them in my next posting. Many thanks to Mike Weideli, Chris Roper & Dave Cope for useful tips.