Issue: September 2005

 

  

 

In This Issue: 

 

Tip 1: The Attendance To-Date Report
Tip 2: The Cash Flow/Balance List Report
Tip 3: Removing a Day's Giving in Contributions
Tip 4: Pushing SQL Queries to the Dashboard
New Feature: Project Budgeting 

Thanks for Asking

 

Lisa from Colorado asks,

"There are a few accounts in my account structure that I no longer use and wish to delete. I am setting up a new fiscal year and want to know if I can remove the account now. I have been told three different answers to this question, which are not to delete the account for two fiscal years, not to delete the account for one whole fiscal year, and that I can delete it in the new fiscal year as long as it is not being used. Which of these answers is correct?"

 

Lisa, actually all three are correct, depending on your reporting needs. If you or anyone in your organization uses the Preliminary Budget Report (which relies on data from the current fiscal year, last year, and the year previous to last year), then you should retain three complete years involving the budget setting processes. Several reports allow you to compare last year's activity to this year's. If you use any of these reports, then you should keep the account for two complete fiscal years. If the reports your organization use only reflect activity for the current year, then feel free to remove the account with the creation of the new fiscal year.

 

Lisa, thanks for asking.

 

Have a Shelby v5 question you would like to see answered in this column? 
Send E-mail to the Editor. 

SSTips Newsletter is published exclusively for interested users of Shelby v5 Software and available only by paid subscription.

To Unsubscribe (cancel your paid subscription), send reply with "Cancel" in subject line.

SSTips Publishing is a division of Shelby Systems, Inc.

Tip 1

     The Attendance To-Date Report

Overview:  The Attendance To-Date report is useful when comparing the attendance of multiple user-defined date ranges, or "terms". For example, it can be used to compare the attendance history of your Sunday School for this quarter compared to last quarter, for this month compared to last month, for this year compared to last year, etc. Up to four terms can be selected for comparison. This SSTip will demonstrate how to use the Attendance To-Date report, and show you one new option that is being added to the report with the fall v5 CD release.

 

Approach: Start the Attendance To-Date report. Choose date ranges for one, two, three, or four terms. Choose your other report options and execute the report.

 

Use the Following Steps to Run the Attendance To-Date Report

  1. Start Shelby v5.

  2. Locate and click on the Attendance link.

  3. Choose the organization you wish to run the Attendance To-Date report for by selecting it from the drop down menu on the Home Base screen.

  4. Click Reports.

  5. Click on the To-Date report.

  6. (Optional) If you wish to run the report for just one department, type in the department ID in the first ID field (or choose the department by clicking the Pick-box). Likewise, if you wish to run the report for only one class, type or insert the class ID into the second ID field.

  7. Choose a "from" and "to" date in the Term 1 From and To fields for the first date range.

     

  1. Choose a "from" and "to" date in the Term 2 From and To fields for the second date range for comparison.

  2. (Optional) Choose a "from" and "to" date in the Term 3 From and To fields for the third date range for comparison.

  3. (Optional) Choose a "from" and "to" date in the Term 4 From and To fields for the fourth date range for comparison.

  4. Choose additional options for the Birthdate, Which Address, Which Phone#, and Report Name fields.

  5. Place a checkmark by the Print Attendance Info for Previous Groups option in the Make Selections area.

Checking the Print Attendance Info for Previous Groups checkbox will include the attendance history from previous groups in that organization if the person changed groups or was promoted during the selected date range or across terms.

 

Unchecking this checkbox will cause the report to include attendance history for the currently enrolled group only .

 

For example, John was enrolled in the ninth grade boys' choir for the first half of 2005, and was promoted to tenth grade boys' choir in June of 2005. An Attendance To-Date report was run in September with the Print Attendance Info for Previous Groups checkbox checked so that all of John's history from January to September showed on the report. If the same report were run again with the checkbox unchecked, then John's history from only July to September would show on the report.

 

  1. Choose any other additional options in the Make Selection area that you wish to include on the report.

  2. Click OK to run the report.

 

 

