Home‎ > ‎Reports‎ > ‎

Reports Manual

How to build and edit reports.

If you just want to learn about running existing reports see Running Reports.


Creating New Reports

There are 4 ways to create a new report:

1. "Add New Report" link on the main Reports page.
2. Using the "Save Report" button when running a report (not in edit mode)
3. Using the "Save As" link in edit mode.
4. Importing an exported report from another site.

The first method, Add New Report, creates a report "from scratch", while the last three let you create new reports based on existing reports.

Add New Report Link

To start creating a new report, go to the top level Reports tab then click the "Add New Report" action link.

Step One: Give the report a name (do not use backslashes "\" in the name), select the information to base the report on, and optionally enter notes about the report. The "Information to base report on:" field is a dropdown list of the top level report tables in LegalServer. The most commonly used are Case Data and Timekeeping, but also include System Users, Contact, Activities, and others.

Step Two: Select a few columns (fields and columns are interchangeable terms). Choose columns by checking the box next to each column you want to add, then clicking any of the Add buttons on the right side of the page. You can add and remove columns later after the report is created.

Step Three: Set some initial filters. It is highly recommended to set initial filters that will return a small set of data at first. For example on a case report, set Disposition = Open or Date Opened = Current Month; on a time report, set Date of Service = Current Week, Current Month, or similar. Not setting initial filters could result in your report attempting to return hundreds of thousands of cases, or millions of timeslips.

LegalServer then displays the report in edit mode, where you can use the report editing features to modify it. Note: Your report is not yet saved. Click the "Save Changes" link when ready.

"Save Report" Button for a New Saved Report

When you run a report (not in edit mode), there is a Save Report button next to the Run Report button. The Save Report button makes a copy of the current report settings — not the data. The copy will automatically appear on the Saved Reports tab of the main Reports page. A common use of this feature is to save a copy of a report with changes to one or two filters and the default columns that are displayed. For example, you might open a Closed Cases report, select the legal problem codes and funding code that apply to a particular group of cases, adjust the columns that are displayed, then save that as Closed Cases - Domestic Violence Group.

The Save Report button can be disabled per user role by unchecking the Show Save Report Button permission for any desired role on the Admin > User Roles (Permissions) page.

"Save As" Link for a new Base Report

The "Save As" link is available in edit mode. It will save a copy of the current report as a new Base Report, so the report will appear on the Base Reports tab (as opposed to the Saved Reports tab if you used the Save Report).

Importing an Exported Report

You can export the structure of a report in LegalServer to an XML file. Another LegalServer site can upload, or import, the XML file to recreate that report on that site.

To import one of these files, you need to get the file and save it to your computer. Then click on the top level Reports tab and use the "Import Reports" actions menu link. Use the Browse buttons on the import page to upload one or more reports.

To export a report to an XML file, go into edit mode on the report, click the "Export to File" link at the top of the page, then save the XML file to your computer. You can send or transfer this file to someone like any other file.

See Example Reports for several examples you can download and import to your site.

Edit Mode

Enter edit mode by clicking the edit pencil icon or link for a report on one of the lists on the main Reports page.

The top of the edit page contains several links, the name of the report, and several expandable sections.



The Save Changes link will appear red when you have made changes to a report that need to be saved. If you close your browser window or navigate away from a report before clicking Save Changes, you will lose those changes.

The Save As link and Export to File link are covered earlier in creating new reports.

The Add Column, Add Fields, and Edit Library links are covered later.

The Automatically Refresh Results checkbox only affects edit mode. It determines if the data table is refreshed after each change to a report. We recommend leaving it unchecked.

Below the report name are several expandable sections. Click on each section name to expand it and see the options it provides.

Attributes (Rename, Mark inactive, etc.)

The Attributes section lets you rename a report, set it to inactive (so it will not appear in the reports lists by default), determine if the report will immediately return data when run, and select which roles can view the report. You can also set a category and grouping if you want to use the filters on the various Reports tabs. You can also edit the notes, if any, that display at the top of the report.



After you make any changes in the Attributes section, click the Apply button that appears at the end of the section, then the Save Changes link at the top of the screen.

Forgetting the Apply button inside Attributes is the most common mistake we all make.

If a user's role is not selected in the "Which Roles Can View This Report?" section, the user will see a message explaining this if they click on a report:


Excel Settings

Set the Print Orientation of the exported Excel sheet to either Landscape or Portrait.

Filters

Filters let you limit the data returned by a report. For a report on cases, you typically do not want the report to return every case in your database, but a limited, or filtered, set of those cases - like open cases, or cases closed within a certain date range, or cases with specific legal problem codes.

The Filters section allows you to add and remove the filters users will see when they run a report, and set sensible default values. People running the report will be able to select different values. They aren't locked in to the defaults you set.

Expand the Filters section to display all the filters on the report. Here is part of an example Filters section:

How Filters Are Applied

Note the "AND" to the left of the filters. As this indicates, the report will only return data that meets the conditions in all the filters — in this example, only cases that were closed in 2014 with a close reason of A or B.

Some filters, like the Close Reason example above, allow you to select multiple values. Multiple values *within* a filter will return results if either (or any) of the conditions are met. In the example, the report would return a case if the Close Reason was either A or B.

See "Creating an OR Query" below for information on creating OR conditions between filters.

Filter Elements

All filters have some common elements. The Viewer Modifiable checkbox determines whether the filter can be seen when someone runs the report. You can uncheck this box to "hide" a filter from users when reports are run. Hiding filters should be used with caution. Report results can be confusing when you do not know which filters have been applied to a report. The Advanced checkbox is a deprecated feature you can ignore.

An exception to the advice not to hide a filter is when you are using the "Is Null" option. Most filters set to "Is Null" in edit mode will display "is not supported" in the filters section when someone runs a report. The filter will work, it's the display that isn't supported. Adding a note in the report Attributes can be helpful when any filter is hidden so people running the report understand the results.

Between Viewer Filter and Advanced is a Label text box that lets you change the text people will see when they run the report. Below that is a Category text box that lets you display filters in groups. You might for example enter "Citizenship Information" as the category for several filters related to citizenship to group them together when a report is run.

Filters appear in alphabetical order by filter name if you have not used the Category feature. If you have defined one or more categories, then uncategorized filters will appear first in the list, followed by the first category (with those filters sorted alphabetically within the category), followed by the second category, and so on.

Filters have different options depending on the type of data being filtered:
  • Lookup fields, like Close Reason above, show a list of the active items in that lookup. You can Ctrl-click on items in the list to select one or more items. You can also check the "Exclude Selected" box below the list to exclude the items you select. Many lookup field filters will also have a "Show Inactive" checkbox to allow filtering on inactive lookup values. As with all filters, your selections will be the default selections users will see when they run the report. They will be able to change the default selections (unless you have hidden the filter).

    NB: Filtering on inactive values works in edit mode, but not run mode (Ref: 69227).

  • Date fields have several helpful presets like Current Month, Previous Month, Current Year, Previous Year, etc. Using the preselects can save time in running certain reports. If you run a report at the beginning of the month for cases closed in the previous month, using the Previous Month filter will automatically select the previous month's dates each time you run the report.

    Date fields also have a "Custom Dates" option in edit mode. Currently, Chrome will display custom date boxes for editing with mm/dd/yyyy options. In Firefox, enter custom dates in yyyy/mm/dd format.

  • Numeric fields have filter options like Between, Greater Than, Less Than, etc. The "Between" data points are inclusive; for example "0 to 125" will include 125.

  • Text fields default to providing a filter that allows entering a list of comma separated values. Other options are "Contains", "Does not Contain", etc. "Contains" is typically the most useful (most easily understood by users). We recommend using the "Label" option to make it clear what type of search will done. For example, a 'contains' filter on the outreach Name field should have a label like "Outreach Name (contains the text)". 

Adding and Removing Filters

To remove a filter, click the pencil icon, then the "Delete" button in the properties window. Deleting a filter does not delete that field from the report.

There are two ways to add filters to a report:

1) Click the pencil icon on a column to open the properties window and Click the "Add Filter" button; or 

