KPI Id

The unique name of the KPI that is shown in the KPI Definition List.

KPI Name

The name of the KPI that is shown in DB Analytics.

Category

The category that the KPI will be listed under when adding a new analytic in DB Analytics.

Class Name

The class name is used by DB Analytics to distinguish between different types of KPIs.

Description

The description of a KPI that will be shown in DB Analytics

First Version No

Version of SAMPro where the KPI was first introduced.

Version Number

Current Version Number.

Case Number

Databasics Fogbugz case that resulted in the KPI being created.

Obsolete?

Indicates if the KPI is obsolete.

KPI Definition User x

Fields to allow custom properties to be specified.

name

The name of the filter that will be displayed next to the text input on the Analytic settings page.

columnname

The name of the column in the table that the filter supplies a value for. Must use the convention
[tableName].[columnName] in order for the filter lookups to function properly.

description

Description of the filter.

value

Prefilled default value for the filter.

type

The type of setting. Not used. Char by default.

lookup

If set to exclusive (lookup=exclusive), DB Analytics will not consider any other filters from the same
table when doing a filter lookup. Typically, a filter lookup will return a list sorted by the filter clicked
on that has values for all the filters from the same table.

options

Allows the user to list the values they want to be able to choose from as a comma separated list.
(options=red,blue,green)

filtercolumn

If set to true (filtercolumn=true), the filters on the page that have the same table specified in the
columnname property will use a where clause created from the value property of the filter.

filtertable

If set to true (filtertable=true), the filters on the page that have the same table specified in the
columnname property as the tablename property of this filter will use a where clause that is exactly
the value specified in the value property. This value can use a token that will be available to the
particular KPI.

tablename

A property that specifies the table that a filter applies to. Used only with filtertable and
@LookupJoin@.

where

Specifies how the @LookupJoin@ or tablejoin property value is being used to join the filter lookup.

tablejoin

A property that specifies the (multiple) columns that a filter is being joined to. Used with
@LookupJoin@ or in a filter. Used with a where property in a filter to specify a join.

Example:
name=@CustomLabel1@;columnname=wbprflecstm1.wbprflecstm1_vle;tablejoin=wbprfle,wbusr;wh
ere=wbprflecstm1.wbprfle_rn=wbprfle.wbprfle_rn and wbusr.wbprfle_rn = wbprfle.wbprfle_rn and
wbusr_rn=@UserRn@;

cleartablestate

A property that when set to true will wipe out the table state (removing sorting, column reordering,
column hiding) when the value is changed. Meant to be used with filters or variables that can alter the
composition of the result set.

the filters that will be joined, the value (what is being joined), and the where clause (how it is
being joined). Should also be hidden.

Example1: tablename=clntste;value=clntste.clntste_rn=rmte_wbusrclntste.clntste_rn;
where=rmte_wbusrclntste.wbusr_rn=@UserRn@;hidden=true;

Example2:
tablename=clntste;tablejoin=rmte_wbusrclntste,wbusr;where=clntste.clntste_rn=rmte_wbu
srclntste.clntste_rn and and rmte_wbusrclntste.wbusr_rn=wbusr.wbusr_rn and
wbusr.wbusr_rn=@UserRn@; hidden=true;

@LookupWhere@

Can be used to set the where clause for a set of filter lookups.

Example:
 tablename=clnt; where=clnt.clnt_id='DBASICS'; hidden=true;

@CustomLabelX@

Where X = [1..5]. Used to represent the Label in wbprfle_cstmX_lbl. This will be replaced with
the proper Custom Field Label value for the user's web profile.
Example:
 clntste.clntste_wbprfle_cstm1_vle as "@CustomLabel1@"

@CustomLabelValueX@

Where X = [1..5]. Used in Drilldowns to represent the data passed as a token as
@CustomLabelX@. This should correspond to wbprfle_cstmX_vle.

name

The name of the variable that will be displayed next to the text input on the Analytic settings page.

description

Description of the variable.

value

Prefilled default value for the variable.

type

The type of variable.
Valid Values:

• time - creates a time picker dialog in DB Analytics.

• date - creates a date picker dialog in DB Analytics.

• int create a input that only allows integer values.