If one term in the selected date range has more meeting dates than another, then the total number of meeting dates that appear on the report will be equal to the term that has the most number of meeting dates. In the picture above, we see that Term 1, May, has four meeting dates where Term 2, July, has five. Thus, the total number of meeting dates that will appear on the report is five.
 

If you are still running a version prior to the fall v5 CD release, make sure that the term with the highest number of meeting dates is used for Term 1. Term lengths, measured in the number of meeting dates, will truncate to match the number of meeting dates for Term 1 in these older versions.

***************************************************************************

Tip 2

     The Cash Flow/Balance List Report
 

Overview:  Starting with Shelby v5.4.2062, the Balance List in Special Reports can be used to create complex cash flow reports.  This report has been renamed Cash Flow/Balance List in the coming fall release.  This SSTip shows a very simple cash flow design that you can use as a guide to create your own monthly Cash Flow report.

 

Approach: Create a Special Report in General Ledger that includes a starting balance for your operating account(s) along with all activity that affects the ending balances of those accounts.

 

Use the Following Steps to Create a Cash Flow Report

 

Creating a Special Report

  1. Start Shelby v5.

  2. Locate and click on the General Ledger link.

  3. Verify that you are using the correct company or change the company using the pull down arrow on the company field located at the top of the General Ledger Home Base screen.

  4. Click the Reports link.

  5. Select Special Reports.

  6. Choose Special Report Design.

  1. Click the New icon found on the toolbar in the top-left corner.

  2. Type a name for your report in the Title field.  We will use Cash Flow Report.

