Newest KB Articles in Reporting

Exporting Totals by Year - Contact Report

Wed, 29 Nov 2023 18:04:51 GMT

Follow these steps to export Donation Transactions with Yearly Totals across (5 years).

Reports > Contacts
General Tab: Output: Export
Transactions Tab:
1. Category: Donation
2. Date Filter: Last 5 Years (e.g. 1/1/2019 - 12/31/2023)
3. Other Options:
  1. Filter Type: Choose Limit, the default (which is the most common need).
    - Limit: Limit the results to show only the contacts that have these donations.
    - Show: Show the Totals on the result, but don't limit. This will include all contacts regardless of their donations.
    - Exclude: Only show contacts that don't have these donations.
  2. Export Total Column: Optional is you also want the total of all 5 years.
  3. Export Columns by Years: Check this to show column totals for each of the five years.
Columns Tab. Check any additional fields related to the contact that you want exported (Email, Phone, Address, etc.)
Click OK

Contact Report - New and Improved

Wed, 24 Jul 2013 02:26:53 GMT

Based on user feedback, the old view of the contact report windows was hard to use. Despite its power, the features were too hidden. In reworking the Contact Report window, it was decided to expose its features to make them easier to find.

The redesign of the Contact Report window was done having in mind the following questions: What do you want to do and who should be included.

To decide what you want to do, you first choose the Output: There you have choices such as Print Preview, Export, Word Merge, Email and MyChabad.org Communicator.

When you make the choice, the next section will update accordingly asking for more specific input. For example, if you choose Word Merge, you’ll have choices for Merge Type (Letters, Envelopes, etc.), if you choose Export, you’ll have choices for Export Format (Excel, Text, etc.) and when choosing MyChabad.org Communicator, you’ll have choices for which List to update the email addresses to.

Options

Report Type: Clearly gives the choice of “Main Contact Record” or “Individual Family Member”. This naming convension is used in all other reports as well (Occasions, Adult Education). (this choice was in previous version called Contact/Family).

Main Contact Record: Choosing this option will give you both primary contact members on a single row.

Individual Family Member: Choosing this option will give you each family member on a separate row. You then have the option of whether to “Include Primary”, to “Limit to Primary”, or both. For example, if you want the report only show children (non-primary family members), you should uncheck the “Include Primary” check box. If you want the report to have all family members (primary or otherwise), you would uncheck the “Limit to Primary” check box.

Columns tab has options for which phone types, to limit to primary phone number in each type and filtering options.

As described elsewhere, the contact number types have been simplified in this version. The Contact Report allows for selecting from which of these contact number types to include on the report (Home, Work, Mobile, Other, Page, Emergency, Fax or Email).

Because there is no longer a limit to how many contact numbers can be entered in a particular contact number type, there is an option on the report to limit to “Primary Only.” This is checked by default. Limiting to “Primary Only” means that only the first (default) contact number of each contact number type will be included on the report. If you uncheck this check box, you will get all contact numbers of the types you choose.

Whether you choose “Main Contact Record” or “Individual Family Member” in the options (see above), the contact numbers that will be displayed on the report will be those that are either directly associated with that individual or those that are shared numbers. In all cases, only those contact number types that are checked off in the Fields to Include section will come up.

For example: if you choose Main Contact Record as the Type and check off only the Home, Work and Mobile fields and are showing Primary Only, you will get Home, His Work, Her Work , His Mobile, and Her Mobile on each row of the report.

If you choose Individual Family Member as the Type with the same options, you will simply get a Home, Work and Mobile fields for each row since each row displays an individual family member.

This concept applies to printable list reports, exports, group and individual emails, uploading to MyChabad.org Communicator or OneCallNow and any other Output that is available.

Contact “Programs” Limitation.

In prior version of CMS, there was a limit of 255 Programs. While the system would not stop you from entering more programs, when it came to reports, an error would occur indicated that you have more that 255. The only work-around to this would be to consolodate the programs and delete the programs that aren’t needed as much. In many cases, this work-around was simply not an option.

The new version of CMS resolvs this issue.

Include Subprgorams Bug Fix

