Unit 15: Charts and Reports

Questions Covered •

What are charts and reports?

•

How can I build custom charts and reports?

•

Is there any way to schedule a report to be run on a monthly basis?

•

How are reports distributed?

•

What happens if the user receiving the report doesn't have permission to view the data?

2

What are Charts and Reports •

Reports are ways of collecting, summarizing and displaying information from a table.

•

Charts/reports are created and edited within the same wizard and they are displayed in the same table.

•

An integrated chart/report may display information in graphical, HTML, Excel, and text formats. A saved instance of a chart/report can include a chart only, report only, or both a graphical chart and one or more report formats.

•

In contrast, a custom summary report can only be created in HTML format. We will learn more about summary reports later in this unit.

•

Charts and reports, including summary reports, can be viewed interactively, distributed via email, or written to the hard drive for viewing via a web browser or another program.

3

Reports and Performance •

Complex charts and reports can be resource intensive, particularly when thousands of records are found and must be collated.

•

This may come into play when you are deciding who can and cannot run charts and reports; it is often helpful to restrict this access to managers and users who know what they are doing.

•

It is also helpful to schedule charts/reports to run daily or weekly, during off hours when possible.

•

Charts/reports can be scheduled and distributed to a URL where they can be accessed by users without needing to be re-run. For instance, in a time-sensitive setting, running a report every 10 minutes and saving to disk so others can access it without re-running it can make a lot of sense.

4

Access to the Charts/Reports Wizard •

Charting and reporting capability is automatically provided for every table, whether that table is included in the default knowledgebase or added by the local administrator. Only default tables have preconfigured charts.

•

The Charts and Reports wizard is accessed through the Charts/Reports hyperlink, located in the left pane under the table you wish to report on. If you do not see the Charts/Reports hyperlink under the table, your user group has not been given permissions to use reports. To activate Charts/Reports access, navigate to Setup > Access > Manage Groups > [Select group to edit] > Table tab > [Select table] and click Next. Now, on the MenuSpecific tab you can select the appropriate options under Allow creating/modifying/deleting Reports? and Allow using Reports?. Click Finish to save your changes.

•

Click on the Charts/Reports link below a table to see the list of available charts/reports for that table, if any. Click New or edit an existing report to open the Charts/Reports wizard.

5

The Charts and Reports Wizard •

Most tabs in the wizard are common to both charts and reports, but some tabs affect only one or the other.

•

When the wizard opens, you will initially see only the General and Type tabs. The remaining tabs will (or will not) appear depending on the Output Format chosen on the General tab. Report-only tabs

Chart-only tabs

These tabs are common to both charts and reports

6

General Tab •

As in other wizards, the General tab is where you name your chart/report and give it a description, as well as select the chart/report Output Format.

•

We will focus on the integrated chart/report options for now, and come back to custom summary reports later on. It is important to note that the Output format options control which tabs you will see to the right of the Type tab, as chart-only tabs will only appear if you select Graphical chart, and report-only tabs will only appear if you select HTML, Excel, or Text report. Customize Excel tab will only show if you select Excel. Report Template tab will only show if you select HTML.

7

Type Tab •

The Type tab defines the type of chart/report being created.

•

There are three basic types of reports: 

Standard. Show values from fields in records with the potential to calculate averages or totals of field values and to sort and group down to an unlimited number of levels.



Elapsed Time Analysis/SLA. Show how much active time various individuals or teams spent working on a record. There are two issues to consider when designing an SLA report: How "active" is defined and how to deal with cases when a record was assigned to multiple individuals or teams. The Elapsed Time Analysis/SLA option will only appear if the current table contains a field with an Elapsed Time datatype, such as the Tasks table.



Trend Analysis. Show historical trends over time, such as the number of open support tickets that existed at the start of each month. It uses data that you see when you click on the History tab in a record. 8

Trend Analysis •

To see how your data is changing over time, use the Trend Analysis report type.

•

Trend charts use the history engine to display data based on slices of time at specified intervals.

•

If you select Automatically find optimal time increment, the system will use common sense to choose a time segment that produces between four and twelve slices.

•

For example, if there are five weeks of data, it will choose one-week increments; if there is only one week, it will choose one-day increments.

9

Elapsed Time Analysis/SLA Reports •

To create a chart or report which shows the average length of time a record spends in each state (Status), or how long records are assigned to a team/person, use an Elapsed Time Analysis/SLA chart/report.

•

For example, to show the average time a support case ticket spends assigned to a given person, use the Total Hours to Close field and segment changes based on Assigned Person.

•

To create a chart based on the table date, we recommend using a Column Bar chart. On the Axis tabs of the Charts wizard, choose an X Axis of Assigned Person and set the Y Axis to Average of the Total Hours to Close field.

