Report Columns

Categorize report data with columns

Information shown on a report is specified by the fields and formatting options chosen for the columns. 

Fields can be added to a report as columns by selecting the desired field from the Report Fields list and dragging it into the Columns pane. The fields within the Columns list will be prioritized from top to bottom and will display from left to right when viewing the report. Simply click and drag to reorder the columns.

Fields can be removed from a report within the Report Builder by hovering over the field and clicking the X icon.

For a complete list of available fields and their descriptions reference the Report Field Description Guide


Follow the steps below to add a column(s) to the report.

  1. Select Reports > Builder.

  2. Use the Show All button to view all custom reports. Or use the Search Bar to further drill down your search.

  3. Select the Reports from the list. 

  4. In the Report Fields pane

    1. Expand the Report Field category folder you wish to begin building the report off of 

    2. Or use the Search for Fields to search for fields in all categories.

      When using the search feature, the list will filter a list of categories that have a full or partial match related to the text entered. Expand the category by clicking on the folder to see the fields that match your search.    

  5. Drag and drop the field into the Columns pane.

To rename a column, simply right click on the report field after moving the field from the Reports Field pane and select Change Name.


Format report data

Formatting options are available for Date and Money fields. Where applicable, follow the steps below to change how the value is displayed on the report.

  1. Select Reports > Builder.

  2. Use the Show All button to view all custom reports. Or use the Search Bar to further drill down your search. 

  3. Select the Report from the list. 

  4. Right click on the field after it has been dragged onto the Columns pane and select the desired option from the Format menu list.


Date Field Formats

Date fields allow you to display date and time serial numbers according to the type that you specify. Date fields that display time stamp information (hours, minutes, and seconds) along with the date information only display the first four formatting options for the date. The timestamp portion of the field will always show as "HH:MI:SS".

Money Field Formats

Money fields display monetary values and the default currency symbol with numbers. You can specify the number of decimal places that you want to use, whether you want to use a thousand separator, and how you want to display negative numbers. 

Protected Health Information Field Formats

Social Security Numbers, by default, are masked preventing the first six digits from being viewed. All CollaborateMD standard reports protect Patient Health Information (PHI), however, you can choose to unmask the social security number (SSN) if needed.

  1. Right click on the field after it has been dragged onto the Columns pane and unselect the “Mask SSN” from the menu list to unmask the first 6 digits from the social security number.

    Important: In order to remain HIPAA compliant we strongly recommend keeping the SSN masked. Only users with the Patient Health Information permission can obtain the full value by accessing the patient's account.

Categorize report data with aggregations

Setting an Aggregation on a field will group like rows together based on other columns.  Aggregation options are available for Date, Money, Number, and ID fields. By default, no aggregation is selected for any field types.  If an Aggregation option is selected for a field, you will find the type of aggregation in parenthesis next to the field.

Where applicable, follow the steps below to change the aggregation for each field:

  1. Select Reports  > Builder.

  2. Use the Show All button to view all custom reports. Or use the Search Bar to further drill down your search. 

  3. Select the Report from the list. 

  4. Right click on the field after it has been dragged onto the Columns area and select the desired option from the Aggregation menu .


Date Field Aggregation

For Date fields, the following aggregation options that can be chosen :

  • Maximum: Displays the most recent date.

  • Minimum: Displays the earliest date.

In the example, a report was created showing the Patient ID, Patient Last Name, Patient First Name, Patient Balance, Last Payment Date, and Claim From Date.

If no aggregation is specified for any date related fields, the report may display the following data: 



If Aggregation is set to Maximum for the Claim From Date the report will only display the most recent Claim From Date.  

Money Field Aggregation 

For Money fields, the following aggregation options that can be chosen:

  • Sum:  Displays the summation (total) of the values.

    • Average: Displays the average of the values.

    • Maximum: Displays the largest value.

    • Minimum:  Displays the smallest value.

In the example, a report was created showing the Patient ID, Patient Last Name, Patient First Name, Patient Balance, Claim ID, and Charge Amount.

If no aggregation is specified for any money related fields, the report may display the data:

If Aggregation is set to Average for the Charge Amount field the report will only display the average value for the Charge Amount. For example:

 If Aggregation is set to Sum for the Charge Amount field the report will display the sum value for the Charge Amount. For example:


Check Your Understanding:

Notice the two like rows have been grouped together based on the non-aggregated fields and the Charge Amount is now reporting the average value for the grouped like rows. The last row on the report was not grouped with the first two rows because the Claim ID value was different (i.e. this charge was associated with a separate claim).


Number Field Aggregation 

For Number fields, the following aggregation options that can be chosen :

  • Sum: Displays the summation (total) of the values.

    • Average: Displays the average of the values.

    • Count All: Displays the count of all occurrences of the value.

    • Count Unique: Displays the count of all unique occurrences of the value.

    • Maximum: Displays the largest value.

    • Minimum: Displays the smallest value.

