Hello.
I'm trying to create a report showing the number of additions and deletions to the membership over the course of a given year. When I create the filter list for the report, I can easily make it return everyone who either joined or left in the given year. What I would like to include as well is the total number of members at the beginning of the year and the total number of members at the end of the year.
Can anyone tell me how to get these totals? I'd like to display the beginning of the year total at the top and the end of the year total at the bottom.
Thanks,
Bob
SQL Statements in Custom Reports
Moderators: Moderators, Tech Support
-
- Program Development
- Posts: 1225
- Joined: Fri Sep 05, 2003 11:43 am
- Location: PowerChurch Software
- Contact:
One way to accomplish this would be to have the filter go ahead and include all members with their join date. Order people by their join date. In the layout you would need to create a variable to count the number of members. i.e. m.members = m.members + 1. You can then suppress the members that joined prior to the date you want by using the "print when" and "remove line if blank" options.
-
- Posts: 290
- Joined: Tue Aug 21, 2007 5:59 pm
- Location: Old Hickory Presbyterian Church Old Hickory, TN
Eden,
Sure, I'd be happy to share it. Of course it will be specific to the way we have structured our files but it may well be that you could modify it to fit your data structure.
When it's done I will post here and then we can figure out how to share it. I've never done that before but I'm sure there is a way.
Bob
Sure, I'd be happy to share it. Of course it will be specific to the way we have structured our files but it may well be that you could modify it to fit your data structure.
When it's done I will post here and then we can figure out how to share it. I've never done that before but I'm sure there is a way.
Bob
-
- Posts: 290
- Joined: Tue Aug 21, 2007 5:59 pm
- Location: Old Hickory Presbyterian Church Old Hickory, TN
Hello again,
While writing my report I did insert a variable as suggested earlier. Now, when I try to open the report I get a yellow dialogue box which says "The report form last used with this query contains more fields than exist in the output table". My options are "Redo form" or "Cancel". If I select "Redo form" all of the work I have done (in this case over the last several hours) is lost and the form is reset to what it was when I began work on the report. Selecting "Cancel" is no help either because then I have the same trouble next time I try to run the report.
I know how to save my work through the database manager but the only "fix" is to remove the variable that I originally inserted. I don't want to do that because I need it. The report works as I want with that variable in.
Does anyone know how I can get it to accept the variable I added. The variable name by the way is "Subtotal"
Thanks
Bob
While writing my report I did insert a variable as suggested earlier. Now, when I try to open the report I get a yellow dialogue box which says "The report form last used with this query contains more fields than exist in the output table". My options are "Redo form" or "Cancel". If I select "Redo form" all of the work I have done (in this case over the last several hours) is lost and the form is reset to what it was when I began work on the report. Selecting "Cancel" is no help either because then I have the same trouble next time I try to run the report.
I know how to save my work through the database manager but the only "fix" is to remove the variable that I originally inserted. I don't want to do that because I need it. The report works as I want with that variable in.
Does anyone know how I can get it to accept the variable I added. The variable name by the way is "Subtotal"
Thanks
Bob
-
- Program Development
- Posts: 1225
- Joined: Fri Sep 05, 2003 11:43 am
- Location: PowerChurch Software
- Contact:
When you insert variables in the expression you need to preface them with a "m." so if you named the variable "subtotal", in the expression you would enter "m.subtotal".
To fix a report without resetting it you can:
Go to Utilities > Database Manager
Type: "Modify report ? "
Click Run Command
This will open a file selection dialog. Navigate to the Rpt_Cust folder. Change the view to detail and click the date modified column header to order the files by modification date. Your report should be last one modified. Select it and Click open.
You can make your changes to correct any problems. You won't be able to do a screen preview because the query has not been run.
The custom reports checks the report layout after running the query because it is afraid you removed a column that is used in the report layout. By prefacing variables with the notation "m." it tells that routine that this is a "memory variable" and not a field. If you have ever did some old time dBase programming this notation would be familiar to you.
To fix a report without resetting it you can:
Go to Utilities > Database Manager
Type: "Modify report ? "
Click Run Command
This will open a file selection dialog. Navigate to the Rpt_Cust folder. Change the view to detail and click the date modified column header to order the files by modification date. Your report should be last one modified. Select it and Click open.
You can make your changes to correct any problems. You won't be able to do a screen preview because the query has not been run.
The custom reports checks the report layout after running the query because it is afraid you removed a column that is used in the report layout. By prefacing variables with the notation "m." it tells that routine that this is a "memory variable" and not a field. If you have ever did some old time dBase programming this notation would be familiar to you.
Thanks for the reply, Jeff. That worked well.
I have another question - the report I'm writing is for membership additions and deletions in 2007, and thus there are numerous references to 2007 in the various conditions within the report. For example, I've used "print when" year(user2_desc)=2007. This works fine right now, but in future years, someone would have to change all of the references to 2007 to whatever year they'd like to run the report for. Is there a way to prompt a user for a year, and then have the conditions such as the one mentioned above reference the year the user specifies?
Thanks.
I have another question - the report I'm writing is for membership additions and deletions in 2007, and thus there are numerous references to 2007 in the various conditions within the report. For example, I've used "print when" year(user2_desc)=2007. This works fine right now, but in future years, someone would have to change all of the references to 2007 to whatever year they'd like to run the report for. Is there a way to prompt a user for a year, and then have the conditions such as the one mentioned above reference the year the user specifies?
Thanks.
-
- Program Development
- Posts: 1225
- Joined: Fri Sep 05, 2003 11:43 am
- Location: PowerChurch Software
- Contact:
Unfortunately not. You could set a report variable to 2007 and then reference that in all the print when statements. Create a report variable called m.printyear and set it to 2007. In the print whens you could then say year(user2_desc)=m.PrintYear. Make sure that m.printyear is first in the list of variables since the other variables refer to it.
If you really wanted to be fancy you could set m.printyear to YEAR(DATE())-1, but that would limit the report to always show the prior year. Or you could do YEAR(DATE())-IIF(MONTH(DATE())=1,1,0)
This will set the print year to the prior year when the system date is in January, otherwise the current year will be used.
If you really wanted to be fancy you could set m.printyear to YEAR(DATE())-1, but that would limit the report to always show the prior year. Or you could do YEAR(DATE())-IIF(MONTH(DATE())=1,1,0)
This will set the print year to the prior year when the system date is in January, otherwise the current year will be used.