The Contact Report has a feature that when reporting based on a Program (association tab) that has one or more sub-programs, you can check the Include Sub-programs check box so that the report will include any contact who is associated with any of the sub-programs (and sub-sub…) as well. An error would occur, in previous versions, if there was a sub-program that had no contacts associated with it. If this occurred, you would either have to delete unused programs, or not use the “include sub-programs” feature.

The new version of CMS resolves this error.

Transactions

Contact report can be made based on Transactions. You can make a report to include only contacts that have given (payments) or have pledged (bills). You can also narrow down the Transaction criteria to include certain categories, date range and amount range.

However, if you wanted to limit the contact report to EXCLUDE contacts who have given or pledged, there was an extensive workaround that needed to be done.

The new version of CMS adds the abiility to make reports that exclude contacts that match the criteria on the Transaction tab. Simply check the “Exclude Contact that match critiria” check box.

Another Improvement: In prior versions you needed to select at least one category or subcategory in order for the criteria to register. This is no longer the case: just selecting a Date Range, or Amount Range will trigger the citeria.

Year Comparison Transaction Reports

Thu, 03 Dec 2009 21:58:39 GMT

Year Comparison Transaction Reports

Generate a transaction report with year columns, giving you an instant comparison of activity from one year to the next.

Here are some examples of what you can do with such a report:

Compare how much a donor contributed this year compared to last year.
Get reports on who contributed over $100 last year but did not yet contribute that amount this year. (You can pick the amounts and years.)
How well did the Pesach fundraiser (Category: Donation\Pesach) do over that last five years. (You can choose the category and year range.)

To generate Year Comparison reports follow these steps:

Click Reports > Transactions.
On the General tab under Options, choose the Show Totals Only option.
In the Total Columns By drop-down, choose Years.
The Total By drop-down will determine how the rows on the report are totaled. For example: If you choose Name/Company, you will get a report that will show you how much each contact contributed over the years; if you choose Category, the report will show how each category did over the years.
Use the other tabs on the Transaction Report screen to choose other criteria such as Date Range, Categories, etc.
Click OK to generate the report.

This report will show all donation of all contacts limited to the criteria you chose. The report will display it in a format of rows (by what was chosen in the Total By) and columns (by what was chosen in the Total Columns By).

If you want to restrict the report to show only contacts who contributed a total of x dollars during x year but not this year and other such options, you must first build a custom query.

Follow these steps to build this query:

On the Other tab, click the Year Comparison button.
In the Comparison Builder window, enter comparison information. For example: In the "Contacts who have contributed..." section, on the first row type 2020 for the year and $100.00 for the amount. In the "But have not yet contributed..." section, on the first row type 2021 for the year and $50 for the amount. (In simple words you are choosing everyone who contributed $100 or more last year but have contributed only $50 or less this year.)
Enter a Query name. This name is used later when choosing this query (see below). For example: enter YearComp. Do not use spaces or dashes in the query name.
Click the Save button to save the query. You will get a message confirming that the query was saved successfully.
Click the Close button.

Now that a query has been created, you can use this query anytime to generate transaction reports, including the Year Comparison report above.

Follow steps 1-5 above (Generate Year Comparison reports).
Do not click OK (step 6), rather, click the Custom Query button on the Other tab.
Select the query you created. In the example above "YearComp" (Note: it will have the letters "CUS" before the name of the query, "CUSYearComp".)
Click the Open Report button to generate the report.

And there you have it. The report will now be limited to the criteria of the custom query, displaying (in the example above) only those who have contributed over $100 last year but less than $50 this year.

New Banquet Reports

Thu, 10 Sep 2009 00:57:36 GMT

More banquet reports were added and are more easily generated.

All banquet reports and exports are now generated within the program (not in seperate report database).
Full version of Access is no longer required. These reports work with Access Runtime.
Reports available include
- Guest Lists sorted and/or grouped by Name, Table, Section, Honoree.
- Place Cards
- Guests and Hosts
- Reservation Notes

To get to these reports, click Reports > Banquet.

These new reports have been implemented in version 9.9.9

