1 Topic by HBRG Data Manager

  • HBRG Data Manager
  • Veteran
  • Offline
  • Registered: 03-06-2016
  • Posts: 85

Topic: A reporting problem

I thought I had transferred successfully to a new W11 machine – until I tried to run a report I have been using without trouble for years.  The script was copied over from the old W10 machine.  It works fine on the old, but an omission was immediately obvious when I tried on the new one.  It was the field Sample Altitude (None).

I have spent hours trying to find why, without success.  Creating reports sequentially in the wizard has established that all the 12 required fields can be added, displayed correctly in the wizard, and exported to Excel, and saved as .wzd.  However, when I run the saved report the Sample Altitude (None) field is not included.  The problem seems specific to that field, as a report including the other two altitude fields (Min and Max) only loses that one.  I have failed to find any relevant advice in Settings or the Help pages.

My R6 is 6.30.1.291.  The code that works correctly on the old machine follows.  The altitude field is included as
<Attribute visible="True" type="Measurement" key="NBNSYS0000000003" position="5" context="SAMPLE_DATA"/>

<?xml version="1.0"?>
<Report><SQL>SELECT  Distinct 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 Survey_Event ON Survey_Event.Survey_Event_Key = Sample.Survey_Event_Key )
LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key
WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE  USR.Name_Key = 'CI0001660000016Y') AND ((Survey.Survey_Key IN ('CI0001660000003Z')))</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="JNCCDEV100000004" position="11"/>
<Attribute visible="True" type="Standard" key="JNCCDEV100000014" position="6"/>
<Attribute visible="True" type="Measurement" key="NBNSYS0000000003" position="5" context="SAMPLE_DATA"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000019" position="2"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000024" position="3"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000027" position="4"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000049" position="8"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000053" position="7"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000063" position="9"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000064" position="10"/>
<Attribute visible="True" type="Standard" key="SRA0000400000026" position="0"/>
<Attribute visible="True" type="Standard" key="SRA0000400000029" position="1"/>
</Attributes></Report>

Any advice on solving this will be gratefully received.

Murdo

2 Reply by MikeWeideli

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

Re: A reporting problem

Hi

Do you have Management Studio intstalled ?

Mike

Mike Weideli

MikeWeideli's Website

3 Reply by HBRG Data Manager

  • HBRG Data Manager
  • Veteran
  • Offline
  • Registered: 03-06-2016
  • Posts: 85

Re: A reporting problem

Yes, I do, Mike.  That was a very quick reply.  Thanks.

M.

4 Reply by MikeWeideli

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

Re: A reporting problem

Hi

You can use the Profiler to actually see the sql that is running and you cam copy the sql statement and run it in Management Studio to see just what the problem is. The basic instructions are as follows but if you haven't used it before you may need to play about a bit. Let me know if you have any difficulties,

