Best Practices

Standards and Best Practices are critical for the methodical construction of dashboards and consistency between them. They facilitate rapid dashboard development and easier collaboration between developers who wish to share their ideas. Developers can more easily help each other fix problems in a common and consistent coding environment. Even the most challenging specifications can be realized when building atop a well-constructed foundation.

Crystal Dashboard Standards and Best Practices

I.       SPREADSHEET

A.    Spreadsheet Structure

1.      Sheet set:

a)      must only include sheets from the following list and must be ordered as follows:

(1)   od (ObjectData)

(2)   f (Formulas)

(3)   p (Parameters)

(4)   d (Data)

(5)   Notes

(6)   Test01, Test02, … (Test Data)

2.      Sheet purpose:

a)      od-sheet:

(1)   must contain the data passed to the Canvas

b)      f-sheet:

(1)   should perform most data processing

c)      p-sheet

(1)   must contain:

(a)    all user input

(b)   received connectivity status

(c)    SQL parameters to be sent

(2)   may contain

(a)    data sent to the Canvas for user selection

d)     d-sheet:

(1)   must receive data from data connections

e)      Notes sheet:

(1)   must contain specifications which override specific rules

(2)   must contain definitions for non-standard abbreviations which are used in the dashboard

f)       Test01, Test02, … sheet:

(1)   must be a replica of the d-sheet, except for:

(a)    contents of cells which receive data from data connections must contain hardcoded test data

3.      Test Data, Notes, d- and f-sheet may not send data to the Canvas

B.     Formula Usage 

1.      Formulas may not reference a cell in Test Data or Notes sheets

2.      Formulas may not reference a cell in a sheet to the left, except for:

a)      p-sheet formula may reference a cell on the f-sheet

b)      yellow cell may reference a cell on any sheet

3.      d-sheet, Notes and Test Data sheets should not contain formulas

4.      A sheet may not be specified in a formula to refer to a cell in the same sheet

5.      Calculation errors and exceptions must be intercepted and displayed as relevant business text, such as:

a)      ‘n/a’ for #VALUE!

b)      ‘n/a’ for #DIV/0!

c)      ‘’ for #N/A

6.      Formula in a cell to which the Dynamic Visibility > Show component only if status matches key > Status property is bound should return 0 or 1

7.      INDEX() function should be used where the list of cells to choose from is sequential

C.     Text 

1.      Construction:

a)      general:

(1)   English words entered by the programmer must be spelled correctly

(2)   Sentences must be governed by standard English rules

(3)   Articles and auxiliary verbs may be omitted from error messages

(4)   Unnecessary words and symbols may not be used

(5)   Singular nouns should be used

(6)   Rules used in client data may take precedence over other rules

b)      case:

(1)   Each word must begin with an uppercase letter, except for:

(a)    sentences and incomplete sentences

(b)   If article, preposition or conjunction is not the first word in the text, it must begin with an lowercase letter

c)      abbreviations:

(1)   Abbreviations used must be standard, widely known, approved or listed on the Notes sheet

(2)   If an abbreviation was used instead of the full identifier once, it should be used throughout the dashboard instead of that identifier

(3)   ‘Versus’ must be abbreviated as ‘vs’

D.    Spreadsheet Formatting

1.      Tab colors must be:

a)      od:

(1)   Standard Colors > Light Green (146,208,80)

b)      f:

(1)   Theme Colors > White (255,255,255)

c)      p:

(1)   Standard Colors > Yellow (255,255,0)

d)     d:

(1)   Standard Colors > Orange (255,192,0)

e)      Notes, Test01, Test02, …:

(1)   No Color

2.      Cell fill colors must be:

a)      default:

(1)   No Fill

b)      containing ID Block, Reference Block of a table header:

(1)   Standard Colors > White > Darker 15% (216,216,216)

c)      sending data to the Canvas object:

(1)   Standard Colors > Light Green (146,208,80)

d)     sending data to the data connection:

(1)   bright green (0,255,0)

e)      receiving data from the Canvas object:

(1)   Standard Colors > Yellow (255,255,0)

f)       receiving data from the data connection:

(1)   Standard Colors > Orange (255,192,0)

g)      both sending and receiving data from Canvas object:

(1)   located on the p-sheet:

(a)    Standard Colors > Yellow (255,255,0)

(2)   located on the od-sheet:

