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.
Creating New Reports
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.
Add New Report Link
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.
"Save Report" Button for a new Saved Report
When you run a report (not in edit mode), there is a Save Report button next to the Run Report button. The Save Report button makes a copy of the current report settings — not the data. The copy will automatically appear on the Saved Reports tab of the main Reports page. A common use of this feature is to save a copy of a report with changes to one or two filters and the default columns that are displayed. For example, you might open a Closed Cases report, select the legal problem codes and funding code that apply to a particular group of cases, adjust the columns that are displayed, then save that as Closed Cases - Domestic Violence Group.
The Save Report button can be disabled per user role by unchecking the Show Save Report Button permission for any desired role on the Admin > User Roles (Permissions) page.
"Save As" Link for a new Base Report
The "Save As" link is available in edit mode. It will save a copy of the current report as a new Base Report, so the report will appear on the Base Reports tab (as opposed to the Saved Reports tab if you used the Save Report).
Importing an Exported Report
You can export the structure of a report in LegalServer to an XML file. Another LegalServer site can upload, or import, the XML file to recreate that report on that site.
To import one of these files, you need to get the file and save it to your computer. Then click on the top level Reports tab and use the "Import Reports" actions menu link. Use the Browse buttons on the import page to upload one or more reports.
To export a report to an XML file, go into edit mode on the report, click the "Export to File" link at the top of the page, then save the XML file to your computer. You can send or transfer this file to someone like any other file.
See Example Reports for several examples you can download and import to your site.
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.
Attributes (Rename, Mark inactive, etc.)
The Attributes section lets you rename a report, set it to inactive (so it will not appear in the reports lists by default), determine if the report will immediately return data when run, and select which roles can view the report. You can also set a category and grouping if you want to use the filters on the various Reports tabs. You can also edit the notes, if any, that display at the top of the report.
After you make any changes in the Attributes section, click the Apply button that appears at the end of the section, then the Save Changes link at the top of the screen.
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:
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.
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.
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:
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:
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.
Column Breakdowns, Unique Counts, and Totals
You can display breakdowns, unique counts, and totals at the end of a report by using the Breakdown, Unique, and Sum symbols that appear at the bottom of each column in edit mode. In the following example, a breakdown has been applied to Gender, Race, and Language, an Average on Age at Intake, and a unique count on Case ID:
Each of these icons is an on/off toggle. Click it once to turn it on, click it again to turn it off.
The information displays at the bottom of each page when you are viewing a report on screen, in either edit mode or standard mode. The information is for all rows in a report, not just the rows displayed on screen.
If your report has Sections, this information appears after each section (giving you subsection breakdowns, unique counts, and totals), in addition to the end of the report containing overall values for the entire report.
Breakdowns do not include a count of blanks for a column. The sum of the breakdown counts will not equal the row count if any of the records contain blank values in that column. In the above example, several records do not have a Gender. There isn't a breakdown that shows "(Blank): 61" (168 rows - 70 female - 31 male - 6 other = 61). Summations and crosstabs (see below) are helpful if you have missing data like this. Each will display an "n/a" row or column to make missing data stand out.
The Unique Count option provides a count of the unique values in text fields. The above example illustrates one way it can be useful. That report contains 168 rows, but many of the cases are repeated because it displays a row for the client and each household member. Therefore the report row count is not a case count. The unique count on case number ID allows one report to provide the detail for gender, race, and language counts, but also provide a case count.
Another common example is a report on time spent on cases during a range of dates. The row count on the report would equal the number of timeslips, with many (or all) case numbers repeated, once for each timeslip. Adding a total on the Time Spent column and a unique count on the case number column will let you answer how much time was spent as well as how many cases the time was spent on in one report.
Crosstabs and Summations
There are several different display formats you can add to a report in the Additional Display Formats section, in addition to, or in place of, the standard data table. If you expand this section at the top of a report you see a list of the possible formats. The options can vary depending on the features enabled for a site. The most common formats used, Crosstabs and Summations, are covered in this document. For charts, see Reports - Advanced Charts.
Summations present counts for one field. For example, total cases closed per office, total hours per timekeeper, total outcomes entered per outcome, and so on. Crosstabs present counts of two fields in a matrix, with one field as the rows and the other field as the columns of the matrix.
Click the Summations link in Additional Display Formats to add a new section to your report:
The Summations settings above would produce output like this on the report:
Click the Crosstabs link in Additional Display Formats to add a new section to your report:
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:
Values to Count
Summations need a displayed numeric value to count. For a time report, this would typically be Time Spent. For a case report, you need a magic column that displays a "1" for each 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:
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.
Exporting Data from a Report
Sending Links to Reports
Designing Faster Reports
Naming and Organizing Reports
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.
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.
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:
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.
Aggregating (Collapsing Repeated Rows)
Clickable Case Number Links in Excel
The Matter/Case ID# column in reports displays a nicely clickable link to each case. But if you export the report to Excel, the underlying URL doesn't come along. There are various solutions for this. Here is one that was posted to the Siteadmins mailing list:
Add the case's Database ID to your report, in addition to the case number, preferably as the final column.
In Excel, in the column immediately to the right of the database ID, enter this formula, replacing the URL and cell column/row with your own info:
=HYPERLINK(CONCAT("case profile url minus the last digits",DATABASEID1))
as an example, it would look like this:
Then extend the formula through the remaining rows of the column.
Thanks to SV at LAFLA for this contribution.
Contract Term Allocations on Timeslips
Date Open Range
Docket Number / Court Case Number
Family Member / Household Information
User-Friendly Filters on Funding Codes/Grants
Primary and Secondary Funding Codes
Literal Text and Literal Number Fields
Matters - Limit Results Based on the User Running the Report
Matters - Limit Results to the Case the Report is Embedded On
Most Recent Things Done on a Case
Notes - Limit Display of Case Notes to Those Entered by the User Viewing
Number of Days a Case Is/Was Open
Original Date Opened
Payroll Leave Balances
Pending Case Transfers
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.