Open MS and connect.
Choose Tools and you should SQL server profiler as the top option.
The profiler should open
From the file menu select new trace
You may need to select the connection again
Leave this open
Start R6 and get to the point where you about to  run the report
Go back to the profiler and click on run (nothing else needs to be changed.
Run the report in R6
Go back o theProfiler and in the File menu choose Stop Trace.
You should now be able to scroll through the profiler results until you find the query.
You can copy this into a MS Query  and run it from there.which might tell you what is going wrong.

Let me know and also send me a copy of the query.

Mike Weideli

MikeWeideli's Website

5 Reply by MikeWeideli

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

Re: A reporting problem

Hi

I am not sure why this is happening and why it was working before and not now. I would guess that it is something to do with 'Select Distinct' which is a bit fussy about the data it is accessing. If can get the actual query from the profiler we should be able to find exactly what is going on and fix it.

Mike Weideli

MikeWeideli's Website

6 Reply by HBRG Data Manager

  • HBRG Data Manager
  • Veteran
  • Offline
  • Registered: 03-06-2016
  • Posts: 85

Re: A reporting problem

I am not sure if the attached is what you need, Mike.

The .wzd is the dummy report I ran, and the output from R6 is also there.

M.

Post's attachments

Files.zip 34.55 kb, 4 downloads since 2025-09-29 

You don't have the permssions to download the attachments of this post.

7 Reply by MikeWeideli

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

Re: A reporting problem

Please can you copy the following into an SQL Query and try running it. Open MS and choose New Query. Coipy the follwoing into the window. Make sure NBNData is shown in top left dropdown box (not master). Click on execute. Does this produce anything and does it look right.

SELECT Distinct Taxon_Occurrence.Taxon_Occurrence_Key AS Occurrence_Key, 'T' AS Type, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SURVEY_KEY, Convert(char(16), null)
collate SQL_Latin1_General_CP1_CI_AS as SURVEY_EVENT_KEY, Convert(char(16), null) collate SQL_Latin1_General_CP1_CI_AS as SAMPLE_KEY, Convert(char(16), null) collate
SQL_Latin1_General_CP1_CI_AS as LIST_ITEM_KEY , Convert(Integer, Null)  as [Event_Vague_Date_Start], Convert(Integer, Null)  as [Event_Vague_Date_End], Convert(varchar(2), Null) Collate
SQL_Latin1_general_CP1_CI_AS as [Event_Vague_Date_Type], Convert(varchar(100), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Event Location Name], Convert(varchar(8000), Null) Collate
SQL_Latin1_general_CP1_CI_AS as [Sample Recorders], Convert(varchar(40), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Sample_Spatial_Ref], Convert(varchar(4), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Sample_Spatial_Ref_System], Convert(float, Null)  as [Sample_Lat], Convert(float, Null)  as [Sample_Long], Convert(varchar(100), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Recommended Taxon Name without Sub-genus], Convert(varchar(100), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Taxon Order], Convert(varchar(8000), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Obs Abundances (LC)], Convert(char(1),Null) as [Obs Comment], Convert(bit, Null)  as [Obs Confidential], Convert(varchar(40), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Obs Record Type], Convert(varchar(70), Null) Collate SQL_Latin1_general_CP1_CI_AS as [Obs Determiner] into "##REPORT_OUTPUT" 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 and Taxon_Determination.Preferred = 1 ) LEFT JOIN Survey_Event ON Survey_Event.Survey_Event_Key = Sample.Survey_Event_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 Survey ON Survey.Survey_Key = Survey_Event.Survey_Key ) LEFT JOIN Index_Taxon_Name ITN2 ON ITN.Recommended_Taxon_List_Item_Key=ITN2.Recommended_Taxon_List_Item_Key ) LEFT JOIN Index_Taxon_Group ITG2 ON ITG2.Contained_List_Item_Key = ITN2.Taxon_List_Item_Key ) LEFT JOIN Index_Taxon_Name ITN4 ON ITN4.Taxon_List_Item_Key=ITG2.Taxon_List_Item_Key ) LEFT JOIN Index_Taxon_Name ITN5 ON ITN5.Recommended_Taxon_List_Item_Key = ITN4.Recommended_Taxon_List_Item_Key WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE USR.Name_Key = 'CI0001660000016Y') AND ITN5.Taxon_List_Item_Key IN ('NBNORG0000072621')

SELECT * FROM ##REPORT_OUTPUT

Mike Weideli

MikeWeideli's Website

8 Reply by MikeWeideli

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

Re: A reporting problem

Hi

Not sure from your original report what is actually wrong. Did you mean it isn't showing the sample altitude.

Mike Weideli

MikeWeideli's Website

9 Reply by HBRG Data Manager

  • HBRG Data Manager
  • Veteran
  • Offline
  • Registered: 03-06-2016
  • Posts: 85

Re: A reporting problem

Yes it was, Mike.  The other fields showed as expected.

I am about to try your code.

M.

10 Reply by HBRG Data Manager

  • HBRG Data Manager
  • Veteran
  • Offline
  • Registered: 03-06-2016
  • Posts: 85

Re: A reporting problem

The query worked with NULL in every cell, but I assume that is expected.

The fields exported are (altitude missing, but see below):
Occurrence_Key
Type
SURVEY_KEY
SURVEY_EVENT_KEY
SAMPLE_KEY
LIST_ITEM_KEY
Event_Vague_Date_Start
Event_Vague_Date_End
Event_Vague_Date_Type
Event Location Name
Sample Recorders
Sample_Spatial_Ref
Sample_Spatial_Ref_System
Sample_Lat
Sample_Long
Recommended Taxon Name without Sub-genus
Taxon Order
Obs Abundances (LC)
Obs Comment
Obs Confidential
Obs Record Type
Obs Determiner

Looking at your code, Sample Altitude (None) seems not to be included, so maybe try an edited version.

M.

11 Reply by MikeWeideli

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

Re: A reporting problem

Hi

This query should have produced valid data.

Mike Weideli

MikeWeideli's Website

12 Reply by HBRG Data Manager

  • HBRG Data Manager
  • Veteran
  • Offline
  • Registered: 03-06-2016
  • Posts: 85

Re: A reporting problem

So where do I go now, Mike?  The whole problem seems bizarre to me, as any .wzd I create containing the Sample Altitude (None) field works fine until I save it.  Then when I run that saved query the field (and only that one) is ignored.  And that applies only to the W11 setup.  In the original there is no problem.

