Display style and summary options are available for each column of data you pull into a report. These can fine-tune the appearance and behavior of your data.
To access a column properties, click the green gear box in the upper right corner of the data column's header. The column's properties box will open.
Data Style options can vary depending on the type of data your column contains. Here we see the options available for a Date column:
- Normal: this is the default setting for a date column and shows the date in DD/MM/YYYY style.
- Month: Displays the name of the month.
- Month and Day: Displays the name of the month and the day. For example: October 8.
- Month and Year: Displays the name of the month and year. For example: October 2014
- Quarter: Displays the quarter as "Quarter 1," "Quarter 2," etc.
- Quarter and Year: Displays the name of the quarter and the year. For example: Quarter 4, 2014
- Age: calculates the years between the date in the column and today's date. This is useful for calculating a client's age if you only have their date of birth.
- Days Ago: counts the number of days between the date in the column and today's date. Ideal for a date field that contains dates that have already passed.
- Days from Now: counts the number of days between today's date and the date in the column. Ideal for a date field that contains a future date.
- Day of Month: Displays the day of the month. For example: 8.
- Day of Week: Displays the name of the day.
- Week of Year: Displays the week of the year. For example: 41.
- Year: Displays the year only.
Summary styles show information at the bottom of a column of data.
- No Summary: column data will not be summarized.
- Earliest: The earliest date in this column will be listed at the bottom.
- Latest: The latest date in this column will be listed at the bottom.
- Count Distinct: The number of distinct or unique values in this column will be totaled at the bottom.
Group By This Column
Selecting "Yes" will create a line of data for each unique value in that column, with record results for that value which can be viewed when expanding the down arrow of a group.
+ When Summary Options Change
In some cases, if you change the data style for your column of data, new summary options will become available.
You'll see this green text appear: "Data Style changed - Click here to refresh."
Click the green text or refresh your browser window. After it reloads, open the column gear box again and you will be able to see your new available summary styles.
- Summary Options available for numeric data include:
- Total: adds the values.
- Average: averages the values
- Average No Zeroes: averages the values without counting any zero or null values.
- Minimum: displays the minimum value.
- Maximum: displays the maximum value.
- Count Distinct: counts the number of distinct or unique values.
Another Column property that can be useful in organizing your data is grouping. To access this feature, open the column gear box, and under "Group by This Column," select "yes."
- Here we have grouped by the column called "Referred for Other Services." The available answers are Yes and No. The data is now organized into two groups, one for Yes answers, and one for No answers.
- When a column has been "grouped," the column header text turns white so you can see at a glance how your data is organized.
- We now get a row count for each "Grouping."
- Note: Groupings are limited to 4 per report section. If you need to group on more than 4 columns, we suggest splitting the outermost grouping into report sections of their own.
The Formatting section of the column properties is where cosmetic changes can be managed.
- Sort order: Choose "Ascending" or "Descending." This can be useful if you need to order date values or put names in alphabetical order.
- Hidden: This will hide your column when it is run. It will still appear in the Report Builder, but this column gear box will be grayed out. This can be useful if you are including calculations in your report and would like to hide the columns that are necessary to create the final calculations.
- Width: You can set a width for your column up to 440 pixels.
- Alignment: Justifies the column title and contents.
- Font Style: Sets the column title and contents to the selected style.
- Color: Changes the column title and contents. Color choices are dependent on which browser you are using (Chrome, Firefox, etc.)
Expand the black arrow next to "Form Info" to reveal the form, section, and field the column came from. This can be useful if you need to make edits to a report someone else built or if you re-named a column and now need to figure out where it came from.
- Form: Name of the form your column came from.
- Section: Name of the section in the form your column came from.
- Field: Name of the specific field in the form your column came from.
- When you expand the "Form Info" section in the column properties, the form and section will become highlighted in green in the right hand menu. This makes it easy to find the form in the Field Choices palette.
Special Columns: Calculation Columns
Special columns allow you to add additional math and time calculations and text concatenations to your report. These columns do not exist as fields in your forms, but they can assist you in analyzing your data.
- Calculation column types include:
- Number Calc: Allows you to add, subtract, multiply or divide two or more numeric columns in your report section.
- Date/Time Calc: Calculates the time between two date/time columns in your report section.
- Text Calc: Allows you to concatenate text (combine two or more text columns) in your report section.
2. To use a special column in your report, drag and drop it into your report section as you would with other fields from your forms.
+Number Calc Column
- Click into the text box at the top to change the header of your calculation column.
- The same data styles and summary options are available here as for other columns with numeric or number values.
- Choose your Calculation Type. Here we are calculating how many years until our clients turn 18, so we are going to choose "Subtract."
- Choose your Calculation Columns. Expand the black arrow in the "Select a Column" dropdown to select the first column you would like to use in your calculation. The choices available here will be all the numeric or number value columns in your report. If you do not see a column here but think it should be here, check its data style. A numeric column that has been changed to a text value will not show up here. "[Static Value]" allows you to enter in your own number or numeric value to use in the calculation. If we need to find out how many years we have until our clients turn 18, we would enter a [Static Value] of 18 for the top Calculation Column and our Age column as the second Calculation Column.
- If you need to add more columns to your calculation, you can do so by clicking the "+" sign.
+Date/Time Calc Column
- Click into the text box at the top to change the header of your calculation column.
- The same data styles and summary options are available here as for other columns with date or time values.
- Choose your Calculation Type. Your choice here will determine which columns are available under "Calculation Columns." If you choose "Days Between," "Days After" or "Days Before," your date columns will be shown as available. If you choose "Time Between," only columns with time data will be available.
- Expand the black arrow in the "Select a Column" box to select the column you would like to use in your calculation. - "[Static Value]" allows you to enter in your own date or time value to use in the calculation. If you need to see how many days someone has been in the program since the beginning of the year, January 1 (01/01/2014, for example) would be your static date value.
+Text Calc Column
Although this column is called "Text Calc," it is not limited to only the text columns in your report. It can be used to combine the data from several columns into one.
- "Combine" is the only calculation type in a text calc column. It will add together whatever appears in the columns you select.
- Choose your columns to combine by expanding the black arrow next to "Select a Column."
- If you need more columns, add them by selecting the + sign.
- A common use for a text calc column is to combine names into one field, so that it looks cleaner on a report. Or you may choose to combine a client name with the name of the program they are participating in, or their name with their ID number.
- When combining several fields, it can be useful to select [Static Value] of a space - or a comma and a space - to separate the column values in your final result.
Summary Columns allow you to bring the results of an entire report section into one column. It can be useful if you needed several report sections to create or calculate results and would like to combine them into one area of your Report, where they can be highlighted or be easier to read.
- Summary Column types include:
- Section Summary: Displays the summary from one column in any other section in your report.
- Section Count: Displays the count from one other section in your report.
2. To use a Summary Column in your report, drag and drop it into your report section as you would with other fields from your forms.
3. Summary Columns work best in their own section in a report.
- First select your report section by expanding the black arrow under "Choose a Section."
- Then select your column by expanding the black arrow under "Choose a Column."
Note: Only columns that have a "summary" value or display style will appear here. This includes columns where totals or averages have been selected in their column properties gear box. If you don't see the column here that you need, go back to the original column's gear box and select a summary style.
- Expand the black arrow under "Choose a Section" to select which section of your Report you would like to display.
- This Summary Column will display the "Total Rows" count from another section of your report.