(a)    Standard Colors > Light Green (146,208,80)

h)      unused cells in a table:

(1)   Standard Colors > White > Darker 5% (242,242,242)

3.      Text and data must be:

a)      default:

(1)   Calibri | 11pt | Regular | Theme Colors > Black (0,0,0)

b)      cell not containing a formula within a table body:

(1)   Bold, except for:

(a)    unused cells in a table

c)      table header:

(1)   18pt | Bold | Bottom Align | Center, except for:

(a)    if Title Block is not entirely visible it must be left-justified

(b)   if Reference Block contains more than seven references it should be 11pt

d)     row and column headers:

(1)   Standard Colors > Blue (0,112,192), and:

(a)    Center, except for:

(i)     row header must be left-justified

(ii)   row header with a numeric value must be right-justified

(iii) text width which can exceed cell width must be left-justified

4.      Column width (for non-empty columns):

a)      must be a multiple of 4

b)      if ≤ 8 then it must be a multiple of 2

c)      should not be set to accommodate the ID, Title or Reference Block

5.      Row height must be 15

6.      Table borders:

a)      regular border:

(1)   must be used in table bodies

b)      thick border:

(1)   must enclose

(a)    table body

(b)   table header

(c)    table extension

(d)   auxiliary table

(e)    largest horizontal or vertical groups of data within table body

(2)   must separate

(a)    groupings from values and groupings from groupings

(b)   table data from row/column headers

c)      no border:

(1)   border may not be used to separate ID, Title and Reference Blocks in a table header

d)     Light Green border:

(1)   must be used for non-Light Green colored cell from which it is necessary to send data to the Canvas object

e)      Yellow border:

(1)   must be used for non-Yellow colored cell which is necessary to use for receiving data from the Canvas object

7.      On the sheets to the left of the d-sheet thousand separators should be used in numbers which represent quantities

8.      Cell containing a rounding formula should be formatted to the minimum amount of decimal places needed to show the precision of the rounding

9.      Spreadsheet formatting rules may be ignored on the Notes sheet

10.  If the table is used by the Canvas object directly, its formatting rules may be overridden

E.     Table Positioning

1.      Distance from sheet boundaries to tables:

a)      Table must be separated from the top of the sheet with one blank row if there is no table blocking it and the data flow permits

b)      Table must be flush to the left border of the sheet if there is no table blocking it

c)      Matching headers across tables may be aligned to one another

2.      Distance between tables:

a)      Vertical distance between two tables must be at least two blank cells

b)      Horizontal distance between two tables must be at least one blank cell

c)      Corners of tables may not touch

3.      Tables should be aligned as closely as possible to the data flow, except for:

a)      Tables in the od-sheet may reflect the order of the objects on the Canvas

b)      Extreme table dimensions may take precedence over the data flow

F.      Table Header

1.      Table header:

a)      may not be narrower than the table body

b)      should be left-aligned with the table body

2.      Structure:

a)      table header must consist of 3 separate merged cells that are 2 rows in height, and:

(1)   ID (leftmost) Block must contain the table ID

(2)   Title (central) Block must contain the name of the table

(3)   Reference (rightmost) Block must list IDs of all other tables that the current table references in its formulas

3.      ID Block:

a)      table ID must be composed of the worksheet abbreviation (T – Test data, D – Data, P – Parameters, F – Formulas, OD – ObjectData) and the ordinal number of the table on the sheet, and:

(1)   numbers must be given to tables in order from left to right incrementing down one row at a time

b)      contents must be visible

c)      may be more than one column in width

4.      Title Block:

a)      content must be visible or the cell must be at least 32 units in width

5.      Reference Block:

a)      general:

(1)   may be more than one column in width

(2)   contents must be visible

b)      Table ID list:

(1)   must be constructed using cell references to the leftmost blocks of other tables’ headers, and:

(a)    must contain IDs in descending sheet and ID order

(b)   IDs must be separated by commas

(2)   if empty, must be represented by ‘-’

(3)   must be enclosed in square brackets 

G.    Table Naming Conventions

1.      od-sheet:

a)      Tables must be named after the theme of the data they supply and the type(s) of the object(s) which they supply, and:

(1)   groupings should not be included in the data theme

(2)   if more than one table with the same data theme supplies data to an object, the object type in each table name may be suffixed with a colon followed by the name of the object property to which the data is supplied

