Descriptive names make the search box on report lists more useful. Entering 'time' in the search box should return all reports related to timekeeping, entering 'vawa' should return all VAWA reports, etc.
Naming schemes that are popular are prefixing:
by office (SB for South Bronx, CC for Center City),
by program (FP for Foreclosure Project, DV for Domestic Violence unit),
by grant or project (VAWA -, LITC - , GAR -, IOLTA -),
by purpose (QA for quality assurance, DI for data integrity),
or a combination of these.
The scheme chosen is less important than following it consistently. Consistency will also improve results with the search box.
The Path information refers to the table structure in Advanced Add Columns, Method 2. Some of the fields can be added via Method 1, or even Add Columns, but Method 2 is guaranteed to lead you there.
There is a top level Activity table, as well as Case Data > Activities (One Row per Activity), and Case Data > Activity (Latest).
The Venue that can be collected on activity records is Activity > Organization > Organization Name.
County of Residence
This field is on the Case Data > Person > Primary Home Address > Address > County of Residence table.
Client Addresses
Home Address fields are on the Case Data > Person > Primary Home Address > Address table.
Mailing Address fields are on the Case Data > Person > Primary Mailing Address > Address table.
Note the use of the Primary subtables above. There is also an Addresses (One Row Per Address) subtable. But if you pull fields from that subtable you could get multiple rows per case. Every case has a primary home address, many will have a primary mailing address, and some could have many more (see the next paragraph).
Institutionalized At: The Address block can be configured to collect an organization where the client is located. The name of that organization is Case Data > Person > Institution > Organization Name. The address fields (Street, City, etc.) are in the usual location.
Other Client Addresses and address history can be collected with the Address Extended block. If your site uses that block, in addition to the above subtables, the Case Data > Person > Addresses (one row per address) subtable will be useful.
Permanent Address fields (collected via the Permanent Address block) are in the Fields list of the top level Case Data table. This is not technically a 'client' address since the information is stored directly on the case, not on the client person table. Most sites do not collect this.
Contact's Addresses
A contact can have a home address and a work address. One of these addresses can be marked preferred.
A contact's work address depends on the Bind to Organization Address setting on the contact's profile. If Bind is set to Yes, the contact's work address is the address of the organization he or she is affiliated with. If Bind is set to No, the contact's work address is contained in the fields prefixed "(Individual) Work".
Here are the fields available in the Contacts > Contact Addresses table:
Person Full Address: the full home address in one field.
Work Full Address: the full work address (depends on the Bind to Organization setting).
Preferred fields: the various work or home fields depending on the Preferred setting.
Org fields: the address fields of the contact's affiliated organization, if any.
Home fields: the various home address fields.
Work fields: the various work address fields (depends on the Bind to Organization setting).
Primary Assignment's Address
A primary assignment, like all users, can have different addresses. The most common desire is for the name of the primary assignment's current organization affiliation, and that organization's address. These fields are available under the Case Data > Primary Assignment > Advocate > Current Organization Affiliation > Organization Information subtable. Organization Name is a field on that subtable, and the Primary Address subtable under it contains the address fields.
Filtering by Zip Code or City
You can filter on either or both, but both are text fields, so a report filter doesn't behave like a filter on a lookup field. You don't see a list of cities or zip codes to pick from.
The default filter style for text fields is "comma separated values", so you could enter "90210,90211,99000" etc. Likewise for city: "san diego,el cajon".
For some reports, changing the style to "contains" can be helpful. A broad example is a "contains" filter on city set to "san" would pull San Francisco, San Diego, Picosan, etc.
Using the option to change the Label of the filter is recommended, so people running the report will know if the filter is "City (contains the text)" or "Zip Codes (comma separated values)", etc.
Case Data > Adverse Party Summary
Puts all the adverse parties on a case in one 'cell' so the case is one row per case (unless other tables make it otherwise).
If you filter on this field, consider changing the filter from the default "Is in the comma separated list" to "Contains", and change the filter label to something like "Adverse Party Summary (contains the text)". This allows user friendly filtering for text like "bank" when people run the report.
Case Data > Adverse Party Summary with Address
Same as the above table, with, no surprise, addresses.
Case Data > Adverse Parties (one row per party)
Provides access to detail on individual and organization adverse parties, including AP Notes, AP Alerts, Business Type, Relationship, etc. Note: This subtable does not provide a field to report on common adverse party names.
Case Data > Common Parties (one row per case)
All common adverse parties on a case, concatenated into one cell. The filter on this field is a lookup-style filter, listing all common adverse parties.
The Case Data > Person > Age at Intake field will display an applicant/client's age based on their Date of Birth and Intake Date. The Current Age field on the same table will display their age as of the run date of the report. If you want yet another option, add the person's DOB field and format it to Age (in Years).
Somewhat related: Any date column can be formatted with one of the Age formats to get the 'age' of records. For example, add Date Opened to a report, format it to Age in Days, and it will display the number of days since a case was opened. NB: A filter will not do what you hope and dream it will; in other words, it will not filter on the numeric number of days.
Aggregating (Collapsing Repeated Rows)
Aggregating columns to collapse repeated rows in a report is an advanced feature that is beyond the scope of this help page. You will typically not need this, but if you do, there is a video.
The Case Data > Types of Assets field displays a concatenated list of assets types. The Case Data table also contains fields like Total Assets.
Case Data > Assets (One Row per Asset) lets you get at all the detail.
Case Data > Primary Assignment
This table contains the fields for the current advocate (Caseworker Name), program (Program Name), and office (Office Name) a case is assigned to.
Case Data > Current Assignments > Assignment List
A concatenated list of the names of everyone currently assigned to the case in one cell. The type of assignment is indicated in parentheses after each name. Example: "Bob Staff (Primary), Jane Volunteer (Pro Bono)".
Case Data > Current Probono Assignment
This table contains several fields related to probono advocates currently assigned to the case, including "Full Name, Last Name First (filterable)". Despite the singular "Assignment" in the name, it returns one row for each current pro bono assignment.
Case Data > Additional Assignments (One Row Per Assignment)
As the name implies, using fields on this table can return multiple rows per case. Despite the "Additional" label, this table includes all assignment types, including primary assignments.
Frequently used fields from this subtable include Assignment Type > Assignment Type, and Assigned To > Person > Full Name (Last, First) to get the name of the person assigned.
Citizenship Status is recorded for a matter, not a client. The correct table is Case Data > Citizenship Status. Although the incorrect table can no longer be added to reports, older reports may still contain references to Case Data > Person > Citizenship Status.
Clickable Case Number Links in Excel
The Matter/Case ID# column in reports displays a nicely clickable link to each case. But if you export the report to Excel, the underlying URL doesn't come along. There are various solutions for this. Here is one that was posted to the Siteadmins mailing list:
Add the case's Database ID to your report, in addition to the case number, preferably as the final column.
In Excel, in the column immediately to the right of the database ID, enter this formula, replacing the URL and cell column/row with your own info:
=HYPERLINK(CONCAT("case profile url minus the last digits",DATABASEID1))
Thanks to SV at LAFLA for this contribution.
The Case Data > Client field contains the client ID that a case is linked to. Associated cases will all have the same client ID. NB: If you use the search box in the Fields list, there are a lot of matches for 'client'. Be sure to scroll down the list to find this elusive, but sometimes helpful field.
There is a top level table, Clinics, to report on clinics, clinic events, and clinic event appointments.
To report on matters/cases linked to clinic appointment slots, use the Clinics > Clinic Events (one row per event) > Appointments (one row per appointment) > Case subtable.
Contract Term Allocations on Timeslips
For sites using advanced grants management, the Code field in reports now presents a report filter that combines the Code and Source fields, so it displays as "2252 VOCA Grant". The path is: Timekeeping > Contract Term Allocation > Contract Term > Funding Code > Code.
Custom fields are available on "Custom module_name" subtables. For cases, that's Case Data > Custom Matter, for timekeeping, Timekeeping > Custom Timekeeping, etc.
Older reports may have just a Custom subtable (like Case Data > Custom). Fields selected from the older subtables will continue to work, but if you are adding any new fields to the report, add the Custom Matter, Custom Timekeeping, etc. subtable and add new things from there.
Date Open Range is a special column often used as a filter to answer grant questions like "How many cases were open during the report period" and "How many cases were open at the beginning (or end) of the report period".
The filter returns a case if it had the Open disposition at any time during the range of dates specified, regardless of whether the case was opened before or during the date range or is now closed.
Note: When filtering on Date Open Range, always filter on Disposition = Closed, Open. Matters with other dispositions can have an 'infinite' date open range and will be returned in the results, which is seldom the desired outcome.
For example, a Date Open Range filter of 1/1/2013 - 12/31/2013 will return all these cases:
A case opened before 2013 that is still open or was closed any time on or after 1/1/2013.
A case opened during 2013 that is either still open or is now closed.
A case opened and closed in 2013.
For cases open at the beginning of a period or the end of a period, use a date range of a single date.
For cases remaining open at the end of a grant period, say 12/31/2013, enter 1/1/2014 - 1/1/2014. In this example, you may also want to add a filter on Date Opened, set to "Has a Date Before" = 1/1/2014 (if you might have people opening cases on Jan 1.)
Date Open Range is on the subtable Case Data -> Case Open Date Range -> date_open_range.
If you need to set the Aggregate option on a Date Open Range column, you must use Count. You typically want to hide the column because a number in that column may confuse people. A filter on Date Open Range will still work as expected.
Note: Similar functionality for Outreaches can be found at Outreach>Outreach Date Range>Date Range, which will filter for Outreaches with a Presentation Date in the range you specify.
Docket Number / Court Case Number
Litigation records can have a docket number; sometimes re-labeled as Court Case Number. The Court Case block can be used directly on a case form or profile to record a docket number, which creates a blank litigation record in the background.
Now you know why the path to this field is: Case Data > Litigation Records (Multiple Rows per Case > Docket Number.
There is a top level Document table with limited information, but includes a Module ID field, which will display the case number for documents linked to cases.
There currently is not a subtable to pull Expense information collected on cases. (Ref: 90126)
Family Member / Household Information
Reports can contain information about individual family members such as gender, race, SSN, etc.
The Case Data > Non-Adverse Parties/Family Members (one row per party) table and its sub-tables, particularly the Person subtable, will report on family/household members, but not the client.
The Case Data > All Household People Associated With a Case table includes information about family/household members and the client.
Both of these tables will return one row per person, so row count will not equal case count unless a case only has a client and no family/household members.
The Case Data > All Household People Associated With a Case table includes two special fields: Relationship Type and Relationship Subtype. NB: These will display and filter like the lookup fields they are, but don't follow the usual pattern of lookup fields being on separate subtables.
"Funding Codes", a/k/a "Grants", a/k/a whatever label an administrator might have applied, typically appear like "01 LSC" and "42 VAWA", etc., when picking the funding code on a case or timeslip. This is actually a combination of the "Code" and "Source" fields. In reports, if you want to see both, you need to add both. There isn't a combined field like there is on some forms.
The Code and Source columns live under a Funding Code subtable. The location of that subtable varies depending on the top level table of your report:
Case Data > Case Funding Code > Funding Code
Timekeeping > Funding Code (for the funding code on the timeslip)
Timekeeping > Cases > Case Funding Code > Funding Code (for the funding code on a case if the timeslip is linked to a case)
Activity > Matter > Case Funding Code > Funding Code
You get the idea.
User-Friendly Filters on Funding Codes/Grants
If you want a nice filter for funding code on a report, where "nice" is defined as the combination of the Code and Source fields (like "01 LSC" and "42 VAWA") use the following fields:
For the funding code on a case:
Case Data > Case Funding Code > Funding Code > ID column (it might be listed as "Database ID").
For the funding code on a timeslip on a case report:
Case Data > Timekeeping > Funding Code > ID
For the funding code on a timeslip on a Timekeeping report:
Timekeeping > Funding Code > ID
For the funding code on an Outreach:
Outreach > Funding Code Connection > Funding Code > ID
Note that this column will contain numerical values to which you cannot add a breakdown or unique count, but you will still get the nice filter values you are looking for. To avoid confusion, we highly recommend changing the column header to something helpful like "Case Funding Code Filter", "Timeslip Funding Code Filter", etc. before adding the column as a filter, and then hiding it. You can hide this column and still filter on it.
Filters on older reports based on the Source field will continue to function, but will continue to have an issue if the Source field text is the same on two or more grants. The ID field does not have this issue.
Primary and Secondary Funding Codes
Do not use these subtables unless you know you need them.
Case Data > Primary/Secondary Funding Codes (Combined)
This table has two fields, one for Primary and one for Secondary.
Both fields will automatically display all active codes into one 'cell', so a case with multiples won't create two or more rows in reports.
The filter for each field lists the respective funding codes, and displays them in the form "code space source", or "1234 Title III".
These are often the preferred fields because they cannot inadvertently cause a report to produce more than one row per case.
Case Data > Primary Funding Code
This table has all the fields available for a funding code: start/end date, code, source, etc.
You may need this subtable if you have imported cases with multiple primary funding codes.
Case Data > Primary/Secondary Funding Codes (One Row Per Code)
This table has all the fields available for funding codes, including the Primary field to distinguish primary funding codes.
As the name indicates, this table produces one row per code, not per case.
Case Data > Income Types > Income Types concatenates all the income types for a case/matter into one field. This allows a report to display all income types but still be one row per case. NB: The filter option mentioned here is not currently working (Ref: 96776): If this field is used as a filter, the filter comparison type is usually set to "Is any of substring". Substring allows filtering on a type like "SSI", and will return cases where the only income is SSI, but also cases where there are other income types; for example, "Child Support, Employment, SSI".
Case Data > Income Entries (one row per entry) provides several fields related to each income entry. As the table name indicates, it produces one row per income entry, not per case.
Selecting "No Income" or "Income Not Provided" in the Income Type dropdown on a matter does not record an Income Type (the column is blank on reports), but sets the "Zero Income" or "Senior Income" field, respectively, to Yes.
Literal Text and Literal Number Fields
Each top level table in reports has a "New Literal Numeric Field" and a "New Literal Text Field" in the Fields list.
These are useful when every row in a report needs to contain a static number or some static text. For example, when a funder requires every row to contain your agency ID number, or a column that displays "Not applicable" or "Foo" for a field you don't collect but is required to be a column on a report you submit.
The column properties for each field contains an Expression box where you can enter the text, or number, to be displayed. In this example, the column will display "Data not collected" on every row of the report. In real life you would also change the Heading.