Programmatic Control

Create Documents Programmatically

You have already learned how to create and work with CCH Tagetik Supply Chain Planning Studio documents through the software’s menus and toolbars. This chapter describes how to create and modify documents programmatically using DScript™.

There are many reasons for creating documents programmatically rather than going through the standard menus and toolbars. For example, you may need to:

• dynamically create and/or manipulate a graph at the end of a data processing routine;
• write a function to color nodes of a tree or set node properties based on node values;
• create a custom graph or table with a combination of properties that are not available through the standard menus; or
• create your own add-in that automatically creates/modifies Studio documents.

This chapter describes how to programmatically create and manipulate the following CCH Tagetik Supply Chain Planning Studio documents:

• Sheets (Tree Sheet, Text Sheet, Data Table, Report, Diagram);
• Charts (Presentation Table, Graph (2-D), Surface Plot (3-D)); and
• Nodes

Create and Work with Documents

The following code fragment will create a new Presentation Table called MyTable and specify its data, vertical legend, and horizontal legend:

var x=inserttable("MyTable");
document[x].data="[[1,2,3],[4,5,6]]";
document[x].vlegend="['Row 1','Row 2','Row 3']";
document[x].hlegend="['Col 1','Col 2']";

The inserttable function in the example above creates a new Presentation Table and returns the name of the new sheet ("MyTable" in the example).

The following are the functions for creating other CCH Tagetik Supply Chain Planning Studio documents:

Function	Creates A New
insertdatasheet	Data Table
insertdiagramsheet	Diagram
insertgraph	Graph
insertgraph3	Surface Plot
insertreportsheet	Report
inserttable	Presentation Table
inserttextsheet	Text Sheet
inserttreesheet	Tree Sheet

You can refer to existing and previously created documents in either of the following ways:

var x="sheetname";
document[x].property=value;

or

document.sheetname.property=value;

Nodes can be referenced and modified programmatically the same way as sheets. However, the process for creating new nodes is a little different.

You create new nodes by defining the nodes in the appropriate namespace. For example, the following code creates a new Tree Sheet that contains a simple Return On Equity (ROE) model.

BuildTree:=(
  var x=inserttreesheet("MyTree");
  document[x].rootnode="ROE";
  ROE:=Profit/Equity;
  Profit:=PBT-Tax;
  PBT:=100000;
  Tax:=PBT*34%;
  Equity:=Assets-Liabilities;
  Assets:=700000;
  Liabilities:=250000;
)

Note: Note: The BuildTree definition above is inside parenthesis rather braces ({ }). If the code had been placed inside braces, it would create a new block, and, when the function completed, the new nodes would no longer exist in the model scope.

Document Property Reference

Data Table Properties

The following are the properties for the Data Table document created by insertdatasheet:

data [any] Data contained in the table

vlegend [any] Data contained in vertical legend

hlegend [any] Data contained in horizontal legend

dformat (int) Number Format for Data of table, specified by combining Number Format Constants

vformat (int) Number Format for Vertical Legend of the table, specified by combining Number Format Constants

hformat (int) Number Format for Horizontal Legend of the table, specified by combining Number Format Constants

cellwidth (real) Width of each cell in the table, specified in number of characters (uses average character width for specified font)

legendwidth (real) Width of the vertical legend, specified in number of characters (uses average character width for specified font)

Example:

var x=insertdatasheet("Stock Prices");
document[x].data=[[34.5,33.6,35.1],[80.1,85.2,85.9]];
document[x].vlegend=[2004,2005,2006];
document[x].hlegend=["Stock 1","Stock 2"];
document[x].dformat=FMT_D2+FMT_FIX+FMT_DLR;
document[x].vformat=FMT_D0;
document[x].hformat=0;
document[x].cellwidth=30;
document[x].legendwidth=20;

Diagram Properties

There are currently no properties that can be modified programmatically for Diagram documents. However, new Diagram documents can still be created by insertdiagramsheet.

Graph Properties

The following are the properties for the Graph document, created by insertgraph:

data (exe)->[real] Y-data points contained in the graph, represented as evaluatable text (e.g., a node name). Can contain up to 4 sets of data points

x (exe)->[real] X-data points, represented as evaluatable text (e.g., a node name)

style1 (int) Style for the first set of data. Specified by combining Graph Style Constants. Contains both the Global Graph Style for the graph and the Individual Trace Style for the first set of data.

style2 (int) Style for the second set of data. Specified by combining Graph Style Constants. Contains the Individual Trace Styles for the second set of data. Note that any Global Graph Styles are ignored.

