1 Reply by Gordon Barker

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

Re: Unable to use report

Can someone please point out the, presumably blindingly obvious, error that I cant see in the following report file, that prevents it from loading into Recorder?

  <?xml version="1.0" ?>
- <!--  GB Test 1
    Locations without gridsquares
     11 August 2011
     Menupath
     
        
   

  -->
- <CustomReport menupath="System reports\XML Examples" title="GB1-Loc grid" description="GB test 1 - locations without gridsquares">
  <SQL>SELECT L.LOCATION_KEY, LN.ITEM_NAME, LN.PREFERRED, COUNT(GS.SPATIAL_REF) AS Expr1, L.SPATIAL_REF FROM LOCATION L LEFT OUTER JOIN LOCATION_NAME LN ON L.LOCATION_KEY = LN.LOCATION_KEY LEFT OUTER JOIN GRID_SQUARE GS ON L.LOCATION_KEY = GS.LOCATION_KEY WHERE (LOCATION_NAME.PREFERRED = 1) GROUP BY L.LOCATION_KEY, LN.ITEM_NAME, LN.PREFERRED, L.SPATIAL_REF HAVING (COUNT(GS.SPATIAL_REF) = 0) ORDER BY ITEM_NAME</SQL>
  </CustomReport>

Gordon Barker
Biological Survey Data Manager
National Trust

2 Reply by MikeWeideli

  • MikeWeideli
  • Administrator
  • Offline
  • From: Littlefield Consultancy
  • Registered: 05-07-2006
  • Posts: 2,627

Re: Unable to use report

It doesn't show because you have left out the XML    <Where keytype="Default">

There is a minor error in the SQL which is fixed below. It then runs, but haven't looked to see if it is producing the right results.


<?xml version='1.0' ?>


<!-- JNCC  Example Report
     XML Report by Mike Weideli of Littlefield Consultancy for JNCC
     http://www.lfield.co.uk
     Version 2 17 April 2009
     Menupath
     
        
   
-->


<CustomReport menupath="System reports\XML Examples" title="GB1-Loc grid" description="GB test 1 - locations without gridsquares">

<SQL>

<Where keytype="Default">
SELECT L.LOCATION_KEY, LN.ITEM_NAME, LN.PREFERRED, COUNT(GS.SPATIAL_REF) AS Expr1, L.SPATIAL_REF FROM LOCATION L LEFT OUTER JOIN LOCATION_NAME LN ON

L.LOCATION_KEY = LN.LOCATION_KEY LEFT OUTER JOIN GRID_SQUARE GS ON L.LOCATION_KEY = GS.LOCATION_KEY WHERE (LN.PREFERRED = 1) GROUP BY L.LOCATION_KEY,

LN.ITEM_NAME, LN.PREFERRED, L.SPATIAL_REF HAVING (COUNT(GS.SPATIAL_REF) = 0) ORDER BY ITEM_NAME   


</Where>


</SQL>

</CustomReport>

Mike Weideli

MikeWeideli's Website

3 Reply by Gordon Barker

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

Re: Unable to use report

Thanks Mike,

Seems to give the right results.

Final version, in case anyone else finds it useful

<?xml version="1.0" ?>
- <!--  NTGB1 Locations without gridsquares
    Gordon Barker, National Trust
    gordon.barker@nationaltrust.org.uk
     12 August 2011
     Menupath
     
        
   

  -->
- <CustomReport menupath="Geographic Area/Location Reports\Location Hierarchy Reports" title="NTGB1 Locations without gridsquares" description="Returns all locations without any gridsquares allocated and allows selection within Location hierarchy">
- <SQL>
  <Where keytype="Default">SELECT L.LOCATION_KEY, LN.ITEM_NAME, LN.PREFERRED, COUNT(GS.SPATIAL_REF) AS Expr1, L.SPATIAL_REF FROM LOCATION L LEFT OUTER JOIN LOCATION_NAME LN ON L.LOCATION_KEY = LN.LOCATION_KEY LEFT OUTER JOIN GRID_SQUARE GS ON L.LOCATION_KEY = GS.LOCATION_KEY WHERE (LN.PREFERRED = 1) GROUP BY L.LOCATION_KEY, LN.ITEM_NAME, LN.PREFERRED, L.SPATIAL_REF HAVING (COUNT(GS.SPATIAL_REF) = 0) ORDER BY ITEM_NAME</Where>
  </SQL>
- <Columns>
  <Column name="Item_name" width="260" position="1" caption="Preferred Name" keycolumn="Location_key" />
  <Column name="Spatial_Ref" width="100" position="2" caption="Grid Reference" />
  <Column name="Location_key" visible="False" tablename="Location" />
  </Columns>
  </CustomReport>

Gordon Barker
Biological Survey Data Manager
National Trust