Topic: Hot potato: XML versus SQL reports

I relied heavily on SQL reports in R2K2, harnessing some of the power of the structured database and straightforward queries to deliver a range of outputs outside the bog-standard taxon/biotope/place-based reports which confine us so much.
Notably:
1. SQLs based around Location and their Status (on the Designations tab) provided an excellent way of tracking inventories for BAPs, Phase1 and Local Wildlife Sites and their progression through proposal to ratification
2. Incorporate a rucksack into this and you can confine these lists to specific regions in your Location hierarchy
3. A few simple calculations and you have a method that turns Ancient tree girth into a file which can create appropriately sized circles in GIS
4. Slide a table into nbndata which provides multiple 1Km square descriptors against a single job reference and you've got yourself a whole new way of reporting on selected 1Km squares (I know others have done this too - Mike Weideli's spotted it) and actually use this as our default reporting system as its better and quicker than any geospatial reporting facility built into the Recorder.
5. The navigation to all these was swift and could be arranged in a logical hierarchical way.
6. Output was to a variety of formats
Stuart Ball wrote this and its a terrific piece of work

Now in Recorder 6 we lose pretty well all of this and have to use XML
1.  XML requires an additional set of skills and tools and apart from a few useful tips from Mike there is no-one busying themselves on developing a range of XML scripts (I'll happily hand over my SQLs to someone prepared to turn them into XML)
2. Navigation is slow and the only hierarchy you've got is through Explorer (confined to a tiny box)
3. Output is crippled and limited (see my "Catastrophic failure" thread on Recorder eGroup)

Getting information out of the Recorder is a major part of the application and I'm afraid I see no signs of progress in moving from Recorder 2002 to 6.
1. There's no sign of support from JNCC, they cannot all be on paternity leave, hiking around the world or working on other projects
2. Restoration of SQL was something Dorset software know little about as it was written by Stuart and he works on other projects for JNCC now.

So where is the support, even a useful Help file would be a start because that one's appalling.

Darwyn Sumner

Re: Hot potato: XML versus SQL reports

Hi Darwyn,

It's true that XML Reports is quite a change from SQL Reports and it's more difficult to use, but it's so much more powerful given that we have now T-SQL at our disposal rather than JET SQL. Granted it doesn't have the hierarchy view of SQL Reports, but it does allow us to attach queries to element of the UI such as a location by right clicking on it and selecting 'quick reports'. So it's more powerful, but also more difficult to use. Output is somewhat crippled, but you can copy/paste the results out to a text file or Excel, which can usually solve most problems. This is what I used to to with SQL Reports anyway.

Compared to learning T-SQL, learning the XML involved in XML Reports is actually quite easy. You don't have to learn or use XML to query the database - it's not in any way a replacement for SQL. Here's a very basic example (taken from Mike's 'beginning XML reports' document - I'll send you a copy if you want):

<?xml version="1.0" ?> 
<CustomReport  description="Example 1"> 
    <SQL>
        SELECT sample.*  From Sample
        
        <Where keytype="Default">    
        </Where>
    </SQL>

    <Columns>
    </Columns>

</CustomReport>

As you can probably see, the XML is just a 'wrapper' for the SQL (SELECT sample.*  From Sample). So I suppose, to be more accurate, XML Reports should be called 'SQL Reports enhanced with XML'. You would run this report by going to Report > Run.. then choosing the report from the drop-down list. The report simply presents a list of all samples in the db.

If you send me your SQL queries Darwyn, I'd be happy to have a look at them to see how easy it would be to convert them to be compatible with SQL Server and XML Reports. They are different beasts, though: XML Reports is a solution for a specific organisation (Thames Water I think); it is not an upgrade or rewrite of SQL Reports. As far as I know SQL Reports is no longer maintained by Stuart or anyone else, and unless someone (or a group of people) finds money in order to pay Dorset to add those features found in SQL Reports, yet missing in XML Reports then I fear we're out of luck. Features I'd like to see are: allow reporting on a rucksack, export to a text file, and a hierarchy view.

Perhaps it's something that can be tackled when the working group/user group/website gets off the ground? Regarding support, Hannah has indeed now left and Steve is on paternity leave (until the end of Jan, I'm told); John Morris is holding the fort until Steve's return, so now is probably quite a hectic time in JNCC Towers (at least in the Recorder department). smile

As an afterthought, can you not simply use Access to run your queries?

Charles

Re: Hot potato: XML versus SQL reports

A couple of potentially good ideas from your suggestion are messed up in our installation. Firstly I have no "Quick reports", use of this gives me an unhandled message error and as I say, I cannot get a peep out of JNCC (sent 30th December). Secondly your Access suggestion depends upon me having a linked Access database which I haven't got and cannot create due to absence of tools (or is that "know-how" again) here.
I'd got to grips with some fairly basic stuff in XML, this one picks out Location records of a single designation:

<?xml version="1.0" ?> 
<CustomReport  description="Locations designated in Site_Status as H16"> 
    
    <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)='Rutland verges')
        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>

Note the inclusion of the Location_key so that you can map it, column-width idea is a failure so far, map gives open circles only as there is no field for it, perhaps a false Year would do it since there's no obvious dates associated with Locations.
My old SQLs start getting a bit complicated after that, though. One very useful technique was the inclusion of a Location or group of taxa in the Rucksack, others depended on other queries (perhaps this is the where the disadvantages of Jet SQL are improved in T-SQL).
I'll keep hammering away at them when I get chance, in the meantime thanks for the offer of conversion, don't be surprised if an email bundle arrives in your inbox.

Darwyn Sumner

Re: Hot potato: XML versus SQL reports

How can I extract the Date part of the ENTRY_DATE in the new XML system?
In Access, I 'd used DateValue([TAXON_OCCURRENCE].[ENTRY_DATE]) to do this but I can't find an equivalent function so I  presue I have to create one? doe anyone have any ideas on this?

Dave Slade
Senior IT & Records Officer,  SEWBReC
13 St Andrews Crescent, Cardiff, CF10 3DB

Re: Hot potato: XML versus SQL reports

Regarding my 'Date only' question, the answer is that there does not appaer to be a function that extracts only the date part of a smalldatetime or datetime data type.

In order to get the results I wanted (which was to count the number of records entered by a person each day), I had to create a new function:

CREATE FUNCTION dbo.DJSDatePart(@EntryDate smalldatetime)
RETURNS smalldatetime
--
--    DESCRIPTION
--    Function to return dates to be grouped together ignoring the time element.
--   
--
--    PARAMETERS
--    NAME            DESCRIPTION
--    @EntryDate        The Entry Date
--
--
--    AUTHOR:    David Slade, SEWBReC
--    CREATED: 31/01/2006
--

AS
BEGIN

--****************************************************************************************************
Declare @date1 smalldatetime

set @date1= CONVERT(DATETIME,CONVERT(CHAR(8),@EntryDate,112))

RETURN @date1
--****************************************************************************************************

END

This essentially returns the EntryDate with the time set to midnight - thus enabling aggregate functions to work as I had them in the old SQL reports version.

Dave Slade
Senior IT & Records Officer,  SEWBReC
13 St Andrews Crescent, Cardiff, CF10 3DB