style3 (int) Style for the third set of data. Specified by combining Graph Style Constants. Contains the Individual Trace Styles for the third set of data. Note that any Global Graph Styles are ignored.

style4 (int) Style for the fourth set of data. Specified by combining Graph Style Constants. Contains the Individual Trace Styles for the fourth set of data. Note that any Global Graph Styles are ignored.

title (text) Title of the graph

xlabel (text) Label of the x-axis

ylabel (text) Label of the y-axis

legend (exe)->[text] Label for each y-data set, represented as executable text (e.g., a node name)

analysis (int) Analysis type. This is an integer that represents the value selected in the Data Filter list box on the Analysis tab of Graph Properties

param1 (real) First Parameter value for the Analysis specified by the analysis property

param2 (real) Second Parameter value for the Analysis specified by the analysis property

param3 (real) Third Parameter value for the Analysis specified by the analysis property

param4 (real) Fourth Parameter value for the Analysis specified by the analysis property

formula (bool) True to show the formula on the graph, False to not show the formula

extent (real) Value to which to extend the trend of the graph. This value is only used if useextent is True

useextent (bool) True to extend the trend of the graph (to value specified in extent), False to not extend trend

yformat (int) Number format for y-axis data, specified by combining Number Format Constants

xformat (int) Number format for x-axis data, specified by combining Number Format Constants

x1 (real) X-axis lower limit, only used if GS_LIMITX1 graph style is used

x2 (real) X-axis upper limit, only used if GS_LIMITX2 graph style is used

y1 (real) Y-axis lower limit, only used if GS_LIMITY1 graph style is used

y2 (real) Y-axis upper limit, only used if GS_LIMITY2 graph style is used

Example:

var x=insertgraph("Product Sales");
document[x].data="Sales";
document[x].x="Months";
document[x].style1=GS_BAR+GS_AXES+GS_COLOR+GS_LIMITY1;
document[x].style2=GS_BAR;
document[x].title="Sales";
document[x].xlabel="Year";
document[x].ylabel="Millions";
document[x].legend="['ABC','XYZ']";
document[x].yformat=FMT_D0+FMT_DLR;
document[x].y1=0;
Sales:=[[3,7,12,15],[9,10,10,12]];
Months:=[1,2,3,4];

Note: The example code above will only work if inside parenthesis rather then braces so it executes at the model namespace.

Surface Plot Properties

The following are the properties for the Surface Plot document, created by :insertgraph3

data (exe)->[real] Z-axis values, represented as executable text (e.g., a node name)

x (exe)->[real] X-axis values, represented as executable text (e.g., a node name)

y (exe)->[real] Y-axis values, represented as executable text (e.g., a node name)

style (int) Graph Style, specified by combining Graph Style Constants

title (text) Title for the graph

xlabel (text) X-axis label

ylabel (text) Y-axis label

zlabel (text) Z-axis label

contours (int) Number of contours to draw on the graph. Setting this value to 0 will cause the graph to automatically select a reasonable number of contours

xformat (int) Number format for the x-axis labels, specified by combining Number Format Constants

yformat (int) Number format for the y-axis labels, specified by combining Number Format Constants

dformat (int) Number format for the z-axis labels, specified by combining Number Format Constants

Example:

var x=insertgraph3("Sales Grid");
document[x].data="SalesData";
document[x].x="SalesX";
document[x].y="SalesY";
document[x].style=G3_DEFSTYLE;
document[x].title="Title";
document[x].xlabel="x";
document[x].ylabel="y";
document[x].zlabel="Millions";
document[x].dformat=FMT_D0+FMT_DLR;
SalesData:=[[10,18,20],[30,52,40],[25,30,25]];
SalesX:=[1,2];
SalesY:=[1,2];

Note: The example code above will only work if inside parenthesis rather then braces so it executes at the model namespace.

Report Properties

The following are the properties for the Report document, created by insertreportsheet:

text (text) Text of the sheet

rtf (text) RTF (rich text format) version of the sheet (which can include text formatting)

html (text) HTML version of the sheet. This property is read-only.

editmode (bool) TRUE to make the report editable, FALSE to display calculated values (as read-only)

Example:

var x=insertreportsheet("Sales Report");
document[x].text="\
Sales Report
Sales (in millions) were: <%Sales%>";

Presentation Table Properties

The following are the properties for the Presentation Table document, created by inserttable:

data (exe)->[real] Data to display, represented as executable text (e.g., a node name)

vlegend (exe)->[real] Values for vertical legend, represented as executable text (e.g., a node name)

hlegend (exe)->[real] Values for horizontal legend, represented as executable text (e.g., a node name)

