KevMcCord

ShelbyUsers Yahoo Group

It was June 2001 when I started participating actively in the ShelbyUsers Yahoo Group.  I've made lots of friends over the years from that email discussion forum.

Earlier this year I noticed a decrease in activity on the group.  Things picked up this past couple of weeks, but the activity in November of 2008 was the lowest on record since I started in 2002.

ShelbyUsers Yahoo Group Posts By Month And Year

 

Then I noticed the even more active ChurchAdmin discussion forum also had a 6 year low in November of 2008.

ChurchAdmin Yahoo Group Posts By Month and Year 

I think all the other ways people communicate now are starting to eat into these forums.  Surely there are other explanations in the above anecdotal examples, but I for one communicate things on Facebook and Twitter and on this blog that used to go out to those other types of forums.

Giving By Purpose Last Week

Here's a simple query for Shelby v5 churches to show you totals and statistics about tax-deductible giving by purpose (designation) last week.  Makes a decent dashboard query.  With minor modifications it makes a good dataset for analysis.

Download Giving-By-Purpose-By-Week

Multi Year Balances

Sometimes I just need to see how my General Ledger balances are flowing from one year to the next.  So I wrote a query to show me balances in each year over the entire time we have used the Shelby v5 General Ledger.

Nothing I had or Shelby had provided a look at multiple years.  This query works best as deployed to a pivot table.  Each row shows the beginning balance, current year activity, and ending balance for a single balance sheet account for a specific year.  The Pivot table can put multiple years on a single line side by side.

Among other things you can see how account names are changing from year to year, you can also check your ending balances against your opening balances very quickly.  Of course, being able to see all years is also helpful.  My issue today was with a new account midway through last year that had an opening balance inserted rather than added via Journal Entry.

 The query below is free for other Shelby v5 General Ledger users.

Download Multi-Year-Balance-Sheet

Stephen's Basic Web Interface

I've visited and consulted with at least 100 churches that use Shelby v5 over the years.  I'm no longer surprised to find out that many of them use some version of Stephen's Basic Web Interface.  Many have modified it to suit their needs and loaded it on their intranet.  Oasis Church deployed this tool as part of our SharePoint Intranet for years.  We've since found a better, more robust solution (Stephen built it too).

The Basic Web Interface was a simple, classic ASP tool that Stephen developed and released for free many years ago.  Recently some folks on the ShelbyUsers Yahoo Group were asking about a way to access contact information from the Shelby database outside of Shelby.  Of course, Stephen's tool came up as the immediate suggestion.

Long story short, the website that hosted the tool was taken down so the tool wasn't easily available.  Stephen put it up on his new site for those who still might find value in this tried and true way to access your Shelby contacts and groups over the Intranet. 

I was reminded again of the value Stephen has created for churches without expecting anything in return.  It is one of many reasons I'm glad we work together as part of Think Ministry Inc.

Free Report: Apartment Dwellers

How much of your church lives in apartments?  This code can help you find out if you use Shelby v5.

Download apartment_dwellers.txt

As usual, please enter any questions as comments on this post.  This report is free of charge for internal use by churches.  It is not in any way endorsed by Shelby Inc.

Free Report: Tithe Sermon Facts

Recent news reports confirm for those of us who work in a church what we already know. 

Most people don't tithe.  We may not agree on the best approach for addressing this reality, but whatever we think about the topic of money it will probably come up.  Your pastor is in good company if he does the occasional sermon on biblical stewardship.

So, I'm a facts guy.  We know pastors like a few numbers, but we also know they can play fast and loose with them.  Ever heard the term pastorally speaking?

So, here is a cool report for the Shelby Contributions users.  It is actually like 10 cool reports all bunched up together each trying to answer just one of your pastor's questions about giving. 

This one you can't just copy and paste!  At the top of the code You have to set:

• The calendar year you want to study (optional, looks at last year by default)
• The minimum threshold for what you think a "tithe" might be in your church.  Say $4,000 on an average household income of $40,000.
• The Shelby Contributions purpose code you want to study.

However, you will get answers to these cool questions:

