1 (edited by Darwyn Sumner 13-03-2006 17:07:38)

Topic: Reports problems

This is one I posted on the Recorder user group - I've had no response from that and I'm now more convinced that there is something wrong as I've double checked everything with Lizzy:

I am finding that the Report Wizard will only return a limited number of fields. I am losing a number of user-defined Sample Measurements that display properly when I select the attributes during a normal Wizard session. But when I save this wizard, after adding a template (which also displays the fields correctly during this run) and rerun it (right click on Location name, select the wizard name) it omits some fields from the template and the filter now does not show those fields. The template keeps checking out fine.

Is this because there is an upper limit or because there are user-defined formats that it does not like.
Does anyone else have a similar experience; can anyone offer an explanation or fix?

Darwyn
(Lizzy says "please")

Darwyn Sumner

Re: Reports problems

I posted a reply on the smartgroup, did you not get it?

Can you post the exact steps you're taking in order to trigger this problem? I'll see if I can reproduce.

BTW, I wouldn't trust the smartgroup if I were you, many messages on it are simply not getting through.

Charles

Re: Reports problems

I also posted a reply!  I think sorting out a better forum is becoming a priority!

Anyway, I have indeed managed to reproduce this and can confirm that its a bug, when you run a report generated with the Report Wizard not all the attributes are reselected.  I will raise this as an incident and get it fixed.

John van Breda
Biodiverse IT

4 (edited by Darwyn Sumner 24-04-2006 10:40:46)

Re: Reports problems

Thanks, Charles.
This is going to be a little complex to convey but here's the list of fields which I am trying to bung into my hedgerow report:

Biotope
 (6) Biotope Code
 Biotope Short Term
Event
 Event comment
 Event date
 (1) Event location
 (2) Event location name
Observation
 Obs surveyor's ref
Sample
 (4) Sample date
 (3) Sample location
 Sample location name
 Sample recorders
 Sample reference code
 Sample spatial reference
Sample measurements
 Sample woody species (Hedgerow)
 Sample management (Dense)
 Sample management (Gappy)
 Sample management (Overgrown)
 Sample management (Clipped)
 Sample management (Coppiced)
 Sample management (Laid)
 Sample width
 Sample height
 Sample feature (Ditch)
 Sample feature (Bank)
 Sample feature (Wall)
 Sample feature (Tree every 50m)
 Sample feature (10% dead wood)
 Sample feature (Parallel hedge)
 Sample gaps
 Sample shape (Box)
 Sample shape (Wide bottom)
 Sample shape (Wide top)
 Sample shape (A shaped)
 Sample verges
 Sample hedge standards
 Sample shape (Stag-headed)
 Sample length
Survey
 Survey type
Taxon
 (5) Taxon latin name
 Taxon name
 Taxon sort order
(is that 41 - the outdented ones are, of course parents in the "Select attributes" selection box)

Many of them are, of course, user-defined measurements (but they are pretty well consistent with hedgerow regulations)
It occurs to me that, since the wizards are saved as a form of xml, the following text version of it might give some clues:

<?xml version="1.0"?>
<Report><SQL>SELECT Taxon_Occurrence.Taxon_Occurrence_Key AS Occurrence_Key, 'T' AS Type 
FROM (((((Taxon_Occurrence LEFT JOIN Sample ON Taxon_Occurrence.sample_Key = Sample.Sample_Key ) LEFT JOIN Taxon_Determination ON Taxon_Determination.Taxon_Occurrence_Key = Taxon_Occurrence.Taxon_Occurrence_Key ) LEFT JOIN Location LSamp ON Sample.Location_Key = LSamp.Location_Key ) LEFT JOIN Index_Taxon_Name ITN ON (ITN.Taxon_List_Item_Key = Taxon_Determination.Taxon_List_Item_Key and ITN.System_Supplied_Data=1) ) LEFT JOIN Location_Name LNSamp ON LSamp.Location_Key = LNSamp.Location_Key ) LEFT JOIN Taxon_List_Item ON Taxon_List_Item.Taxon_List_Item_Key = ITN.Taxon_List_Item_Key 
WHERE (LNSamp.Preferred = 1 or LNSamp.Preferred is null) AND LSamp.Location_Key IN ('LERC000100000023','LERC000100000740','LERC000100000741','LERC000100000742') UNION SELECT Biotope_Occurrence.Biotope_Occurrence_Key AS Occurrence_Key, 'B' AS Type 
FROM ((((Biotope_Occurrence LEFT JOIN Sample ON Sample.Sample_Key = Biotope_Occurrence.Sample_Key ) LEFT JOIN Biotope_Determination ON Biotope_Determination.Biotope_Occurrence_Key = Biotope_Occurrence.Biotope_Occurrence_Key ) LEFT JOIN Location LSamp ON Sample.Location_Key = LSamp.Location_Key ) LEFT JOIN Biotope_List_Item ON Biotope_List_Item.Biotope_List_Item_Key = Biotope_Determination.Biotope_List_Item_Key ) LEFT JOIN Location_Name LNSamp ON LSamp.Location_Key = LNSamp.Location_Key 
WHERE (LNSamp.Preferred = 1 or LNSamp.Preferred is null) AND LSamp.Location_Key IN ('LERC000100000023','LERC000100000740','LERC000100000741','LERC000100000742')</SQL><additional_filters><confidential state="0"/><unchecked state="0"/><invalid state="0"/><zero_abundance state="0"/></additional_filters><Attributes><Attribute visible="True" type="Standard" key="NBNSYS0000000082" index="6"/><Attribute visible="True" type="Standard" key="NBNSYS0000000084"/><Attribute visible="True" type="Standard" key="NBNSYS0000000018"/><Attribute visible="True" type="Standard" key="NBNSYS0000000019"/><Attribute visible="True" type="Standard" key="NBNSYS0000000022" index="1"/><Attribute visible="True" type="Standard" key="NBNSYS0000000024" index="2"/><Attribute visible="True" type="Standard" key="NBNSYS0000000054"/><Attribute visible="True" type="Standard" key="NBNSYS0000000032" index="4"/><Attribute visible="True" type="Standard" key="NBNSYS0000000038" index="3"/><Attribute visible="True" type="Standard" key="NBNSYS0000000040"/><Attribute visible="True" type="Standard" key="NBNSYS0000000049"/><Attribute visible="True" type="Standard" key="NBNSYS0000000050"/><Attribute visible="True" type="Standard" key="NBNSYS0000000027"/><Attribute visible="True" type="Standard" key="NBNSYS0000000016"/><Attribute visible="True" type="Standard" key="NBNSYS0000000071" index="5"/><Attribute visible="True" type="Standard" key="NBNSYS0000000073"/><Attribute visible="True" type="Standard" key="NBNSYS0000000074"/><Attribute visible="True" type="Measurement" key="LERC000100000005" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC000100000012" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC000100000013" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC000100000014" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC000100000015" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC000100000016" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC000100000017" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC000100000018" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC000100000019" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001A" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001B" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001C" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001D" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001E" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001F" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001G" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001H" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001I" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001J" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001K" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001L" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001M" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="LERC00010000001T" context="SAMPLE_DATA"/><Attribute visible="True" type="Measurement" key="NBNSYS0000000002" context="SAMPLE_DATA"/></Attributes><Output><Template file="F:\LRC\Recorder6Server\Templates\Hedgerow.Tpl"/></Output></Report>