• char (default) normal text input with no restrictions

options

Allows the user to list the values they want to be able to choose from as a comma separated list.

(options=red,blue,green)

hidden

Default false. If set to true (hidden=true), the variable does not show on the Analytic settings page. It
can still be used as a SQL variable if a value is provided.

cleartablestate

A property that when set to true will wipe out the table state (removing sorting, column reordering,
column hiding) when the value is changed. Meant to be used with filters or variables that can alter the
composition of the result set.

@DateOption@

Allows the user to specify a date range that will be reevaluated each time an analytic gets new
data. This can be used in conjunction with @DateStart@, @DateEnd@, or both. Possible values
are:

• Today

• Tomorrow

• Yesterday

• This Week

• Last Week

• Next Week

• This Month

• Last Month

• Next Month

• This Year

• Last Year

• Next Year

• Month to Date

• Year To Date

• XDaysAgo (where X is a number)

• XDaysOut (where X is a number)

• XMonthsAgo (where X is a number)

• XMonthsOut (where X is a number)

• All

• Past

• Future

• TodayAndPast

• TodayAndFuture

• LastXDays (where X is a number)

• NextXDays (where X is a number)

• LastXWeeks (where X is a number)

• NextXWeeks (where X is a number)

• LastXMonths (where X is a number)

• NextXMonths (where X is a number)

• Custom

(Case and spacing insensitive). These values must be supplied to the user by setting the options
property to the chosen values (options=Today,Tomorrow)

@DateStart@

Specify the start date for the Analytic. Must have type=date and takes dates in the form YYYY-
MM-DD. Can be set by @DateOption@ and will be greyed out if @DateOption@ is set to
anything other than Custom.

@DateEnd@

Specify the end date for the Analytic. Must have type=date and takes dates in the form YYYY-
MM-DD. Can be set by @DateOption@ and will be greyed out if @DateOption@ is set to anything other than Custom.

@PeriodOption@

Allows the user to specify a fiscal period range that will be reevaluated each time an analytic gets
new data. This must be used in conjunction with @Period@. Possible values are:

• Last Year

• This Year

• Last Period

• This Period

• Custom

 (Case and spacing insensitive). These values must be supplied to the user by setting the options
property to the chosen values (options=This Period,Last Period)

@Period@

Fills a select list with all the fiscal periods used by SAMPro. Will be greyed out if
@PeriodOption@ is set to anything other than Custom. It can be used without @PeriodOption@.

@GLEntityGroupId@

Fills a dropdown list with the list of GL Entity Groups available for the user. Upon selection, the
GL entities that are selected will be linked to the GL Entity Groups and can be used in a query by
using the variable @GLEntityGroupId@ if a join to the glenntty table is included.

name

The name of the setting that will be displayed next to the text input on the Analytic settings page.

description

Description of the setting.

value

Prefilled default value for the setting.

type

The type of setting.

options

Allows the user to list the values they want to be able to choose from as a comma separated list.
(options=red,blue,green)

hidden

Default false. If set to true (hidden=true), the setting does not show on the Analytic settings page.

title

The title of the dialog that opens from a @Drilldown@ setting. Can use token replacement.

filteronclick

Used with @TabColumnFiltering@ to indicate that the designated Analytic on a page should filter
the other Analytics on a page when a row is clicked

table

Used with @Upload@ to specify the table that the newly uploaded document will be attached to
when the upload message is read by SAMPro. Should use @RefTable@ when possible.

id

Used with @Upload@ to identify the token that will be used by SAMPro to specify the value of the
id for a given table. Should use @RefRn@ when possible.

category

Used with @Upload@ to categorize the type of the uploaded file.

modal

Used with @Drilldown@ to make a drilldown modal. (modal=true)

column

Used with @Drilldown@ to indicate the name of the column that should have a link to a drilldown
on each row.

filter

Used with @TabColumnFiltering@ to indicate the name of the filter that will be replace with data
from the filtered analytic. This is used to update GenericCharts in realtime. (Generic Tables are
filtered automatically) The updated charts are temporary and not saved (as the in-page filtering is
not saved). 'filter=@f[filterName]@'

drilldownWhere