(3)   if a table header contains multiple object types, they must be enumerated in a comma-separated list in alphabetical order

(4)   object type in the list must be singular, except for:

(a)    if the table provides data for several objects of the same type, object type must be plural

(5)   if metrics in the table change dynamically, all possible metrics should be given in a list and separated by ‘or’

2.      f- sheet:

a)      table must have a meaningful name, such as:

(1)   the metric(s) + ‘Summarized by’/‘Filtered by’ + groupings

(2)   one which reflects its purpose

(3)   one which describes its product

3.      p-sheet:

a)      Tables processing user input:

(1)   must be named after the theme of the data they receive and the type of the object(s) which they receive the data from, and:

(a)    object type in the list must be singular, except for:

(i)     if the data comes from several objects of the same type, object type must be plural

(2)   if a table header contains multiple object types, they must be enumerated in a comma-separated list in alphabetical order

b)      Tables exchanging data with data connections:

(1)   must be named after the theme of the data they contain suffixed with ‘(Data Connection)’ 

4.      d-sheet:

a)      table name may consist of a list of metrics + ‘by’ + a list of groupings, and:

(1)   groupings must be listed first from the axis opposite to the metric headers from inside out, and then from the same axis as the metric headers from inside out

(2)   lists must be complete

(3)   items in lists of metrics or groupings must be separated by ‘,’ with the last item separated by ‘and’

(4)   if there is only one metric, it must be considered to be on the horizontal axis

(5)   lists may contain a single item

(6)   if metrics or groupings in the table change dynamically, all possible metrics or groupings should be given in lists separated by ‘or’, except for:

(a)    large lists of dynamic metrics or groupings may be replaced with 'Dynamic Metric' or 'Dynamic Grouping'

5.      Two tables with different data themes may not be merged even if they have the same headers

H.    Row and Column Headers 

1.      Every column must have a distinct column header, except for:

a)      single column header ‘Text’ in a merged cell occupying several columns may be used for multiple value columns in a table

b)      if header is in the od-sheet table and required to be blank by an object 

2.      Every row must have a distinct row header, except for:

a)      in a table which has as its primary purpose to provide labels for parameter selection to a single property or provide headers to another table

b)      in a single column table containing data not related to a real-world or material entity

c)      in a single column table containing a list of self-identified items

d)     in a table extension or auxiliary table

3.      Columns with headers must be to the left of columns with values

4.      Rows with headers must be above rows with values 

5.      In a table body, every row or column in a group must have a header with the same text, value or interpreted value

6.      If a set of headers repeats, a higher tier of headers must also be used

7.      Boolean column or row must be suffixed with 'T/F' and either state the boolean condition being determined or contain the name of an object property

8.      Table extension must have either column or row headers independent from the main table body, and:

a)      column headers in the main table body must apply to a table extension located inline below

b)      row headers from the main table body may not apply to a table extension located to the right

9.      Column headers of an auxiliary table must be one row lower than the lowest tier of the column headers of the main table

10.  If objects of different types have the same name, which appears in a row or column header, and the object to which the object name belongs cannot be determined from the table name, the name in the header should be suffixed with its object type in parentheses

11.  Column header on the od- or p-sheet may be named after an object property which uses the column

I.       General Organization

1.      Unused formulas and text may not be present

2.      Cell containing a formula or text must be located within a table 

3.      Before saving an .xlf file the cursor on each sheet should be placed in cell A1 and the od-sheet should be selected

II.    CANVAS

A.    Object Browser

1.      Objects of the same type must have distinct names

2.      Object Browser name:

a)      default:

(1)   Abbreviations used must be standard, widely known, approved or listed on the Notes sheet

(2)   must have any word or abbreviation it contains capitalized

(3)   must contain information, in combination with names of hierarchical groups to which the object/group belongs, about object’s/group’s contents, usage, placement, or leading objects/groups in it

(4)   may not contain spaces or unnecessary symbols

(5)   should use singular nouns whenever possible

b)      object, except objects that can contain other objects/groups:

(1)   default:

(a)    must not contain unnecessary words, such as:

(i)     name or part of the name of the Xcelsius component on which the object is based, except for:

(a)    Local Scenario Button may

(b)   Connection Refresh Button may

(ii)   name or part of the name of the group containing it, except for:

(a)    group names or their parts may be used to create a name distinct from other object names, and:

(i)     within an object name the group names must be listed first and in hierarchical order