10

Elapsed Time Analysis/SLA Reports (continued) When calculating formulas and averages in an Elapsed Time chart/report, checking the Collate results per ticket box means the system will treat each record as a single event. It will add up the time an individual user or team spends on that record before calculating averages. •

To illustrate the point, let’s look at the following example: User A spends 2 hours on a support case ticket. The record is then reassigned to someone else for a few hours. The record is reassigned again, and User A spends another 3 hours on the record.

•

Selecting Collate results tells the system to add up all of User A’s time spent on the ticket. This results in an average of 5 hours per ticket (based only on the data from this one hypothetical record).

•

Not collating results tells the system to treat each time period as a separate instance. The results in this case show that User A spent an average of 2.5 hours per ticket.

11

Reports and Security •

Charts/reports take each user’s group permissions into account when extracting data. Any information a user is not allowed to access will automatically be excluded. This maintains security, and also means that different users may see different data even when viewing the same chart/report.

•

You can preview how a given user will see the report on the Preview tab by supplying their login name.

•

The Preview tab still enforces the permissions of the user doing the preview, so a typical staff user can not use this to preview a chart/report as the admin.

•

For emailed charts/reports, the recipient’s permissions are used.

•

On the Schedule tab of the Charts/Reports wizard, any email addresses entered manually under the Send this report to heading will use the Guest group credentials. It is best to select users and teams who will receive the chart/report. The system will look up their email address from their user record and use their permissions. 12

Common Tabs (Shared By Charts and Reports) •

Filter: Allows the user to apply various filters including: 

Relative and Absolute date ranges of record creation, modification, or simple existence.



Saved search filters allow the user to set up complex filter criteria. 

It is possible to use run-time filters to limit the number of required saved charts/reports (see next slide).

•

Schedule: Controls the frequency and timing of recurring charts/reports. It also controls whether the chart/report is emailed to users and/or saved to a disk. If the chart/report will be distributed via email, you can select the recipients and draft the email’s contents.

•

Preview: Allows you to preview the chart/report prior to saving, either as yourself or as the chart/report will be viewed by another user.

13

Using Run-Time Filters to Limit Chart/Report Quantity •

Using run-time search filters can help minimize the number of charts/reports you need to maintain. For instance, suppose you do consulting projects for a number of companies and want the ability to easily send different customers charts/reports covering different ranges of time.

•

In this case, you can use a single chart/report which is filtered by a runtime saved search. The run-time filter allows you to enter the start/end dates and company name when the chart/report is run.

•

To utilize this option, navigate to the Filter tab and create a saved search which includes a Run-Time filter for the chart/report results.

14

Chat-Only Tabs •

Chart Type: Controls the chart type, e.g. pie chart, bar chart, funnel chart, etc. An example of a multiple-axes chart appears on the next slide.

•

X Axis: Controls what is shown on the X axis of your chart and determines the first level of grouping if there is an associated report.

•

Y Axis: Controls what is shown on the Y axis of your chart and determines the second level of grouping if there is an associated report.

•

Formatting: Controls the details of the look and feel of your chart. This is where you decide if you want your chart to have 3D effects, display numeric values, include a legend or title, etc.

15

Special Chart Type: Multiple Axes

16

Report-Only Tabs •

Report Template: This tab only appears with HTML output format selected. Use the WYSIWYG editor or edit the source HTML to control the appearance, colors, and formatting of HTML reports.

•

Grouping/Summary: Controls how the information in the report is grouped, which view will be used to display it, and the summary information to include.

17

Grouping/Summary Tab Details •

If you are also displaying a graphical chart, the first two levels of report grouping are defined by the X and Y axis.

•

Click New to add additional groupings. This will open a wizard.

•

The same wizard appears after clicking the edit icon next to each grouping. On the Grouping tab, you see current summary information and add new summary information.

•

The Sorting tab in the wizard controls the sort order of the items in the group.

18

Example of Sorting by Count/Ascending •

This group has been sorted:

19

Grouping/Summary Tab Details (continued) •

If none of the counts/averages/totals provide the exact summary information you need, click Formula Help and define an appropriate formula using the Formula wizard.

20

Customize Excel Tab •

The Customize Excel tab allows you to prepare an Excel workbook template where report data will be placed.

21

Customize Excel Tab (continued) •

The report data will be placed on the Data worksheet and will be based on the view selected on the Grouping/Summary tab.

•

The other tabs of the spreadsheet can include anything you require, including Agiloft variable references and formulas/pivot tables which are sourced from the Data sheet.

22

Summary Reports •

Summary reports allow you to use data from multiple tables in an HTML report format.

•