The LERC... keys above are evidently referring to our user defined sample measurements, I'm afraid that's not something I could easily reproduce through this site. Nor is that template or any underlying data.
It's encouraging that John has found a bug in this area, it might offer a solution.
John:
It might even fix the fact that, despite choosing it in the Wizard, the recursing into sub-sites doesn't work using the Quick Reports system. I've solved that by reattaching Events & Samples of the children locations to their parent and using the Event's Location Name to provide the second (child) Band in the report writer but I cannot help feel that this is an unsatisfactory solution.
1. You can test this restriction on the way that the hierarchy is forced to operate using Related Date: Occurrences which will return no data for an unattached parent but will work for all its children.
2. Reattach the children as I've outlined above and this system works the other way around - all occurrences returned for the parent and none for the children.
3. I find it peculiar that one is forced to breach sensible practise in this way just in order to deliver a service (output must take priority in a business context)
4. Possible solutions are a) an "include sub-sites" option on the Related Data button & b) a similar function built into Quick Reports
5. Since there seems to be a possibility that a fix might improve the situation I'm going to delay attacking all my Local Wildlife Sites with item 2. since it's resource-consuming so do let me know if your anticipated bug fix will address this and roughly when one could expect such a fix as this delay will get me into deeper trouble as the days march on.
Many thanks
Darwyn
P.S. sorry about the smartgroup site thing, I thought I'd better see if it was more widespread before bunging it on here because its a very tricky area to be sure one hasn't made a boo-boo

Darwyn Sumner

Re: Reports problems

Darwyn

I've fixed the problem reloading Report Wizard measurement attributes so that should be available in the next release due very soon.

As to making the Quick Reports facility and Related Data incorporate subsites for locations, both of these would be changes to functionality which could be proposed through the new Future Development page on www.recordersoftware.org.

John van Breda
Biodiverse IT

6 (edited by Darwyn 14-03-2006 19:01:07)

Re: Reports problems

That's what I call service !
As for some of the other stuff I guess I'll have to do some more work on scripts.
Charles has pointed me to Mike Weideli's downloadable files at http://www.lfield.co.uk/lcDownloads.html
recently.
No tools yet arrived at work so I'll be using my home setup again.
I've found the Access 2003 projects quite handy so far as it is very similar to the Access 8 stuff I'd got so familiar with.
The routine I've used up to this point is:
1. set up a query in an Access 2003 project which has all your Recorder tables linked in and check it works properly,
2. copy the SQL as text,
3. drop it between the <Where keytype="Default"> and </Where>  statements of another simple XML file (which you've also opened with a text editor)
4. tidy the thing up a bit and save it in Reports directory with an .xml extension
5. keep your fingers crossed when you run it from Recorder

The script stuff is going to be a little more complex.
Here goes ...

Darwyn

Re: Reports problems

Darwyn, can you not use the snappily titled Microsoft SQL Server Management Studio Express? It's free and very capable. In many ways it's better than Enterprise Manager + Query Browser (that come with MSSQL2000), although it's missing some of the more powerful features of those two.

Charles