2) Scroll to the bottom of the Filters section and use the "Add Filter" dropdown to select a field. Only fields that have been added to the report will appear in the list, but they can be hidden or displayed fields.

Creating an OR Query

As noted above, a record will not be returned in a report unless it meets the conditions set in all filters, as indicated by the AND in the filters section. For example:


These filters will only return a case if the Date Closed is in the specified range AND the Legal Problem Code is Domestic Abuse AND Citizenship Status is one of the 2 values.

By dragging and dropping the Citizenship Status filter onto the Legal Problem Code filter, an OR condition is created:


These filters will return a case if the Date Closed is in the specified range AND either the Legal Problem Code is Domestic Abuse OR Citizenship Status is one of the 2 values.

Just as a filter can be dragged and dropped on to another filter to create an OR condition, a filter can be dragged and dropped out of a group to break the OR condition. In addition, the AND and OR boxes can be clicked to flip between the two states.

Columns

Columns, or fields, determine the data that will be displayed on a report by default, the order of the data in the data table, the fields that are available when the report is run, and how the data is sorted.

Hidden Columns

The Hidden Columns section at the top of every report lists all the fields that do not appear as columns on a report by default.


When a report is run, these columns will appear in Data Options in the list of Available Fields. To un-hide a column, click the pencil icon next to the field, change the Show attribute to Yes, then click Apply Changes.