These reports make use of global summary variables defined by users. These variables can also be used elsewhere in the system wherever variables are used. Each variable in a summary report must be created by the user, making them a bit more time-consuming to set up than a standard chart/report.

•

Like standard reports, summary reports can be scheduled to run at predetermined intervals, and can be emailed and/or saved to a hard drive.

•

Summary reports can not include an attached chart.

•

The next slide shows an example summary report.

23

Example Summary Report

24

Accessing Summary Reports •

•

There are two ways to access summary reports: 

Click on the Summary/Combined Reports hyperlink under Home in the left pane. Once the screen refreshes, click the Summary Reports button to see a list of current summary reports, edit existing, or create new reports.



Or, select Custom Summary Report on the General tab of the Chart/Report wizard.

The real customization happens on the Custom Summary tab. From here, you can click the Formula Help button to begin creating the necessary report variables.

25

Table Variables in Summary Reports •

All variables created for summary reports can be edited by navigating to Setup > System > Manage Table Variables.

26

Practice

Create a New Report •

Let’s create a few charts and reports for our new Tasks table.

•

Navigate to the Charts/Reports under Tasks in the left pane, and select New.

•

First, let’s create a chart-only report.

•

This chart/report should apply to Tasks.

•

Title your report: Assignee's tasks in the past month, segmented by type. 

Like rules and actions, it is important to use a clear naming convention.

•

Give your report a description.

•

Do not check the box: Published.

•

Your Output Format will be Graphical Chart.

•

Click Next.

27

Practice

Create a New Report (continued) •

On the Type tab, select a Standard Chart.

•

On the Chart Type tab, select Segmented Column Bar Chart.

•

For your X Axis, select Assigned Person, then sort by Content, sort in Ascending order.

•

For your Y Axis, select the option for The number of Tasks, segment by Task Type, and select the option for each segment to show The number of Tasks.

•

On the Filter tab, use a saved search filter. Use the Create Saved Search button to create a search that finds all tasks that were created or updated in the last month. Remember to select your saved search. Show current data.

•

On the Formatting tab, set Show 3D effects, Display numeric values, and Show legend to Yes. Place the title above the chart, limit text to 12 characters, and do not show the generation date. The chart size should be 580x435.

•

On the Schedule tab, select Monthly on the 1 day of the month at midnight. Uncheck both options under Distribute chart via.

•

Click Finish.

•

To see how your new report looks, click the View icon next to your new report! 28

Practice

Create a Column Bar Chart/Report •

Now let’s create a report which includes both an HTML report and a graphical chart.

•

This chart/report should apply to Tasks.

•

Title this report: All Tasks, segmented by assignee.

•

Give your report a description.

•

Do not check the box: Published.

•

Your Output Type will be Graphical Chart and HTML.

•

Click Next.

29

Practice

Create a Column Bar Chart/Report (continued) •

On the Type tab, select Standard chart/report.

•

On the Chart Type tab, select Column Bar Chart.

•

For the X Axis, select Assigned Person, sort by Content, sort in Ascending order.

•

For the Y Axis, use The number of Tasks. For each segment show The number of Tasks.

•

On the Filter tab, use Relative dates to include all tasks from the date of the earliest task through today. This should be based on the Date Created and show All Tasks. Do not use a saved search filter. Use current data.

•

On the Formatting tab, set Show 3D effects, Display numeric values, and Show legend to Yes. Place the title above the chart, limit text to 12 characters, and do not Show generation date. The chart size should be 580x435.

•

On the Grouping/Summary tab, the summary information is count(). Under Show records?, select Personal View. Under Group data by, group by Assigned Person.

•

Skip the Report Template tab (it defaults to the correct values).

•

On the Schedule tab, do not create a schedule (select Never).

•

Click Finish.

•

To see how your new report looks, click the View icon next to your new report! 30

Practice

Create a Run-Time Report •

Now let’s create a new chart/report in the Projects table.

•

This time, you will be receiving minimal direction.

•

When finished, check your work in the Training Sample KB.

•

The report will be measuring the number of active projects, grouped by week, with a run-time filter where Target Completion Date is within a user-defined date range.

•

Title your report: Active Customer Projects: Runtime Target Completion Date Range.

•

Output as a Graphical chart and HTML.

•

Display using the Detail View.

•

Do not schedule any recurrence.

•

Click Finish. 31

Charts and Reports Summary •

We have learned how to create, edit, and view charts and reports of different types.

•

We have learned that charts and reports can be scheduled to run at any desired frequency and then be either saved or emailed to the audience.

•

We have learned that you can get more bang for your buck if you use a run-time search, instead of building multiple reports.

•

To further your understanding, try building a few charts and reports using the different options we have covered.

32