(2)   geometric object (e.g., Horizontal/Vertical Line, Rectangle, Ellipse):

(a)    should indicate what it encloses, separates or connects, and:

(i)     ‘|’ must appear between separated items

(ii)   ‘-‘ must appear between connected items

(3)   Background:

(a)    should indicate what it encloses, separates, hides or blocks, and:

(i)     ‘|’ must appear between separated items

(ii)   hidden item must be prefixed with ‘Hides’

(iii) blocked item must be prefixed with ‘Blocks’

c)      group, or an object that contains other groups/objects (e.g., Tab Set, Panel Container, Canvas Container):

(1)   default:

(a)    may not contain unnecessary words, such as:

(i)     ‘Group’ in a group name, except for:

(a)    if ‘Group’ is integral in the description of the data of the group, it may

(ii)   name or part of the name of the group containing it, except for:

(a)    group names or their parts may be used to create a name distinct from other group names, and:

(i)     group names or their parts must be listed first and in hierarchical order

(b)   if it contains several objects/groups serving a similar purpose or several leading objects of the same type/category, it may be plural

(2)   Tab Set:

(a)    should consist of names of the tabs separated by ‘|’ 

3.      Objects or object groups which are similar or form a functional and/or cohesive unit should be grouped

4.      Service objects (e.g., History, Source Data) should be made invisible using Object Browser Hide function

B.     Object Text Formatting

1.      Objects fonts must be:

a)      default:

(1)   Appearance > Text > … > Format Selected Text > Verdana | 14pt | Regular | Black

b)      control-only object (e.g., Check Box, Combo Box, Label Based Menu, Radio Button, Slider):

(1)   Title:

(a)    Bold

c)      objects forming a table (e.g., List View, Spreadsheet Table, Grid):

(1)   column headers:

(a)    Bold | Center | no more than 2pt smaller and no larger than the values

d)     Toggle/Push/Local Scenario Button:

(1)   Label(s):

(a)    Bold | Center

e)      Charts object category:

(1)   Title or an object acting as such:

(a)    18pt

f)       Slider:

(1)   Value | Limits:

(a)    Bold

g)      Label:

(1)   Text:

(a)    chart title:

(i)     18pt

(b)   control-only object title:

(i)     Bold

(c)    error message:

(i)     Bold | Red

(d)   formula:

(i)     12pt | Bold | Italic | Gray (White > Darker 50%)

(e)    “disabled combo box”:

(i)     Bold | Gray (White > Darker 50%)

(f)    watermark:

(i)     Bold | Gray (White > Darker 15%), and:

(a)    Size of watermark on a chart should be 17% to 23% of the chart height excluding X-axis labels

(g)   dashboard name:

(i)     28pt

(h)   CopyrightNotice:

(i)     11pt | Gray (White > Darker 50%)

h)      Connection Refresh Button:

(1)   Label:

(a)    Bold | Red

i)        URL Button:

(1)   Label

(a)    CopyrightNotice:

(i)     11pt | Gray (White > Darker 50%) | Color > Labels > Pressed Color: Gray (White > Darker 35%)

2.      Formula text for display to user:

a)      Mathematical operators (addition [+] subtraction [-] multiplication [*] division [/] ) and comparison operators (greater than [>] less than [<] equal [=] greater than or equal [>=] less than or equal [<=]) must be preceded by, and followed by one space, except for:

(1)   If they are operating on two constants, they may not be separated by spaces

b)      Commas (,) and ellipsis (…) must be followed by one space

c)      Function names for displaying on the Canvas must be all uppercase letters

3.      Information presented to the user may not be repeated, except for:

a)      if text is represented by a color and appears more than once, both may appear together, except for:

(1)   if such a text and color combination needs to appear often, only the color should be used

4.      List of time periods in Selectors must be displayed in ascending order, except for:

a)      Items which represent a year or span of years in a Selector list should be displayed in descending order 

5.      ‘Not Available’ must be represented with ‘n/a’ for individual values within a data set and ‘N/A’ for an entire object data set 

C.     Object Positioning and Sizing

1.      Objects from the same category in a group should be aligned or connected

2.      Left and right margins defined by the distance between the Canvas edge and perimeter objects with visible outlines should be the same

3.      Alignment of objects must be done to the precision of Xcelsius aligning functions

4.      Objects of the same type, which have similar internal space requirements, should be the same size 

