1 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: Accessing other data sources in XML reports

Perhaps someone can come up with an idea about how to incorporate other data sources into an XML report, by this I mean a list either from within Recorder (e.g. a Rucksack of Protected/CRoW 74 species) and/or a table in an external Access database. There is a thread about this on the LRC Technical Forum at http://forum.lrcs.org.uk/viewtopic.php?id=62.
Here's a real example to show what I mean:
Our Districts deliver Planning lists to our surveyors once a week, these lists contain grid references of various developments that are contemplated in their area. What our surveyors need to do is to provide advice on which of the proposed developments are likely to need a bit of care under various wildlife legislation (e.g. bats, badgers etc.). To do this they currently enter each grid square coordinates into the Report Wizard's bounding box and, with the Protected species Rucksack open, determine which species are present. They do this for each of the 20 or 30 items in each District's weekly list - a laborious and time-consuming task. The results are then hand written to produce a final report.
What I would like to do is develop an XML report which responded again to the open Rucksack and to a list of all those 1Km search areas (via a single key field which identifies multiple rows in an Access table - one row for each development - more at the above thread). All the surveyor would need to do then is populate an Access table with the Planning list essentials, open an XML report, bung in the Planning list's unique key and obtain a final report fit for mailing to the District planners (and saving our staff a heck of a lot of time)
So how do I get an XML to:
a) respond to an open Rucksack
b) respond to an Access table

Getting useful reports out of Recorder is bread and butter work for an LRC so it's been disappointing to see so few tips and tricks in this section. Please give this one a try if you know how.

2 Reply by johnvanbreda (edited by johnvanbreda 21-07-2006 13:46:33)

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Accessing other data sources in XML reports

Darwyn

If the Access database is on the same server as the sql server, then it would be possible to use one of the Transact SQL commands for accessing external data (OPENROWSET being a good example).  If not, then you can dump the data into a CSV file and use the CSV file integration feature of the XML Reports to use the CSV file as an input table.  Likewise, you could manually dump the content of a rucksack into a CSV file and use that through this method.  The XML Report feature doesn't support direct integration with a rucksack though this does sound like an obvious and useful upgrade.

Hopefully the following example report code should illustrate how the CSV files work:

<?xml version="1.0" ?> 
<CustomReport>
<CSVTables>
<CSVTable name="#CSV1">
<CSVFile description="Counties" /> 
<CSVColumn name="CSV1" datatype="Char" size="20" /> 
<CSVColumn name="CSV2" datatype="Date" /> 
</CSVTable>
<CSVTable name="#SecondCSVTable" filename="C:\Temp\ChampData.csv">
<CSVColumn name="CSV3" datatype="Int"/> 
<CSVColumn name="CSV4" datatype="Char" size="50" /> 
</CSVTable>
</CSVTables>
<SQL>
SELECT Field1, Field2, CONVERT(CHAR(20), NULL) AS CSV1, CONVERT(DATETIME, NULL) AS CSV2, 
CONVERT(INT, NULL) AS CSV3, CONVERT(CHAR(50), NULL) AS CSV4, CONVERT(CHAR(20), NULL) AS Update1 
FROM Table1 T1 JOIN Table2 T2 ON T1.PrimaryKey = T2.ForeignKey 
JOIN #CSV1 ON T2.PrimaryKey = #CSV1.CSV1 
JOIN #SecondCSVTable C2 ON C2.CSV3 = T1.Field4
<Where KeyType="Default">
WHERE CSV1 = 'Dorset' AND 
<Condition field="CSV2" operator="equal" type="Date" name="Survey Date" /> 
AND 
<Condition field="CSV3" type="CSVFile" name="Event Status File" datatype="text" /> 
AND 
<Condition field="CSV4" operator="greater than equal" type="OptionSet" name="Location">
<Option name="Poole" value="P32545" /> 
<Option name="Bournemouth" value="B9876" /> 
<Option name="Christchurch" value="C1234" /> 
<Option name="Wimborne" value="W13244" /> 
<Option name="Wareham" value="W234544" /> 
</Condition>
AND
<Condition field="" operator="not equal" type="VagueDate" name="Sample Date" />
</Where>
<Where keytype="Location">WHERE Location_Key = '%s'</Where> 
<Orderby name=”by Field1 then Field2” sql=”Order by Field1, Field2”>
<Orderby name=”by Field2 then Field1” sql=”Order by Field2, Field1”>
</SQL>
<Columns>
<Column name="CSV2" position="1" caption="Location" keycolumn="Field1" />
<Column name="Field1" tablename="Location" visible="False" />
</Columns>
</CustomReport>
John van Breda
Biodiverse IT

johnvanbreda's Website

3 Reply by Darwyn Sumner (LERC)

  • Darwyn Sumner (LERC)
  • Veteran
  • Offline
  • Registered: 07-06-2006
  • Posts: 176

Re: Accessing other data sources in XML reports

That's looking good, John. Any chance you could bung that test CSV file onto the site to help relate those field names to real data.

4 Reply by johnvanbreda

  • johnvanbreda
  • Administrator
  • Offline
  • From: UK
  • Registered: 27-04-2006
  • Posts: 1,237

Re: Accessing other data sources in XML reports

Sorry, Darwyn, this was extracted from documentation written way back when the XML Report facility was designed and I don't have the files.  Here's what the documentation says about the CSV files:

The CSV file format used by Recorder is based on the ‘standard’ csv file as saved by Excel, the following limits are imposed:
•    The first row must be the column titles
•    The text delimiter must be ".  This is required to delimit a field if it contains the field delimiter.
•    The record delimited must be CRLF
•    The field delimiter must be a comma.

Note that the first CSV file (Counties) is actually a report parameter - the user has to select a CSV file using the input parameters dialog.  The second is hard coded to C:\Temp\ChampData.csv, in this case reflecting some output from a GIS.  The first CSV file content might be something like:

CSV1, CSV2
"East Dorset", 2005-07-29,
"West Dorset", 2005-08-31

etc

John van Breda
Biodiverse IT

johnvanbreda's Website