Creating & Editing Pivoted Graphs in OnePager Charts for Version 8.0

Introduction

Illustrations used in this article are from OnePager Pro Add-in and Standalone using data from Microsoft Project, however, Graphs & Data Cells features, functions, controls, and editing apply equally to other OnePager Add-in and Standalone editions that import from data sources like Microsoft Excel, Smartsheet, Oracle Primavera P6, Planisware Enterprise, Asana, and Wrike. At present Microsoft Project Planner source plans do not provide time-series data that can be processed and displayed by the Graphs & Data Cells feature of OnePager.

The purpose of this article is to provide an overview of OnePager’s ability to display Pivoted Graphs and describe the creation of Stacked and Splayed Graphs in the graph Areas.

Overview

Graphs produced by OnePager can be Pivoted in two different orientations: Vertically and Horizontally. Further, OnePager support five Graph types as listed below:

Area
Column
Line
Smooth Line
Smooth Area

Examples of these Graphs can be found at the links in the table below:

Using Graphs & Data Cells in OnePager
Gallery of Graphs and Pivot Options

Pivoted Graphs

OnePager has the capability to help you create Pivoted Graphs where another imported field is used as the basis of the pivoting operation. OnePager supports Horizontally and Vertically Pivoted Graphs for the Graph types shown in the table below:

Preparing Source Plan Data for Vertical or Horizontal Pivoting

It is important to understand how OnePager gathers the information from your source plan in order to create a Vertically or Horizontally Pivoted Graph for the Graph types shown in the above table. This sub-section details that gathering process.

Caution 1 – Selecting Pivot Fields Where There Are Unwanted/Irrelevant Values

When you set up the controls in the Graph ‘’Vertical’’ or ‘’Horizontal’’ Pivoting – Panel #n, Series x form and click the OK button on the form, OnePager uses the field (column) you selected in the form’s dropdown list to return to the source plan and build one Conditional Formatting Rule for EVERY VALUE found in the source plan for the selected Pivoting field (column).

As a result, and depending on the selected Pivoting field’s type and the extent that it is populated in the source plan, OnePager may create a large set of Conditional Formatting Rules in the form’s grid. We recommend, therefor, that the grid in the Graph “Vertical” or “Horizontal” Pivoting – Panel #n, Series x form be carefully reviewed so that entries that are not necessary for the specific goals and objectives of your Chart are turned OFF before the Pivoting operation is started. That is, before you click the OK button on the completed Graph “Vertical” or “Horizontal” Pivoting – Panel #n, Series x form. Unexpected results may occur if this caution is not observed.

Caution 2 – Selecting Pivot Fields Where Values are Unconstrained

To emphasize this further, we recommend that fields (columns) selected for Pivoting be chosen where there are a relatively small number of unique values, say a handful, which become used and displayed in the Pivoted Graph Panels. For example, recommended choices are fields such as those related to Resources (names or groups) and most Text fields where the number of individual unique contents are constrained.

Poor choices, in our opinion, include any Date fields, Percent Complete fields, or Numeric fields where the contents may be unlimited such as Unique ID’s or Costs.

For more information on using the Pivoting controls available in OnePager, please see the article listed in the table below:

Using Pivot Properties Controls for Graphs in OnePager

Combining Multiple Pivoted Series in a Single Panel

Where multiple series are used in a Chart, you have the capability to further pivot graphs using the Combined Series control group which is enabled whenever two or more Series have Horizontally or Vertically Pivoted Columns.

You can also create up to three (3) Graphs in a single Panel. This can be done using the methods described from creating multiple Graphs in multiple Panels. However, when creating multiple Graphs within a single Panel, please be aware that if you use the same Cost Field for two or more of the Series in the single Panel and the Graphs option are the same, one of the Graphs may be obscured and you may not achieve the visual representation desired for your schedule conversation.

Additionally, we DO NOT recommend using different X-Axis units in Graph mode Panels as OnePager supports one X-Axis per Panel.

For more detailed information on creating and editing, and Combining Pivoted Graphs, please see the article listed in the table below:

Using Combine Series Controls for Graphs in OnePager

Creating Pivoted Graphs

OnePager provides a great deal of flexibility to assist you in the preparation of Pivot Graphs. Accordingly, some thought and planning is recommended to go into the creation of a Pivoted Graph in the Chart because other fields besides the time-series field you are going to graph are involved. For example, and this example is used throughout this article, you might want to use a Resource field to create a Pivoted Graph of Cost so as to portray how Costs are being accumulated by various Resources used in the project. As another example, you might need to build a Chart that not only consists of a Chart area but a Graph area that displays Cost attributed to different Phases of the project being tracked by your source plan.

The value of using Pivoted Graphs is to display a time-series field allocated against a field that is directly related or has impact on the time-series field displayed.

The examples used in this article use the Cost field as the time-series field and Resource Names as the related field.

However, before discussing the particulars of creating Pivoted Graphs, a review of the Graphs & Data Cells controls provided by OnePager is provided below.

Graph Panels