In the example, a report was created showing the Patient ID, Patient Last Name, Patient First Name, Claim ID, and Charge Units.

If no aggregation is specified for any number related fields, the report may display the following data:

 

If Aggregation is set to Sum for the Charge Units field the report will only display the sum value for the Charge Units. 

If Aggregation is set to Maximum for the Charge Units field the report will only display the largest value for the Charge Units. For example:


Check Your Understanding:

Notice that the two like rows have been grouped together based on the non-aggregated fields and the Charge Units is now reporting the summed value for the grouped like rows. The last row on the report was not grouped with the first two rows because the claim ID value was different (i.e. this charge was associated with a separate claim). The Charge Units field now represents the total number of units billed on the claim regardless of the number of line items.


ID Field Aggregation 

For ID fields, the following aggregation options that can be chosen :

  • Count All: Displays the count of all occurrences of the value.

    • Count Unique: Displays the count of all unique occurrences of the value.

In the example, a report was created showing the Patient ID, Patient Last Name, Patient First Name, Claim From Date, and Charge ID.

If no aggregation is specified for any ID related fields, the report may display the following data:

 If Aggregation is set to Count Unique for the Charge From Date field, the report will only display the total count of the Charge ID(s). For example:


Check Your Understanding:

Notice that the two like rows have been grouped together based on the non-aggregated fields and the Charge ID is now reporting a count of the unique values in the Claim ID field for the grouped like rows. The last row was not grouped with the first two rows because the Claim From Date value was different. This is because the charge was associated with a separate claim. The Charge ID field now represents the total count of unique charges (or line items) for each claim. 


Hidden Columns

You can easily configure a column to be hidden by default when a report is run. A hidden column won’t display on the report, but can still be shown if desired via the Report Viewer’s right-click capabilities., 

Follow the steps below to hide a particular column by default.

  1. Select Reports > Builder

  2. Use the Show All button to view all custom reports. Or use the Search Bar to further drill down your search. 

  3. Select the Report from the list. 

  4. Right click on the field after it has been dragged onto the Columns pane and select Hide column from the menu list.

Check Your Understanding:

Under what situations would someone want to hide a column on their report instead of simply removing it? There may be many different scenarios in which hiding a column(s) on the report would be more beneficial than removing it, but the 2 most common reasons are: 

  1. You use a column in some situations when running the report but not all the time. If this is the case, hiding the column prevents you from having to create 2 separate reports.
  2. You are using the column as part of a Calculated Column, but all you care about is the resulting calculation. In this case, the column is necessary for other columns (and needed on the report), but in itself may not be useful to you.


Add Calculated Columns

A Calculated Column takes information from two existing fields and performs a calculation between the two fields to present new data. 

Two types of calculated columns may be created based on the fields that are added to the Columns pane: Numeric-based and Date-based calculated columns.

  • Available calculations for numeric-based calculated columns are: Addition (+), Subtraction (-), Multiplication (x), Division (/)
  • Available calculations for date-based calculated columns are: Days between, Months between

The first step to creating a calculated column is to verify that the fields you want to be calculated are added to the Columns pane. The columns must exist in order for the calculation to occur.

  1. Select Reports > Builder.

  2. Use the Show All button to view all custom reports. Or use the Search Bar to further drill down your search. 

  3. Select the Report from the list.

  4. Add the desired columns to the report.

  5. Click the Calculated link found on the top right corner of the columns pane.

  6. Enter the Title for the calculated column.

  7. Use the Columns 1 drop-down menu to select the first column to be used in the calculation.

  8. Use the Calculation drop-down menu to select the calculation.

  9. Use the Columns 2 drop-down menu to select the second column to be used in the calculation.

  10. Click Done



Edit Calculated Columns

  1. Select Reports > Builder.

  2. Use the Show All button to view all custom reports. Or use the Search Bar to further drill down your search.

  3. Select the Report from the list.

  4. Right-click on the column name within the Report Builder and select the option titled Edit Calculation.

  5. When the Calculated Column Editor window opens, make your changes.

  6.  Click the Save.

Display Totals

The Display Totals at the bottom of the report option is available for Money, Number, ID, and Text fields. By default, the Display Totals option is not selected for any all field type(s).  If the Display Totals option is selected for a field, the field will be underlined.

Enabling the Display Totals option for Money and Number fields will insert a line at the bottom of the report and show the summation for that specific value of all rows on the report.  

Enabling the Display Totals option for ID and Text fields will insert a line at the bottom of the report and show the count of all values or all unique values within that column for all rows on the report.

Where applicable, follow the steps below to change the Display Totals option for fields.

  1. Select Reports > Builder.

  2. Use the Show All button to view all custom reports. Or use the Search Bar to further drill down your search.

  3. Select the Report from the list.

  4. Right click on the field after it has been dragged onto the Columns pane and select Display Totals from the menu list.

If one or more fields on the report is grouped and the Display Totals option is enabled for a specific field, the subtotals for the field within each group will be displayed on the report along with the Grand Total for the report.