Sorting Columns

To sort a report by the values in a column, click one of the triangles in the column header.


Click on the left triangle to sort ascending (A-Z, 0-9) or on the right triangle to sort descending. You can sort a report by more than one column. Multiple sorts are performed in the reverse order you apply them. For example, if you want a report to be sorted by Last Name, then First Name, you would click on the triangle on the First Name column, then click on the triangle on the Last Name column. To remove a sort, click a filled-in triangle.

We recommend enabling the "Show Ordering on Report Results" setting (on the Admin > Site Settings page) to display the sort order above the data table.

Re-Arranging Columns

To move a column, drag and drop the column heading.

The column order set in edit mode is the default arrangement users will see when they run the report. Users can re-arrange the columns using the Data Options section of the report as well as drag and drop.

Editing Columns

You can edit a column to rename it, hide it, change the format the data is displayed in, and perform other operations. To edit a column, click the pencil icon next to the column heading to display the properties for that column:

This window displays the default column name. In this example "Close Reason [lookup]: name". The Heading box below that lets you provide a more descriptive column heading for this report. Each column has default heading text, in the example, "Close Reason", but you could change it to something like "Reason Case Closed".

The Format options for a column will depend on the data type. For text fields, there are options like Phone Number, Zip Code, First Word, and Leading Numbers. For number fields, there are options like Round to 2 Digits, Money, Number without Commas, etc. For Date fields, you have options like Date Only, Year Only, Day of Week, etc.

First Word can be used to provide a compact display of a field like Close Reason. Close Reasons usually have values like "A - Counsel and Advice". If you apply the First Word format to a Close Reason column, it will only display "A".

Leading Numbers can provide a compact display for a field like Legal Problem Code that usually has values like "32 Divorce".

Number fields have a property not shown called Show Nulls?. By default, this option is not selected and null values in numeric fields are displayed as zeros. For example, if the Total Hours for Case field is on a report and a case has no time recorded against it, the field would display "0" instead of a blank cell. Select this option to display blank cells instead of zeros. If a column has an average set (see Breakdowns and Totals), records that display a "0" are counted in calculating the average; records that display nulls are not.

The Aggregate setting for a column is an advanced feature that is beyond the scope of this document. You will typically not need to set this option. (If you are curious, there is a video.)

The Show? setting has four options: Yes, Hide, Section, and Export to Multiple Excel Worksheets. In the example above, it is set to Yes, which means the column is displayed by default. If you want to hide a displayed column, change the Show setting to Hide, then click Apply Changes.

The Section option in Show will do two things: it will move the column to the left side of the report and group the report results by the column. If you Section on an Office column for example, the rows of the report will be grouped by office, and the name of each office would only appear once in the left column for each group of results. You can Section more than one column if you want multiple levels of grouping.

The last option in Show will create multiple worksheets if you export a report to Excel. If you select this option for an Office field, the Excel workbook created would contain a separate sheet for each office and that office's results.

The Width setting allows you specify a specific width for a column.

The Notes box allows you to enter any notes that might be helpful for other report editors, or yourself when you return to a report later.

After you make changes to the properties of a column, click on Apply Changes to save them, or Cancel Changes to discard them.

The Delete Column button will do what it says. Deleting a column also deletes any filter based on that column.

Adding Columns

At the top of each report is an Add Column link with a green plus sign in front of it. Clicking this link opens a window that displays fields that have been added to the report library on your site (see Report Library in the next section), followed by a standard list of fields. Check as many boxes as needed, then click on one of the Add buttons to close the popup window. You may need to refresh the report data to see the new column(s).

If a field you need is not available in the Add Column list, you can use the more powerful Add Fields link to access all the tables and fields that can be added to a report. Add Fields is covered below.

Report Library

The Report Library feature provides a way for each site to highlight commonly used fields so they are easy to add to reports. As noted above in Adding Columns, the Add Column link first lists fields that have been added to a site's library. When you add a field to the library, it also saves information about whether the field is displayed or hidden, the format applied to the field, and if the field is a filter on the report. If you commonly use a field as both a column and a filter, adding it to the library can save the time of adding the field and then adding the filter to another report.

To add a field to your site's library, open a report in edit mode that contains the field as a column (or add it to a report). Make the column displayed or hidden, whichever you think will be the most common in other reports. Optionally make the field a filter if you want the filter to be automatically added in other reports when you add the column. Click the pencil icon next to the field/column name. At the bottom of the window that opens is the Column Library section:

 
If this a case related field, leave the second button at the default Client Data. If it is a timekeeping related field, change the second button to Timekeeping Data. Click the Add To button to add the field to the library.

