Taxon Order
Recommended Taxon Name without Sub-genus
Event Date
Event Location Name
Sample Spatial Reference
Sample Altitude (None)
Obs Abundances (LC)
Obs Comment
Sample Recorders
Obs Determiner
Obs Record Type
Obs Confidential

And for the Polygon and Bounding box

Taxon group
Recommended Taxon Name without Sub-genus
Recommended Common Name
Event Date
Event Location Name
Sample Spatial Reference
Sample Altitude (None)
Obs Abundances (LC)
Obs Comment
Sample Recorders
Obs Determiner
Obs Record Type
All Designations - Short Names
Survey Name

In the Polygon and Bounding box reports I need to be able to edit the definitions (map sheet key or lat/long, taxon restrictions, date restrictions) within the code.

Thanks,

Murdo

I don't think there is a problem with the comment field - that was just a random string in the dummy record.

M.

I will fix the comment field. Can you let me know what order you want the report to come out in, the column order and the field names you would prefer and I will make the changes.  I can then look at the other reports.

And the Order is shown as Hymenoptera.

M.

I don't think I am too far off with the report. I have picked up the Taxon Sort order not the Taxonomic Order and this will be easy to fix, as will the Obs comments.

I have made some changes and can run the report in Windows 11 on SQl Server 2017. Can you give it a go please. (Not done obs comment yet). 

If  there is a solution to the original problem I fear it may be a long time coming. Not sure if the steering group would authorise the expenditure or if John van Breda will be willing to take on the work. If I can get you the reports you need then this will give me some breathing space to investigate.

Taxon_Occurrence_Key    CI0001660000FJCM
Taxon_Determination_Key    CI0001660000FJD7
Taxon_List_Item_Key    NBNORG0000072076
Sample_key    CI00016600006TCO
Taxon_order    000204010403030B020B0401290000000000
Taxon_Name    Andrena scotica
Event_date    01/02/1989
Event_Location    Inverness
Sample_Spatial_ref    NH1234
Abundance   
Obs_comment    iuoyijthugrytfedrw%0A
Sample_Recorder    Murdo Macdonald
Obs_determiner    Murdo Macdonald
Obs_Record_Type    None
Obs_Confidential    False
Sample_Altitude    100

All my required fields are there, but Taxon_order seems odd.  Why not Hymenoptera, and where does that string come from?

On the W11 machine the query runs, but although all the field names are shown, they are all empty.  That survey has a number of records of various taxa, but I don't think your code restricts the taxa returned.

I feel this is a problem that is not going to be easily solved, and you have spent a lot of time on it already.  I still have a working system on W10.  Obviously I would like it to work on the other machine, but I wonder if I should just uninstall R6 and SMS 2017, and start again with SMS 2014 or 2016.  Is that a potential solution for me?  Or what would you advise?

The problem is clearly not confined to me, so maybe needs a more comprehensive solution.

M.

It has not proved possible for me to use .wsd reports as a basis for xml reports so I have had to start again. Attached is my first stab at the first of your reports. Copy the file into the R6 reports folder. It should then run either from Run Reports in the HBRG menu or as a quick report by selecting a survey and right clicking on this to run a quick report also from the HBRG menu.

As it stands I know the field names are not the same of the old report and I Have done nothing about output order. At this stage I just need to know if the fields are the ones you want and that the data looks correct. We can get the field names and their order correct once the basic output is right. Also field you don't want can be stopped from displaying.

I can duplicate the Wizard output but should something different  help you then this could be done instead.

I only used taxon restrictions in the examples above to limit the output.

AAA Basic has no taxon restrictions, but refers to SURVEY_KEY 3Z.  That is in fact irrelevant, as I told you a few years ago.  The report chooses the item that is selected at the time of running (even if it is not a survey!).  I don't actually have a survey 3Z any longer, but as long as the appropriate survey is selected when the report is run all is well.

The other two sometimes require taxon restrictions (usually bat records for EIAs) as you will see from the comment lines in the code.

If you are able to suggest a solution or work-around it would be great.

I'm glad you were able to replicate the problem as I was starting to wonder if it was all down to my incompetence.

Murdo

If I can't find it quickly I will look to doing you an xml report which does the same thing. It looks like the report is being run on a selected species. Are there any other criteria which you change at run time ?

I had been comparing the behaviour of old and new versions of R6, and comparing the tables in the linked Access file, and could find no problem other than the failure to run properly the saved .wzd files.  Within the wizard itself no problem is apparent and all requested fields are returned and saved to Excel.

M.

I can now duplicate this on an SQl 2017 instance. It looks to be related to this version of SQL Server. I am investigating. Nothing obviosu so far. Doesn't seem to be picking up any of the Measurewments.

M.