Using the Opening Balance for Cash Accounts

  1. Click New while on the Special Report Setup screen.

  2. Enter 10 in the Line # field.

  3. Choose Balance - Opening using the pull down arrow on the Line Type field.

  4. Type the description that identifies this activity in the Description field.  We will use Opening Balance -- Operating Account.

  5. Make sure the Reverse sign when printing? checkbox IS NOT CHECKED.

  6. Enter the account number of the cash account in the yellow grid below.  (Note:  You can include additional lines for each cash account that you want to include in your report, or group them together on one line by including multiple accounts on the screen shown below.  If the account spans multiple funds, you can leave the Fund # field and Dept # field blank to include activity across funds.  If you leave the Fund # field and/or the Dept # field blank, then you will have to type the account numbers into the Account Low and Account High fields rather than using the pull down arrows.)

  1. Click the Apply button to save line 10 and keep the form open for the next line.

Tracking Income and Expense Activity

  1. Enter 20 in the Line # field.

  2. Choose Detail using the pull down arrow on the Line Type field.

  3. Enter a description for this activity.  We will use Income.

  4. Place a checkmark in the Reverse sign when printing field.

  5. Enter a value for Fund # if you use funds.  (Note:  Leave this field blank if you wish to include all funds for the account number range on this row.)

  6. Enter a value for Dept # if you use departments.  (Note:  Leave this field blank if you wish to include all departments for the account number range on this row.)

  7. Enter the account number range for the income to be included on this report line.  We are including all accounts from 40000 through 49999.

  8. Click the Apply button.

  1. Enter 30 in the Line # field.

  2. Choose Detail using the pull down arrow on the Line Type field.

  3. Enter a description for this activity.  We will use Expense.

  4. Place a checkmark in the Reverse sign when printing field.

  5. Enter a value for Fund # if you use funds.  (Note:  Leave this field blank if you wish to include all funds for the account number range on this row.)

  6. Enter a value for Dept # if you use departments.  (Note:  Leave this field blank if you wish to include all departments for the account number range on this row.)

  7. Enter the account number range for the income to be included on this report line.  We are including all accounts from 50000 through 99999.

  8. Click the Apply button.

 

  1. Enter 40 in the Line # field.

  2. Choose 5 - Total Level for the Line Type using the pull down arrow.

  3. The Reverse sign when printing field should NOT be checked.

  4. Enter a description for this total line.  We will use Net of Income and Expenses.

 

Tracking Asset, Liability, and Capital Activity

  1. Enter 50 in the Line # field.

  2. Choose Detail using the pull down arrow on the Line Type field.

  3. Enter a description for this activity.  We will use Accounts Receivable Activity.

  4. Place a checkmark in the Reverse sign when printing field.

  5. Enter a value for Fund # if you use funds.  (Note:  Leave this field blank if you wish to include all funds for the account number range on this row.)

  6. Enter a value for Dept # if you use departments.  (Note:  Leave this field blank if you wish to include all departments for the account number range on this row.)

  7. Enter the account number for the Accounts Receivable to be included on this report line.  We are including Accounts Receivable and Unapplied Cash, 10400 through 10450.

  8. Click the Apply button.

 

  1. Enter 60 in the Line # field.

  2. Choose Detail using the pull down arrow on the Line Type field.

  3. Enter a description for this activity.  We will use Accounts Payable and Liabilities Activity.

  4. Place a checkmark in the Reverse sign when printing field.

  5. Enter a value for Fund # if you use funds.  (Note:  Leave this field blank if you wish to include all funds for the account number range on this row.)

  6. Enter a value for Dept # if you use departments.  (Note:  Leave this field blank if you wish to include all departments for the account number range on this row.)

  7. Enter the account number for the Accounts Receivable to be included on this report line.  We are including all Liability accounts, 20000 through 29999.

  8. Click the Apply button.

 

  1. Enter 70 in the Line # field.

  2. Choose Detail using the pull down arrow on the Line Type field.

  3. Enter a description for this activity.  We will use Direct Postings to Capital Accounts.  (Note:  Any journal entries that post directly to a capital account will be included by using this line.)

  4. Place a checkmark in the Reverse sign when printing field.

  5. Enter a value for Fund # if you use funds.  (Note:  Leave this field blank if you wish to include all funds for the account number range on this row.)

  6. Enter a value for Dept # if you use departments.  (Note:  Leave this field blank if you wish to include all departments for the account number range on this row.)

  7. Enter the account number for the Accounts Receivable to be included on this report line.  We are including all Capital accounts, 30000 through 39999.

  8. Click the Apply button.

 

Creating the Ending Balance Line for the Cash Account

  1. Enter 80 in the Line # field.

  2. Choose 6 - Total Level for the Line Type using the pull down arrow.

  3. The Reverse sign when printing field should NOT be checked.

  4. Enter a description for this total line.  We will use Ending Balance -- Operating Account.

  5. Click the OK button.

 

Running the Cash Flow/Balance List Report

  1. Return to the General Ledger Home Base screen.

  2. Click the Reports link.

  3. Choose Special Reports.

  4. Select Cash Flow/Balance List.

  1. Click the Include/Exclude button.

  2. Select the special report that you want to run from the list.

  3. Choose the report period using the pull down arrow on the Period to Print field.

 

  1. Click OK.

 

 

***************************************************************************

Tip 3

    Removing a Day's Giving in Contributions

Overview: Sometimes we make so many mistakes performing a task at work, it is simply easier to start the project over from scratch. At some point in the future, you may find a lot of entry mistakes after saving your Contribution batches. You conclude that it would be easier to start the gift entering process over rather than hunting down and correcting the mistakes individually. This SSTip will demonstrate how to wipe out a day's saved giving in just a few minutes. 

 

Approach: Create a backup. Run the Change Contribution Dates utility found in ShelbyTools, changing the date of the contribution batch back one hundred years. Run Options for Purging History in Closing Procedures and purge all gifts that were saved prior to 99 years ago.

 

Use the Following Steps to Remove all Giving for a Chosen Day/Batch

 

Use the Following Steps to Change the Date of a Batch

  1. Make a full Shelby Backup using the Shelby Backup-Restore program (see SSTips article Tip 1, July 1st 2004 for more details regarding backups).

  2. Start Shelby v5.

  3. Locate and click on the Utilities link.

  1. Click the ShelbyTools button.

  2. Highlight Change Contribution Dates in the Utilities List.

  3. Click the Run button in the bottom-right corner.

  1. Click the Yes button on the "Are you sure you want to run the Change Contribution Dates utility" warning message window.

  2. Choose the date of the flawed contribution batch in the Wrong Date drop down menu.

  3. Enter 01011900 (January 1st, 1900) in the New Date field.

  4. (Optional) If you wish, you may choose not to wipe out all of the batches that were saved on the day of the flawed entries. If you only want to remove a certain batch, click the Specific Batches radio button and place a checkmark by only the batch or batches you wish to remove.

  1. Click the OK button.

  2. Click the OK button on the "Update is Complete" indicator window message.

  3. Close the Utilities module.

Use the Following Steps to Purge Contribution Data

  1. Start Shelby v5.

  2. Locate and click on the Closing Procedures link.

  3. Click Options for Purging History.

  4. Click the Yes button on the backup warning message.

  5. Check the Contributions checkbox.

  6. Click the OK button.

  7. Type 01011901 (January 1st, 1901) in the Thru Date date field.

  1. Click the OK button.

  2. Click the NO button on the window that asks if you wish to purge for another date.

Permanently Delete Purged Contribution Data

  1. Click Select Purged History Set to Restore.

  2. Click the YES button on the backup warning message.

  3. Locate and highlight the contribution dataset that you just purged in the grid list.

  1. Click the Trashcan icon on the icon bar.

  2. Click YES on the message warning you that this will permanently remove the purge history.

  3. Click Close

  4. Exit Closing Procedures.

Note: If you received a message while trying to purge that says a purge database does not exist, you will need to run the Check Shelby Database Setup utility found on the Shelby CD. To do this:
  1. Go to the Shelby Server.
  2. Insert the latest Shelby CD that was mailed to your organization in the
    CD-ROM drive of your organization's server.
  3. Click the Launch Database Install button on the splash menu.
  4. Click the Check Shelby Database Setup button.

The utility will continue to run for a few minutes. After it is completed, exit the splash menu and try purging contributions again.

 

***************************************************************************

Tip 4

   Pushing SQL Queries to the Dashboard

Overview: Starting with the fall 2005 v5 CD release, Shelby will have the unique ability to allow you to "push" SQL queries to its main user interface, called the Shelby Dashboard. This will allow you to obtain frequently needed information with a single click of the mouse. For an early look at the new Shelby Dashboard, see the
August 2005 issue of SSTips. This SSTip provides a sample SQL statement that your organization may find helpful, and demonstrates how to push this statement to your Shelby Dashboard. 

 

Approach: Open ShelbyQuery. Copy and paste the SQL statement provided below in the box with the blue border into the query area of ShelbyQuery. Save the query, and choose which Shelby Users will be able to see this query. Return to the Shelby Dashboard and click on the query name to execute the statement.

 

Use the Following Steps to Push a SQL Query Report to the Dashboard.

 

Open ShelbyQuery

  1. Start Shelby v5.

  2. Log into Shelby using your username and password.

  3. Click Shelby Menu in the Recently Modules Used area.

  1. Locate and click on the ShelbyQuery link.

Push the SQL Query to the Dashboard

  1. Copy and paste the SQL query in the box below into the the query window (triple-click inside the blue box to automatically select all of the text).

This query will obtain a list of all individuals that have contributed, how many times they have contributed, and the sum total of their gifts.

SELECT [Shelby].[NANames].NameCounter, [Shelby].[NANames].LastName, [Shelby].[NANames].FirstMiddle,
[Shelby].[NANames].EMailAddress, count(cnhst.namecounter) as '# Times Contributed', sum(cnhst.amount) as 'Total Contributed'

FROM [Shelby].[CNHst]

INNER JOIN [Shelby].[NANames] ON [Shelby].[CNHst].NameCounter = [Shelby].[NANames].NameCounter
group by nanames.namecounter, lastname, firstmiddle, e-mailaddress
order by '# Times Contributed' desc, 'Total Contributed' desc

  1. Click the Load Grid button.

  1. Click the Save button on the toolbar in the top-left corner of the window.

  2. Type in Giver's List for the report title

  1. Click OK.

  2. Click the YES button on the "Do you want to push this query to anyone's dashboard" message.

  3. Place checkmarks in the My Status and/or My Queries column(s) by the usernames of individuals that should have access to the report.

  1. Click OK.

  2. Close ShelbyQuery.

Important Note: In order to create or push a query to the dashboard, you must have Shelby rights to the module's tables. For instance, if you wish to create, use, or push the Contributions-based query above, you would have to have rights the the Contributions module.

However, anyone that you pushed the Contributions query to will be able to run the report, whether or not they have rights to the Contributions module.

 

Use the Following Steps to Run the Pushed Query

 

Accessing the Report from My Queries on the Dashboard

  1. Navigate back to the Shelby Dashboard.

  2. Click My Queries in the Dashboard Sidebar.

  3. Click the Refresh button. You should now see the report name "Giver's List" in the viewing space. To the right of the report name, you will see a number. This number is the number of records that were returned.

  1. Click on the name "Giver's List" to see the results of the query.

  1. (Optional) Click the Refresh Results button to have Shelby re-query the database for new results. This is useful when other users are entering records and you want up-to-the-minute feedback of everyone in the database.

  2. (Optional) Clicking the View in EZView button will launch EZView with the records that were selected by the query already marked (a checkmark is placed beside the record's name). With the records already selected in EZView, it will be quick and easy to perform a mail merge or send an e-mail to the group. (Note: This button is available only when the query includes the NameCounter field.)

Accessing the Report from My Status on the Dashboard

  1. Navigate back to the Shelby Dashboard.

  2. Click My Shelby in the Dashboard Sidebar.

  3. Click the Refresh button. You will see the report name "Giver's List" in the list under the My Status area. Click on the report name to view the results of the query.

 

  1. (Optional) Clicking the View in EZView button will launch EZView with the records that were selected by the query already marked (a checkmark is placed beside the record's name). With the records already selected in EZView, it will be quick and easy to perform a mail merge or send an e-mail to the group.

 

More Sample Queries Will Be Included in Future Issues

***************************************************************************

New

     Project Budgeting

Overview:  Project Budgeting is a new feature that will be available with the fall v5 CD release. Financial analysts will now be able to sub-divide allotted monies for a period across multiple projects.

 

To access the Project Budgeting Area:

  1. Start Shelby v5.

  2. Locate and click on the General Ledger link.

  3. Click Account Information.

  4. Click the Budgets radio button.

  1. Double-click on an income or expense line.

  2. Click the Project Budgets button.

  1. Choose which projects you want to be associated with this income or expense line by placing checkmarks by the project names.

  1. Click the OK button.

 

 

An extra option is being included on the Budgeted Financial Statement that will allow the report to run only showing selected project information.

 

 

 

***************************************************************************

Ask

     Need a Tip or Want to Share One?

If you need a tip or have developed a special way of using a Shelby feature, or just want to comment about SSTips, then e-mail the editor.

***************************************************************************

SSTips Newsletter is published and distributed by SSTips Publishing, a division of Shelby Systems, Inc.

We respect your privacy and will not distribute or share your e-mail address with anyone without your prior approval.

 

Trademarks: All brand names and product names used in this article are trade names, service marks, trademarks, or registered trademarks of the respective owners. Shelby Systems, Inc. is not associated with any product or vendor mentioned in this publication.

 

Copyright Ó 2001, 2002, 2003, 2004, 2005, 2006  SSTips Publishing, a division of Shelby Systems, Inc.  All Rights Reserved.  Alfred Johnson, Managing Editor.