1 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Adding field to Report Wizard

I have tried to add the sample spatial reference qualifier to be usable by the Report Wizard. As suggested elsewhere I added into the USABLE FIELD table

DSS00046GB000003    SAMPLE    SPATIAL_REFERENCE_QUALIFIER    Sample Spatial Reference Qualifier    Text    A    True    True    True    SAMPLE.SPATIAL_REFERENCE_QUALIFIER

but it doesn't seem to have done anything.

Any idea what I am doing wrong?

Gordon Barker
Biological Survey Data Manager
National Trust

2 Reply by Gordon Barker

  • Gordon Barker
  • Veteran
  • Offline
  • Registered: 15-05-2006
  • Posts: 344

Re: Adding field to Report Wizard

I think I have worked this out based on http://forums.nbn.org.uk/mw2/index.php?title=Report_Attributes

REPORT _ATTRIBUTE SQL set to #REPORT_OUTPUT.[Spatial reference qualifier] = SAMPLE.SPATIAL_REF_QUALIFIER

REORT JOIN SQL set to FROM #REPORT_OUTPUT left join SAMPLE ON #REPORT_OUTPUT.SAMPLE_KEY = SAMPLE.SAMPLE_KEY

Gordon Barker
Biological Survey Data Manager
National Trust

3 Reply by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 387

Re: Adding field to Report Wizard

Gordon,

Here's an example I used to create a "Sample 2km square" report attribute. This may help you in some way?

USE [NBNData]
GO

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 (
    'LC00033400000103'
    ,'Sample\Grid square'
    ,'SAMPLE'
    ,'Sample 2 km Square'
    ,'#REPORT_OUTPUT.[Sample 2 km Square] = dbo.LCRectifyGR(SAMPLE.SPATIAL_REF, SAMPLE.SPATIAL_REF_SYSTEM, 2)'
    ,'NBNSYS0000000007'
    ,'LC00033400000004'
    ,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 (
    'LC00033400000103'
    ,'LC00033400000103'
    ,'Sample 2 km Square'
    ,'Text'
    ,NULL
    ,'LC00033400000004'
    ,getdate()
    ,0
    )
Les Evans-Hill
Senior Data Officer
Butterfly Conservation, Butterflies for the New Millennium and National Moth Recording Scheme

nmrs's Website

4 Reply by nmrs

  • nmrs
  • Veteran
  • Offline
  • From: London
  • Registered: 22-11-2006
  • Posts: 387

Re: Adding field to Report Wizard

Here's another, maybe more appropriate?

USE [NBNData]
GO

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 (
    'LC00033400000101'
    ,'Sample'
    ,'SAMPLE'
    ,'Sample Spatial Precision'
    ,'#REPORT_OUTPUT.[Sample Spatial Precision] = CASE SAMPLE.SPATIAL_REF_SYSTEM WHEN ''OSGB'' THEN CASE LEN(SAMPLE.SPATIAL_REF) WHEN 4 THEN 10000 WHEN 5 THEN 2000 WHEN 6 THEN 1000 WHEN 8 THEN 100 WHEN 10 THEN 10 ELSE 1 END WHEN ''OSNI'' THEN CASE LEN(SAMPLE.SPATIAL_REF) WHEN 3 THEN 10000 WHEN 4 THEN 2000 WHEN 5 THEN 1000 WHEN 7 THEN 100 WHEN 9 THEN 10 ELSE 1 END END'
    ,'NBNSYS0000000007'
    ,'LC00033400000004'
    ,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 (
    'LC00033400000101'
    ,'LC00033400000101'
    ,'Sample Spatial Precision'
    ,'int'
    ,NULL
    ,'LC00033400000004'
    ,getdate()
    ,0
    )
Les Evans-Hill
Senior Data Officer
Butterfly Conservation, Butterflies for the New Millennium and National Moth Recording Scheme

nmrs's Website