The Edit Library link at the top of each report lets you delete a field from your library if it was added by mistake, or if you want to replace it with different properties. The link opens a window that displays all the fields in the library. Check the box next to any field you want to delete from the library, then scroll to the bottom of the window and click the Remove Selected button.

Add Fields

The Add Fields feature gives you access to all the fields that can be added to a report, in contrast to Add Column which contains a limited set of fields. Clicking the Add Fields link will open a popup window:



Note: Do not add fields using both Method 1 and Method 2 in one Add Fields 'session'. If you add fields via Method 1, close the window to add the fields to your report, then re-open Add Fields to use Method 2, and vice versa.

The Method 1 section provides two ways to add fields.
  • The Field option is a searchable dropdown list of fields. Report writers who have also edited forms and profiles will recognize this feature. The list in Method 1 contains a large majority of the fields available, but report writers will need to use Method 2 for some fields, especially deeply linked ones.

  • The Form (fields only) option is a list of all Primary Forms from the module for the top level table. Picking a form from the list and clicking the Add button will add all field elements from that form to the report. Other elements (headers, instructions boxes, blocks (and the fields 'inside' of the block elements) are not included. 
Multiple fields can be added to a report in one Add Fields "session" using Method 1. Search and select a field then click the Add button. A message will appear at the top of the popup window indicating the field has been added. Search and select another field then click the Add button again, and so on. After all the fields you want have been added, use the "X" in the upper right corner of the popup window to close it. You may need to refresh the report data to see the new fields.

Method 1 is a quick way to add fields, but as mentioned above does not provide access to all the fields available on a report.

Using Method 2 of Add Fields requires understanding the structure of tables, subtables, and fields, and requires some knowledge of how data is organized in a relational database like the one used by LegalServer. For example, a field like "Date Closed" will appear in the field list of the Case Data table, but a field like "Date of Birth" will appear on the Case Data > Person subtable.

The Nuggets section below contains table path references for some commonly used fields.

Method 2 displays the "top level" table that the report is based on. In the example above, the top level table is Case Data. Other common top level tables are Timekeeping, Activities, Outreach (or Other Matters), etc.

A table contains fields, and may contain links to subtables of information. Each has a button to expand the information. Here is an example of what the window might look like after clicking both of the expand buttons:



 
This window shows that 3 of 295 available subtables have already been added to this report. Above those subtables is a dropdown list of all the subtables that can be added to the report using the Add button. To remove a subtable from a report, and any subtables and fields under it, click the red X icon.

The window next shows that 13 of 474 fields available on the top level table Case Data have been added. Note the word "case" in the small search box under Fields. This has limited the long list of fields contained in the Case Data table to only those fields that have the letters "case" in their name. You can enter any text in the search box to help find a field, or leave it blank to see all the fields a table contains.

Each field has a green icon that you can click to add that field to your report. Two of the fields shown have already been added to the report, as indicated by the red X icons next to the fields. Click the red X to remove a field.






You can make multiple changes during one Add Fields "session"; adding and deleting fields and subtables. The report will be updated after you close the Add Fields window. You cannot cancel or undo changes you make during an Add Fields session.

Lookup fields

Lookup fields merit special mention because they are so common in LegalServer, and because they illustrate the use of subtables.

This example highlights "Close Reason":


Note that "Close Reason [lookup]" is in the list of fields for the top-level Case Data table. Whenever you see "[lookup]" in a field name, that is a cue that you want to add the corresponding subtable to your report, then add the field from the subtable (as the example above shows). The lookup field will contain the ID reference number for the lookup values, not the descriptive text. In this example, the field would display numbers like 23 and 24, instead of "A — Counsel and Advice" and "B — Limited Service".

The proper field to add for some lookups will be named "Name", as in the example. Edit the column Heading value after you add such a field to your report to make it clear which field a column is displaying (see Editing Columns above).












Column Breakdowns, Unique Counts, and Totals

You can display breakdowns, unique counts, and totals at the end of a report by using the Breakdown, Unique, and Sum symbols that appear at the bottom of each column in edit mode. In the following example, a breakdown has been applied to Gender, Race, and Language, an Average on Age at Intake, and a unique count on Case ID:


Each of these icons is an on/off toggle. Click it once to turn it on, click it again to turn it off.

The information displays at the bottom of each page when you are viewing a report on screen, in either edit mode or standard mode. The information is for all rows in a report, not just the rows displayed on screen.

If your report has Sections, this information appears after each section (giving you subsection breakdowns, unique counts, and totals), in addition to the end of the report containing overall values for the entire report.

Breakdowns do not include a count of blanks for a column. The sum of the breakdown counts will not equal the row count if any of the records contain blank values in that column. In the above example, several records do not have a Gender. There isn't a breakdown that shows "(Blank): 61" (168 rows - 70 female - 31 male - 6 other = 61). Summations and crosstabs (see below) are helpful if you have missing data like this. Each will display an "n/a" row or column to make missing data stand out.

The Unique Count option provides a count of the unique values in text fields. The above example illustrates one way it can be useful. That report contains 168 rows, but many of the cases are repeated because it displays a row for the client and each household member. Therefore the report row count is not a case count. The unique count on case number ID allows one report to provide the detail for gender, race, and language counts, but also provide a case count.

Another common example is a report on time spent on cases during a range of dates. The row count on the report would equal the number of timeslips, with many (or all) case numbers repeated, once for each timeslip. Adding a total on the Time Spent column and a unique count on the case number column will let you answer how much time was spent as well as how many cases the time was spent on in one report.

Crosstabs and Summations

There are several different display formats you can add to a report in the Additional Display Formats section, in addition to, or in place of, the standard data table. If you expand this section at the top of a report you see a list of the possible formats. The options can vary depending on the features enabled for a site. The most common formats used, Crosstabs and Summations, are covered in this document. For charts, see Reports - Advanced Charts.

Summations present counts for one field. For example, total cases closed per office, total hours per timekeeper, total outcomes entered per outcome, and so on. Crosstabs present counts of two fields in a matrix, with one field as the rows and the other field as the columns of the matrix.

Click the Summations link in Additional Display Formats to add a new section to your report:



 
The Summations settings above would produce output like this on the report:



Click the Crosstabs link in Additional Display Formats to add a new section to your report:



The above settings would produce output like this:



Values to Count

Summations need a displayed numeric value to count. For a time report, this would typically be Time Spent. For a case report, you need a magic column that displays a "1" for each case. Your site might already have a "Case Count" or "Count for Totals" field available in Add Column. If it does not, add the "Database ID" field from the Case Data table to a report, then edit the column properties to set the Aggregate property to "Count" (and optionally use the Add To button to add the column to your report library). The Database ID field for timeslips, outreaches, assignments, etc., can be used if counts are needed for reports on those records.

Crosstabs can count a Database ID column directly, without needing to display it or aggregate it. The Value attribute in crosstabs can also Sum, Average, and provide a Unique Count of the value being counted. Unique Count can be useful on reports with repeated rows. For example, a report on time on cases will have one row per timeslip, and if displayed, the case number will be repeated on each row. If you want an accurate count of cases on that report, set the Value to Database ID (of the case) and the Operator to Unique Count. This will only count a case ID once, even it appears multiple times because a case has multiple timeslips.

Subsections

Summation settings have an optional Row Subsections setting. Crosstabs have Row Subsections and Column Subsections.

Summation row subsections are limited to fields that have a relationship defined within the database, for example Legal Problem Codes and Legal Problem Categories.

Crosstab subsections can be unrelated fields, for example Gender and Race as shown above.

Bins

Summations and crosstabs can use Bins to group numeric fields into rows or columns. A common use of this is with the Age at Intake field in the standard Age-Race crosstab report. Without bins, a field like Age at Intake would produce a row or column for each age in the report: 1, 2, 3, 4, and so on up to the oldest age returned by the report. To provide a compact and more useful report, you can enter the following text into either a row bin or column bin:

0-17,18-59,60+:60-999

This will group the results into 3 columns or rows. Used in a column bin, this would produce results like this:


 
The format for a bins statement is numeric ranges separated by commas. Each numeric range can be preceded by an optional label and colon. In the example, 60+:60-999, the "60+:" is the label shown and "60-999" is the numeric range of ages to put into that bin.

The 'upper' range in this example could be set to 60-100 instead of 60-999. If the data returned by the report contained an Age at Intake greater than 100, a new "n/a" column would appear. This can be used to identify records that likely have an incorrect DOB.

Numeric ranges in bins are inclusive for the end of a range. For example, if a bin statement is "0-125,125-200", a case with a percentage of poverty of 125.00 would fall into the first bin, while a case at 125.01 would fall into the second bin. More explicit ranges like "0-125,125.01" are recommended since they are easier to read and don't require knowing how ambiguous ranges are interpreted.

Sorting

Crosstabs have built in sorting. Summations have an optional Sort setting. If your summation is not sorting as expected, the field is likely also sorted in the data table. Remove the sort on the data table by clicking the triangle by the column heading for that field, or deselect the Sort option in the Summation settings. 

Sending Links to Reports

To send someone a link to run or edit a report, do not copy and paste the URL that appears as you are running or editing the report. That URL will include session information and will not open properly (because reports are run and edited in temporary sessions). To send a useful link, right click on the report name in one of the report lists and choose "Copy Link" (or your browser's equivalent). To send a link that will open into edit mode, right click the pencil or "Edit" link next to the report name.

A useful URL to run a report will look like this: "https://abc.legalserver.org/report/dynamic?load=138"

A URL to open a report in edit mode will look like this: "https://abc.legalserver.org/report/display/?load=138"

Designing Faster Reports

  • Use default pagination. Reports that return more than 20 rows are paginated by default ("1 - 20 of 3,333") and have an "All on One Page" link, both in run mode and edit mode. Report writers should generally avoid saving reports with the "All on One Page" option selected. The slowest part of displaying a report is building the HTML needed to display the rows in the data table. Displaying all rows isn't necessary to see column totals for all the data, or for summations and crosstabs to display counts on all of the data.

  • Avoid, or remove, unnecessary fields and tables. The report framework attempts to avoid processing columns that aren't displayed on any given run of a report, but there is always some overhead for each column and for each join created by subtables.

  • Apply sensible filter defaults. Avoid returning unnecessary rows each time a report is run. A "Closed Cases" report, for example, should have a Date Closed filter that defaults to a preset like "Previous Month" or even "Previous Year" instead of initially returning all closed cases. Likewise, time reports should have a Date of Service filter with a preset default, instead of potentially returning thousands or millions of rows.
     
  • Enable Nestloop option. The Attributes section contains a checkbox for "Enable Nestloop". The box is checked by default. There is no bright line rule on when disabling nestloop might improve performance of a report. Like other settings within Attributes, you must click the Apply button within that section after changing nestloop, and ultimately click on "Save Changes" to permanently save the change. If a particular report has taken over 5 minutes to process, a prompt about trying to reload the report is displayed. The code behind that prompt is flipping the nestloop option in the background.

  • Run on Open. Unchecking this option does not make a report run faster, but is useful if people running a slow report will typically change the filters each time they run it. Unchecking this option avoids the initial delay of building the data table, allowing people to change filters, then click on the "Run Report" button. Check or uncheck this option in the Attributes section of a report. 

Naming and Organizing Reports

A recurring discussion on the siteadmins mailing list is how to organize reports, both for end users and report writers. Here are some suggestions that have been posted.

Note: Do not use backslashes ("\") in report names.

The Favorites Preference is a good choice for end users who frequently run a subset of reports. This per-user preference adds a default filter to the report lists so the person viewing only sees reports they have tagged as a Favorite (using the heart icon).

To set this preference in the new interface, users float their mouse cursor over their name in the upper right corner of any page, then go to the System Preferences page. In the old interface there is a "Preferences" link in the upper right near the red "Search" link.


Descriptive names make the search box on report lists in the new interface 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. 

Nuggets

The Path information refers to the table structure in Add Fields, 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.


Addresses

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.

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). 

Adverse Parties

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.


Age

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.


Assets

There is no 'one row per asset' table as there is for income. The Case Data > Types of Assets field displays a concatenated list of assets types. The Case Data table also contains fields like Total Assets.


Assignments

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 the probono advocate currently assigned to the case, including "Full Name, Last Name First (filterable)".

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 assignments 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

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.


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

A site can have two types of custom fields - site specific fields and custom system fields (created by PSTI) that only apply to particular sites.

Site specific fields that administrators create under Admin/Site Specific Field Management are on the Case Data > Custom table, except site specific lookup fields which will typically appear as subtables directly under a top level table, and have " [Custom Lookup] " at the end of the table name.

Custom system fields that only apply to particular sites are on the Case Data > Extended Matter table. This table will only exist if there are custom system fields.

Similar subfolders will exist for other top level tables (Outreach, Timekeeping, etc.) that contain site specific or custom system fields.


Date Open Range

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. Rejected matters 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, like 12/31/2013 - 12/31/2013.

Date Open Range is on the subtable Case Data-> Case 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.


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.


Income

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. 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.


Matters - Limit Results Based on the User Running the Report

You can limit the matters/cases a report returns to those of the user's office, program, and or assignment.

Office - use the "Viewer's Office" checkbox in an office filter

Program - use the "Viewer's Program" checkbox in a program filter

Assignment - Add the Case Data > Additional Assignments (One Row Per Assignment) > Assigned To > Database ID field as a filter. Set it to "Is the User Viewing this Report". This will return one row for every assignment. The report can be limited further by filtering on a specific assignment type from the same Additional Assignments table. If Jane did the intake and is the primary assignment on a case, the report will show both rows. Filtering on Assignment Type = Primary, for example, would only show the one row.

(Untested): Primary Advocate - Add the Case Data > Primary Assignment > Advocate > Database ID field as a filter. Set it to "Is the User Viewing this Report".

Related: For time, see below: Time - Only Show Timeslips for User Running the Report


Most Recent Things Done on a Case

Case Data > Matter Last Service -- service here means a timeslip

Case Data > Most Recent Case Status Update

Case Data > Most Recent Note Time > Most Recent Note

Related: Case Data > Matter Has An Incomplete Task > Incomplete Task List


Names

The Case Data > Person table contains the various fields that make up a client's name. This table also contains the "Organization Name" field, which displays the name of group clients.

There is also a "Full Person/Group Name (Last First)" field that is useful for reports that mix individual and group clients. It will display the organization name for a group client or the last and first name for an individual client.

The Case Data > Aliases > Alias field displays all additional names for a client in one cell on a report.


Multi-Select Fields

Some lookup fields allow selecting multiple values by holding the Ctrl or Cmd key and clicking. Reports display these fields as a comma separated list of values in one 'cell' in the data table.

For example, if two values were selected for Senior Characteristics, the report row for a case would look like this:



NB: Unless a 'one row per value' table has been built for a multi-select lookup field, you can only get a count of individual values by filtering on that field, selecting one of the values, running the report to get a count, then changing the filter to the next value, running the report to get a count, etc.


Notes

Notes Data (One Column Per Type)
This subtable provides a list of fields, one field for each note type, such as Prescreen Notes, Intake Notes, Financial Notes, etc. The list of fields will vary for each site because administrators can create, edit, and merge/delete note types in Admin/Lookups/Note Type. This subtable will produce one row per matter, with each note type displayed as a column.

Case Notes - One Row per Case
This subtable will produce one row per matter, concatenating all the notes on the matter into one cell in the data table. It provides two fields, Note Count and Combined Notes.

Case Notes - Multiple Rows per Case
This subtable will produce a row for each note on a matter. It provides a list of fields such as Subject, Body, Date Posted, etc. It also provides a Note Type [Lookup] subtable that contains the Note Type field.

Case/Service Notes - Multiple Rows Per Case
Allows reporting on standard case notes as well as case notes produced from timeslips.

Formatting Note Body Columns
Report columns that contain the body of notes can often benefit from the Format options "Strip HTML" and "Preserve Newlines" (available in column properties).

Email Address(es) a note was sent to
Although the email address(es) a note was sent to (or more precisely, what was typed into the Recipients box) is displayed in the body section of the note when viewed on a case, the "Note emailed to" information does not display in the Body field on a report. There currently isn't a way to report on these email addresses.


Outcomes

Case Data > Outcomes > Outcome provides a concatenated list of outcome information on a case in one cell. Example: "Child Support: $100.00 (01/01/2010), Alimony: $200.00 (12/21/2011)". Useful on reports that need to display outcomes in a single row per case format. If this Outcome field is used as a filter, the Comparison Type option must be set at "Is any of substring".

Case Data > Outcome [Multi Lookup] > Outcome provides a comma separated list of all outcome names on a case in one cell. Example: "Child Support, Alimony". Useful on reports that need to display outcomes in a single row per case format.

The Case Data > Outcomes Split table produces a one row per outcome report. It provides subtables and fields that allow reporting on each outcome's name, category, date, frequency, amount, value, etc.


OutcomesV2

Case Data > OutcomesV2 will only appear if your site has this feature enabled. This subtable contains multiple fields and subtables.


Primary and Secondary Funding Codes

Note: The standard Case Data > Case Funding Code table should not be used on sites using primary and secondary funding codes.

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. 

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. 

Pro Bono Users

A report based on the top level System Users table has some fields you might find helpful:

System Fields: Is Probono - can be used as a filter to only report on pro bono users.

System Users > Connection to Most Recently Closed Case > Latest Closed Case
This table has the Date Closed and other information about the last case closed that a user was assigned to.

System Users > Current Organization Affiliation > Organization Information: Organization Name (and other fields)

System Users > Donations (Total): Total Amount (and other fields)

System Users > User Total Cases
This table has 3 fields: Total Number of Cases, Total Number of Open Cases, and Total Number of Closed Cases


Pro Bono Assignments

The following tables on a report based on the top level Case Data table contain information about pro bono assignments on cases. Note that "currently assigned" means the assignment does not have an End Date.

Case Data > Current Probono Assignment
This table contains several fields related to the probono advocate(s) currently assigned to the case, including "Full Name, Last Name First (filterable)". This table will produce one row per current pro bono assignment.

Case Data > Current Probono Assignment > Assigned To > Contact > Current Organization Affiliation > Organization Information
This table contains information about the organization, if any, the pro bono user is currently affiliated with, including Organization Name.

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 > All Pro Bono Assignments
Contains three magic fields. Pro Bono Count is the number of pro bono assignments ever made on a case (whether current or ended). Pro Bono Name is a concatenated list of all the pro bono users ever assigned to a case. Pro Bono User Detail contains a concatenated list of the pro bono names and assignment start and end dates in one cell. Example: "Abel, Jane [07/02/2014-], Barrister, Bob [02/28/2012-06/04/2013]".

Case Data > Additional Assignments (One Row Per Assignment)
As the name implies, using fields on this table can return multiple rows per case. Add the Assignment Type subtable and filter on pro bono assignments to only get those assignments.


Site Specific Fields

Site specific fields that are not lookup fields will appear on a subtable called Custom in reports. For example, a site specific field for the case module will appear in the Add Fields window on a report under the "Case Data > Custom" subtable. Site specific lookup fields will be under the top level table with a "[Custom Lookup]" designation, for example "Case Data > My New Field [Custom Loookup]".


Time - Cases/Matters

* Total Time on Case field - On the top level Case Data table. The total time on the case regardless of when the time was entered. Useful because it will not cause a report to have multiple rows per case.

* Individual Timeslips on a Case - Use the Case Data >Timekeeping subtable. Guarantees that the report will only contain timeslips linked to cases/matters (no chance of pulling in outreach or other time). Allows reporting on time spent during a range of dates, as opposed to all time on a case (how much time was spent last month on open cases). Using fields from this table will result in one row per timeslip per case. To maintain a one row per case report, apply the Sum aggregate to the Time Spent column, and the Min aggregate to any other field used, like Date of Service, and the Min aggregate on the hidden Timekeeping: Database ID column that will be automatically added to the report.

* Date of most recent timeslip on a case - Case Data > Matter Last Service > Service Date.

Time - All Timeslips

Time linked to cases, outreaches, or neither, and the 'pro bono' variants if used.

Report using the top level Timekeeping table.

To get the caseworker name: Timekeeping > Caseworker > Caseworker Name. The subtable also has the "Is Probono" field, allowing the report to filter for only time spent by pro bono users.

Timekeeping > Cases subtable to pull in information from the matter a timeslip is linked to.

Timekeeping > Outreach subtable to pull information from the outreach a timeslip is linked to.

Timekeeping > Combined Outreach/Case Information to display in one cell either the case number or the outreach name a timeslip is linked to, if any. Easier than adding both the Cases and Outreach subtables if other detail isn't needed. Using both subtables would also result in a column for case numbers and a column for outreach names.

Time - Pro Bono Time

Time can be considered 'pro bono time' based on various criteria, and depends on how an organization chooses to configure its timeslips.

The Probono Time field is set to Yes 'in the background' when a timeslip is created from an "Add probono time" link using the static timekeeping processes and forms. Most sites use dynamic forms, so this field doesn't apply unless it has been added to those forms.

The PAI Time field can be selected by the user (pro bono or staff) if the timeslip form is configured to display it. This is the recommended setup because it provides the most flexibility in reporting on pro bono time. PAI (Private Attorney Involvement) is the acronym used by many organizations to refer to work done by volunteers or by staff in supporting volunteers.

There is a Probono Time field on the top level Case Data table. This field gives the total pro bono time recorded for a case, where 'pro bono time' is the time entered on pro bono timeslips.

Focusing on the caseworker the time is entered for, there is the Timekeeping > Caseworker > Is Pro Bono field.

Time - Reporting on Missing Time

If you need to report on users who have not recorded time, or recorded less than a certain number of hours, for any given date during a range of dates, you can build the following report. Alternatively, you can download the XML file and import it to your site.
  • Create or edit a report that is based on the System Users top-level table.
  • Click on Add Fields and add the following fields and sub-tables:
  • (Optional for filters) System Users: Active, System Users: Current, and System Users: Is Probono
  • System Users > Person: Full Name (Last, First)
  • System Users > Users by Days: Date
  • System Users > Users by Days > Timeslip Entries for Day-User Combination: Time Spent
  • Add other fields as desired
  • Edit the properties of the Time Spent column and set Aggregate to "Sum".
  • Sort the report by the Date column.
  • Add the Time Spent field as a filter, set it to "Is less than", enter a number of hours, for example 7, and check the "Apply After Aggregate" box.
  • Add the Date field as a filter and set it to a common range of dates like "Previous Week" or "Past 7 Days".
  • (Optional) Add the day of the week to help distinguish weekends. Click on "Add Fields", expand the Users by Days table and add the Date field. Edit the properties of the column and set Format to "Day of Week".
  • Group the report by user name. Edit the properties for the name column and set Show to "Section" 

Time - Only Show Timeslips for User Running the Report

The "My Time" links that may be enabled for the main timekeeping page use a hidden filter to run the "My Time Today" and related reports, showing timeslips only for the user running the report. To add this filter to other reports, add the Timekeeping > Caseworker > User Data > Database ID field to a report. This will automatically add a filter that looks like this:


Select "Is the User Viewing this Report".