Welcome to the Birder's Diary Forum for Support And General Questions
Use the Support forum for all questions or issues.
Use the Wish List forum to leave your ideas for improving Birder’s Diary.
Setup your Forum photo and profile here.
I have a report that shows total species summarized by When Seen. But since When Seen includes the time, it summarizes by every single time. I want it to ignore the time.
4/19/2022 - 5
4/21/2022 7:21:48 AM - 1
4/21/2022 7:22:02 AM - 1
4/21/2022 7:22:05 AM - 1
4/21/2022 7:22:51 AM - 1
4/22/2022 7:53:38 AM - 1
4/22/2022 7:54:12 AM - 1
4/22/2022 7:57:25 AM - 1
4/22/2022 8:01:01 AM - 1
I want to see this:
4/21/2022 5
4/21/2022 4
And it if could do this it would be even better:
4/21/2022 Location 1 19
4/21/2022 Location 2 5
4/22/2022 Location 1 4
But attempts to show location give me something like this
4/21/2022 (time) 24
Location 1
Location 2
4/22/2022 (time) 4
Location 1
Hi David,
That ability does exist in v5.3.19.
As shown above, there are two different Sighting When Seen fields; one with time and one without.
That should do what you want.
Let me know.
Oh wow, I had not noticed that!
I fear the screen shot is too small to see.
It has only that one field, but shows the date and count. But the date is in Sun. 1-Jan-CCYY format. Not 01/01/CCYY. But it's getting there.
Now, if there was a way to show the breakdown by location with the date and location on the same line, that would be even better.
On further inspection, I see the report is not giving a correct count. As if it is getting only the first few records per date. I will have to look at it later
Hi David,
Is this a Sightings Report? Or Trip, or something else?
On the right side of the Output Columns pane, you select what is summed on the Group By columns.
You can stretch/expand the column widths by clicking on the header divider and dragging to see the full text.
The Report...
Group-By fields will always show on separate lines. E.g. you can't get Date-Only and Location on the same line.
You set the Date format from View | Options, General Tab
Let me know if there is anything else to resolve.
I sent you an example by email of how the counts are off, doing nothing but changing the selection date. The last date in the report will have the wrong count. I see you have responded.
It is a "life list" report because that is the only way I know to get it to count species.
I did not know about the View|Options function, but it seems to have no effect on the Sighting-Date Only "field". I tried removing the Day of the Week, but it still showed on the report.
By the way, the Option description says Grid Date Format, so it is not obvious that would also affect a report.
I tried adding the common name as you suggested. That shows which species are not included. Nothing jumps out as special about them. A life-list disabled UDF would do it, but that is not the case here.
Plus the fact that if I only select the one date, then the count is right. It is only when I have multiple dates that the count on the LAST date is wrong.
But when I extended the criteria by another date. The second to last date is wrong and the new last date is wrong too.
I sent you an example by email of how the counts are off, doing nothing but changing the selection date. The last date in the report will have the wrong count. I see you have responded.
It is a "life list" report because that is the only way I know to get it to count species.
Ok - thanks. Have been testing with Sightings reports up to this point.
However, even with now using the Life List report and your basic parameters of a Start Date, but no End Date; my final count is correct on the group by.
Sorry.. hit Enter too soon on the last reply... continuing here...
I did not know about the View|Options function, but it seems to have no effect on the Sighting-Date Only "field". I tried removing the Day of the Week, but it still showed on the report.
By the way, the Option description says Grid Date Format, so it is not obvious that would also affect a report.
My bad on this one. The format for Date-Only in reporting is fixed with the format you see.
I tried adding the common name as you suggested. That shows which species are not included. Nothing jumps out as special about them. A life-list disabled UDF would do it, but that is not the case here.
Not sure I understand this. I simply wanted you to include the "Common Name" in the report output, as I have done above in my previous reply, so that we can count the number of names it sees and confirm the computed count against actual value. If you can, please simply add "Common Name" to this report and show us the last couple pages pasted here as a screenshot.
Plus the fact that if I only select the one date, then the count is right. It is only when I have multiple dates that the count on the LAST date is wrong.
Oh - I think I know what is causing this confusion.
You are asking for a Lifer Report on your sightings; whether given a date range of one day or a date range of 30 days.
Here are the 2 screenshots that you sent to me via email...
Now, on it's own, 9-May, every species seen is a Lifer. You are asking for a Lifer report for that day as if the counting started and ended on that day.
When you set the range from 1-May to 9-May, only 17 of those sightings on 9-May are now lifers, as others seen prior to this date, in the range specified, supersede some of these from being Lifers.
So this is correct as the report was intended.
So, let's attack this from another angle...
What is the purpose of this report?
I may be able to tell you how to get what you want another way.
For example, if you want a count of Lifers seen from 1-May to 9-May and which ones they were, then
- Generate a Sightings report for that date range
- And select the "Highlight First Sightings" checkboxes that matter to you
Now you will get a report where the total lifer count is in the header...
And the actual lifer Sightings/species are highlighted per your selected highlighting rules.
But if you want a count of Lifers seen by Day and Location that only covers 1-May through 9-May, then you have to produce a Lifer Report for ALL TIME, and just look at the pages for these dates.
Ok. I think I am better understanding what you are after now, let's see if any of this helps you get what you want. Let me know.
When I added the common name, it allowed me to see that Pine Warbler was missing from the count for that day. I guess that was because I had seen a Pine Warbler on a previous day. I should have known the life list section of reports would work like that.
I am not looking for a lifer report. I just thought it would be great to see how many species I saw per day. One row on the report for each day. The sightings reports will total the number of individuals, but I don't know how to make it total species. But let me work with it some more.
Hi David,
Thanks for the info.
I don't believe I have anything in BD currently that reports the count of species seen per day.
However... there are a few undocumented features in BD that exist solely to account for these edge cases where only a few customers have a specific or want a specific functionality from BD. In this one feature, you can do almost anything you want with the BD data if you have some familiarity with SQL (structured query language); a database programming language.
Many versions ago, I built in an SQL Query Engine with Table/Field assist. For a few customers I have made this available with a simple switch placed on the Target line for the BD desktop shortcut.
e.g. "C:\Birder's Diary\BDV53.exe" /SQL
Once you add this switch, you can start BD and this feature will be available under the Features | Advanced | SQL Queries menu.
If you execute that menu command, you will get the following window.
It has the following features (perhaps not a complete list)
- Ability to enter SQL queries in the Query editor at the top-left, and display the result data in the grid at the bottom left.
- Ability to Save queries by name and reload.
- Ability to Export the data to any spreadsheet editor for advanced editing/charting/etc.
- A list of all Tables on the top-right; double-clicking on a table name will insert it into the editor. Single-clicking on a table name will display the columns available in that table.
- A list of table columns/fields in the right-middle pane. Double-clicking on a column name will insert it into the editor at the current edit point.
To get you started, here is the SQL query that will
Count the number of species seen per day, using the latest Clements taxonomic list in your database, at location 'Mexico', starting with 1-May-2022; ordered by the most recent day to the most distant day.
Select Q1.Day, Count(*) As NumSpecies
From (
Select DISTINCT DateValue(DateAndTime) As Day, GenusID, SpeciesID
From sightings s inner join classifications c on s.ThingID=c.ThingID
Where locationid IN
(Select DescendantID from LocationDescendants Where LocationID=(Select LocationID From Locations Where Name='Mexico'))
AND c.TaxonomyID IN (Select Top 1 TaxonomyID from TaxonomyList Where Name Like '*Clements*' Order By TaxonomyID DESC)
AND (SpeciesID > -1)
AND DateAndTime >= #05/01/2022#
Order By DateValue(DateAndTime)
) As Q1
Group By Q1.Day
Order By Q1.Day Desc
Here is the example output from my database.
Even if you don't know SQL, you might be able to figure out how to modify this query to fit your needs for
- Location
- Taxonomic List
- Begin/End Dates
You can save this copy and recall it anytime you want to use it, edit the particular parameters and get the data you want.
DISCLAIMER: This was built for special needs and for satisfying edge-case functionality such as this. Every attempt has been made to make sure that you cannot Insert nor Delete data using this interface, but there may be ways of doing this. This is not supported other than you can feel free to ask me to help with providing base SQL queries to meet this and other similar needs you may have. But I can't teach SQL.
Have fun and let me know if you have any questions.
Thanks for the tips. I could have a lot of fun with that. Sadly, My SQL skills are rudimentary. There is much in there I don't understand. However, I was about to execute the query you provided. That is good enough for my purposes now. Thank you.
As an aside, while the new Charts/Graphs feature that was introduced way back when doesn't have a species per day chart, it does have two fun charts that give the bigger picture:
- Lifers per Year, and
- Species per Year
Both accept multiple taxonomic lists and can display the data in a variety of chart formats. Here are two that I like and have displayed on my BD desktop all the time.
The first is Lifers per year for Birds and Butterflies in North America.
The second is a stacked bar chart for the number of species seen each year in NA across Birds, Butterflies, Reptiles, Plants, etc. In 2015, I had over 450 of all those combined.
fyi
