Column Sets

Form ID: (CS206020)

To define what data will be displayed in each analytical report and how it will be organized, you need to define the row set and column set; optionally, you can define the unit set. A column set defines the columns to be included in the report and the data to be displayed in each column, as described in Column Sets.

This form, which is part of the Analytical Report Manager, displays the column sets defined for the analytical reports. You can view or modify existing column sets, create new column sets, and delete any column sets. For each column set, add the columns to be included in the analytical report and define the properties for each column.

Form Toolbar

The form toolbar includes the buttons described below.


Button	Description
Copy Column Set	Initiates copying of the selected column set configuration. If you click this button, the Copy Column Set dialog box opens, where you can enter the new column set code and its description.

New Column Set Dialog Box

This dialog box opens when you click New Record. You specify the code, type, and the description of the new column set.


Element	Description
Column Set Code	The unique code used to identify the column set. You can use up to 10 alphanumeric characters.
Type	The data source of the column set. Select one of the following options: GL: The General Ledger is used as the data source. The data is retrieved from the GLHistory table. PM: Projects is used as the data source. The data is retrieved from the PMHistory table joined with PMBudget. One of the join parameters is InventoryID, which serves as an input parameter of the data source. If PMBudget contains no records with the InventoryID value that matches PMHistory, the system returns zero for the corresponding budget amounts. When Projects is used as the data source, the starting and ending periods in the Period box list the financial periods according to the master financial calendar. For more information about the Master Financial Calendar, see Master Financial Calendar.
Description	The descriptive name of the column set.
This dialog box has the following buttons.
Save	Creates a new column set and closes the dialog box.
Cancel	Closes the dialog box without creating a new column set.

Copy Column Set Dialog Box

This dialog box opens when you click Copy Column Set on the form toolbar. You specify the code and its description for the new column set.


Element	Description
New Code	The code used to identify a new column set.
Description	The descriptive name of a new column set.
This dialog box has the following buttons.
Save	Creates a new column set and closes the dialog box.
Cancel	Closes the dialog box without creating a new column set.

Columns Table

The table shows the header rows and columns included in the column set, along with their properties, which you can modify on the Data tab. You can add a header row or column to the column set or delete a header row or column from the set.

Table 1. Table Toolbar
The table toolbar includes the buttons described below.
Button	Description
Insert Header Row	Inserts a header row.
Insert Column	Inserts a column.
Delete Header Row	Deletes a header row.
Delete Column	Deletes a column.
Shift Left	Shifts the selected cell value to the left.
Shift Right	Shifts the selected cell value to the right.

Data Tab

On this tab, you can specify the properties for headers defined for the columns and columns. You select the header row or column in the Columns table and then specify the properties.

Table 2. Header Text SectionThis section is available only for header rows.
Element	Description
Calculation Formula	The formula that defines the header name for the range of columns. To specify the header name, you can use text or formulas. You click the pencil in this box to view the Formula dialog box. To specify a formula, enter it in this box. For more information about formulas, see Formulas.

Table 3. View Condition SectionThis section is available only for header rows.
Element	Description
Columns	The column range for which the header is displayed. Enter the first and last column names in these boxes. When you select a column, the system inserts its name to the boxes.

Table 4. Column Value Section
Element	Description
Type	The column type, which defines how the values in the column are calculated. You can select one of the available options: GL: Use this row type to select the source of the data to calculate the values in the column from the general ledger functional area if the type of the column set is GL or from the projects functional area if the type of the column set is PM. Calc: Use this row type to calculate the values in the column using the formula in the Calculation Formula box. Descr: Use this row type to display the description of a row in the column.
Calculation Formula	The value to be displayed in the row. You can create a formula to define the value and use parameters in the formula you define. You click the pencil in this box to view the Value dialog box. To specify a value, enter it in this box. For more information about formulas, see Formulas.
Description	The descriptive name of the column.
Cell Evaluation Order	The source of the formula used to evaluate the cell's value if both the row set and the column set contain formulas. You can select either of the following options: Row: The formula from the row set is used to calculate the value. Column: The formula from the column set is used to calculate the value. Attention: You cannot set Cell Evaluation Order to Column for columns of the GL type.

Table 5. Data Source Section
Element	Description
Ledger	The ledger to be used as the data source.
Account Class	The account class to be used as the data source.
Account	The starting and ending accounts in the range of account numbers to be included in the analytical report.
Subaccount	The starting and ending subaccounts in the range of subaccounts to be included in the analytical report.
Amount Type	The amount type to be used to calculate the values in the report. Select one of the following options: Not Set: No specific amount type is defined for the report. Turnover: The turnover amounts are included in the report. Credit: The credit amounts are included in the report. Debit: The debit amounts are included in the report. Beg. Balance: The beginning balance amounts are used in the report. Ending Balance: The ending balance amounts are used in the report. Curr. Turnover: The turnover amounts in a foreign currency are included (retrieved from the denominated accounts). Curr. Credit: The credit amounts in a foreign currency are included (retrieved from the denominated accounts). Curr. Debit: The debit amounts in a foreign currency are included (retrieved from the denominated accounts). Curr. Beg. Balance: The beginning balance amounts in a foreign currency are included (retrieved from the denominated accounts). Curr. Ending Balance: The ending balance amounts in a foreign currency are included (retrieved from the denominated accounts).
Expand	An option that determines how data will be displayed. You can select one of the following options: Nothing: The data related to the selected range of accounts and subaccounts will be displayed in a single row. Account: The data for each account in the selected range will be displayed in a separate row. This increases the number of rows in the report. Sub: The data for each subaccount in the selected range will be displayed in a separate row, increasing the number of rows in the report.
Row Description	An option that determines the description that will be displayed in the selected row. The following options are available: Not Set, Code, Description, Code-Description, Description-Code. The box is available if Account or Sub is selected in the Expand box.
Company	The company to be used as the data source.
Branch	The starting and ending branches in the range of branches to be included in the analytical report.
Period	The starting and ending financial periods in the range of periods to be used in the analytical report.
Offset (Year, Period)	The offset from the starting or ending financial period (or both of them) specified in the Period box. Examples: If you set the value to -1 for the ending period, the ending period shifts one period back from the specified ending period. If you set the value to 2 for the starting period, the starting period shifts two periods forward from the specified starting period.