5.      Canvas should have proportions corresponding to standard monitor dimensions, and:

a)      height and width in pixels should be a multiple of 10

6.      Error message should not hide other information, except for:

a)      error message detail window may hide other information

7.      Bar/column groups on a chart should be separated by approximately the width of a single bar/column. Group can consist of one or more bar/column(s).

8.      First bar/column on a chart should be located close to the values axis 

9.      Label displaying dashboard name should be located 22px from the left and 10px from the top of the header picture

10.  Distance between two vertically stacked Combo Boxes which share or have no column header should be 3px

11.  Distance between two dependent sequential horizontal Combo Boxes which share or have no row header should be 4px

D.    Object Colors

1.      Object colors must be relevant, logical and complement each other 

2.      Color properties of an object must be set through the color scheme where possible

3.      For control objects, the mouse-over background color must be the same color as the background color of the selected item, but of a lighter shade, and darker than background color of default item

4.      For a Tab Set or object(s) acting as such, the shade of the mouse-over background must be darker than the background of the selected tab, but lighter than the other tabs

5.      Color and shade of the background of a selected item should not be the same as the tab background of a Tab Set or an object acting as such

6.      Red should only be used in objects to identify or detect an error or warning

7.      Selectors and slider colors:

a)      single color should be used across properties (e.g. 0,0,102) for:

(1)   default:

(a)    Appearance > Color:

(i)     Label(s) > Over Color

(ii)   Label Background(s) > Selected Color

(iii) Drop-Down Button > Button Color | Selected Button Color

(iv) Scroll Bar > Thumb Color

(2)   List View:

(a)    Appearance > Color:

(i)     Header Labels > Over Color

(ii)   Header Background > Selected Color

(3)   Radio Button:

(a)    Appearance > Color:

(i)     Marker > Selected Color

(4)   Slider (all types):

(a)    Appearance > Color:

(i)     Marker Color

(5)   Toggle Button:

(a)    Appearance > Color:

(i)     Button > On Color

8.      List View:

a)      Appearance > Color > Label Backgrounds:

(1)   Row 1 Color should be:

(a)    Standard Colors > White (255,255,255)

(2)   Row 2 Color should be:

(a)    Standard Colors > White > Darker 5% (242,242,242)

E.     Object Properties 

1.      Text properties should be bound to the spreadsheet where possible

2.      Property which has a visible effect on the Canvas and is always set to the same value in multiple objects must be bound to the same cell in the spreadsheet 

3.      Properties that have no effect should be set to the default value

4.      If Dynamic Visibility > Show component only if status matches key > Status property is set, Key property should be set to 1

5.      If user interaction with an object or its part is undesirable, Behavior > Common > Enable Interaction and properties with a similar function must be unchecked in the object. If it has no such properties or unchecking them does not prevent interaction, the object or its part should be blocked by another object with General > Block Mouse Events property checked.

6.      Data > By Series option should be used in a Chart where available

7.      If Chart displays more than one metric, it should have a legend or its equivalent 

8.      If blank labels can appear in a list of Selector, Behavior > Common > Ignore Blank Cells > In Values must be checked

9.      General > Data Insertion > … > Insertion Type property of Selectors should be set to the same value unless a Selector has a special requirement

10.  Number of Labels Displayed property of Selectors should be maximum amount of labels to be displayed, and:

a)      drop-down box should not exceed half the dashboard height or go beyond the dashboard boundary

11.  General > Data Insertion > Destination property of different Combo Boxes should not be bound to the same cell, except for:

a)      if Combo Boxes have the same set of choices and the same default value, they may

12.  Error message and Connection Refresh Button

a)      Behavior > Animation and Effects > Entry Effect > Type propery may be set to Wipe Right-Down

b)       Behavior > Animation and Effects > Entry Effect > Duration(seconds) property may be set to 7

13.  Combo Box Title placement

a)      Position > Left, and:

(1)   Offset > X > -4

(2)   Offset > Y > 0

b)      Position > Top, and:

(1)   Offset > X > 0

(2)   Offset > Y > 4

F.      Design Precepts 

1.      Form must follow function

2.      Numbers must be correct

3.      Things must be consistent

4.      Information must be clear and legible

5.      Code or text which is not useful and relevant should not exist

6.      Unnecessary duplication must be eliminated

7.      Products may not fail to function