You have the option to create up to three (3) Graph Panels in which there are up to three (3) Graph Series to display time-series. Each Graph Panel has its own Display as checkbox, so any or all of the three Graph Panels may be displayed in the graph areas. The Panel sub-tabs are highlighted in the Template Properties form for Graphs in the illustration shown below:

Template Properties Form’s Graphs & Data Cells Tab for Graphs

As shown in the Template Properties form above, there are three Panels for Graphs. Additionally, please note that each Graph Panel contains controls for creating three Series of Graphs per Panel.

Graph Panel Position Controls

For each Panel where you plan to display Graphs, you can locate the Panel at the top of the Chart area or below the Chart area, specify the Time Unit for timespans in the Graph, and specify the Graph Height of the Panel in inches or centimeters. The controls for these properties are directly below the Panel sub-tabs as shown here:

Graph Panel Data Source(s) Control Group - Series

Each Graph Panel can have up to three Series which are designated Series A, Series B, and Series C. Series A is always turned ON when a Graph Panel’s Display as: checkbox is turned ON. To show an example, the illustration below has one Series ON in graph Panel #1 which is Series A:

Graph Panel Data Source(s) Control Group – Series Properties

Within each of the three (3) Series in a Panel for Graphs there are eight (8) controls. These controls allow you to specify the field (column) from the source plan that is used to create the Graph, how you want that source plan data treated before the Graph is displayed, and how the displayed Graph is represented in the Graph areas. A complete discussion of these controls is provided in the article found at the link below:

Graph Mode Data Source(s) Control Group

Graph Panel Data Source(s) Control Group – Pivot Series A, B, and C Controls

Associated with each Series in the Data Source(s) control group is a Pivot Series x button which accesses the controls for creating Pivoted Graphs as shown above in the Chart Properties form’s Graphs/Data Cells tab. Clicking any of the three (3) Pivot Series x buttons when the Series is enabled accesses the Graph Vertical Pivoting – Panel #x – Series X form which is configured for the first step in the creation process for Pivoted Graphs as shown below:

Recall that Column graphs can be Pivoted both Vertically and Horizontally while the remaining Graph types can only be Vertically Pivoted.

When a field is selected in the dropdown list shown above, OnePager returns to the source plan and creates the Conditional Formatting rules requires and assigns colors as shown below:

As shown above, when the above form is accessed the Vertical Pivot tab is enabled. If your plan is to create a Horizontally Pivoted graph, click the Horizontal Pivot tab when available for a Column Graph and the form looks like this:

When a field is selected in the dropdown list shown above, OnePager creates the Conditional Formatting rules requires and assigns colors as shown below:

In the two illustrations above, when the grid is populated, the rows can be edited in a fashion identical to any other Conditional Formatting rule available in OnePager, with the exception that the Pivot Field cannot be edited.

Graph Panel Data Source(s) Control Group – Combine Series

The Combine Series sub-control group is enabled when two or more series in the Panel are set up to present Vertically or Horizontally Pivoted Column Graphs. The enabled Combined Series: sub-control group looks like this when Panel #1, Series A and Series B are set up as Graphs Column and Vertically Pivoted with respect to Resource Names:

The Chart Properties form’s Graphs & Data Cells tab above shows the creation of two Graph Series, (Series A and Series B), both with Column Graphs and both Pivoted Vertically using the Resource Names field as the Pivot field for Series A and Text30 (Phase) for Series B.

The Combined Series control group is enabled because there are at least two Series that have Graphs set as Columns. When there are two or more Series in a Panel with Graph Types set to Column you have the option of combining them either in a Vertical Stack or Horizontally Splayed configuration across the timeline above or below the chart area.

When creating multiple graphs within a single Panel, please be aware that if you use the same Cost Field for two or more of the Series in the single Panel and the Graph types option are the same, one of the graphs may be obscured and you may not achieve the visual representation desired for your schedule conversation.

Further, it is not recommended that you use different field units for multiple Series in the same Panel as this is not supported by OnePager.

The next two sub-sections provide examples for Graph areas containing two or more Column graphs that are Vertically Stacked and Horizontally Splayed.

Vertically Stacked Vertically Pivoted Column Graphs - Example

In the Vertically Stacked example both Series A and Series B need to be Pivoted Vertically as shown in the series of illustrations below.

The first step is to Pivot Series A based on Resource Names. The Graph Vertical Pivoting – Panel 1, Series A form is shown below after the Vertical Pivoting operation completed:

The second step is to Pivot Series B based on Text30 (Phase). The Graph Vertical Pivoting – Panel 1, Series B form is shown below after the Vertical Pivoting operation completed:

The forms shown above display the color assignments made by OnePager in the form of a Conditional Formatting Rules grid where you have the capability to edit the values in the columns and rows of the grid with the exception of the Pivot Field column.

A complete discussion of the Combined Series control group is provided in the article found at the link below:

Using Combine Series Controls for Graphs in OnePager

At the completion of the setup described above, clicking the OK or Apply button on the Chart Properties form creates a Graph Area that looks like this for Panel #1, Series A and Series B that are Vertically Stacked:

In the illustration above, the controls in the Chart Properties form’s Graphs/Data Cells tab’s Graph Elements control group for the Legend Properties were used to highlight the Legend text in the Graph Area.

Horizontally Splayed Vertically Pivoted Column Graphs

You also have the option to represent the multiple Series of Column graphs that are Vertically Pivoted in a Horizontal Splayed configuration. To make the change simply click the Splay Horizontal button in the Combined Series control group as shown below:

After making the above edit, clicking the OK or Apply button on the Chart Properties form to reconfigure the Panel #1, Series A and B in the Chart’s Top graph area to look like this:

In the above Chart, the Graph area shows two Column Graphs configured from left to right in each Quarter of the timeline where Series A is represented as a column graph horizontally pivoted based on Resource Names and Series B represented as a Column Graph Horizontally Pivoted based on Text30 (Phase).

A complete discussion of the Combined Series control group is provided in the article found at the link below:

Using Combine Series Controls for Graphs in OnePager

Caution – Attempting Multiple Vertically Stacked Horizontally Pivoted Column Graphs

OnePager provides a warning message should you attempt to create two or more Series in Panel configured as Vertically Pivoted Column graphs as shown in the illustration below:

When the Combined Series is changed from Stacked Vertically to Splayed Horizontally, the resulting Graph area looks like this:

You can also create up to three (3) graphs in a single Panel. This can be done using the methods described from creating multiple graphs in multiple Panels. However, when creating multiple graphs within a single Panel, please be aware that if you use the same Cost Field for two or more of the Series in the single Panel and the Graphs option are the same, one of the graphs may be obscured and you may not achieve the visual representation desired for your schedule conversation.

Graph Elements Control Group

The Graphic Elements control group in the Graph mode consists of four checkboxes used to enable and disable the four corresponding buttons as shown here:

Checking any of the four checkboxes ON as shown above enables the button. The four checkbox and button combinations provide control form for:

Y-Axis Properties
X-Axis Properties (Enabled Only When the Panel has a Column Graph Horizontally Pivoted)
Graph Legend Properties
Graph Border Properties

These controls allow you to establish Y and X axis properties, configure the graph’s Legend, and establish the borders for the Graph Panel. A complete discussion of these Graph Elements controls are provided in the article found at the link below:

Managing Graph Elements for Graphs in OnePager
Managing the Graph Y-Axis in OnePager
Managing the Graph X-Axis in OnePager
Managing the Graph Legend in OnePager Charts
Managing Graph Border Properties in OnePager
Graph Elements Control Group

Editing Pivoted Graphs

Once a Chart is enhanced with Graph and/or Data Cell Panels using the creation techniques available with the Template Properties and Chart Properties forms it is easy to make edits to the Chart and the Graph areas using these same tools. Making edits to existing Graph and/or Data Cells Panel is only a manner of accessing the current Chart Properties form, navigating to the Graphs/Data Cells tab, selecting the desired Panel and/or Series and using the controls to select alternative settings. You may have to access the available sub-forms to edit Y-Axes, X-Axes, the Graph Legend, Graph Border settings, and value formats. These sub-forms are all available from the Graphs/Data Cells tab.

Summary

This article provides an overview of the capability in OnePager’s Graphs/Data Cells premium feature to create, display, and edit Pivoted Graphs where the pivoting is based on a field (column) imported from your source plan. Additionally, this article is an overview of the pivoting controls available and provides examples of Vertical and Horizontally Pivoted Graphs.

Creating & Editing Graphs & Data Cells in OnePager	Creating & Editing Graphs In OnePager Charts
Creating & Editing Data Cells In OnePager Charts	Combining Horizontally Pivoted Column Graphs in Panels and Series in OnePager Charts
Combining Vertically Pivoted Graphs in Panels and Series in OnePager Charts	Managing Panels and Series for Graphs & Data Cells in OnePager Charts
Using Template Properties Forms to Create Graphs & Data Cells in OnePager Charts	Selecting Fields for Graphs & Data Cells in OnePager
Using the Skip Zeros Checkbox in Graphs & Data Cells	Using the Cumulative Checkbox in Graphs & Data Cells in OnePager
Selecting Interpolation Options for Graphs & Data Cells	Selecting Include Options for Graphs & Data Cells in OnePager
Selecting Graph Type Options for Graphs in OnePager	Selecting Series Style Options for Graphs in OnePager
Using Pivot Properties Controls for Graphs	Using Combine Series Controls for Graphs in OnePager
Managing Graph Elements for Graphs	Managing the Graph Y-Axis in OnePager
Managing the Graph X-Axis in OnePager	Managing the Graph Legend in OnePager Charts
Managing Graph Border Properties in OnePager	Managing Formatting for Data Cells
Managing Data Cell Label and Data Cell Formatting in OnePager	Managing Number Formats for Data Cells in OnePager
Snapshot-Dependent Graphs & Data Cells

Conditional Formatting Rules for Horizontal & Vertical Pivoted Graphs	Using Hover Boxes in the Chart & Graphs
Graphs & Data Cells Tab	Template Only Features for OnePager Pro
Gallery of Graphs and Pivot Options