Table 6. View Condition Section
Element	Description
Visible Formula	The formula that defines the visibility conditions for a column in the generated report.
Printing Group	The printing group that includes the column. If you specify a printing group, the data in this column will be printed only for rows that have the same column group value as the value defined here.
Unit Group	The unit group to include the row.
Suppress Empty	A check box that prevents (if selected) the printing of empty columns.
Hide Zero	A check box that prevents (if selected) the printing of zero values in the row.
Suppress Line	A check box that prevents (if selected) the printing of empty lines.

Formatting Tab

On this tab, you can specify the settings for text, layout, and style of the header rows and columns.

Table 7. Buttons
Button	Description
Copy	Copies the cell formatting from a header or column to use it in another header or column.
Paste	Pastes the copied formatting. You can choose whether to apply the cell formatting to a single cell or to the entire column.
Reset	Resets the column formatting.

Table 8. Text Section
Element	Description
Font	The font name. You can select it from the available embedded fonts.
Font Size	The size of the font. You can select the unit of measure the font size. The following predefined options are available: Pixel, Point, Pica, Inch, Mm, and Cm.
Bold	The check box indicates (if selected) that the font is bold.
Italic	The check box indicates (if selected) that the font is italic.
Strikethrough	The check box indicates (if selected) that the font is struck through.
Underline	The check box indicates (if selected) that the font is underlined.
Color	The color of the text.
Text Alignment	The alignment of the text in the report lines. The following options are available: Not Set, Left, Center, Right.

Table 9. Layout and Style Section
Element	Description
Number Format	The format used to convert the data selected from the data source to the string value used in the printed report. For details, see Cell Formatting. The box is available only for the columns. The box is available only if a column is selected.
Cell Format Order	The source of the format for the cell if both the row set and the column set have a format specified. You can select either of the following options: Row: The format from the row set is used. Column: The format from the column set is used. The box is available only if a column is selected.
Rounding	The rounding rule, which the system uses to round the values in the corresponding columns of the report. Select one of the following values: No Rounding: The value is not rounded in the report. Whole Dollars: The system rounds the value to an integer. (For example, $1,117,559,400.58 is rounded to $1,117,559,400.) Thousands: The system truncates the last three digits of the value (before the decimal point) and rounds the number to an integer portion and one decimal place. (For example, $1,117,559,400.58 is rounded to $1,117,559.4.) Thus, the values in the selected column of the report will be considered thousands. Whole Thousands: The system truncates the last three digits of the value (before the decimal point) and rounds the number to an integer value. (For instance, $1,117,559,400.58 is rounded to $1,117,559.) Thus, the values in the selected column of the report will be considered thousands. Millions: The system truncates the last six digits of the value (before the decimal point) and rounds the number to an integer portion and one decimal place. (For example, $1,117,559,400.58 is rounded to $$1,117.6.) Thus, the values in the selected column of the report will be considered millions. Whole Millions: The system truncates the last six digits of the value (before the decimal point) and rounds the number to an integer value. (For example, $1,117,559,400.58 is rounded to $1,118.) Thus, the values in the selected column of the report will be considered millions. Billions: The system truncates the last nine digits of the value (before the decimal point) and rounds the number to an integer portion and one decimal place. (For example, $1,117,559,400.58 is rounded to $1.1.) Thus, the values in the selected column of the report will be considered billions. Whole Billions: The system truncates the last nine digits of the value (before the decimal point) and rounds the number to an integer value. (For example, $1,117,559,400.58 is rounded to $1.) Thus, the values in the selected column of the report will be considered billions. The selected rounding rule is applied to all column cells except those whose rows contain the `RoundingDiff()` function in the formulas. The values of such cells will be rounded by the rows' formulas. The box is available only if a column is selected.
Width	The column width (in pixels). The default value is 70.
Auto Height	A check box that adjusts (if selected) the height of the cell in the selected column. You can use this option when you need to wrap a long text string onto the next line within a cell. The check box is available only if a column is selected.
Printing Control	The way the column will be printed. You can select one of the following options: Print: The column will be printed in the report. Hidden: The column will be hidden from the report and used only to store some values. Merge Next: The column will be merged with the next one in the report. The box is available only if a column is selected.
Page Break	A check box that indicates (if selected) that a page break should be inserted after the column in the printed report. The box is available only if a column is selected.
Extra Space	The extra space added to the column (in pixels). The box is available only if a column is selected.
Background Color	The color of the background.