Quote from Deleted user on April 8, 2019, 2:23 pm

Hello @CSSystems

Please upload a sample pub file to tinyupload.com & provide us with its link so that we can check it and understand your requirement better.

After querying the date of birth from the database & passing it to your routine, save it to a [variable] after that you can insert this variable in your Report Designer.

Quote from CSSystems on April 8, 2019, 3:54 pm

I can do this for one person but not a group of people who have different birthdays from the query unless I miss understand the actions of a query.

I am trying to avoid looping through the database and calculating the age of each person before I do a report.

Quote from Gaev on April 8, 2019, 4:29 pm

@CSSystems:

I want to calculate a person's age to incorporate in a report based on a query.
I am trying to avoid looping through the database and calculating the age of each person before I do a report.

1) Method 1

a) Add a numeric field (integer or decimal, depending on how you want to show the age) to your Database Table; keep it hidden in all your displays.

b) You do not have to "refresh all records" before running your Report; just apply the same Query to your Table and update the calculated Age in this (Age) field for the records returned from the Query.

Now, you just use the newly minted Age values in your Report that were refreshed in (b).

2) Method 2

This might be a little more complex.

a) Instead of a permanent field for Age, use dbpExecSQL to create the results in a temporary Results Table ... which would contain an additional "Calculated Column"

b) Then run the Report against this Results Table.

 

P.S. How many records in your Table, and how many (on average) per Query/Report ?

Quote from CSSystems on April 9, 2019, 1:59 am

Gaev,

Thanks for Method 1 and 2.  The average per query could be around 50 on a table of approx 400 active records.

The query is for birthdays of the month.  Using the date format mm/dd/yyyy, I query on the first 2 characters i.e. for April I query on birthdays beginning with 04.

Method 1

I can use the subroutine I developed for one record and scan the table to insert the age.

Method 2

I have used something similar in another program and may try this.

Both Methods are good suggestions.

Thank you again.

Quote from Gaev on April 9, 2019, 6:22 pm

@CSSystems:

The average per query could be around 50 on a table of approx 400 active records.

You should be able to loop through 50 records in no time at all.

Method 2
I have used something similar in another program and may try this.

You might try something like ...

SELECT CustomerName, DateOfBirth, OtherField1, OtherField2, etc, etc, 2019 - Val(Right(DateOfBirth, 4)) AS CurrentAge
FROM Customers
INTO BirthdayPeople
WHERE Left(DateOfBirth, 2) = "04";

a) I have not tried it myself; there may be slight syntax variations depending on Database being deployed

b) replace field and table names as appropriate

c) run Report against BirthdayPeople

... if this works, you can then dynamically compose this string using [neoVariables] ...

SELECT CustomerName, DateOfBirth, OtherField1, OtherField2, etc, etc, [thisYear] - Val(Right(DateOfBirth, 4)) AS CurrentAge
FROM Customers
INTO BirthdayPeople
WHERE Left(DateOfBirth, 2) = "[thisMonth]";