style (int) Table style, specified by combining Table Style Constants

title (text) Title of the table

vlabel (text) Vertical label of the table

hlabel (text) Horizontal label of the table

tbmargin (real) Top and bottom margins within each cell in units of a character height

lrmargin (real) Left and right margin within each cell in units of an average character width

dformat (int) Number format for table data, specified by combining Number Format Constants

vformat (int) Number format for vertical legend, specified by combining Number Format Constants

hformat (int) Number format for horizontal legend, specified by combining Number Format Constants

Example:

var x=inserttable("My Table");
document[x].data="Sales Data";
document[x].hlegend="Sales Products";
document[x].style=TS_DEFSTYLE;
document[x].title="Product Sales";
document[x].vlabel="Year";
document[x].hlabel="Product";
document[x].dformat=FMT_D0+FMT_DLR;
Sales Data:=[[3,7,12,15],[9,10,10,12]];
Sales Products:=["ABC","XYZ"];

Note: The example code above will only work if inside parenthesis rather then braces so it executes at the model namespace.

Text Sheet Properties

The following are the properties for the Text Sheet document, created by inserttextsheet:

text (text) Text of the sheet

rtf (text) RTF (rich text format) version of the sheet (which can include text formatting)

html (text) HTML version of the sheet. This property is read-only.

Example:

var x=inserttextsheet("Instructions");
document[x].text="This is a model for forecasting sales for the next few quarters";

Tree Sheet Table Properties

The following are the properties for the Tree Sheet document, created by inserttreesheet:

rootnode (text) Name of the root node of the sheet

pageroot (text) Name of the paged node (i.e. different from the rootnode if the user is paged into a branch) of the sheet

changenodetype (method) In Decision Tree analysis, calling this property toggles the selected node between a Decision Node and an Event Node.

style (int) Tree Style, specified by combining Tree Style Constants

title (text) Title of the tree sheet

Example:

var x=inserttreesheet("ROE Model");
document[x].rootnode="ROE";
ROE:=Profit/Equity;
Profit:=PBT-Tax;
PBT:=100000;
Tax:=PBT*34%;
Equity:=Assets-Liabilities;
Assets:=700000;
Liabilities:=250000;

Note: The example code above will only work if inside parenthesis rather then braces so it executes at the model namespace.

Node Properties

The following are the properties of Nodes:

note (text) The comment for the node

form (text) The node's dialog form, or null if there is no form

nodeformat (int) Specifies the node's format, including maximum node length. This property is specified by combining Node Format Constants, and optionally adding the value for Maximum Node Length (1-255).

valueformat (int) Number format for the node, specified by combining Number Format Constants

color (int) Color of the node (RGB hex format). The RGB function can assist in translating RGB intensity values to hex format.

showformat (int) Show format for the node, specified by combining Node Show Format Constants

group (int) If non-zero, defines the ID of the group that contains this node

groupchild (int) If non-zero, this is a group node and this value defines the ID that nodes in this group will have in their group property.

value (any) Value of the node

reductionunit (bool) True if the node defines a reduction unit

execnamespace (int) Name of the execution namespace (where this node will execute)

homenamespace (int) Name of the home namespace (where this node is defined)

minargs (text) The minimum number of arguments (+ 1 for the function name itself) accepted by the node. This property is read-only.

maxargs (text) The maximum number of arguments (+ 1 for the function name itself) accepted by the node. This property is read-only.

definition (text) System definition for the node. This property is read-only.

Example:

Note: This example is based on the sheet/nodes created in the example for the Tree Sheet document (ROE). It is recommended you run that example to create the ROE sheet before running the example below.

each(document[x].valueformat=FMT_SEP+FMT_DLR,x,
user(treelist("ROE")));
document.ROE.valueformat=FMT_PCT;
document.ROE.color=RGB(0,0,255);
document.ROE.note="Return on Equity";

Format Constants Reference

This section lists the format constants used by the document properties discussed in this chapter. In general, styles and formats are specified by setting the appropriate format property to the combination of desired format constants. Format constants are defined as "bits" that, when combined, turn on or off style and format properties. Format constants can be combined using either the Add (+) operator or Bitwise Or (|) operator. For example, you can define the style for a graph using either:

document.Graph1.style1=GS_BAR+GS_GRID+GS_AXES;

or

document.Graph1.style1=GS_BAR|GS_GRID|GS_AXES;

When defining a complete style or format using these flags, in the example above, the two methods are equivalent. However, when modifying an existing style, you should always use the Bitwise Or and Bitwise And methods. For instance, if you want to change a graph to color mode, you should use the following method:

document.Graph1.style1=document.Graph1.style1|GS_COLOR;

