Editing and Adding Report Columns

Columns determine the data that will be displayed on a report, other columns that are available to be displayed, and how the data is sorted.

In this Article:

• Hidden Columns
• Sorting Columns
• Re-arranging Columns
• Editing Column Properties
• Formatting Columns
• Add Column Link
• Advanced Add Columns Link
• Column Breakdowns, Unique Counts, and Totals

Hidden Columns

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

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

Sorting Columns

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

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

If you don't see the "Ordering:" line above the data table, enable that by checking "Show Ordering on Report Results" setting on the Admin > Site Settings page.

Re-Arranging Columns

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

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

Editing Column Properties

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

Formatting Columns

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

Clickable Link is only useful if the text in the column contains a URL (like https://abc.example.com). Often used with the Website field from Organization records.

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

Length Limited Text will shorten the display of long blocks of text such as the Body of case notes and add an ellipsis. NB: The shortened text is what will print, and export to Excel or CSV.

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 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 if the data is exported to Excel or CSV.

The Notes box allows you to enter any notes that might be helpful for other report writers, 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 Link

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. 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 "Case Data" (previously "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 Link

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 (but limited) 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.

Method 2 displays the "top level" table that the report is based on. In the example below, 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. One of the fields shown has already been added to the report, as indicated by the red X. Click the red X to remove a field.

The window shows that 1 of 679 fields available on the top level table Case Data have been added. Note the word "open" 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 "open" 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 4 of 370 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

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 friendly 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 most columns 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 run 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 and some numeric 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.