Banquet Module is required for Banquet Reports.
For more information about the banquet module, see the userguide at http://www.chabadms.com/userguide/

Event Report

Fri, 03 Apr 2009 14:46:17 GMT

This article outlines how to generate a report of your event's attendees.
For how to manage and enter this information, please see Events and Events Tab.

Click on Reports >Transaction

On the 'Report' tab
- change the 'Report Type' to 'Bill Details'
- change the 'Total by' to 'Price Detail' (or if doing multiple events then 'Total by' should be 'Subcategory' and 'Subtotal by' should be 'Price Detail')
On the 'Categories' tab
- select the category(s) or subcategory(s) of the event(s)
on the 'Columns' tab
- check the 'Qty(Price/Dis)' check box
- check the Description check box
- Uncheck the 'Category/Subcategory' check box.

If you'd like to save this report for future use, click the 'Save' button on the bottom/middle of the window. This will allow you to quickly load (by clicking the 'load' button) this report in the future.

Contact Report Include AND Exclude Programs

Wed, 04 Mar 2009 07:59:25 GMT

You can now make a Contact Report limiting to Programs AND excluding Programs at the same time.

For example, get a list of contacts who are enrolled in Adult Education, Holiday or Community and exclude those enrolled in "Pesach Mailing Only".

(Previously one had to use Manage Programs to prepare a new Program, first updating it to the program you wanted, and then updating it to the program to exclude.)

Related Articles:

For more information on Contact Reports in general, see Contact Report
For more information on Manage Programs see Manage Programs
For more information on Programs in general, see Programs Tab, View Programs, Assign Programs and Quick Programs

Create a Program of Non-donors

Thu, 22 Jan 2009 23:36:45 GMT

This is a tutorial on how to create a new program/list with all your non-donors.

Go to the 'Programs' tab in the 'Contact View' and click on 'View Programs' on the top right.

Click the New Program button to create a new program. You will be prompted for a name for the program. Be sure not to enter a name that is already in use for another program. Let's call this program "Non-Donors".
Click View List View to see your contacts in a list view.

Click the 'Check All' button and then click the 'Update Program'.
This will open the 'Manage Programs' tool.

Select the "Non-Donors" program from the top drop-down or use the 'Choose Program' button.
Select the 'Update based on Selected Names' radio button.
Click 'Update'.

Click 'Yes'.
Now all your contacts are in our "Non-Donors" program
Go back to the Manage Programs tool (Tools>Manage Programs) Select the "Non-Donors" program from the top drop-down or use the 'Choose Program' button.
This time select the 'Update based on Transactions' radio button.

Change the 'Update to' drop-down to 'No' (middle right side).
Select the 'Type of Transaction' - 'Payments' and highlight the 'Donation' category.
Click update.

You are now left with a program that includes only contact who have not given you any payments in the 'Donation' category.
(You can repeat the last few steps to exclude other transaction types and/or other categories.)

Saved List

Sun, 28 Dec 2008 03:50:31 GMT

If you want to limit to people who either gave you x amount of dollars OR live in zip code x, bringing up people who fit EITHER criteria, this would require you to either use a 'saved list' or the 'manage programs' tool.

Saved List:
The concept here is that you append contacts to this 'saved list' based on criteria again and again. So the first step would be to choose your first criteria - say donated x dollars in this year. Now, instead of printing labels with these people or viewing a list, you would choose the 'output' to be 'export' and the 'type' should be 'saved list'.

NOTE: The first time this step is done you should check the 'clear the saved list' check box to ensure that there are no contacts saved in the 'saved list' from a prior such report.You should get a message stating that you are about to append x number of contacts to the 'saved list'.

