Creating New Reports
Add New Report Link
"Save Report" Button for a new Saved Report
"Save As" Link for a new Base Report
Importing an Exported Report
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 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.
Restricting Reports to an IP Range
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.
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.
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 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 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, 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.
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.
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.
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:
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".
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 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.
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 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".
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.
Advanced Add Columns
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:
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":
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.
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:
Some lookups will not show you a field like "Close Reason", but a field called "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).
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".
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: