The worksheet panel displays the data of a selected resulting Portfolio instance and a DataModel object used in the Portfolio object.
The buttons at the top of a worksheet panel are grouped according to their purposes.
Some of them duplicate menu commands. The following table contains the descriptions of all these UI elements.
TABLE: Worksheet panel’s buttons
|Select models/sources to show||Opens a box containing a hierarchy (tree) of the DataModel objects on which the Portfolio object is based. You can select several nodes of the tree to display the corresponding data in separate worksheets (several worksheet panes are opened at once). The button is unavailable if the currently used Portfolio object is based on only one DataSource object. For more information about this function, see Select models/sources to show later in this chapter|
|Choose drill-down||Duplicates the Choose drill-down command on the View menu|
|Move to previous drill down, Move to next drilldown||Switches between the results of performed drill-down functions. If the first or the last result is reached the corresponding button is unavailable|
|Reload Data, Interrupt data loading||Duplicate the Reload Data and the Interrupt data loading commands on the View menu, respectively|
|Header manager||Duplicates the Header manager command on the Tools menu|
|Find Column||Opens the Select COLUMN for Portfolio dialog box that contains a list of the columns displayed in the active worksheet (see Illustration 1). You can select any item from this list to jump to the respective column in the worksheet (a cell of the column will be selected).
The text box at the top of the dialog box serves as a filter to find column items in the list.
If several worksheet panes are displayed in the panel, you can open such dialog box for each pane at the same time
|View data as a chart||Duplicates the View data as chart command on the View menu|
|Manual adjustments||Duplicates the Manual adjustments command on the Edit menu|
|Copy to clipboard for Report Writer||Copies the content associated with the cells selected in the active worksheet to the Clipboard. (This content is used on the Free Form Report Setup screen, also known as the Report Writer screen.) The button is unavailable if the worksheet is empty|
Illustration 1 Select COLUMN for Portfolio dialog box
A displayed worksheet panel contains the Portfolio structure tree of the currently used Portfolio object on the left and the data worksheet on the right. Each node in the Portfolio structure tree relates to the portion of structured data related to this node. Pointing to a node opens a required selection of the data.
The data worksheet contains both the resulting Portfolio instance data and the joined DataModel instance data displayed in different colors. According to the following figure, the level 2 and level 3 columns colored blue are the resulting Portfolio instance columns, but the Position type, subtype, Amount colored pink are the joined DataModel columns.
When you right-click a column heading in a worksheet, a shortcut menu appears. This menu contains several commands for managing the column and its content.
Sort Ascending and Sort Descending sort the data stored in the selected column in ascending or descending order, respectively.
Hide Column hides the selected column.
Note: To make this column visible again, click the respective column heading in the Current columns box of the Header Manager window.
When you right-click a cell or a range of selected cells in a worksheet, a shortcut menu appears. This menu contains two commands: Copy and Add Filter.
Copy copies the values from the selected cells to the Clipboard.
Add Filter adds a condition record based on the values from the selected cells into the condition panel. If the selection includes cells of two or more columns, the cells of the very left column are used.
Select models/sources to show
When you click the Select models/sources to show () button in the worksheet panel, a drop-down box opens (see the following figure). It contains a hierarchal tree with selectable nodes. The structure of this tree matches the structure of the DataModel objects used in the Portfolio execution task. A selectable node name with the prefix ‘Portfolio[portfolio_name].’ corresponds to a DataModel object name specified on the Models tab of the currently used Portfolio setup screen. For example, if the DataModel name on which the Portfolio object is based is hv11, the respective selectable node name is ‘Portfolio[portfolio_name].hv11’.
You can select the nodes manually or use the buttons at the bottom of the box.
Select All selects all the nodes in the tree.
Deselect All clears the selection of all the nodes in the tree.
Hide closes the box.
When you click Choose drill-down on the View menu, or the Choose drill-down () button in the worksheet panel, initially a warning message is displayed informing you that no aggregated function is specified in the current drill-down.
To set up an aggregated function, point to Header Manager on the Tools menu or the Header Manager button in the Worksheet panel and select an aggregated function, for example, Sum from the drop-down list under a calculated field and click the OK button.
See the Amount field in the following figure.
Now if you click Choose drill-down on the View menu, or the Choose drill-down () button in the worksheet panel, the Drill-Down dialog box opens. Initially, it looks as shown in the following figure.
The dialog box contains a list of selectable items (see the Current Details box in the preceding figure). They correspond to the worksheet columns for which aggregated functions are not specified in the Header Manager window. The drill-down process is based on the columns selected in this list. The data displayed in the worksheet will be broken down by the selected columns (‘1’) and the calculated columns (‘2’), i.e. the ones for which aggregated functions are specified (see an example in the following figure).
Besides the Current Details box, the Drill-Down dialog box contains three checkboxes: Drill-down current selection, Join with all possible detail values, and 2-Dimensional drill-down.
Drill-down current selection checkbox defines the rows through which data the system will drill down: only the rows highlighted in the worksheet (if the checkbox is selected) or all the rows displayed in the worksheet.
Join with all possible detail values displays all the values from a Lookup source joined to the resulting Portfolio instance. In order for these values to be displayed, the following requirements must be met:
1) the used Portfolio object must be based on two DataModel objects.
2) a DataSource in one DataModel must be specified as a Lookup source in the Layout of the other DataSource object.
3) an aggregated function must be specified for a calculated field on the Header Manager screen
4) a detail (non-calculated) field with a Lookup source you select for the drill-down process must be specified on the Header Manager screen for sorting in the ascending or descending order.
The Example at the end of this document will help you to understand better the Join with all possible detail values functionality.
2-Dimensional drill-down defines whether to display the result of drilling down as a matrix (if selected) or as a regular worksheet. When you select this checkbox, corresponding UI elements appear on the right side of the dialog box (see the 2-Dimensional drill-down box in the following figure). The checkbox is available only if two items are selected in the Current Details box.
The options button allocates the coordinate axes for the selected columns.
Freeze Totals rows/columns checkbox locks (i.e. freezes) the Total row and Total column so that they are visible when scrolling through the data (if selected).
An example of 2-dimension drilling down is displayed in the following figure.
Open in a new window opens the result of drilling data on a separate Portfolio Viewer screen.
Example. Setting CV objects included in the Portfolio object for illustrating the Join with all possible detail values functionality
In order for all possible detail values to be displayed when drilling down a specified detail (non-calculated) field, follow the instructions below.
1. Create a DataSource object, for example, named ds. Create a DataSource instance and make adjustments on the Manual Adjustment screen like in the following figure.
2. Create another DataSource object and on its Layout tab, specify the ds DataSource object as a Lookup Source. Specify the Value Field and Description Field columns for the Lookup Source.
3. Create a DataSource instance for the new DataSource object and on the Manual Adjustment screen, enter any values you want for the country, city, and amount fields taking into account the data type of each field. Click the Lookup values for the cell button and in the open Select Value dialog box, select only USD and GBP values to be populated in the currency field like in the following figure.
It is important not to select the PLN value!
4. Create two DataModel objects and include the earlier created DataSource objects in each DataModel object.
5. Create a Portfolio object and include the DataModel objects on the Models tab of the Portfolio Setup screen.
6. After saving and executing the Portfolio object open the Portfolio Viewer screen.
7. Click the Select models/sources to show button and then click the Header Manager button to open the Header Manager screen.
8. On the Header Manager screen, specify:
i. an Aggregated Function for a required calculated field;
ii. a detail field which must be sorted for the display of all its possible values when drilling it down.
Note: Initially when you select the Join with all possible detail values checkbox, the following error message is displayed.
In order for the detail field to be sorted, do the following:
1) on the Header Manager screen, click the Add Sort Column lookup button
2) select a required column in the open Select the column dialog box
3) click OK.
You can specify a sorting order for the added column in the Column table if you want. (Ascending is the default item in the Order combo box).
9. After clicking OK and closing the Header manager, click the Choose drill-down button for drilling down a specified detail column.
10. Now, if you specify a detail field for drilling it down and select the Join with all possible values checkbox, the values of the illustrated detail field will look like in the following figure. As you can see, the PLN currency is displayed as a possible detail field value of the joined Lookup Source after the drill-down process.
11. However, if you clear the Join with all possible values checkbox and drill down the currency detail field again, only the values specified from the joined Lookup Source will be displayed.