1. Select all new members gained since the beginning of the year, pull the mailing name for the report Simple right ? Use the personal status, and the gain date.
Then comes the hard part:
2. Check all the children (Directory Sequence >= 3) and see who was born after the turn of the century (also easy = birth date > 12/31/1999). Pull the name, date of birth, and sex of each child qualifying under the select statement
Since there are no inner joins available in the custom report builder, is there a way I can do this from within PC+ ??
And here's something to think about for an enhancement for a new version... see if there is a way straight SQL statements could be used in the report builder.
Jeff wrote:Are these two separate reports or does information from both selections need to be on one report?
That's what makes it so much fun ... I need to be able to put the data in one report. So I need to show the gain date of the member (Dir Seq #1) and pull the name and birthdate of the kids (Seq 3+).
With a SQL inner join, its not an issue ... however .. <sigh>
I think this will give you the report you are looking for. Your query needs to include both the children and the adults. So you query would look something like the following:
(Joined Date is greater than December 31, 2004 and
Personal Status Code begins with Member and
Directory Sequence is less than 3) or
(Directory Sequence is greater than 2 and
Birth Date is greater than December 31, 1999)
Be cautious of the parenthesis and join connectors. The two groups need to be joined with an 'OR'
This will give you both the children and the adults. Select your fields you need for the report including:
Household key Lastname
Household Key First Name
Address
Directory Sequence.
Sort your records by
Household Key Lastname
Household key first name
Address
Directory Sequence
This will make sure that families are grouped together with the parent record first.
Run your query. In the report layout you will need to add a group. Group on the following fields:
Household Key Last name
Household Key First Name
Address
(Do not include directory sequence here)
This will give you a separate group for each family
Next we need to create a couple of variables.
Create a variable called m.printit
Value to store: IIF(Pers_no < 3, .T., m.printit)
Initial Value: .F.
Reset at: PCPLUS.Lastname + PCPLUS.FirstName + PCPLUS.Address
This variable will let us know on the children records if we have seen their parents or not.
We need another variable to keep hold of the parents join date. That will look something like:
Name: m.parentjoin
Value to store: IIF( PCPLUS.pers_no<3, PCPLUS.date3, m.parentjoin)
Inital Value {}
Reset at: PCPLUS.Lastname + PCPLUS.FirstName + PCPLUS.Address
Add all the fields you want for the children in the detail band. For the parents join date enter "m.parentjoin" as the expression.
Next we need to edit the print when condition for each field to only show the individual child when we have seen their parents. to do so:
Double click the field. The report expression window opens.
Click the Print When button
Enter "m.printit" in the Print only when expression is true: text box.
Check the "Remove line if blank" option.
Repeat this for all fields.
I think that will do it. Here is the basic strategy. Include all adults and children that meet are two selections. Sort the output into familes making sure the adults are first in the group. In the report layout group by families so we can reset variables when a family group changes. Add a variable to keep track of seeing an adult in that family. Add another variable to keep ahold of the adult join date. Edit the print when for each field object to only print when the printit flag has been thrown.
Here is a troubleshooting tip. Before changing all the print when expressions, add a field to the detail band for the m.printit and preview the report to make sure that only the children you are expecting to print are getting the printit turned on.
Well .. there's a glitch ... and its something that I can't fix.
When I try to include the birth date, when I'm in report builder, it works fine. I can see the date when I do a print preview. I save everything, then try to come back in and run the report.
I then get an error saying that the report format is looking for a field that in not in the output set. Trouble is, I'm including the birth date fine, and when I build the report, it shows PCPLUS.born as a valid data item.
Coming back in however ... it gives the error message that it can't find PCPLUS.born. Sounds like a naming issue in the system.
I've got another problem where the mailing list profile name repeats for every child, but that's easy to fix. The data item problem is not.
On another note ... if you try to do a print preview, and there's an error in an expression, the system gives an error, prompts you to save your report, then exits PC+ completely. I suspect a Crystal Reports issue, but am not sure.
Remove the "pcplus." from before the expression before saving it. The pcplus is not really needed and there is code that opens the report and tries to remove it automatically. It sounds like for some reason it is not getting updated correctly.
On another note ... if you try to do a print preview, and there's an error in an expression, the system gives an error, prompts you to save your report, then exits PC+ completely. I suspect a Crystal Reports issue, but am not sure.
There might be an error in a variable. When I had this it didn't kick me out of the layout it just forced me back into design view. This isn't crystal reports, you are using the report writer built in to Visual FoxPro. VFP allows us to give end users access to the VFP report designer in the runtime. Crystal won't give us this functionality without paying them per person that uses PC+.
Jeff wrote:Remove the "pcplus." from before the expression before saving it. The pcplus is not really needed and there is code that opens the report and tries to remove it automatically. It sounds like for some reason it is not getting updated correctly.
On another note ... if you try to do a print preview, and there's an error in an expression, the system gives an error, prompts you to save your report, then exits PC+ completely. I suspect a Crystal Reports issue, but am not sure.
There might be an error in a variable. When I had this it didn't kick me out of the layout it just forced me back into design view. This isn't crystal reports, you are using the report writer built in to Visual FoxPro. VFP allows us to give end users access to the VFP report designer in the runtime. Crystal won't give us this functionality without paying them per person that uses PC+.
Well. ... I'm able to run the report fine. Still getting some phantom blank lines, but I can live with that for now. Any hints on how to find what is printing these ??
Had to add another flag in the system to let me know if children were found or not. It was printing the 'head of household' even if there were no kids.
All in all ... a pleasant experience ... I've been away from programming for a while, and this reminded me of a few techniques I had forgotten about.
