How to build and edit reports.
Your user role needs the "Reports Management" permission to do this. If you don't see an Edit Pencil for each report in the report lists, your user role doesn't have that permission.
If you just want to learn about running existing reports see Running Reports.
See also:
There are 4 ways to create a new report:
"Add New Report" link on the main Reports page.
Using the "Save Report" button when running a report (not in edit mode)
Using the "Save As" link in edit mode.
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.
To start creating a new report from scratch, 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.
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.
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).
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.
You can't delete reports, but you can mark them inactive so they don't show up in the various lists by default. You can also optionally rename a report at the same time. See the Attributes section below.
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 (now called Advanced Add Columns), and Edit Library links are covered later.
The Automatically Refresh Results checkbox has been removed.
Below the report name are several expandable sections. Click on each section name to expand it and see the options it provides.
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:
LegalServer has the ability to restrict user access to certain IP Addresses. See Restricting Access to LegalServer by IP Address. This means that if a user is connecting from an unspecified location, they will have limited access to LegalServer based on their user role. You can use a similar methodology to restrict reports on a case-by-case basis from being run if a user is accessing LegalServer from an unspecified IP Address. This is useful if you want to ensure that certain reports containing personally identifiable information or other sensitive data aren’t being run in court hallways, airports, internet cafes, or other public areas.
To use this feature, you’ll first need to define your internal/external IP address ranges. Follow along with the instructions here Restricting Access to LegalServer by IP Address
Once you’ve completed that task, you’ll need to enable the sitewide setting to allow reports to be restricted by IP range under the Admin > Site Settings page.
You now have the ability to restrict reports individually by IP range. To do so, navigate to the Attributes of the report you want to restrict and check the Disallow Access from External IP Range option.
Note: This also affects API calls for that report from the Reports API.
Set the Print Orientation of the exported Excel sheet to either Landscape or Portrait.
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. Hide filters 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 to not hide a filter is when you are using the "Is Null" or "Is not Null" options. Most filters set to either option in edit mode will display "is not supported" (or "Is not null not supported" or similar) in the filters section when someone runs a report. The filter will work, it's the display that isn't supported (Ref: LS-44629). 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 in run mode. You can "group" the display of filters in run mode using the Category feature. If you define 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.
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 Filter
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 (or OR query if you prefer) 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 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, 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.
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.
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 highly recommend enabling the "Show Ordering on Report Results" setting (on the Admin > Site Settings page) to display the sort order above the data table.
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.
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 window.
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. A text field that contains a URL (like https://abc.example.com) can be made a clickable link with the "Clickable Link" format. 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.
Leading Numbers can provide a compact display for a field like Legal Problem Code that usually has values like "32 Divorce".
The Percentage of Total format option is often use on a Time Spent column to show a breakdown per user of how they are spending their time between case, outreach, and other time. If used, the column so formatted needs a column sum set. See the Column Breakdowns, Unique Counts, and Totals section below.
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".
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.
There is a special Format option called "Replace Null and Not Null With Value". With that format option selected, the column will display what you put in the Null/Empty and Not Null/Empty text boxes below it (not shown in the screenshot). Example use: A "Type of Abuse" column could display "Data Collected" or "No Data Collected", instead of showing the specific type of abuse lookup values. Your imagination is the only limit.
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.
Add Column
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 Advanced Add Columns link to access all the tables and fields that can be added to a report. Advanced Add Columns 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.
NB: Custom fields often do not play nice in the report library. We recommend only adding system fields to the library to avoid errors when using Add Column. Use Advanced Add Columns to add custom fields to reports.
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.
The Advanced Add Columns feature (formerly called "Add Fields") 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 Advanced Add Columns link will open a popup window:
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 Advanced Add Columns "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 Advanced Add Columns 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:
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.
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.
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.
You can make multiple changes during one Advanced Add Columns "session"; adding and deleting fields and subtables. The report will be updated after you close the window. You cannot cancel or undo changes you make during an Advanced Add Columns session.
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 lowercase el "[lookup]" field will display ID numbers like 23 and 24, instead of human compatible text like "A - Counsel and Advice" and "B - Limited Service".
Lookup subtables on older reports may only offer you the "Name" field. You can delete and re-add the subtable to get the field like, in the example, "Close Reason". Or just use the "Name" field and edit the Heading value after you add it to the report.
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.
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:
NB: If you export a crosstab to Excel, the Title field text will be truncated to no more than 31 characters. Do not include special characters like "/" or "\" in the Title field.
The above settings would produce output like this:
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 row. Your site might already have a "Case Count" or "Count for Totals" or "Row Count" field available in Add Columns. If not, go into "Advanced Add Columns". Under Method 2, expand the Fields list under the top level table and add a "New Literal Numeric Field". After you close that window, refresh the report data. Edit the new column, optionally change the Header to something like "Row Count", then put a "1" (without the quote marks) in the Expression box. Then "Apply and Refresh". You can now use that column to count in your summation(s) on the report.
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.
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.
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.
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.
Every report displays an Excel icon to allow exporting the data into an XLSX file.
Report writers can add an option to a report to allow downloading a CSV (comma separated values) file instead. CSV files typically download faster when a report contains thousands of rows, and are generally easier to work with when the goal is to put the data into another database.
The CSV option is added to a report in the Additional Display Formats section. A report with this option enabled displays "Click here for CSV Output" just above the data table when it is run. If you add this element to a report, we recommend clicking the Edit pencil in the CSV box and checking the "Headers in First Row" option.
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 may not open properly (because reports are run, and edited, in temporary sessions). To send a robust 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 Edit pencil and copy the the link.
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"
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.
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.
Enable Nestloop option. This option being on or off is unlikely to affect the speed of reports these days. Suggestion: Skip this section. 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.
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).
Users can float their mouse cursor over their name in the upper right corner of any page, then go to the My Preferences page.
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 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.
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))
as an example, it would look like this:
=HYPERLINK(CONCAT("https://lafla.legalserver.org/matter/dynamic-profile/view/",B1))
Then extend the formula through the remaining rows of the column.
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.
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.
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)
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.
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.
You can limit the matters/cases a report returns based on the user running the report.
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.
Primary Advocate - Add the Case Data > Primary Assignment > Advocate > Database ID field as a filter. Set it to "Is the User Viewing this Report".
Service County(ies) of User's Office - Only useful if service counties are selected for offices on the Admin > Offices page. Add the Case Data > My Service Area (Service County of User's Office) > In My Service Area field to a report, then add that field as a filter and set the filter to Yes. Optionally hide the filter if people running the report shouldn't be allowed to change it.
Supervisor of the Primary Assignment - For a list of cases someone is supervising. Only useful if user records are setup with the Supervisor block. Add the Case Data > Primary Assignment > Advocate > Supervision - Supervisee > Supervisors (One Row Per Supervisor) > Supervisor > Database ID field and set it to "Is the User Viewing this Report".
Related: For time, see below: Time - Only Show Timeslips for User Running the Report
The Report Part block has a "Restrict to Case/Matter" configuration option to limit a report's results to the case the report is embedded on.
Case Data > Matter Last Service -- service here means a timeslip. NB: Fields from this subtable can make a report quite slow (Ref: 82652)
Case Data > Most Recent Case Status Update
Case Data > Most Recent Note Time > Most Recent Note
Case Data > Activity (Latest) -- warning: pulling fields from this subtable can make a report slow to load. Ref: 87135
Also see: The "Cases/Matters Days Since Most Recent Note, Timeslip, Activity, or Service" report on the Example Reports page.
Related: Case Data > Matter Has An Incomplete Task > Incomplete Task List
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.
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 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.
Confidential Notes
The notes field on timeslips are often labeled "Confidential Notes". See the above table.
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.
Email Address a note was sent from
An email sent to a case (or outreach, etc.) creates a note. The Posted By user is always System User. The first line of the note is who sent the email, for example, "From: Jane Smith <janesmith@abclegal.org>". You can filter on the note Body field, and set that filter to a "Contains" filter.
Possibly useful to embed on, for example, a Pro Bono View of a case. Show the pro bono user all the notes they entered on a case, but not notes entered by staff/others.
For only case notes: Add the Case Data > Case Notes - Multiple Rows Per Case > Posted By (User) > Database ID field.
For case notes and notes from timeslips: Add the Case/Service Notes - Multiple Rows Per Case > Notes > Posted By (User) > Database ID field to the report.
Both fields automatically add a filter that gives you the "is the User Viewing the Report" option.
Case Data > Amount of Time Open > Open Time in Days.
Case Data > Original Open Date > Original Date Opened. Uses the disposition log to figure out the first date that a case was opened (useful if a case is opened, closed, then re-opened).
The classic/original outcomes. See the next section for Outcomes V2.
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 (one row per outcome) 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. Useful when you need to count outcomes and/or total values.
The Amount field is the number entered on the form by the user. The Value field is the annualized amount for outcomes with a monthly frequency. So if the user enters Child Support, Monthly Recovery, $500, the Amount field will display 500 and the Value field will display 6,000.
Older reports may have a Case Data > Outcomes Split subtable. If you need to add or remove fields, we recommended deleting this subtable and using the one row per subtable above.
Case Data > Outcomes V2 (One Per Row) contains multiple fields and subtables where you can pull as much detail as needed.
Older reports may have a Case Data > Outcomes V2 Split subtable. If you need to add or remove OV2 fields, we recommended deleting this subtable and using the one above.
A common question is how to make outcomes appear in columns instead of multiple rows per case. The short answer is you can't, at least not in the way you may be thinking. This is not possible:
O1 Group | O1 Type | O1 Date | O1 Amount | O2 Group | O2 Type | O2 Date | O2 Amount | O3 Group ... etc.
If you need a total of the Amounts per case, you can do that by putting the Amount column on the report and applying a Sum aggregate (and aggregating the corresponding Database ID in Hidden Columns to Min).
You can use the Lookup Transform Index feature to, for example, pivot Outcome Group lookup values into columns. Can be useful for counting, or just display purposes for some funders. Note well, Virginia, this will not be a one row per case report, but still a one row per outcome report.
These subtables are only useful if you have the Payroll Leave Balance module enabled. Reports with these subtables will show all users' leave time. Adjust the Roles Allowed to View report permissions as desired.
These subtables provide the net number of leave hours currently remaining.
Timekeeping > Report Current Leave Time Sick > Net Hours
Timekeeping > Report Current Leave Time Vacation > Net Hours
Timekeeping > Report Current Leave Time Additional > Net Hours
Timekeeping > Report Current Leave Time Other > Net Hours
Timekeeping > Report Current Leave Time Personal > Net Hours
Timekeeping > Report Current Leave Time Holiday > Net Hours
If your site has Electronic Case Transfers enabled, you'll have the ability to create reports that utilize the top level table of Pending Case Transfers. These are reports for received Electronic Case Transfers. This includes details about the Case, LPC, County, Notes, the Date of Request, and the Originating Agency.
If you want to have some additional details enabled, please file a ticket asking for the Pending Case Transfers subtable to be enabled on the Pending Case Transfers top level table. This will add fields for the Applicant's Name, SPLC, Emergency, and Time of Day for the preferred response.
Once a case has been accepted, details about the original transfer can be found on the Case Data -> Inbound Transfer Information subtable.
If your site has the Address Validation/GIS module, the Congressional District, State Legislature District Lower, and State Legislature District Upper data are all lookup subtables under Case Data.
If enabled, Census Tract is a lookup subtable under Case Data > Person > Primary Home Address > Address. Other political boundaries on your site may also be under the Address subtable.
Not technically a part of GIS, your site may have lookup subtables under Case Data > Extended Matter Fields like "Borough" that are political boundaries in real life, but are set by users manually. As well, some sites add custom fields and lookups for boundaries; those live under Case Data > Custom.
Case Data > Application Office > Name
You almost certainly want to change the column heading after adding it to your report.
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 lastest 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
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.
The System Users > Username field displays the username as a clickable link.
The name of the person (or people) who supervise the user you are reporting on:
System Users -> Supervision - Supervisee -> Supervisors (One Row Per Supervisor) -> Supervisor -> Person -> Full Name (Last, First)
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. This may slow down your report.
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.
NB: The above is not currently giving a nice filter with a list of user names when newly added to reports. For now, you can get a nice filter by using the Timekeeping > Caseworker > User Data > Person > Full Name (Last, First) column. (Ref: 89497)
Timekeeping > Cases subtable to pull in information from the matter a timeslip is linked to. If you really only want timeslips on cases, start over with a Case Data report and use the Case Data > Timekeeping subtable.
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 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.
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 from the Example Reports page and import it to your site.
Create or edit a report that is based on the System Users top-level table.
Click on Advanced Add Columns 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 "Advanced Add Columns", 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"
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".
This subtable can do two things that may take you by surprise - even if you are not pulling any fields from it - just the presence of the subtable can:
Cause your one row per case report to produce multiple rows.
Cause your case report to return no results, or limited results, for users whose role does not have the "View Other Users' Time" permission.
