Templates and Data

Overview of Templates and Data

Initially, Professional Services configures the project template, form templates, and data fields in Qeystone. Professional Services or Qeystone administrators can modify templates and data fields, but it's a good idea to understand the details of data before you make any modifications.

Terminology

Form
Forms are the primary way for users to enter project data, which can be used in Dashboard reports. However, not all forms collect Dashboard data. Forms can contain text, formatting, instructions, and controls for collecting project data.
Control
The mechanism for entering data on a form. Controls may or may not be shared with data fields. Examples include drop down lists, calendar pop-ups, rich edit data fields, and more.
Data Field
The mechanism that stores user-entered data values. When a data field is used by the Dashboard, you can summarize, aggregate, filter, and search for values in the Dashboard. Data fields can be single value items, such as the project Start Date or Project Leader, where there is only one value for the entire project. Or, data fields can contain multiple entries, such as a table of team members that has multiple rows of relevant data.
Data Category
A group of related data fields. You create a data field within a data category. The Financial Data category has data fields such as Hard Savings, Soft Savings, and Implementation Costs. The Team Member category has data fields such as Name, Phone Number, and Email Address.
Data Sharing
The method of setting up a link between a form control and a data field in the Qeystone project template. When the user enters a value in a control that is shared to a data field, the value is stored in the data field. The value is displayed in other forms in the project without the user having to retype it. For example, when you type a value for Start Date, the same value appears on every form that contains Start Date. When you share data fields in a project template, they are automatically available in the Dashboard, where they are aggregated with other user-entered data to create reports.

Data Field Types

Qeystone data fields have the following data types:
Type Examples
Text Project Name, Product/Service
Integer Hard Savings, Implementation Costs
Decimal Cost/Benefit Ratio, Percent Complete
Date Start Date, Due Date, Completion Date
List Business Unit, Division, Location, Region, Replication Opportunity? (Yes, No)
Formula Net Savings, Duration
Rich Text Problem Statement, Scope

A note about List fields

You can select only one value from a list per project. For example, the list field, Methodology, might have the values DMAIC, Kaizen Event, 5S, Just Do It, and DFSS. You can select only one value because a project cannot have more than one methodology.

A note about Formula fields

The Dashboard can only aggregate the values in a data field, such as the sum of Hard Savings, across multiple projects. The Dashboard does not have a way to calculate a new value for each project using a formula. For example, the Dashboard can display each project's Start Date, and each project's End Date, but it cannot calculate the duration of each project by subtracting the End Date from the Start Date. Instead, you must define the Duration field as a formula in the project templates to aggregate and display project duration in the Dashboard.

Data Categories

Qeystone has two types of data categories: Single value and Multiple entry.
  • A single value data category is for data fields that can have only one value per project. For example, a project has one value for Duration, one value for Methodology, and one value for Start Date.
  • A multiple entry data category is for data fields that can have rows of values per project. You can think of multiple entry categories as a table of data within each project, where data fields in the category are the columns of the table and the data values are the rows. For example, the Team Members category has one row per person, and one column for each data field, such as Name, Email, and Group. Each row, or entry, in the Team Members table is a team member.
Projects can have any number of rows, including none, in their multiple entry categories.
Because multiple entry data fields are displayed as a table, you must insert a data table on the form and apply data sharing to the repeating row of the table. Then, share the data table to a multiple entry category.
Next, share the control inside the repeating row with a data field in the selected category.

Example: Defining Data Fields for Effective Dashboard Reports

A Continuous Improvement Director (CID) wants to track the duration of DMAIC projects on the Dashboard. However, she knows that there might be differences in requirements and levels of difficulty between green belt projects and black belt projects. So, the CID wants to see average duration by belt level in a Dashboard report summary, much like the one in the following image.

STEP 1: Determine what data is required on the Dashboard

To create the summary, the CID needs to know the following information about each project:
  • Duration (in days), calculated by finding the number of days between the project's start date and end date.
  • Belt Level, classifying each project as either a black belt or green belt project.
  • Methodology, so she can filter out non-DMAIC projects such as Kaizen events, 5S projects, quick Just-Do-It projects, and long research-driven DFSS projects.

STEP 2: Define data fields within one or more project templates

Currently, the CID is interested only in the duration of DMAIC projects, but she realizes that she might also want to see duration data for other project types in the future. Therefore, she adds the Duration, Belt Level, and Methodology data fields to all the project templates in her company's template repository.

  1. To modify a DMAIC Project template, start a new project from the DMAIC Project template.
  2. To define the three new data fields, choose Tools > Customize, and then add the three data fields to the existing Project Data category.
    1. To add Duration, add a new data field and choose Formula as the data field type. In Expression, enter the expression as: DATEDIF([Start Date], [End Date]). This calculates the number of days between the date entered in the Start Date data field and the date entered in the End Date data field.
    2. To add Belt Level, add a new data field and choose List as the data field type. Enter a default value of Blank, and then add the following values: Green Belt and Black Belt. Ensure that each value matches its display name.
    3. To add Methodology, add a new data field and choose List as the data field type. Enter a default value of Blank, and then add the following values: DMAIC, Kaizen Event, 5S, Just Do It, and DFSS.
  3. Save the project as a project template to overwrite the existing DMAIC Project template.
Important

Because the new data fields are defined in at least one project template, the Dashboard immediately adds them to the set of data fields available in Dashboard reports.

Tip

To control which data fields are available when creating Dashboard reports, open the Dashboard, click the Settings tab, and then choose Select Fields to Display.

The new data fields are now available for reporting in the Dashboard.

STEP 3: Create one or more forms in the project templates