Similarly, if you want to change a graph to grayscale mode (i.e. remove the GS_COLOR bit), you should use the following method:

document.Graph1.style1=document.Graph1.style1&~GS_COLOR;

Note: Use bitwise operators to modify existing styles because you will not always know if the style bit is already set. For example, if you use the Add operator intending to add the GS_COLOR style bit, but the GS_COLOR style bit is already on, the graph style will not be set correctly.

Number Format Constants

The following Number Format Constants can be used to specify the display format for numbers.

Decimals/Significant Digits (select only one):

FMT_D0	0 digits
FMT_D1	1 digits
FMT_D2	2 digits
FMT_D3	3 digits
FMT_D4	4 digits
FMT_D5	5 digits
FMT_D6	6 digits
FMT_D7	7 digits
FMT_D8	8 digits
FMT_D9	9 digits

Format (select only one):

FMT_FIX	Fixed decimal
FMT_SIG	Significant digits only
FMT_SCI	Scientific notation
FMT_ENG	Engineering notation

Style (may combine more than one):

FMT_ZER	Suppress trailing zeros
FMT_PCT	Percent
FMT_SEP	000s separator
FMT_DLR	Currency symbol
FMT_PRN	Parentheses on negative numbers
FMT_UNT	Reduce compound units

Graph Style Constants

The following Graph Style Constants can be used to specify the style/type of graph. Global Graph Styles define global settings for the graph such as axis scales and drawing of grid. Individual Trace Styles specify the style for different sets/traces in the graph (i.e. one set could be shown as a Line Graph, one as Bar Graph, etc). Surface Plot Graph Styles define special styles that are only applicable to Surface Plot graphs. Composite Styles are styles that are a combination of other base styles.

Global Graph Styles

GS_LOGX	Use a logarithmic x-axis scale; all x-values must be greater than zero
GS_LOGY	Use a logarithmic y-axis scale; all y-values must be greater than zero
GS_ZERO	Include zero in the y-axis. The y-axis range is normally selected automatically to include all y-values. This option forces zero to be included in the y-range.
GS_BOX	Draw a bounding box around the graph. This and the GS_GRID style force axes to be placed at the left and bottom edges of the graph rather than in the graph interior.
GS_AXES	Draw x- and y-axes. Same as GS_AXISX + GS_AXISY.
GS_AXISX	Draw x-axis
GS_AXISY	Draw y-axis
GS_GRID	Draw a grid. Same as GS_GRIDX + GS_GRIDY. This and the GS_BOX style force axes to be placed at the left and bottom edges of the graph rather than in the graph interior.
GS_GRIDX	Draw a grid in the x-direction
GS_GRIDY	Draw a grid in the y-direction
GS_ASPECT	Use the same scale factor to adjust both the x- and y-axes. Normally, axes are scaled to fit the window or page on which the graph is drawn. Selecting this style forces one unit in the x-direction to be exactly the same physical length as one unit in the y-direction.
GS_COLOR	Use color to distinguish different traces on the graph. Normally, hatch patterns or dotted lines are used.
GS_AUTO	Automatically reconstruct the graph if any nodes in the system change. Without this option, you can explicitly update a graph by clicking the Recalculate button.
GS_STACKED	Stack successive data sets and plot the sums. The first trace is based on the y1-data set only. The second trace is based on the sum of the y1- and y2-sets; and so on. The final trace represents is the sum of all y-data sets.
GS_LIMITX1	Use x lower limit, as defined in x1 property of graph
GS_LIMITX2	Use x upper limit, as defined in x2 property of graph
GS_LIMITY1	Use y lower limit, as defined in y1 property of graph
GS_LIMITY2	Use y upper limit, as defined in y2 property of graph

Individual Trace Styles

GS_AREA	Shade the area beneath a line graph
GS_LINE	Connect points with a straight line
GS_POINT	Plot the data points
GS_BAR	Create a bar graph. With bar graphs, the x-axis is not drawn to scale, but instead, each x-point is allocated the same space across the x-axis.
GS_DELTA	Draw a vertical arrows whose height represents magnitude in the y-direction
GS_VALUE	Display the y-value above each point

Surface Plot Styles

GS_MESH	Display a grid on the plotted surface corresponding to the mesh of sample points
GS_CONTOURFILL	Shade the surface plot using colors or gray levels to represent different levels in the z-direction
GS_CONTOURLINES	Draw contour lines at the border between discrete levels in the z-direction
GS_LEGEND	Draw a legend relating the contour fill colors to levels in the z-direction
GS_XFIXED	X-values are evenly spaced on the x-axis regardless of value
GS_CONTOUR	Produce a flat contour graph without perspective and 3-D effects