I have just confirmed and put more detail on the problem with a completely new dummy report, identical on both machines.  I created a .wzd to return the taxon plus all the Sample Measurement fields, and on both machines all 23 fields displayed and exported to Excel correctly . 

I then saved the .wzd.  When I ran that from R6 on the old machine all was fine.  On the W11 machine only 8 fields were returned in this order:

Sample Altitude (Max)
Sample Altitude (Min)
Sample Substrate (Concrete)
Recommended Taxon Name without Sub-genus
Sample Substrate (Mud)
Sample Temperature (Soil)
Sample Substrate (Peat)
Sample Temperature (Air)

Normally I would only use Sample Altitude (None), hence it was not obvious earlier that the problem affected more than that field.

I noticed two discrepancies in the Wizard page where you select the fields.  On the new machine they are listed in alphabetical order, and also in the output Excel file.  In the old one there is no obvious order.  Significant?

Does this help in suggesting a remedy?

M.

13 Reply by MikeWeideli

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

Re: A reporting problem

Did you change the SQL server version. I will look in more detail and try to duplicate this over the weekend. I would like to find out why but it could be a lengthy process.


Further note :-

I can see that the query it generates is not picking up the Measurement. Why this is or why it happening on the new installation remains a mystery. The code hasn't changed in a long time. It might be that a new SQL version is not working in the same way as it did before. The fact that there has been a change in the order supports this. I will try and find a way to fix this but  I have never worked on the code which takes a saved report and generates the queries so not sure what I will be able to do. You could try changing the order the statements appear in the .wzd file (see below) . I will know more once I have looked at the code. It might be that the steering group need to pay John to take a look at this.

<?xml version="1.0"?>
<Report><SQL>SELECT  Distinct 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 Survey_Event ON Survey_Event.Survey_Event_Key = Sample.Survey_Event_Key )
LEFT JOIN Survey ON Survey.Survey_Key = Survey_Event.Survey_Key
WHERE Survey.Survey_Key NOT IN (SELECT Survey_Key FROM User_Survey_Restriction USR WHERE  USR.Name_Key = 'CI0001660000016Y') AND ((Survey.Survey_Key IN ('CI0001660000003Z')))</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="JNCCDEV100000004" position="11"/>
<Attribute visible="True" type="Standard" key="JNCCDEV100000014" position="6"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000019" position="2"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000024" position="3"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000027" position="4"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000049" position="8"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000053" position="7"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000063" position="9"/>
<Attribute visible="True" type="Standard" key="NBNSYS0000000064" position="10"/>
<Attribute visible="True" type="Standard" key="SRA0000400000026" position="0"/>
<Attribute visible="True" type="Standard" key="SRA0000400000029" position="1"/>
<Attribute visible="True" type="Measurement" key="NBNSYS0000000003" position="5" context="SAMPLE_DATA"/>
</Attributes></Report>

Mike Weideli

MikeWeideli's Website

14 Reply by MikeWeideli

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

Re: A reporting problem

Hi

I copied your report unchanged into a .wzd file and ran it under Recorder 6 V6.31 and SQL Server 2022. It produced all the columns. Please can you remind me which version of SQL Server you are using. I am not sure at the moment if an upgrade to V 6.31 will help or of the issue is to do with SQL Server.

Mike Weideli

MikeWeideli's Website

15 Reply by HBRG Data Manager

  • HBRG Data Manager
  • Veteran
  • Offline
  • Registered: 03-06-2016
  • Posts: 85

Re: A reporting problem

The old version was SQL 2014.  The new one is 2017.

I wonder if that is the source of the problem.

M.

16 Reply by MikeWeideli

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

Re: A reporting problem

SQL Server 2017 has been a very stable version. I have it installed somewhere so will see if I can duplicate the problem. In the meantime can you try adding another Measurement field.

Mike Weideli

MikeWeideli's Website

17 Reply by HBRG Data Manager

  • HBRG Data Manager
  • Veteran
  • Offline
  • Registered: 03-06-2016
  • Posts: 85

Re: A reporting problem

Could it be that the problem is the move from 2014 to 2017?  I was advised somewhere that would be OK, but I see from the other thread that two steps is still the rule.

M.

18 Reply by MikeWeideli

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

Re: A reporting problem

Going from 2014 to 2017 is fine. Only 2016 is between. If this was the problem it wouldn't connect. Until I can dupliacte the problem it is hard to investigate.

Mike Weideli

MikeWeideli's Website

19 Reply by MikeWeideli

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

Re: A reporting problem

Please could you try the attached.

Post's attachments

Test.wzd 1.79 kb, 1 downloads since 2025-10-07 

You don't have the permssions to download the attachments of this post.
Mike Weideli

MikeWeideli's Website