• Total Giving (for that purpose that year)
• Giving By People Excluding Loose Plate AND Businesses
• How Many People Gave?
• How Many Families Gave?
• Average Giving Per Family?
• Estimated Number of Families Tithing
• Avg Family Giving By Tenure - 0 Years (how does how long they've been giving impact their average?)
• Avg Family Giving By Tenure - 1 Years
• Avg Family Giving By Tenure - 2 Years
• Avg Family Giving By Tenure - 3 Years
• Avg Family Giving By Frequency - a. 1 Gift (how does how often they give impact their average)
• Avg Family Giving By Frequency - b. 1 - 5 Gifts
• Avg Family Giving By Frequency - c. 6-11 Gifts
• Avg Family Giving By Frequency - d. 12-23 Gifts
• Avg Family Giving By Frequency - e. 24 Or More Gifts
• Giving By Top 20% of Families (SQL Server 2005 version only)
• Giving By Top 20% of ALL Giving Units (SQL Server 2005 version only)

Your report won't look nice unless you can format it somehow.  I used a report designer, but you can use Excel by right clicking on the data and exporting it there.

Please note that this report only looks at people who give recordable gifts.  It assumes your loose plate offering goes to a fake organization record not a fake Individual record.  It can't know anything about those who give loose plate offerings or about those who don't give at all.  That means it doesn't average in non givers and annonymous givers who would bring averages down. It also may not include some of your biggest donors who give through foundations or businesses that might bring the averages up.  It certainly will not include giving you didn't put through ShelbyContributions like gifts of stock.

This report does not show any names or indiviudal giving info, but it does require rights to the Contributions tables to execute from ShelbyQUERY

Here is the code.  There are two versions one of which may not work for you!

Download Tithe-Sermon-Questions-SQL-Server-2005.txt (SQL Server 2005 or better required)

Download Tithe-Sermon-Questions.txt (Any SQL Server/MSDE that runs Shelby v5 should be fine)

Disclaimer:  This is the most complex query I've shared to date.  There is at least 3 hours of time invested here.  It may not work for you.  Even if it does, it could cause you some frustration if you are new to this.  Post questions as comments here.  Please note this only handles a single purpose at a time right now and it probably should be your main "general fund" purpose code.  if you have multiple general fund codes that's a major modificaiton to the query.  As always I am not affiliated with Shelby Inc and they have not approved this report.  I do not guarantee its accuracy in any way for your data.

Free Report: Employee Anniversaries

For those who use ShelbyPayroll to pay their staff, this report will allow you to see whose work anniversaries are next month.   This can be deployed to someone's ShelbyDashboard.  Even better is to set it up as a subscription report in SQL Server Report Server.

Download Employee-Anniversaries-Next-Month.txt

As usual, please enter any questions as comments on this post.  This report is free of charge for internal use by churches.  It is not in any way endorsed by Shelby Inc.

Giving Summary Report

Those who use Shelby v5 to track giving by the congregation understand the word "purpose" refers to the designation the donor indicated for all or part of the donation.  Purposes include things like Missions or General Fund.

Download Giving-By-Purpose-By-Sunday.txt

This query will summarize giving by purpose between two dates for a single company as recorded in ShelbyContributions.  It is helpful to run this query twice to compare the results from two different periods with an equal number of giving opportunities (Sundays).  The query will list many stasticts about giving to that purpose during the period in question including:

• The Purpose Code & Name
• Total Giving To that Purpose
• Average Giving Per Sunday
• Average Giving Per Giver
• Average Gift

Please drop me a comment on this blog if you use the report!

Those striving to learn SQL may like to see the algorithm that I wrote to find the number of Sundays between two dates.  I think it works, but I did limited testing ;-).

Upon request I'll gladly port it over to Shelby Donors & Gifts.

Please note that this report is not a guaranteed to reflect all giving.  In fact, it is intentionally limited to deductible, cash or cash equivalent giving. Instructions are in the query itself and should be read carefully.  I'll only answer questions entered as comments on this blog post and I may not answer all of those.

As alwasy please keep in mind that I am not affiliated with Shelby Inc so don't bug them with questions about this report.  Copy the code, paste it into ShelbyQUERY, and click "Load Grid".  If it works, enjoy!

Weekly Entry Report - No Financials

In my last free report I promised a variation for churches that do not own the Shelby Financials if someone asked.  We'll, someone did...

Download Weekly-Entry-Report-No-Financials.txt

I'm thinking of asking those who try a report then go on to use the report to leave a comment on the blog post as a way of letting me know they are using the reports.  I'm not asking yet, just thinking about it.  If you do use one of these reports a comment is welcome as it will encourage me to keep doing this and it may encourage someone else to give it a try.

Weekly Data Entry Report

Dear Reader-Who-Works-With-Shelby v5,

This free report is yours to try, but it may not work for your church!  This report summarizes how much data entry you are doing each week as a staff.  It can be used to make a case for the need for additional staff or expanded hours.

1. How many Gifts did we enter each week?
2. How many People did we enter each week?
3. How many profiles did we add to people?
4. How many people did we add to groups?
5. How many Life Events did we enter?
6. How many AP checks did we cut?
7. How many Payroll Checks did we cut?

You can set a date range, but the query will show a line for each week since 1/1/2007 if you don't change the values at the top of the code.  The code can be copied from the following link and pasted into ShelbyQUERY.

Download Weekly-Entry-Report.txt

The query as written requires that you have the Payroll and Accounts Payable modules in addition to Membership and Contributions.  The user executing the code has to have rights to those moduels. If you don't have those modules you won't have those tables and the query will error out.

The query also assumes your staff is careful to add dates when dates are need but not required.  This is true for Profiles and Group Enrollment.  Other items require dates or create them automatically.

If asked, I will remove the AP and PR tables and post a version without those tables, but that's as far as I commit to go.

Enjoy!

Note:  I do not work for Shelby Inc and the results of this query should not be relied upon as more than an indicator that may or may not reflect all that can be known about your data.