To collect the Duration, Belt Level, and Methodology of each project, the CID must provide users with a way to enter this information. She decides to add this data field to the existing Project Charter template. Because the Project Charter is included in the DMAIC Project template, the CID starts a new project from the DMAIC Project template and modifies the Project Charter in the Roadmap.

  1. In Qeystone Tools, choose New > Project, and then choose the DMAIC Project template.
  2. In the Roadmap, double-click Project Charter.
  3. In design mode, add a Text Box control below the Date Picker controls for the Start Date and End Date data fields, and give it the label, Duration (in days).
  4. In the Properties dialog box for the Text Box control, name the control and choose Apply data sharing. Set the Data Source to the new Duration data field.
  5. Now that you added Duration, use Dropdown controls to add Belt Level and Methodology.
  6. Switch to fill out mode.
  7. Save the form as a template to overwrite the existing Project Charter.
    Important

    This step ensures that the most recent version of the Project Charter is available in the template repository.

  8. To ensure that every project template in the repository contains the most recent version of the Project Charter, including the newly added data fields, replace the old Project Charter with the most recent Project Charter in every project template.
When you add a form template that has data fields that are shared with form controls, the data fields are automatically added to the project. The following steps show how to replace the old Project Charter in the Kaizen Event project template:
  1. Open the Kaizen Event project template.
  2. Delete the existing Project Charter.
  3. Choose New > Form, and then add the most recent version of the Project Charter.
  4. Choose Tools > Customize Data, and then verify that Duration, Belt Level, and Methodology are in the project.
  5. Open the Project Charter, and then from Methodology, choose Kaizen Event.
  6. Save the project as a project template to overwrite the existing Kaizen Event project template.
  7. Repeat this process to update the remaining project templates.

STEP 4: Have users start projects from the project templates

After the project templates are updated, Qeystone users can use the templates to start projects. When users enter data in the fields on the forms, and then save their projects, the values they are enter are available for use in the Dashboard reports.

Example: Setting Up Financial Data in Qeystone

Frequently, there are sets of single value data fields that relate to multiple entry data fields, either conceptually or through formula expressions. The CID wants to see project savings data on the Dashboard. She wants the Dashboard to answer the following questions:
  • How much are the continuous improvement projects saving the company, in terms of hard savings and soft savings?
  • How much are we spending to implement projects?
  • What are the savings and costs by month?
  • What are the savings and costs of projects that were started within the current fiscal year?
From her Dashboard requirements, the CID knows that she needs at least three data fields:
  • Hard Savings
  • Soft Savings
  • Implementation Costs
Also, she wants to create a Net Savings data field so she can look at savings that are already factored in to implementation costs.
  • Net Savings
Finally, she wants to summarize savings by month, so she must associate each savings value with a corresponding month (date).
  • Savings Date

Before she defines the data fields, the CID must decide whether to set up the financial data using single value data fields or multiple entry data fields.

Option 1: Set up financial data as single value data fields

When you use single value data fields, each project records a single value for hard savings, soft savings, implementation costs, net savings, and savings date.

The following image shows financial data as single value data fields:
On the Dashboard, you can then summarize Hard Savings by the Project Completion Date. All $2,580,000 of the project's hard savings are associated with its Project Completion Date of April, 2014. If this is acceptable, then setting up financial data fields as single value data fields is sufficient.

Option 2: Set up financial data as multiple-entry data fields

More frequently, project savings are distributed over a period of time. For example, a project's savings might be distributed evenly over 12 months ($215,000 every month for 12 months = $2,580,000). Or, perhaps the project starts to save money in 2014, but only $110,000 in July, followed by $160,000 in August, and so on, for a total of $2,580,000 in hard savings for all 12 months. To see a project's savings data distributed over a period of time, you must define the savings data as multiple entry data fields to allow for multiple months (rows) of actual savings data, not just a single total.

Notice that total Hard Savings of $2,580,000 is the same in both option 1 and 2. However, when you use multiple entry data fields to define financial data, you can allocate the savings over a period of time, using increments of months, quarters, or years.

The following image shows financial data as multiple entry data fields:

Option 3: Set up financial data using single value and multiple-entry data fields

The choice between single value and multiple-entry financial data fields is not mutually exclusive. Even if you decide to collect monthly savings, which requires multiple-entry data fields, you can also create single value data fields to capture project totals across all months/rows using formulas. The following example shows how you might link multiple-entry financial data to single value data fields using formulas.

Monthly Savings (multiple-entry data fields)
  • Savings Date
  • Hard Savings
  • Soft Savings
  • Implementation Costs
  • Net Savings
Project Totals (single value data fields)
Data Field Formula Description Formula Expression
Project Total Hard Savings Formula that calculates the sum of Hard Savings in the Financial Data Monthly Savings category. SUM([Financial Data Monthly Savings / Hard Savings])
Project Total Soft Savings Formula that calculates the sum of Soft Savings in the Financial Data Monthly Savings category. SUM([Financial Data Monthly Savings / Soft Savings])
Project Total Implementation Costs Formula that calculates the sum of Implementation Costs in the Financial Data Monthly Savings category. SUM([Financial Data Monthly Savings / Implementation Costs])
Project Total Net Savings Formula that calculates the sum of Net Savings in the Financial Data Monthly Savings category. SUM([Financial Data Monthly Savings / Net Savings])
Savings Start Date Date data field n/a
Single value data is especially valuable in the Projects list in the Dashboard because they are displayed directly in the column sets, as shown in the Completion Date and Hard Savings columns in the following image. Multiple-entry data is displayed as a link, as shown in the Financial Data Monthly Savings column. When you click the link, a table containing the savings for each month, is displayed.
By using this site you agree to the use of cookies for analytics and personalized content.  Read our policy