Now you are ready to add more people to this list.
Choose another criteria (you might want to make sure you remove your first criteria - an easy way would be to hit the 'reset' button on the bottom left), and repeat the output to saved list (don't clear the list this time, as this will remove your first batch of contacts from the list).

You can do this over and over, adding more and more contacts, based on various criteria.

Finally you clear all criteria, and choose your ONLY criteria which is 'saved list' - this is done by checking the 'use saved list' check box on the 'Report' tab. Choose your output/report type and done - your report is based on your saved list.

An alternative method would be to use the Manage Programs tool.
Manage Programs:
Please see the following KB article:

Manage Programs
Here is where you can manage your programs by resetting programs and updating programs based on various options.

Transaction Report

Wed, 10 Dec 2008 00:45:01 GMT

The Transaction Report is a report that you can use to track your payments, bills, income and expenses. The report takes information entered under the transactions tab in the main screen and information entered in the bank account register to pull up your report.

The transaction report has a few tabs which allow you to specify what information you want to include in the report.

Report Tab

Report Title:
Here you can enter a name for your report. If you don't choose a name, a default name will be chosen.

Report Type:
You can choose to make a Payments, Bills, Payments/Bills, Expenses, or Income/Expenses.

Payments: A payments report is a list of payments that people have made to your organization.
Bills: A bills report is a list of transactions that were billed to various people (money that people owe your organization).
Payments/Bills: This report includes both payments and bills.
Expenses: This is a report of only your expenses. This information is usually gleaned from the bank account register.
Income/Expenses: This report includes both income and expenses.

Output:
The choice of output is Screen, Printer, or Export. Choose screen to view it, printer to output the report directly to the printer without first viewing it on screen or export to export all the payment information to a separate file to be used in other programs. You will be directed to specify a file name and a file type as well as the location to save the file. You will also be given the option to save as a Query. This is for advanced users only. A basic understanding of Access Queries is necessary. For more information see Access Help on Queries.

Total By:
Choose how you want your report to be totaled by. The Total By drop-down list varies based on the report type, but it includes options such as name, category, subcategory and amounts, among many others.

Subtotal By:
Choose if you want to subtotal your report or not. The options are the same as the Total By drop-down but it also includes the option of None if you don't want to subtotal by anything.

Show Details/Show Totals Only:
You can choose whether or not to show the details or just the amounts. Showing the details will list each transaction followed by a total based on what you chose to total by. If you choose to show the totals only, the report will show totals only totaled by whatever was chosen in the Total By drop-down list.

Sort details by: If you choose Show Details, you have to choose how you want to sort your information.
Amounts by: If choosing Show Totals Only, you have to choose how you want the amounts to be shown. You can choose by sum, average, maximum or minimum.
Totals Columns by: Choose how you want your totals column to be. You can choose Totals Only, Years, Fiscal Year or Quarters. If you choose anything other than Totals Only, you also have the option of choosing a Totals Column or not.

Categories Tab

In this tab you can specify categories to include in the report.

Categories, Subcategories and Details:
Here you choose categories, subcategories and details. Subcategories and details only show up if you have those boxes checked off on the right side.

Show Active Only:
Check this box if you want to include only active categories.

Category Type:
This is available only for the Income/Expense report. You can choose if you want to include only income categories, only expense categories or all. For any other type of report, it will be a default based on the report type.

Include Transfers:
If you would like to include transfers made in the bank account register (transfers are not income or expense), and see all account activity, then check off this box. Otherwise CMS will only bring up income, expense or both (based on what category type you choose) and exclude transfers.

Date Tab

In this tab you can choose specific dates or a range of dates to include in the report. If you are including bills that are due by a certain date, you can choose what date range you want for those as well.

Amount Tab

In this tab you can specify an amount or a range to include in your report.

Amount:
You can choose < (less than), > (greater than), = (equal to) or between. If you choose <, >, or =, you will have the option to enter an amount in the text box to specify the amount criteria. If you choose between, you can either choose a range from the drop-down menu or enter custom amounts in the From: and To: boxes.

Paid Status:
You can choose whether to include only transactions that are paid or outstanding or to include all transactions.

Method Tab

In this tab you can choose to limit the report only to transactions based on a certain method.

Method Type:
These are the built in method types of CMS. You can check off as many as you want to.

More/Less Payment Method Options:
Use this button to show/hide other methods. By showing the methods, you bring up the custom payment method list that you set up. Hiding them only allows you to choose from the built in method types.

Columns Tab

In this tab you can specify information that you want to include or exclude from your report.

Note: This tab is available only if you select to show details on your report. If you choose to show totals only, it will all appear grey. Additionally the availability of options depend on the type of report you selected. For example, when you select bills, you won't have the option of check/CC number, method or receipt number.

Show in contact header:
Choose what information you want to appear together with the contact name.

Show in detail section:
Choose what information you want to include in the detail section.

Column Widths:
You can choose if you want wide, narrow or automatic. When more columns are displayed, the data will wrap around in two lines. Choosing narrow will give a nicer/cleaner look but may trim off some of the text.

Other Tab

In this tab you can choose other limitations such as limiting to a contact, solicitor, account, organization, mailing or receipt numbers.

Pending Thank-you letters:
Check off this box to include only those transactions that have pending thank-you letters.

Contact Criteria:
Press this button to choose specific criteria for contacts. This will bring up another screen where you can limit contacts based on associations or demographics.

Associations Tab
- Programs:
  Click on Select Programs to open up another screen where you can select the programs that you want to specify. Use the right arrow to add a program to the selected programs list and the left arrow to remove a program from the selected programs list. When you are done, click on Select.
  
  You can choose to include sub-programs by checking off the Include Sub-Prgs box. You can choose whether you want to include all the contacts in the programs you selected or include all other contacts and exclude those. To exclude the contacts of the selected programs, check off the box Not. This is useful for example when you are doing a mailing to Baalei Batim and want to exclude Anash and Family members so you choose those programs and check off Not.
  You can choose whether the contacts have to be included in all the programs you choose or only in one. To include them in all programs, click on the radio button And. This means that contacts have to be in each one of the programs you specified. To include contacts that appear in one or more of the programs, click on the radio button Or. This will include contacts that don't necessarily appear in every single program you specified, but appear in at least one.
  Note: To include all contacts regardless of the programs they are associated with, do not click on any of the programs. Even if you click on all the programs and choose Or, you would still be limiting the list of contacts to only contacts associated with any of the programs, but you are excluding contacts that are not associated with any program.
- Custom Fields:
  You can specify contacts that are connected to the custom fields you set up by highlighting those options in the custom fields boxes. Click on any option to select (highlight) it and click again to deselect. The custom fields boxes also have the and/or radio buttons options and the Not checkbox option which works the same way as those buttons in Programs.
Demographic Tab

Here is where you can limit the list of contacts based on their address. You can limit contacts to a city, state, country or zip code.
You can limit the city list to a specific state by choosing a state from the Limit to State dropdown menu. The cities and states that show up on these lists are built-in and the cities and states that you entered through entering contacts. You can choose a city by clicking on it to highlight it. You can click on as many cities as you want to. Clicking on it again will deselect it. You can choose a state and country from the lists in the same manner. You can limit contacts to a specific zip code or a range of zip codes. You can enter the zip codes separated by commas or with a hyphen in between. For example: say you only want a list of contacts in CA between 91104 and 93465. Clicking on CA and entering 91104-93465 in the Zip Code box will easily accomplish this.

Year Comparison:
Click on this button limit transactions based on certain years and amounts.

Categories Tab:
Choose contacts who have contributed a certain amount in a certain category last year but not this year.
Enter query name:
Enter a query name and choose save to save these criteria as a custom query.
Custom Query:
Once a Year Comparison query is saved, you can use it as a custom query. Choose a query saved in the base and CMS will use those criteria for your report.

Banquet Tab

Note: This tab is only available to those who have the optional banquet module. In this tab you can limit transactions to a certain banquet.

Contact Details Report

Wed, 10 Dec 2008 00:18:38 GMT

On the toolbar there is a button for a contact details report. This report brings up details about one or all of the contacts.

To run a contact details report, click on the button or go to Reports > Contact Report and choose Contact Details from the Report drop-down menu. This is a report where you can easily choose which details to show on the report including transaction, family members, programs, occasions, history and notes. When you run this report, another screen pops up first asking you which contacts to include and what information to output to the report.

Who should appear on the report: Choose either Current Contact or All Contacts. Clicking the Select button will return you to the main Contact Report screen where you can choose the criteria by which to define the contacts. What should appear on the report: Check off the information that you want to appear on the report.

When you are done, click on OK.