Composite Graph Styles

GS_DEFSTYLE	GS_LINE + GS_GRIDX + GS_GRIDY + GS_AXISX + GS_AXISY + GS_COLOR
G3_DEFSTYLE	GS_CONTOURFILL + GS_LEGEND + GS_MESH + GS_BOX + GS_GRID + GS_AXES + GS_COLOR

Table Style Constants

The following Table Style Constants can be used to specify the display style of tables. Composite Table Styles are styles that are combinations of other base styles.

Table Style Constants

TS_VRULE	Show vertical rules (lines) between columns
TS_HRULE	Show horizontal rules (lines) between rows
TS_GRAY	Gray background in legend column and row
TS_LEFT	Left justify cell contents if set, right justify if not set
TS_UNIFORM	Use uniform cell widths if set, smallest width possible for each column if not set
TS_LABEL	Place vlabel above the first column as opposed to left of the entire table
TS_AUTO	Automatically reconstruct the table if any nodes in the system change

Composite Table Styles

TS_DEFSTYLE	TS_VRULE + TS_HRULE + TS_GRAY + TS_UNIFORM

Tree Style Constants

The following Tree Style Constants can be used to specify the display style of tree sheets.

Tree Style Constants

IDT_SHOWTREE	Show the tree on the sheet
IDT_SHOWBRANCHES	Show redundant branches
IDT_SHOWTABLE	Show the Input/Output tables
IDT_SHADOW	Show shadows behind nodes
IDT_COLORCODING	Applies color-coding to reflect a node's value
IDT_VARIABLELENGTH	Fit node length to its contents
IDT_AUTOCALC	Automatically recalculate tree when a node's definition is changed
IDT_DECISIONFORMAT	Show tree in Decision Tree format
IDT_SHOWDENODES	Show "hot spots" at each end node, which allow user to double-click to quickly add a child node
IDT_HIGHLIGHTPATH	Highlight the optimal/selected path in decision trees
IDT_DRAFTLAYOUT	Show tree in draft layout
IDT_SHOWFULLNOTE	Show full comment (multi-line) inside each node. If not selected, only first line of comment is shown.
IDT_LISTFORMAT	Show tree in list format
IDT_PRINTLAYOUT	Show tree in print format
IDT_DTREEFORMAT	Show in Decision tree script format
IDT_SHOWPARTENTS	Reverses the tree order from parent-child to child-parent

Node Format Constants

The following Node Format Constants can be used to specify the display format of nodes.

Node Format Constants

(Node Length)	The first 8 bits of the node format styles are the node length / max length (e.g. 55+NFMT_LEFT+...). Node length can be from 0-255.
NFMT_SHOWCOMMENT	Show comment in tree view
NFMT_SHOWNAME	Show name in tree view
NFMT_SHOWDEFINITION	Show definition in tree view
NFMT_SHOWVALUE	Show calculated value in tree view
NFMT_INPUTFORMAT	Make the node an input node
NFMT_OUPUTFORMAT	Make the node an output node
NFMT_SHOWEQUATION	Show the definition in equation format
NFMT_SHOWBORDER	Show a border around the node
NFMT_BUTTONFORMAT	Make the node a button
NFMT_LEFT	Use left alignment for node contents
NFMT_CENTER	Use center alignment for node contents
NFMT_RIGHT	Use right alignment for node contents
NFMT_ISNOTE	Make the node a Floating Text Box
NFMT_ISPICTURE	Make the node a Picture
NFMT_NOSELECT	Makes the node unselectable
NFMT_HOTSPOTFORMAT	Make the node a hotspot
NFMT_FIXEDWIDTH	Set node to use a fixed rather than variable length
NFMT_COMPONENT	Node is defined as a component
NFMT_PRIMITIVEFORMAT	Node will be moved to the primitive namespace when the model is loaded as a component
NFMT_EXPANDBRANCH	Expand the branch of the tree in the list view
NFMT_HIDECHILDREN	Hide all child nodes
NFMT_HIDENODE	Hide a node
NFMT_PAGEBREAK	Insert a page break in node contents

Node Show Format Constants

The following Node Show Format Constants can be used to specify the way that nodes and children nodes are displayed.

Node Show Format Constants

NSFT_PAGEBREAK	Create a page break after this node
NSFT_HIDENODE	Hide this node and all children
NSFT_HIDECHILDREN	Hide children of the node
NSFT_EXPANDBRANCH	Show all children of the node when the tree is in list format. Simulates clicking expand/collapse (±) in the tree