Used with @Drilldown@ to specify a custom where clause snippet to use in place of the
@Drilldown@ token in the drilldown's KPI definition.

Example: drilldownWhere= jb_id = @JobId@;

icon

Used with @Drilldown@ to specify an icon in the menu. Available options: add, pageadd, redo, edit,
cut, copy, share, paste, delete, close, maximize, quit, user, layoutedit, filter, pagefilter, help, about,
settings, refresh.

submenu

Used with @Drilldown@. Indicates a sub menu for the menu that uses the value specified as the
name. Used to create groups of drilldown functions.

menuhidden

Used with @Drilldown@ to specify that a drilldown should not show up in the menu. It should be be
available with the column property.

@Drilldown@

The drilldown setting specifies what actions will take place when a chart or table is clicked on.
The name property can be set for a drilldown and will be the name displayed in the menu if multiple drilldowns are specified for a table or chart. If a chart only has one drilldown, no menu will be displayed. The title property is specific to drilldowns and should be used.

 The value property is the id of the drilldown to be opened.

There are five types of drilldowns set by using the type property. The type can be chart, table, sampro, upload, or email. If the type is set to be sampro (type=sampro), any SAMPro function that a user can run will be executed
by DB Analytics, provided that the user launched DB Analytics from SAMPro. SAMPro drilldowns only work as drilldowns from tables. Drilldowns are explained in more detail in a section below. If used with GenericTables, can set a 'column=[ColumnName]' property to  generate a link to the drilldown in that column.

@UpdateInterval@

Can be used to set a default value for the update interval by using the value=[default] property. It is not used otherwise and is generally hidden. “Update Interval” is a field that is  always present on the Analytic settings page even without a setting.

@ChartTitle@

Can be used to set a default value for the chart title by using the value=[default] property. It is not used otherwise and is generally hidden. “Chart Title” is a field that is always present on the Analytic settings page even without a setting.

@ChartSubtitle@

Can be used to set a default value for the chart subtitle by using the value=[default] property.  It is not used otherwise and is generally hidden. “Chart Subtitle” is a field that is always present on the Analytic settings page even without a setting.

@DataSource@

This setting is replaced with a dropdown list of the names of the current database connections. The two possible connections are the default database and a KPI database. The default value can be set (value=default or value=kpi) and hidden if desired.

@TabColumnFiltering@

Designates an Analytic on a page as having filter control over the other Analytics on a page.
The property 'value=[columnName]' indicates the column that the values filtered in the designated KPI should filter the other Analytics on the page. The property 'filteronclick=true' will apply the value of the specified column to all analytics on a page when a row is clicked.

@SettingsPage@

Allows the settings page for an individual analytic be disabled, or point instead to the tab settings page. If the value is set to 'disabled' the settings page cannot be accessed. If the value is set to 'merged' clicking the settings button on an analytic will instead show the tab settings page

@eTo@

Has a 'value=' property that specifies the email address of the recipient of the email drilldown.  

Can either use an email address or an available registry setting that corresponds to an email address. If a valid email is not given, the 'email-default' registry setting is used.

@eSubject@

Has a 'value=' property that specifies the subject of the email created by the email drilldown.
Can use token replacement.

@eBody@

Has a 'value=' property that specifies the body of the email created by the email drilldown.
Can use token replacement.

@Upload@

Uses the 'table=', 'id=' and 'category=' properties to identify how SAMPro should deal with the uploaded files.

@ButtonControls@

Used to turn off control buttons of an analytic.

Can specify refresh,close,maximize,settings.
[refresh=false;close=true;maximize=false;settings=false;]

@ChartType@

Gives the user the ability to specify the format of the chart. Options are pie, bar, column, scatter, line, spline, area, areaspline, stackedbar, stackedcolumn. (options=bar,column,pie) A default can be set with the value property.

@InvertData@

Gives the user the option to invert the data so that a series becomes a category and a category becomes a series. The options are true or false and a default value should be set. This property allows a user to see the data grouped by the categories typically in the legend.

@xAxisTitle@

Gives the ability to specify a title for the x-axis of a chart. A default can be set with the value property. Token replacement can be used for this setting.

@yAxisTitle@

