1

Re: Eastings and Northings in XML Reports

With refrenece to the post at http://forums.nbn.org.uk/viewtopic.php?id=1890

Below is the SQL needed to add Easting and Northings to the Report Wizard.   If you do try it  please  let me know how you get on. If it is OK they  can be added as a system supplied field to the next release.


USE NBNData
INSERT INTO REPORT_ATTRIBUTE (REPORT_ATTRIBUTE_KEY,    ITEM_GROUP,    SOURCE_TABLE,    ITEM_NAME,    ATTRIBUTE_SQL,    REPORT_JOIN_KEY, ENTERED_BY,    ENTRY_DATE,    SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000102',    'Sample',    'SAMPLE',    'Sample Eastings SW Corner',    '#REPORT_OUTPUT.[Sample Eastings SW Corner] =dbo.LCReturnEastingsV2(SAMPLE.SPATIAL_REF,SAMPLE.SPATIAL_REF_SYSTEM,0)',    'NBNSYS0000000007', 'LC00000100000001' , Getdate(),0)
INSERT INTO REPORT_ATTRIBUTE (REPORT_ATTRIBUTE_KEY,    ITEM_GROUP,    SOURCE_TABLE,    ITEM_NAME,    ATTRIBUTE_SQL,    REPORT_JOIN_KEY, ENTERED_BY,    ENTRY_DATE,    SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000103',    'Sample',    'SAMPLE',    'Sample Northings SW Corner',    '#REPORT_OUTPUT.[Sample Northings SW Corner] =dbo.LCReturnNorthingsV2(SAMPLE.SPATIAL_REF,SAMPLE.SPATIAL_REF_SYSTEM,0)',    'NBNSYS0000000007', 'LC00000100000001' , Getdate(),0)
INSERT INTO REPORT_ATTRIBUTE (REPORT_ATTRIBUTE_KEY,    ITEM_GROUP,    SOURCE_TABLE,    ITEM_NAME,    ATTRIBUTE_SQL,    REPORT_JOIN_KEY, ENTERED_BY,    ENTRY_DATE,    SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000104',    'Sample',    'SAMPLE',    'Sample Eastings Centre',    '#REPORT_OUTPUT.[Sample Eastings Centre] =dbo.LCReturnEastingsV2(SAMPLE.SPATIAL_REF,SAMPLE.SPATIAL_REF_SYSTEM,1)',    'NBNSYS0000000007', 'LC00000100000001' , Getdate(),0)
INSERT INTO REPORT_ATTRIBUTE (REPORT_ATTRIBUTE_KEY,    ITEM_GROUP,    SOURCE_TABLE,    ITEM_NAME,    ATTRIBUTE_SQL,    REPORT_JOIN_KEY, ENTERED_BY,    ENTRY_DATE,    SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000105',    'Sample',    'SAMPLE',    'Sample Northings Centre',    '#REPORT_OUTPUT.[Sample Northings Centre] =dbo.LCReturnNorthingsV2(SAMPLE.SPATIAL_REF,SAMPLE.SPATIAL_REF_SYSTEM,1)',    'NBNSYS0000000007', 'LC00000100000001' , Getdate(),0)





INSERT INTO REPORT_FIELD (REPORT_FIELD_KEY,    REPORT_ATTRIBUTE_KEY,    FIELD_ITEM_NAME,    FIELD_TYPE,    FIELD_SIZE,    ENTERED_BY,    ENTRY_DATE,SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000102',    'LC00000100000102',    'Sample Eastings SW Corner',    'integer', Null, 'LC00000100000001',getdate()    ,0)

INSERT INTO REPORT_FIELD (REPORT_FIELD_KEY,    REPORT_ATTRIBUTE_KEY,    FIELD_ITEM_NAME,    FIELD_TYPE,    FIELD_SIZE,    ENTERED_BY,    ENTRY_DATE,SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000103',    'LC00000100000103',    'Sample Northings SW Corner',    'integer', Null, 'LC00000100000001',getdate()    ,0)

INSERT INTO REPORT_FIELD (REPORT_FIELD_KEY,    REPORT_ATTRIBUTE_KEY,    FIELD_ITEM_NAME,    FIELD_TYPE,    FIELD_SIZE,    ENTERED_BY,    ENTRY_DATE,SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000104',    'LC00000100000104',    'Sample Eastings Centre',    'integer', Null, 'LC00000100000001',getdate()    ,0)

INSERT INTO REPORT_FIELD (REPORT_FIELD_KEY,    REPORT_ATTRIBUTE_KEY,    FIELD_ITEM_NAME,    FIELD_TYPE,    FIELD_SIZE,    ENTERED_BY,    ENTRY_DATE,SYSTEM_SUPPLIED_DATA)
VALUES ('LC00000100000105',    'LC00000100000105',    'Sample Northings Centre',    'integer', Null, 'LC00000100000001',getdate()    ,0)

Mike Weideli

2

Re: Eastings and Northings in XML Reports

Hi Mike,

I'll give this a go now...

Les Evans-Hill
Senior Data Officer, National Moth Recording Scheme

3

Re: Eastings and Northings in XML Reports

Hi Mike,

Initial tests show this works a treat.

Thanks indeed!

Les Evans-Hill
Senior Data Officer, National Moth Recording Scheme