SQL Statements in Custom Reports

Moderators: Moderators, Tech Support

Post Reply
btvp
Posts: 4
Joined: Wed Jan 23, 2008 7:50 pm
Location: Toronto

SQL Statements in Custom Reports

Post by btvp »

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

Jeff
Program Development
Program Development
Posts: 1225
Joined: Fri Sep 05, 2003 11:43 am
Location: PowerChurch Software
Contact:

Post by Jeff »

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.

Eden Whitehead
Posts: 290
Joined: Tue Aug 21, 2007 5:59 pm
Location: Old Hickory Presbyterian Church Old Hickory, TN

Post by Eden Whitehead »

Bob,

Would you be willing to share your report when you have it finalized?

(I'm new to PC+ 10.4 and have not yet ventured into custom reports. I'm doing well to get Fund Accounting set up and going!)

If sharing is not possible, I understand.

Blessings
*Still* learning... and gratefully so!

btvp
Posts: 4
Joined: Wed Jan 23, 2008 7:50 pm
Location: Toronto

Post by btvp »

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

Eden Whitehead
Posts: 290
Joined: Tue Aug 21, 2007 5:59 pm
Location: Old Hickory Presbyterian Church Old Hickory, TN

Post by Eden Whitehead »

:D :D
Thanks!
:D :D
*Still* learning... and gratefully so!

btvp
Posts: 4
Joined: Wed Jan 23, 2008 7:50 pm
Location: Toronto

Post by btvp »

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

Jeff
Program Development
Program Development
Posts: 1225
Joined: Fri Sep 05, 2003 11:43 am
Location: PowerChurch Software
Contact:

Post by Jeff »

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.

btvp
Posts: 4
Joined: Wed Jan 23, 2008 7:50 pm
Location: Toronto

Post by btvp »

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.

Jeff
Program Development
Program Development
Posts: 1225
Joined: Fri Sep 05, 2003 11:43 am
Location: PowerChurch Software
Contact:

Post by Jeff »

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.

Post Reply