In this tutorial, we will review the features of the reporting tool in GLM. This tutorial assumes you have already built a data set from which to run the report. If you have not built your data set, you should first review the Building New Data Sets tutorial.
The reporting tool allows you to manipulate the data contained in a data set. It can be used for a number of functions, including:
- Refining the data set by applying filters;
- Displaying the data in a more useful format;
- Grouping the data and adding totals or averages for each group;
- Creating crosstabs and charts
Running a Saved Report
Run a saved report by clicking on the "Reports and Data Sources" under the Reporting tab at the top of your screen. Then select "Run Report" for the report you want to run from the Saved Reports tab.
Creating a New Report
You can run a new report as soon as you have saved the data set by clicking the Run New Report button in the upper right hand corner of your screen.
Another way to create a new report is by clicking on the Run Report button associated with a data set you have already created under the "Reporting Data Sets" tab.
Once you run a new report your data set will open in a new tab.
Once you have created a new report, you can save the changes you have made by clicking "Save New Report" in the upper right hand corner of your screen.
When editing an existing report, you can save the changes you have made by clicking "Save Changes" or you can save the changes as a new report by clicking "Save New Report."
Exporting Data from Reports
You can export any table from a report to an Excel, CSV, or PDF document.
Tip: Even when you plan on working with the data in Excel, it is often easier to export to CSV, which will normally open in Excel.
Columns and Paging
You can adjust column widths and column order, hide columns, and change the number of rows that appear in the report.
- Click and drag a column to another location in the table by placing your cursor in the left side of any column header. This allows you to change the order of columns in the table. Adjust each column's width by placing your cursor on the right side of a column header, clicking and dragging the column to the desired width.
You can also hide columns you don't need (or show any hidden columns) by clicking on the Columns button, unchecking boxes next to the column you don't want to display (or checking the box next to a hidden column you want to display). Once you have made your selection you will need to click the Save Column Selection button.
Tip: You may need a column in a report for a filter or a formula, but if you don't need that data displayed you can hide the column. It will still be available for filtering and formulas.
You can adjust how many rows show up on each page by clicking the Paging button.
An easy way to filter out unnecessary data before you run your report is to use the Pre-Filters stage. This stage allows you to select specific processes, request statuses, and form types to report on. If you choose to not select something, it will automatically be filtered out of your report when you run it for the first time.
Tip: Example: Many reports will initially contain requests that were marked as "Abandoned" before a decision was made. One common use of pre-filtering is to eliminate these requests from your report by un-checking that request status from the Request Statuses section.
You can use the Filters button to eliminate rows you don't want in your report. In addition to hiding unwanted data, filtering eliminates rows from any totals or averages you have in a report. Rows that you have filtered out will not be included in any charts or crosstabs you create, either.
To begin filtering, click the Filter button and choose the column you want to include in your filter. Then select the type of comparison you would like to make in the column. Finally, add the value you would like to include in the comparison.
Filters can be read like sentences. In the example below, the filter can be read as "Request Status does not equal 'Denied". This filter will include every request in the table except those with the status "Denied" (this was simply an example, that filter can be easily completed in the pre-filters section).
When filtering on numeric data or dates, you can filter by a specific value, or you can filter by a range. In the example below, the filter includes requests with a decision date between January 1, 2017 and a sliding date of "Today". You can also filter on other sliding dates. Those would allow you to filter requests for a date range that would be updated every time you run the report. For example, you could filter out all but the grants made last month, or all but payments due next quarter (updated each time your run the report).
You may need to add more than one filter to a report to have the effect you want. In the example below, two filters have been added. The first filters amount awarded to only include values over 500. The second includes requests with a decision date between January 1, 2017 and a sliding date of today.
Note that the example below filters amount awarded to display values over 500 AND those with a decision date between those two dates. You can also combine filters with an "or" statement. To switch and "And" to an "Or" when looking at a list of filters, simply click on the word "And."
For more complicated situations, you may need to group filters together with parenthesis.
- This can be done by using the arrows that appear to the right of each filter when you have more than one filter in a report.
- The report pictured below, for example, has a single filter that displays amount awarded values greater than 500. Then it has two filters that work together to include requests that fall between two different date ranges. The parenthesis around the two date ranges filters are necessary to ensure requests that were not denied but that fall between one OR the other range are included in the report.
You can sort a report by the contents of a single column by clicking on header of that column.
You can also sort based on the data in one or more columns using the Sort button. After clicking "Sort", select the column and order in which you want to sort and click "Add”. You can sort by multiple columns by adding additional columns to sort by.
You can organize the data in your reports into groups by clicking the "Group" button and then selecting the column you want to group by. Like sorting, you can also group by more than one column.Note that when you group by a column with dates you can choose to group by day, month, quarter or year.
Tip: If you want to see all of the responses that were given to a particular question, you can group by that question and then click the "Exclude Detail Rows" button.
After you add groups to your report, you can aggregate data within that group (or of the report as a whole if you are not using groups). This allows you to perform the following functions:
- Provide a total Sum or an Average for any numeric data in the group
- Provide the Standard Deviation of numeric data in a group
- Count the number of rows in a group
- A Distinct Count the number unique items within a group
- Provide the Maximum or Minimum value for any numeric data in a group
This can be an extremely useful way of seeing the data. In the example below, a crosstab shows the total dollars given within each funding area for each year included in the report.
To create a crosstab, begin by clicking the Crosstab button. You will then need to select the column you want to display across the top of your crosstab (the "Header Values Column") and the column you want to display on the left hand side of your crosstab (the "Label Values Column").
- If you pick a date for either column, you will be asked how you want to group your data (e.g. by day, month, quarter, or year).
Once you have selected the columns you want displayed across the top and left side of the crosstab you need to select what data you want aggregated in each cell of the crosstab.
- You can choose to total (sum) or average numeric data like amount awarded. Or you can count how many items fall into each category.
Crosstabs can be exported as Excel, CSV or PDF files. When imported into a spreadsheet, they can be the basis for a chart.
Tip: You can click and drag columns to reorder them to in a crosstab the same way you can in the rest of the report.
You can add charts to a report by clicking the Chart button and selecting the chart you want to add.
The reporting tool offers different types of charts. Each chart type has slightly different settings necessary to configure it. You may need to experiment with chart types and settings to get the results you want.
Below is an overview of the most common types of charts:
Pie charts and bar charts show numeric data (e.g. total $ awarded or number of grants made) across categories such as funding type, program area, geographic area, etc.
A line chart and a curved line chart show changes in one variable, such as amount awarded, over time.
Tip: In many charts, hovering your cursor over part of the chart will pop up additional information. In the example above we can see the total of grants made in 1997 is $2,005,198.
Formulas offer a powerful way to transform the data in your report. Formulas allow you to perform functions within a report including:
- Perform mathematical functions on the data in your report
- Use logical functions to replace data
- Remove unwanted spaces or shorten text
- Format a number as currency or a percentage
- Format dates and time
- Remove decimal points and round numbers
- Calculate elapsed time
- Count the number of characters in a response
Adding a formula will create a new column in your report. That column is then available for sorting, grouping, filtering, crosstabs and charts, or another formula. You can also hide, move, and resize a column created by a formula like you can any other column.
To create a formula use the "Formula" button.
- The name you give the formula will be the name of the column that is added to your report.
- Once you have named your formula, it is often easiest to visit Formula Help.
- There, you can find a list of available formulas along with instructions on how to use them.
Tip: It's often easiest to copy a formula from Formula Help into your report and then replace its contents with details (column names, values, etc.) from your report.
- Formula's can be entered in the text box labeled "Formula”. You will need to refer to other columns inside each formula. Other columns can be added by selecting them from the dropdown list next to "Insert a column" and clicking "Insert" or by adding the name of the column inside brackets to the formula you are writing.