Gives the ability to specify a title for the y-axis of a chart. A default can be set with the value property. Token replacement can be used for this setting.

@LegendTitle@

Gives the ability to specify a title for the chart legend.

@SeriesHidden@

Gives the ability to hide a series on a chart by default. The hidden chart is not immediately visible but can be displayed by clicking the series name in the legend. The hidden series should be given as a comma delimited list in the value property that corresponds directly to the displayed name of a
column in the SQL statement. (Example: “Select acbd as job” should use 'value=job' if the job column should be hidden.

@ChartColors@

Gives the ability to specify the colors used in creating a chart. The value for this setting is a comma delimited list that uses html colors (red, blue, green,etc.) or hexadecimal notation (#FF00FF).

@PivotData@

Changes the way data is formatted before being displayed. Data of the form
[Series,Category,Value] will be pivoted and turned into a matrix where the series is from the first column, the column header is from the second column, and the value in the cell is from the third column. (value=true)

@Table[AVG,SUM,MIN,MAX]@

Gives the user the ability to add the Average, Minimum, Maximum, Sum for the indicated column.

Example: @TableAVG@ : value=Billings; hidden=true;

@ColSpan@

Gives the user the ability to add a second header row above the main header that can span multiple columns. Can be used to group similar columns together.
Example: value=Address:City,State,ZipCode;

@ColHideLock@

Gives the user the ability to remove GenericTable columns from the Show/Hide menu.
Set the value equal to the names of the columns that will be removed. Will work with ColHide to remove a column completely. Can be used with Invertdata and pivot data if the final column names are used.
Example: value=State,SiteId; name=Prevent From Hiding;

@ColHide@

Gives the ability to hide a column on a table by default. The hidden column can be
made visible using the Show/Hide option if ColHideLock is not set. Used to hide
reference RN and Table names that are used to view documents and notes. The
hidden series should be given as a comma delimited list in the value property that
corresponds directly to the displayed name of a column in the SQL statement.
(Example: “Select acbd as job” should use 'value=job' if the job column should be
hidden.

@InvertData@

Gives the user the option to invert the data so that the first column becomes the
column headers. The column headers then become the first column and the data is
translated accordingly. The options are true or false and a default value should be set.
This property allows a user to see the data grouped by the categories typically in the
legend.

@PivotData@

Changes the way data is formatted before being displayed. Data of the form
[Series,Category,Value] will be pivoted and turned into a matrix where the series is
from the first column, the column header is from the second column, and the value in
the cell is from the third column. The series is displayed as the data in the first column
in a row. (value=true)

@ColRename@

Allows the user to rename a column dynamically after the result set is determined in
SQL. This allows token replacement of column headers.
(value=ColumnName:@Token@,ColumnName2:@Token2@,ColumnName3:Text)

@ListLineText@,@ListLineText[n]@

Specifies the text of the row for a Generic List. The value of this property will be the displayed text. The text can use token replacement to display variables.
Additional tokens available are tokens that are created from the column names of the sql select statement. If multiple cells in a row are desired, use @ListLineText[n]@ (where [n] is a number) to create additional cells.

• text

• textarea

typeattributes

Specify html attributes of the given type of input field.

Example: [typeattributes=rows:10,cols:24;]
when used with the type 'textarea' will create a textarea that is 10 rows tall and 24 cols wide.

options

The data values for dropdowns and radio buttons. A comma separated list with no spaces that contains the values that will be saved to the database when a corresponding option is selected.

optiontitles

The display values for dropdowns and radio buttons. A comma separated list with no spaces that contains the values that will be shown to the user. The values correspond to the options values.

column

The name of the column in the table that will be open to user input.

procedure

The name of the stored procedure to execute when the user clicks the 'Save Changes' button after making changes to an input.

params

A comma separated list of tokens or hardcoded values that will be replaced with values from the KPI's list of tokens and submitted as the parameters of the stored procedure.

onchange

Defines what action should take place after a user clicks the 'Save Changes' button and the input method executes successfully. Default is to overwrite the initial cell with changed data. Possible
alternate options:

• refresh (refreshes the table to get new data from database)

• close (closes the dialog after making the change)

button

The name of the button within the cell that the user will click to make an input change. Default is 'Edit'.