Jump to content
TNG Community
Figi4

What's your fav/most used report?

Recommended Posts

Figi4

Trying to think up some useful reports to add, and curious what your favorite and/or most used report is?

 

 

Share this post


Link to post
Share on other sites
Mark R

i have 3 reports, 1 is for all of the US Census, Locations and people, 2nd is for Outside of the us, 3rd is burial locations.

Share this post


Link to post
Share on other sites
manofmull
On 4/1/2020 at 10:15 PM, Figi4 said:

Trying to think up some useful reports to add, and curious what your favorite and/or most used report is?

 

 

I have these and looking for more!

1.     Age on Death     Age at time of death : oldest to youngest. Some results will be years out i.e. those using "bef" as part of the death date.  
2.     Age on Death 2     With "abt/bef/aft" dates removed
3.     Baptism & Birth Places     Individuals by place of baptism/birth (places in alphabetical order)
4.     Birth to Baptism     List of all person in the database with Birth and Baptism dates, and the number of days between each event (in descending order)
5.     Birthday Month     Birthdays by month (result is only a fifth of the database due to missing birth details)
6.     Birthdays     Birthdays in the current month
7.     Cemeteries / Burial Grounds     List of all cemeteries on site, and number of people buried at each
8.     Cremated     All persons known to be cremated
9.     Family / Child Relationships     Children who are Adopted, Fostered etc.
10.     Family Size     Parents with number of children and names
11.     Last Name Count     Surnames by number of times used : in ascending order
12.     Marriage Duration     Length of marriage (ordered by Years Married) where death date of both spouses is known, or when there has been a divorce with known date
13.     Marriage Duration 2     Length of marriage (ordered by Husband's Name) where death date of both spouses is known, or when there has been a divorce with known date
14.     Multiple Births     List of families with twins, triplets etc...
15.     Nicknames     Persons on site who have a nickname
16.     Occupations     Our ancestors' occupations
17.     Occupations by number     
18.     Orphans     People without a spouse, parents or children
19.     Place of Marriage     Places of site marriages in alphabetical order  
20.     Place of Marriage (surname)     Places of marriages ordered by surname (the first 152 entries or so, are people who don't have a surname as yet)
21.     Places     Places and events in place order
22.     Places and Events     Places and total individuals with associated events (click 'eventcount' to see events for place)
23.     Sibling Age Difference and Numbers     Greatest age difference between, and numbers of, siblings
24.     Surname Frequency     Frequency of surnames with earliest birth and most recent death occurrences in the database
25.     Surnames     All MF surnames
26.     Top Given Names     The most popular given names in descending order
27.     Zodiac AQUARIUS     Individuals born under astrological sign Aquarius : 21 Jan - 19 Feb  
28.     Zodiac ARIES     Individuals born under astrological sign Aries : 21 Mar - 20 Apr
29.     Zodiac CANCER     Individuals born under astrological sign Cancer : 22 Jun - 22 Jul
30.     Zodiac CAPRICORN     Individuals born under astrological sign Capricorn : 22 Dec - 20 Jan
31.     Zodiac GEMINI     Individuals born under astrological sign Gemini : 21 May - 20 Jun
32.     Zodiac LEO     Individuals born under astrological sign Leo : 23 Jul - 23 Aug
33.     Zodiac LIBRA     Individuals born under astrological sign Libra : 24 Sep - 23 Oct
34.     Zodiac PISCES     Individuals born under astrological sign Pisces : 20 Feb - 20 Mar
35.     Zodiac SAGITTARIUS     Individuals born under astrological sign Sagittarius : 23 Nov - 21 Dec
36.     Zodiac SCORPIO     Individuals born under astrological sign Scorpio : 24 Oct - 22 Nov
37.     Zodiac TAURUS     Individuals born under astrological sign Taurus : 21 Apr - 20 May
38.     Zodiac VIRGO     Individuals born under astrological sign Virgo : 24 Aug - 23 Sep
39.     Zodiacal Signs     All persons in the database with a birth date, and subsequent "starsign"

Share this post


Link to post
Share on other sites
Newfloridian

Three which have been / still are useful to me:

1. Deaths occurring between 1914 and 1918: 158 matches

2. Born after the death of a parent: 46 matches

3. Cause of death report: 434 matches

Alan

 

 

 

Share this post


Link to post
Share on other sites
KevyNC

I am working on some new ones.   I wanted a way to dynamically link user from my Wordpress site to the various charts etc available which are not always oblivious how to use and find to all users.   So I came up with a way to add a hyperlink in a column that allows the user to go directly to that page.  I have a version with numerous  links (columns) but due to the column name (not field )  it does not all fit on screen.  

If someone can advise on how to make an Alias that would fit (basically add <br> ) in column I probably could all of them to fit on one screen. I have seen reports where is puts large columns name on multiple lines but I am guess there are built-in to the report generator and may not apply to  manually entered SQL reports

The report can be viewed here View - Pedigree Chart

https://alveslaborefamily.net/tng/showreport.php?reportID=144

It add a column for "Pedigree view" for each person, also makes family ID link to family group page.  This also is depended on the user rights and if logged in. Since I have also had issues with having sync issues between FTM and Ancestry I did not want to have to work if I had to restore  tree and if ID #'s changed it would not cause issues because they are not hard coded. This example obvious is rather long as it is currently only filtered for individual that are linked to a branch. (Still over 1500)

The code looks like this:

SELECT concat('<a href="https://alveslaborefamily.net/tng/pedigreetext.php?tree=T1&parentset=0&generations=6&personID=', personID,  '">', personID, '</a>' ) AS Pedigree_Chart,lastname,firstname, sex, concat('<a href="https://alveslaborefamily.net/tng/getperson.php?tree=T1&personID=', personID,  '">', personID, '</a>' ) AS Individual_Page,birthdate as Birthdate,birthplace, deathdate as Deathdate, deathplace, YEAR(birthdatetr) AS YOB,YEAR(deathdatetr) AS YOD , concat('<a href="https://alveslaborefamily.net/tng/familygroup.php?tree=T1&familyID=', famc,  '">', famc, '</a>' ) AS FamilyGroupSheet,

branch FROM (tng_people ) 
WHERE (branch LIKE "b%")
ORDER BY lastname,firstname

Hopefully, this is helpful. 

Share this post


Link to post
Share on other sites
KevyNC

Okay I have adapted my report -- it had many columns that link directly to many of the various reports.  The query filters for only those associated with a branch and have surname Beginning with letter of alphabet.. The purpose of the reports is to help users find what's available and where they are located. All but 3 columns are hyperlinks

View - Indvidual Charts - SURNAME - A 

https://alveslaborefamily.net/tng/showreport.php?reportID=145

Here is the SQL:

SELECT 

concat('<a href="https://alveslaborefamily.net/tng/pedigreetext.php?tree=T1&parentset=0&generations=6&personID=', personID,  '">', "Pedigree Chart", '</a>' ) AS Pedigree_Chart,

concat('<a href="https://alveslaborefamily.net/tng/ahnentafel.php?tree=T1&parentset=0&generations=8&personID=', "Ahnentafel",  '">', "Ahnentafel", '</a>' ) AS Ahnentafel,
  
concat('<a href="https://alveslaborefamily.net/tng/pedigree.php?tree=T1&parentset=0&display=compact&generations=6&personID=', "Ancestor_Compact",  '">', "Ancestor Compact", '</a>' ) AS Ancestor_Compact,

concat('<a href="https://alveslaborefamily.net/tng/verticalchart.php?tree=T1&parentset=0&display=vertical&generations=6&personID=', personID,  '">', "Ancestor Vertical", '</a>' ) AS Ancestor_Vertical,

concat('<a href="https://alveslaborefamily.net/tng/fan.php?tree=T1&parentset=0&generations=6&personID=', personID,  '">', "Ancestor_Fan", '</a>' ) AS Ancestor_Fan,

concat('<a href="https://alveslaborefamily.net/tng/descend.php?tree=T1&display=compact&generations=6&personID=', 
personID,  '">', "Descendant Compact", '</a>' ) AS Descendant_Compact,

concat('<a href="https://alveslaborefamily.net/tng/descendtext.php?tree=T1&generations=12&personID=', personID,  '">', "Descendant_List", '</a>' ) AS Descendant_List,

concat('<a href="https://alveslaborefamily.net/tng/register.php?tree=T1&generations=12&personID=', personID,  '">', "Descendant_Register", '</a>' ) AS Descendant_Register,

concat('<a href="https://alveslaborefamily.net/tng/descendtables.php?tree=T1&generations=12&personID=', personID,  '">', "Descendant_Tables", '</a>' ) AS Descendant_Tables,

personID, birthdate as Birthdate, deathdate as Deathdate, 

concat('<a href="https://alveslaborefamily.net/tng/getperson.php?tree=T1&personID=', personid,'">', concat (lastname,", ", firstname), '</a>' )  as Individual_Page,

concat('<a href="https://alveslaborefamily.net/tng/familygroup.php?tree=T1&familyID=', famc,  '">', famc, '</a>' ) AS FamilyGroupSheet

FROM (tng_people ) 
WHERE (branch LIKE "b%" and lastname LIKE "A%")
ORDER BY lastname,firstname

 

Capture_View_Invidual_Report.JPG

Share this post


Link to post
Share on other sites
Figi4

Thank you all!  I'm adding a good deal of them to my reports now.

Does anyone know what the "Associations Between People" does exactly?

 

@KevyNC  I really like your idea, I'm going to add it to my to-do list!  Can I ask, how you added the people and media counters?

Share this post


Link to post
Share on other sites
KevyNC

@Figi4  I am working on some modification.  I have also added images using some basic html code in the  description field.  Which works quite nicely when viewing from the report app/view.

https://alveslaborefamily.net/tng/reports.php

What I am also working is a  filtered view of a group of reports using query such as:

Quote

SELECT 
concat('<a href="https://alveslaborefamily.net/tng/showreport.php?reportID=',
reportid, '">', reportname, '</a>' ) AS Report_Name
FROM tng_reports 
WHERE reportname LIKE "%Calendar /%"
ORDER BY reportname

The above pulls all reports with "Calendar /"  in the report name and display them with hyperlinks.  The main reason for this approach  is so I can add links in my WordPress site to link back to TNG.  However, the issue I have experienced is the images are not shown when viewing "ShowReport".  Maybe someone call explain if this is because it is only display text, or if the HTML code/tags are being stripped out before displaying.  (see https://alveslaborefamily.net/tng/showreport.php?reportID=287 ) for example of this.... note it is  displaying as expected from the main reports screen. 

The use of building hyperlinks in reports can be quite useful -- and has potential for many variations.

Kevin

Share this post


Link to post
Share on other sites
KevyNC
10 hours ago, Figi4 said:

Does anyone know what the "Associations Between People" does exactly?

I am not sure if that was a table of previous version(s) or was due a MOD, but it does not appear to be a regular table in the current DB.  Possibly similar to the cousin marriages table with that mod which was not part of the regular install. There were a couple relationships but I am not sure what mod has the table (or what previous versions of TNG if applicable)

Share this post


Link to post
Share on other sites
Ken Roy

The top of the report page on the TNG Wiki indicates that you must change the SQL query

Quote

This report failed in TNG v8.1.1, due to a different table name. To correct, change 'tng_ass' to 'tng_associations' in the below SQL statement.

to match the new table name since TNG v8.1

Share this post


Link to post
Share on other sites
Figi4

Yes, I saw that. I also found where to add the Associations. Im mostly curious what people use "associations" for in their tree.

Share this post


Link to post
Share on other sites
mcm

I have only three, though I keep meaning to create a few more.

1. Born in Ireland

2. Died in infancy or early childhood (died before the age of 5)

3. Died of tuberculosis (those whose cause of death was recorded as "tuberculosis," "pulmonary tuberculosis," or "consumption")

Share this post


Link to post
Share on other sites
Newfloridian

To your list No.3 "Died of tuberculosis" you should add the other commonly used Victorian diagnosis Phthisis

Alan

Share this post


Link to post
Share on other sites
mcm
16 hours ago, Newfloridian said:

To your list No.3 "Died of tuberculosis" you should add the other commonly used Victorian diagnosis Phthisis

Alan

Thank you!  Will add Phthisis to the "Died of tuberculosis" report...

And, inspired by this thread, I've added 6 more reports to my site:

  • Born at Ballymacegan, Lorrha, Tipperary
  • Born at Curraghafoil, Doon, Limerick
  • Born at Forkill, Armagh
  • Death by Drowning
  • Died in Childbirth
  • Killed by the Fall of a Tree [an occupational hazard for early settlers to Upper Canada!]

And I'm really not very imaginative, I'm afraid: all of my reports have to do with birth and death.

 

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×