Using the Reporting Tool

This video explains how to use the Reporting tool:

Overview

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 build your data set, you should first review the Buiding 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" link in the left hand navigation bar.Then select the report you want to run from the Saved Reports tab.

Creating a New Report

You can run a report as soon as you have saved the data set by clicking the Run Report button.


You can also create a new report by clicking on the Run Report button associated with a data set you have already created.

When you click Run Report your data set will open in a new tab.


Saving Reports

Once you have created a new report, you can save the changes you have made by clicking "Save Changes."

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.

Tip: Reducing the number of rows on each page will speed up the time it takes to load each page of the report.

Filtering

You can use filters 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 been filtered out will not be included in any charts or crosstabs you create, either.

Filters can be simple, such as a filter to eliminate requests that were marked as abandoned. Filters can also be used to exclude rows based on a date or number range, such limiting a report to a particular year.

Tip: Many reports will initially contain requests that were marked as "Abandoned" before a decision was made.One common use of filtering is to eliminate these requests from your report.


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

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, 2005 and December 31, 2005.You can also filter on sliding dates.That would let you 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.


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 out requests that were denied.The second includes requests with a decision date between January 1, 2005 and December 31, 2007.

Note that the example below filters out requests that were denied 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 filter 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 eliminates all requests that were denied.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.


Sorting

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 hitting "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.In the example below, the report is sorted first by Organization Name and then by Date Awarded.


Grouping

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.In the example below, the data is grouped by the "Geographic Area" column first and then by "Date Awarded."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.

Aggregating Data

After you add groups to your report, you can aggregate data within that group.This allows you to perform the following functions:

Count the number of rows in a group

Count the number unique items within a group

Provide a total or an average for any numeric data in the group

Provide the maximum or minimum value for any numeric data in a group

Provide the standard deviation of numeric data in a group

Crosstabs

Line 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 Value Column") and the column you want to display on the lefthand side of your crosstab (the "Lable Value 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 aggreaged 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.

Charts

You can add charts to a report by clicking the Charts 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 mad) across categories such as funding type, progrm 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

Formuals offer a powerful way to transform the data in your report.Formaulas allow you to perform funtions 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 formuals along with instrunctions 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 and clicking "Insert" or by adding the name of the column inside brackets to the formula you are writing.


Please Click Here to download the full Reporting Tutorial.

Please Click Here to view the written How do I build and run a report answer.

#FTSummit15

Was this article helpful?