WISE Access Tool Article 8 Reporting

1. WISE Access ToolArticle 8 Reporting Yvonne Gordon-Walker

2. Introduction • Functionality • Concluding remarks

3. Introduction

4. Background • Tool builds on the Access tool developed to support Article 5 reporting. • Schema group recognised the need for a tool to help Member States prepare valid data files for submission • This has now been extended to support Article 8 reporting

5. Development and testing • Initial development • Testing: • DE,FR,FI,UK,NL,BE,IE,ES,SK,SI,SE,CZ,PL,IT: • All documentation and software provided on 12th October 2006. • All testing feedback by 3rd November. • Only testing access tool, not upload to ReportNet. • Some MS testing automatic generation of schema from own internal systems. • Feedback from testers used to improve tool (Nov/Dec 05) • Bug fixes • Functionality improvements • Schema changes

6. WISE data flow (all steps involve QA/QC) Data definition/ requirements Step 1: Compliance or SoE, voluntary (or through comitology) Submission Step 2: End-user tool, schema/DEM, XML/shape/GML, other tools (DD, glossary), help desk Development Database Step 3: central holding area (CDR), management of access rights Acceptance Step 4: Standardised manual or automatic tests, feedback to data submitters Production Databases (WISE nodes) Step 5: Final data storage Viewing, GIS visualisation Analysis Step 6b: Tools for compliance or SoE assessment, CCM2 Step 6a: Public or restricted viewing, map service, aggregation, statistics, GIS reference dataset

7. Relevant Documentation • Access Tool User Guide.doc • Access Tool Getting Started.doc • Access Article 8 Summary of forms.doc • Access Article 8 Tables.xls • Monitoring-reporting Guidance- V5.0.doc • Article 8 Schema Guide.doc • Monitoring.xsd • SurfaceWaterMonitoringStations.xsd • GroundWaterMonitoringStations.xsd • WFDCommon.xsd

8. New Document • Article 8 Workbook.doc • Pulls together relevant info from various documents • Takes you through each step. • Installation and setup • Filling in forms • Importing data • Minimum schema requirements • Generating XML • Does not include ReportNet

9. Download • Access tool, documents etc can be downloaded from ftp site • ftp location: ftp.wrcplc.co.uk • username: wfdsupport • password: wise

10. Access tool overview Local Remote • Interface for data entry • Creation of validated XML files ready for up-loading • HTML file for viewing data Data entry XML schema XML file HTML stylesheet HTML file

11. Access tool structure Front-end: interface WFD Reporting 2006.mdb Forms Queries System tables Modules Report Back-end: data storage WFD Reporting 2005_be.mdb Article 8 Reporting 2006_be.mdb Data tables

12. Front-End Database • WFD Reporting 2006.mdb • Contains all functionality – forms, queries, reports, modules and system tables which are integral with the functionality. • No data are stored in this file. • Contains all Article 5 functionality • Will be familiar to users of Article 5 tool

13. Back-end Databases • WFD Reporting 2005_be.mdb • same back-end as used with the original front-end WFD Reporting 2005.mdb. • can continue to use WFD Reporting 2005.mdb • Article 8Reporting 2006.mdb • Separate database for Article 8 data • Each database has its own set of data tables, stored in separate database files • there is no common data storage. This includes RBDs.

14. Installation and Set-Up • Full instructions in workbook (section 2) • Can set up on any number of PCs • No usernames or passwords required • Requirements: • Access 2000 or Access 2003 • Microsoft MSXML • Edit Set-Up to specify paths etc.

15. Set-Up • You must specify • Country code • Creator • Output file location • Schema and stylesheet locations • May be held locally BUT up to user to ensure latest versions

16. Functionality

17. Functionality • Data entry using forms (programme summary data) • Direct import of data (monitoring stations) • can be viewed using the forms • Validation of data • Creation of XML files • Creation of HTML report from XML

18. Data entry • Corresponds to data requirements identified in Reporting Sheets • Monitoring-reporting Guidance- V5.0.doc • XML schema is derived from data requirements outlined • Database design matches XML schema • Field names match element names • Field types match element types

19. Validation of data • Three levels of validation: • Point-of-entry • restrictions on what the user can enter • Form-level • test data against Access equivalent of the schema • XML generation • direct test against the XML schema • no provision for validating external XML files

20. Point-of-entry validation: Text Fields • Alphanumeric data. • Maximum length of the string, n, is determined by the schema • Included in the table definition • Need to escape XML tokens

21. Point-of-entry validation: Text Fields – XML tokens What you type in How XML interprets it < &lt; > &gt;  &apos;  &quot; & &amp; • Characters recognised by XML as instructions • Automatically ‘escaped’ during XML creation • ‘Escaped’ characters add to parsed length:

22. Data Entry – Caution! • All data entered or edited via the forms are saved automatically • A change that has not yet been saved can be undone by pressing the <Esc> keyboard key. • Use the to create a new record, otherwise existing will be overwritten

23. Direct import of data • For SW and GW monitoring stations and associated data • No user interface provided for data entry • Simple form provided for viewing direct-entry data as datasheets • Can edit, add to, and delete, existing records on this form • Form includes form-level validation • Use Access Article 8 Tables.xls to see how tables relate, mandatory fields etc • Use Access Article 8 Summary of forms.doc to see how forms and tables relate

24. Delete Options • Deletes data for the current record • Cannot be undone • Always asks for confirmation before deleting • Level of deletion depends on where selected • Eg if at RBD then all data relating to programme will be deleted • Delete records in direct entry but seleting record and pressing <delete> key

25. Form-level validation • Tests data for current RBD on current form against requirements of schema • Includes child tables where applicable • Should be done for every form before proceeding to XML file creation stage

26. XML Generation: Validation • XML validation is directly against the schema • Data that has passed form-level validation should pass XML validation • XML error messages may not be as helpful as form-level validation error messages • XML parsers will not ‘assume’ anything so parsing will stop at the first error. • need to fix error before XML validation will continue

27. XML Generation: Stylesheet • XML files difficult to read • Stylesheets convert XML files to a HTML report format • Issues: • Not complete but links set-up and available. • Need valid XML files to run

28. Example HTML output

29. Concluding remarks

30. Benefits • Consistent data structures, compliant with Article 8 • 3 levels of validation • Point-of-entry • Form-level • XML generation • Archive for future submissions • Database for internal use • Potential to develop alternative front-ends, stylesheets • Can be extended as new requirements come into force • Code changes to front-end database only

31. Possible developments • Address current size restriction of 2000 characters in memo fields • Provide better tools for direct entry upload and validation (check for hanging/loose records)

32. Schema changes • Access tool was designed around schema • Several changes to the schema were suggested by testers • Changes to schema affect back-end (database) and front-end design of Access tool • Better to agree any changes before roll-out of tool • Need to minimise database changes