{CALC} is equivalent to the Calc key, F9, which recalculates the active notebook, or converts the formula on the input line into its result when editing a cell.
CAPOFF and {CAPON}
Syntax
CapOff()
Description
{CAPOFF} and {CAPON} are equivalent to Caps Lock off and Caps Lock on, respectively.
ChartExpert
Syntax
ChartExpert()
Description
{ChartExpert} displays the first Chart Expert dialog box.
CLEAR
Syntax
Clear()
Description
{CLEAR} is the equivalent of Ctrl+Backspace, which erases any previous entry in a prompt line or on the input line in Edit mode. This command is useful when loading or retrieving files.
ClearComments
Syntax
ClearComments([PageOnly_ As Integer])
PerfectScript Syntax
ClearComments ([PageOnly?:Numeric])
Description
{ClearComments} deletes the comment in the active cell. PageOnly? flat refers to Group Mode. If Group mode is off, enter 0; if Group mode is on, and the active sheet belongs to a group, enter 1 to operate on only the active sheet or 0 to act on all sheets in the group. Equivalent to Rt-Clicking on the current cell, and choosing Delete Comment.
ClearContents
Syntax
ClearContents([PageOnly_ As _ClearContents_PageOnly__enum])
{ClearContents} erases the contents of the selected cells but leaves cell property settings intact.
Parameters
PageOnly? If Group mode is off, enter 0; if Group mode is on, and the active sheet belongs to a group, enter 1 to operate on only the active sheet or 0 to act on all sheets in the group
ClearFormats
Syntax
ClearFormats([PageOnly_ As _ClearFormats_PageOnly__enum])
{ClearFormats} resets the properties of cells but retains the values.
Parameters
PageOnly? If Group mode is off, enter 0; if Group mode is on, and the active sheet belongs to a group, enter 1 to operate on only the active sheet or 0 to act on all sheets in the group
{COLUMNWIDTH}
Syntax
ColumnWidth(Block As String, FirstPane_ As _ColumnWidth_FirstPane__enum, Mode As _ColumnWidth_Mode_enum, Size As Double)
{COLUMNWIDTH} provides three ways to change the width of a column or columns (it is equivalent to the cell property Column Width). The columns to change are specified by Block. FirstPane? is used when the active window is split into panes. To resize the columns in the left or top pane, set FirstPane? to 1; to resize the columns in the right or bottom pane, set FirstPane? to 0.
The argument Set/Resize/Auto specifies how to change the width. To set a column width, use this syntax: {COLUMNWIDTH Block, FirstPane?, 0, NewSize}.
NewSize is the new column width, in twips (a twip is 1/1440th of an inch). The maximum width is 20 inches (28,800 twips).
To reset a column to the default width (set by Default Width in the sheet Object Inspector) use this syntax: {COLUMNWIDTH Block, FirstPane?, 1}.
To automatically size a column based on what is entered in it, use this syntax: {COLUMNWIDTH Block, FirstPane?, 2, ExtraCharacters}
ExtraCharacters is the number of characters to add on to the calculated width. If this argument is omitted, the default is used (1 character).
Example
{COLUMNWIDTH A:A..B,1,0,1440} sets the width of columns A and B (on sheet A) to one inch (1,440 twips).
{COLUMNWIDTH A:A..B,0,0,2160} sets the width of columns A and B (on sheet A) to one and a half inches (2,160 twips). If the window is split, the columns are resized in the left or top pane.
{COLUMNWIDTH A:C,1,1} resets the width of column C (on sheet A) to the default width.
{COLUMNWIDTH A:C,1,2,3} automatically sizes column C (on sheet A) and adds three characters to the calculated width.
Parameters
Block Cells containing columns to resize
FirstPane? 1 to resize columns in left or top window pane; 0 to resize columns in right or bottom window pane
Set/Reset/Auto 0 to set the column width; 1 to reset the column width; 2 to automatically size the column(s)
Size New width (in twips) if Set/... = 0; not needed if Set/... = 1; resetting size; extra characters (optional) if Set/... = 2
Comment_Edit
Syntax
Comment_Edit(Value_ As String)
PerfectScript Syntax
Comment_Edit (Value?:String)
Description
Creates/updates a comment in the active cell, and leaves comment "bubble" in edit mode for you to insert the comment text. If a comment already exists, it brings up the comment "bubble" in edit mode for you to edit the existing comment.
Comment_EditURL
Syntax
Comment_EditURL(Link_ As String, [Text_ As String], [Loc_ As String], [Relative_ As _Comment_EditURL_Relative__enum])
PerfectScript Syntax
Comment_EditURL (Value?:String)
Description
Brings up the Insert Hyperlink dialog, allowing you to insert, modify, or delete a hyperlink.
ComposeFormula
Syntax
ComposeFormula()
Description
{Compose Formula} is the command equivalent of clicking the Formula Composer button on the Notebook toolbar. The macro has no arguments. {ComposeFormula} displays the Formula Composer dialog box.
{Consolidate} combines data from multiple selections into one using your choice of operators. Block defaults to the current selection if the argument is not supplied.
You can use {Consolidate?} or {Consolidate!} to display the Consolidation dialog box. {Consolidate?} lets you manipulate the dialog box, whereas {Consolidate!} relies on the macro to manipulate it.
Example
The following macro adds the values in the source cellss B2..B4, C2..C3, and D2..D4, and returns values in the destination cells F2..F4.
{Consolidate.Add_Source_Block A:B2..B4}
{Consolidate.Add_Source_Block A:C2..C3}
{Consolidate.Add_Source_Block A:D2..D4}
{Consolidate.Function SUM}
{Consolidate.Destination A:F2..F4}
{Consolidate.Options 1,0,0}
{Consolidate.Go}
{Consolidate.Save CONSOL1}
Options
{Consolidate.Add_Source_Block <Block>} Adds an entry to the Source Cells list.
{Consolidate.Destination <Block>} Sets the cells to contain the consolidation results.
{Consolidate.Function SummaryFunction} Specifies the operations to perform on the source cells.
{Consolidate.Go} Performs the consolidation of the source cells.
{Consolidate.Options OutputWithFormulas?(0|1), LabelsInTopRow?(0|1), LabelsInLeftCol?(0|1)} Selects options for consolidation.
{Consolidate.Remove Name} Deletes the selected setup in the Consolidations list.
{Consolidate.Remove_Source_Block <Block>} Removes an entry from the Source Cells list.
{Consolidate.Reset} Clears Source Cells and Destination Cells, and resets Options to default values in the Consolidation dialog box..
{Consolidate.Save Name} Saves the current consolidation setup.
{CONTENTS} copies the contents of Source into Dest, but unlike {LET} or other copy commands, if Source contains a value entry, it translates the copied value into a label and stores it in Dest. It also lets you specify a different numeric format and column width using the Width# and Format# arguments.
Width# can be any number from 1 to 1023. Quattro Pro will not alter the width of the destination column but will treat the resulting string as if it came from a column with the specified width. For example, if a value is displayed as ***** in the source column because the column is not wide enough, specifying a wider Width# will let the value be copied as it would be displayed within that width, not as *****. Width# is optional, but must be provided if Format# is used. If you do not specify Width#, the width of the source column is assumed. Use the maximum width if you want all values to come across properly. You can use @TRIM with a {LET} command to remove any leading spaces from the label.
Format# can be any number from 0 to 127. Each number in this range corresponds to a specific numeric format and decimal precision. Format# affects the Dest entry only, not the Source value. See Numeric Format Codes for a list of special codes used to indicate numeric formats with Format#.
Example
The following examples assume cell C18 contains the value 48,988 in comma format with a column width of 12.
{CONTENTS A18,C18}
Places the 12-character label ' 48,988 in cell A18 (six spaces are inserted at the beginning).
{CONTENTS E10,C18,3}
Places the 3-character label '*** in cell E10. (Only asterisks are copied because the value does not fit within three spaces.)
{CONTENTS A5,C18,15,34}
Places the 15-character label ' $48,988.00 in cell A5 (five spaces are inserted at the beginning).
Parameters
Dest Cell you want data written to
Source Cell you want data copied from
Width# Optional column width (1 to 1023)
Format# Optional format code
Controls
Syntax
{Controls.Option}
PerfectScript Syntax
Controls_Order ()
Controls_OrderFrom ()
Controls_OrderTab ()
Controls_OrderTabFrom ()
Description
{Controls} affects selected objects in the dialog window.
Options
{Controls.Order} Changes the setting order of controls
{Controls.OrderFrom} Places related controls together in the setting order
{Controls.OrderTab} Sets the tab order for controls
{Controls.OrderTabFrom} Pulls specific controls out of the tab order and groups them together
CR
Syntax
CR()
Description
{CR} or ~ (tilde) are equivalent to the Enter key.
With {CREATEOBJECT} you can add objects to the active window normally added using the Toolbar. {CREATEOBJECT} is context-sensitive, letting you create lines in a chart window or check boxes in a dialog window. Quattro Pro interprets the coordinates specified after ObjectType differently based on the object type. The following table lists the possible chart object settings for ObjectType, and how each chart object uses the (x,y) coordinates.
Chart Objects {CREATEOBJECT} Can Generate
Object # of (x,y)'s Coordinates
Line 2 1st: Start point, 2nd: End point
Arrow (same as for Line)
Block 2 1st: Upper left corner, 2nd: Width and height of the objects (in relative coordinates)
Rect (Rectangle) 2 (same as for Block)
Ellipse 2 1st: Upper left corner of a rectangle bounding the ellipse; 2nd: Width and height of the bounding rectangle
Rounded_Rect (same as for Block)
Text (same as for Block)
Polyline Varies 1st: Start point, 2nd: End point of first segment and start of second segment; 3rd: End point of second segment and start of third segment, ... nth: End point
Polygon (same as for Polyline)
Freehand_Polyline (same as for Polyline)
Freehand_Polygon (same as for Polyline)
Block Objects
The Block object has additional arguments for {CREATEOBJECT}:
Block sets the notebook cells to use. The remaining arguments specify whether to show borders and grid lines and whether to maintain the cells' aspect ratio.
Dialog Controls {CREATEOBJECT} Can Generate
You can create these dialog controls listed in the order they appear on the Dialog Toolbar: Button, CheckBox, RadioButton, BitmapButton, Label, EditField, SpinCtrl, Rectangle, GroupBox, RangeBox, ComboBox, PickList, FileCtrl, ColCtrl, ScrollBar, HScrollBar, TimeCtrl. When creating a control, x1 and y1 specify the upper-left corner of the control; x2 and y2 specify the width and height of the control, in pixels.
ObjectType is enclosed in quotes. The x and y coordinates for each point follow, separated by commas.
Example
{CREATEOBJECT "Rect",86,11,94,74} creates a rectangle with upper-left corner = (86,11), width = 94, and height =74 (pixels).
{CREATEOBJECT "Block", 363, 260, 1278, 1139, "A:B2..D9", "No,No,Yes,Yes,Yes"} creates notebook cells in a chart window with upper-left corner = (363, 260), width = 1278, and height = 1139; the other arguments specify the notebook cells, turn off row and column borders, show grid lines, and maintain the cells' aspect ratio.
{CREATEOBJECT "Line",260,238,356,228} creates a line that starts at (260,238) and ends at (356,228).
{CREATEOBJECT "Polyline",2,2,23,59,11,26} creates a polyline that starts at (2,2), draws a line to (23,59), and then draws a line from that point to (11,26).
Parameters
ObjectName Type of object to create
x1, y1 XY coordinates for the starting point of the object; the upper left corner for rectangles and objects bounded by rectangles
x2, y2 XY coordinates for the end point or next point of the object; the width and height for rectangles and objects bounded by rectangles
x3, y3 XY coordinates for the next or last point of a polyline or polygon object
{CrossTab} creates a summary of your data in a format that is simple and easy to read. This is especially useful when you are working with large pieces of data, such as imported databases.
All items surrounded by <> are optional. All quotes in this macro command must be included in order for the macro to function.
All Column, Row and Data items are to be replaced with the field number containing the data to be used. Fields go from 0 to however many columns are passed into Cross Tabs. Columns are numbered from left to right in the source range, 0 being the first column of the selection.
A sample Cross Tab Report has the following macro commands run against it.
{CrossTabReport.AddField 3;3}
{CrossTabReport.Edit}
The result is that the Winery field (index position 3 in the underlying data source) has been added to the page area of the Report.
CrossTabReport.CenterLabels
Syntax
CrossTabReport_CenterLabels(Enable_ As _CrossTabReport_CenterLabels_Enable__enum)
PerfectScript Syntax
CrossTabReport_CenterLabels (Enable?: Boolean)
Description
Lets you specify whether or not to center the labels on a report.
Parameter
Enable 0 Do not center the labels
1 Center the labels.
Example
A sample Cross Tab Report has the following macro commands run against it.
{CrossTabReport.CenterLabels 1}
{CrossTabReport.Options}
The result is that the Year labels (1991 and 1992) have been centered against the rows of data.
CrossTabReport_ColumnSummary
Syntax
CrossTabReport_ColumnSummary(Enable_ As _CrossTabReport_ColumnSummary_Enable__enum)
PerfectScript Syntax
CrossTabReport_ColumnSummary (Enable?: Boolean)
Description
Lets you specify whether or not to display a column summary.
Parameter
Enable 0 Do not display a column summary.
1 Display a column summary.
Example
A sample Cross Tab Report has the following macro commands run against it.
{CrossTabReport.ColumnSummary 1}
{CrossTabReport.Options}
The result is that each of the columns of sales data (Q1-Q4) have been added together and a grand total displayed at the bottom of each.
CrossTabReport_CopyStatic
Syntax
CrossTabReport_CopyStatic()
PerfectScript Syntax
CrossTabReport_CopyStatic ()
Description
A command macro which creates a static copy of the current Cross Tab Report. The copy does not hold any properties of the report and is not affected by changes in the underlying source data.
CrossTabReport_Create
Syntax
CrossTabReport_Create()
PerfectScript Syntax
CrossTabReport_Create ()
Description
A command macro which is used to generate a new Cross Tab Report. As shown below, this macro is typically used in conjunction with the {CrossTabReport_Source}, {CrossTabReport_Destination}, {CrossTabReport_Name}, and {CrossTabReport_AddField} macros.
Example
A sample spreadsheet is used as the data source for a Cross Tab Report. To create the report, the following sequence of macro commands is run.
{CrossTabReport.Source A:A1..H145}
{CrossTabReport.Destination B:A1}
{CrossTabReport.Name CrossTabs Table 1}
{CrossTabReport.AddField 1;1}
{CrossTabReport.AddField 2;2}
{CrossTabReport.AddField 8;4}
{CrossTabReport.Create}
The result is that a new Cross Tab Report is created. It uses columns A through H in Sheet A as its data source, and cell A1 in Sheet B is used as the destination for the report. The Year, Quarter, and Sales fields are then added to the Cross Tab Report's row, column, and data areas respectively.
CrossTabReport_DataAlignment
Syntax
CrossTabReport_DataAlignment(RowOrCol_ As Integer)
PerfectScript Syntax
CrossTabReport_DataAlignment (RowOrCol?: Numeric)
Description
Lets you specify whether the data fields in a report are aligned by row or column. By default, data fields are aligned in a row.
Parameter
RowOrCol 0 Row
1 Column
Example
A sample Cross Tab Report has its data fields (Sales and Cost Per Case) aligned by row. To change this, the following macro commands are run.
{CrossTabReport.DataAlignment 1}
{CrossTabReport.Edit}
The result is a Cross Tab Report which now has its data fields aligned by column.
CrossTabReport_DefineFieldProps
Syntax
CrossTabReport_DefineFieldProps(Props_ As String)
Description
Lets you specify the fields on which specified options will operate. Typically, this macro will be followed by other macros which perform the desired operation on the specified field. For example, the {CrossTabReport.FieldSummary} and {CrossTabReport.FieldOptions} macros might be used, as shown below, to specify which operations to perform on the specified field.
Parameter
Area
Field Index 1 Row area
2 Column area
3 Page area
4 Data area
The index of the given field based on its position in that area
Example
A sample Cross Tab Report contains two data fields, Sales and Cases Sold, both of which already have the summary option Sum. To add the summary option Max to only the Sales field, and not the Cases Sold field, the following macro commands are run
{CrossTabReport.DefineFieldProps "4;1"}
{CrossTabReport.FieldSummary "1;4"}
{CrossTabReport.FieldOptions}
The result is that the first field in the data area (Sales) is defined as the field on which to apply the summary option Max. For more information on the options applied to the defined field, refer to the help for the macros {CrossTabReport_FieldSummary} and {CrossTabReport_FieldOptions}.
CrossTabReport_Destination
Syntax
CrossTabReport_Destination(Block_ As String)
Description
Lets you specify where the report is located.
Parameter
Block The destination cell
Example
When creating a Cross Tab Report, the following macro command is used to specify a destination cell for the report.
{CrossTabReport.Destination B:A1}
The result is a Cross Tab Report residing on sheet B, cell A1. For a more detailed example, refer to the help for the {CrossTabReport_Create} macro.
CrossTabReport.DisplayInEmptyCell
Syntax
CrossTabReport_DisplayInEmptyCell(Enable_ As _CrossTabReport_DisplayInEmptyCell_Enable__enum)
Lets you specify whether or not to display a specifc value in the empty cells of a report.
Parameter
Enable 0 Do not display a value in empty cells.
1 Display a value in empty cells.
Example
A sample report contains one or more cells which are empty or awaiting future data. To fill these cells with some value, say "TBA", the following macro commands are used.
{CrossTabReport.DisplayInEmptyCell 1}
{CrossTabReport.EmptyCellString TBA}
{CrossTabReoirt.Options}
The result is a Cross Tab Report with the value TBA displayed in any previously empty cells. Note that the {CrossTabReport_EmptyCellString}.can be used to specify the text which will appear in place of the empty cell.
CrossTabReport_Edit
Syntax
CrossTabReport_Edit()
PerfectScript Syntax
CrossTabReport_Edit ()
Description
A command macro which is used to modify the report settings or configuration. Typically, this macro is used after a sequence of operations such as adding a field or changing the destination of a report.
Example
For an example detailing the usage of the {CrossTabReport_Edit} macro, see the help for either the {CrossTabReport_AddField} macro or the {CrossTabReport_DataAlignment} macro.
CrossTabReport_EmptyCellString
Syntax
CrossTabReport_EmptyCellString(Name_ As String)
PerfectScript Syntax
CrossTabReport_EmptyCellString (Name?: String)
Description
Lets you specify the string to be displayed in the empty cells of a Cross Tab Report.
Parameter
Name The string to be displayed in empty cells
Example
A sample report contains one or more cells which are empty or awaiting future data. To fill these cells with some value, say "TBA" the following macro commands are used.
{CrossTabReport.DisplayInEmptyCell 1}
{CrossTabReport.EmptyCellString TBA}
The result is a Cross Tab Report with the value TBA displayed in any previously empty cells. Note that the {CrossTabReport_DisplayInEmptyCell}.is used to specify whether or not a value is displayed in empty cells.
Lets you expand the current report onto several different sheets by specifying the appropriate field indices. By default, this macro command will expand to the maximum number of levels. Note that in order to use this macro, you must have a least one field in the Pages position of the report.
Parameters
Index1 The field on which you want to base the report expansion.
Index2
[optional] The number of levels to which you want to expand the report.
Example
A sample report, located on sheet A of a notebook, contains two fields in the Pages area of the report. The field "Winery", located in index position 1, contains two field items, Beaulieu and Duckhorn. To expand the report based on the items in this field, the following macro commands are used
{CrossTabReport.Expand 1}
The result is that the Cross Tab Report is expanded onto the next two unprotected pages in the notebook; in this case sheet B and sheet C. Sheet B contains the field item Beaulieu and all the data associated with it, and sheet C contains the field item Duckhorn and all the data associated with it.
CrossTabReport_FieldCmp
Syntax
CrossTabReport_FieldCmp(Value_ As Integer)
PerfectScript Syntax
CrossTabReport_FieldCmp (Value?: Numeric)
Description
Lets you specify a comparision option on any given field within a report. Typically, this macro will be used along with the {CrossTabReport_FieldCmpBase}, {CrossTabReport_FieldCmpItem},and {CrossTabReport_FieldCmpItemPreset} macros.
Parameter
Value 0 None
1 DiffFrom
2 PercentOf
3 PercentDiffFrom
4 RunningTotal
5 PercentRow
6 PercentColumn
7 PercentTotal
8 Index
Example
A sample Cross Tab Report has the following macro commands run against it.
{CrossTabReport.DefineFieldProps "4,1"}
{CrossTabReport.FieldCmp 1}
{CrossTabReport.FieldCmpBase 1}
{CrossTabReport.FieldCmpItemPreset -1}
{CrossTabReport.FieldOptions}
The {CrossTabReport_DefineFieldProps} macro is used to indicate that the specified comparision options are to be applied to the Sales field in the Data area of the page. The result is a report which takes the sales data in each row of the Year field (index position 1) and calculates the difference between it and the data from the previous year.
CrossTabReport_FieldCmpBase
Syntax
CrossTabReport_FieldCmpBase(Value_ As Integer)
PerfectScript Syntax
CrossTabReport_FieldCmpBase (Value?: Numeric)
Description
Lets you specify the index of the base field.
Parameter
Value The index of the base field
Example
{CrossTabReport.Field CmpBase 1}
The field with index value 1 is taken to be the base field for comparision. For a more detailed example involving this macro, please see the help for the {CrossTabReport_FieldCmp} macro.
Lets you specify the type of preset to be used during comparision.
Parameter
Value 0 None
-1 Previous
1 Next
Example
{CrossTabReport.FieldCmpItemPreset -1}
Previous is selected as the type of preset to be used during the comparision. For a more detailed example involving this macro, see the help for the {CrossTabReport_FieldCmp} macro.
CrossTabReport_FieldHide
Syntax
CrossTabReport_FieldHide(Value_ As String)
PerfectScript Syntax
CrossTabReport_FieldHide (Value?: String)
Description
Lets you hide one or more data items associated with the report. You can specify the field by using the {CrossTabReport_DefineFieldProps} command.
Parameter
Value [semicolon delimited] Semicolon delimited items
Example
A sample Cross Tab Report has the following macro commands run against it.
{CrossTabReport.DefineFieldProps "1;1"}
{CrossTabReport.FieldHide "1991"}
{CrossTabReport.FieldOptions}
The result is a report which hides the field item 1991 and its data.
Note
You can leave the Value value empty to clear the existing values.
CrossTabReport_FieldLabel
Syntax
CrossTabReport_FieldLabel(Value_ As String)
PerfectScript Syntax
CrossTabReport_FieldLabel (Value?: String)
Description
Lets you specify or change the label on a given field. You can specify the field by using the {CrossTabReport_DefineFieldProps} command.
Parameter
Value Text for the field label
Example
A sample Cross Tab Report has the following macro commands run against it.
{CrossTabReport.DefineFieldProps "1,1"}
{CrossTabReport.FieldLabel Years}
{CrossReport.FieldOptions}
The result is that the label which previously displayed as "Year", has been modified to display as "Years".
CrossTabReport_FieldOptions
Syntax
CrossTabReport_FieldOptions()
PerfectScript Syntax
CrossTabReport_FieldOptions ()
Description
This is a command macro used to modify field options. Typically, macro operations which modify a field will be followed by this command macro.
Example
For examples detailing the usage of this macro, refer to the help for either the {CrossTabReport_FieldCmp} {CrossTabReport.FieldHide} macros.
CrossTabReport_FieldSummary
Syntax
CrossTabReport_FieldSummary(Value_ As String)
PerfectScript Syntax
CrossTabReport_FieldSummary (Value?: String)
Description
Lets you specify one or more summary option flags. Value consists of variables delimited by semicolons. You can specify the field by using the {CrossTabReport_DefineFieldProps} command.
Parameter
Value [semicolon delimited] 1 Sum
2 Count
3 Average
4 Max
5 Min
6 StdDevp
7 StdDevs
8 Varp
9 Var
10 CountNonBlank
11 SumNone (clears existing flags)
Example
A sample Cross Tab Report has the following macro commands run against it.
{CrossTabReport.DefineFieldProps "4;1"}
{CrossTabReport.FieldSummary "1; 3; 4; 5"}
{CrossTabReport.FieldOptions}
The result is a report which now calculates and displays Sum, Average, Max, and Min values for the Sales field.
CrossTabReport_FormatReport
Syntax
CrossTabReport_FormatReport(Enable_ As _CrossTabReport_FormatReport_Enable__enum)
PerfectScript Syntax
CrossTabReport_FormatReport (Enable?: Boolean)
Description
Lets you specify whether or not to apply a predefined format to the report.
Parameter
Enable 0 Do not apply a predefined format to the report.
1 Apply a predefined format to the report.
Example
A sample Cross Tab Report, with a predefined format applied, has the following macro commands run against it.
{CrossTabReport.FormatReport 0}
{CrossTabReport.Options}
The result is a report which no longer has a predefined format applied. Note that in this example the dark cell borders have been lost as a result of the predefined format no longer being applied.
CrossTabReport_Hide
Syntax
{CrossTabReport_Hide
PerfectScript Syntax
CrossTabReport_Hide ()
Description
A command macro used to hide the details of the active or selected field in a report.
Example
Within a sample Cross Tab Report, the active cursor selection is positioned within the Q1 field item and the following macro command is executed.
{CrossTabReport.Hide}
The result is a report which displays without any details for Q1. All other field items continue to display as they were originally.
Note
The {CrossTabReport_Show} macro can be used to return the report to its original state.
CrossTabReport_LabelEdit
Syntax
CrossTabReport_LabelEdit(LabelEdit_ As String)
PerfectScript Syntax
CrossTabReport_LabelEdit (LabelEdit?: String)
Description
Lets you change the label of the selected field cell in the sheet. This macro allows you to edit a field label from the active report without going through the field options.
Parameter
LabelEdit The changed label of the selected field cell
Example
Within a sample Cross Tab Report, the active cursor selection is positioned at the label to be changed and the following macro command is executed.
{CrossTabReport.LabelEdit Years}
The result is that the label which previously displayed as "Year", has been modified to display as "Years".
CrossTabReport_MoveCell
Syntax
CrossTabReport_MoveCell(Row_ As Integer, Column_ As Integer)
Lets you move the selected field to a new position within an active report.
Parameters
Source_Area 1 Row area
2 Column area
3 Page area
4 Data area
Source_Index The numeric index of the source field
Destination_Area 1 Row area
2 Column area
3 Page area
4 Data area
Destination_Index The numeric index of the destination field
Example
Within a sample Cross Tab Report, the active cursor selection is positioned in the Year field, and the following macro command is executed.
{CrossTabReport.MoveField 1;1;2;2}
{CrossTabReport.FieldOptions}
The result is a report in which the Year field is now displayed in the column area instead of the row area as it had been previously. The field has been moved from index position 1 of the Row area to index position 2 of the Column area.
CrossTabReport_Name
Syntax
CrossTabReport_Name(Name_ As String)
PerfectScript Syntax
CrossTabReport_Name (Name?: String)
Description
Lets you specify or change the name of an active report.
Parameter
Name The name of the report
Example
A report is named CrossTabs Table 1. To change this, the following macro command is executed.
{CrossTabReport.Name "CrossTabs Table 2"}
The report is now named CrossTabs Table 2. The new report name can be viewed or verified using the Cross Tabs Options dialog box.
CrossTabReport_Options
Syntax
CrossTabReport_Options()
PerfectScript Syntax
CrossTabReport_Options ()
Description
A command macro used to modify the report options. Typically, this macro is used after a sequence of commands such as showing a column summary or displaying a value in empty cells.
Example
For an example detailing the usage of the {CrossTabReport.Options} macro, see the help for either the {CrossTabReport_ColumnSummary} macro or the {CrossTabReport_DisplayInEmptyCell} macro
CrossTabReport_PageFilter
Syntax
CrossTabReport_PageFilter(Index_ As Integer, Value_ As String)
Lets you apply a page filter to the specified field and value in the page area.
Parameters
Index The numeric index of the field to be filtered.
Value The field value on which you filter the report.
Example
A sample Cross Tab Report has a Winery field located in the Pages area. This field contains the items Beaulieu, Duckhorn, and [All]. To filter this report, the active cursor selection is positioned within the report, and the following macro command is executed.
{CrossTabReport.PageFilter 1; Duckhorn}
The result is a report which has been filtered on index position 1 of the Pages area. In this example, the report now shows only data relating to the Duckhorn winery.
CrossTabReport_PreserveDataFormat
Syntax
CrossTabReport_PreserveDataFormat(Enable_ As _CrossTabReport_PreserveDataFormat_Enable__enum)
Lets you specify whether or not the report should preserve the formatting options found in the data source.
Parameter
Enable 0 Do not preserve the data format from source
1 Preserve the data format from source.
Example
A sample report has been generated without retaining the source data formatting. In the source, all data had appeared in bold. To apply the source data formatting to the report, the following macro command is executed.
{CrossTabReport.PreserveDataFormat 1}
{CrossTabReport.Edit}
The result is a report which now applies the formatting options found in the source. All data now appears in bold.
CrossTabReport_Refresh
Syntax
CrossTabReport_Refresh()
PerfectScript Syntax
CrossTabReport_Refresh ()
Description
A command macro that lets you refresh the active report to reflect changes in the source data.
Example
A sample report is generated from a data source. Now suppose the underlying data source is changed, for example, to reflect an increase in sales of $20,000 for Q4 of 1992. In order to have this change reflected in the report, the following macro command is executed while the active cursor selection is within the report.
{CrossTabReport.Refresh}
The result is a report updated to reflect changes in the source data. Note that the figure in Q4 of 1992 has changed.
CrossTabReport_Remove
Syntax
CrossTabReport_Remove()
PerfectScript Syntax
CrossTabReport_Remove ()
Description
A command macro which removes the active report.
CrossTabReport_RowSummary
Syntax
CrossTabReport_RowSummary(Enable_ As _CrossTabReport_ColumnSummary_Enable__enum)
PerfectScript Syntax
CrossTabReport_RowSummary (Enable?: Boolean)
Description
Lets you specify whether or not to display row summaries in a report.
Parameter
Enable 0 Do not display row summaries for the report.
1 Display row summaries for the report
Example
A sample report summarizes sales data. To add a row summary which calculates the total sales for each year, the following macro commands are executed.
{CrossTabReport.RowSummary 1}
{CrossTabReport.Options}
The result is that each of the rows of sales data (1991-1992) have been added together and a grand total displayed at the end of each.
CrossTabReport_Show
Syntax
CrossTabReport_RowSummary(Enable_ As _CrossTabReport_RowSummary_Enable__enum)
PerfectScript Syntax
CrossTabReport_Show ()
Description
A command macro used to show the details of the active or selected field in a report.
Example
Within a sample Cross Tab Report, the active cursor selection is positioned within the Q1 field item, which has its details hidden, and the following macro command is executed.
{CrossTabReport.Hide}
The result is a report which displays the details for Q1. All other field items continue to display as they were originally.
Note
The {CrossTabReport_Hide} macro can be used to return the report to its original state.
CrossTabReport_Source
Syntax
CrossTabReport_Show()
PerfectScript Syntax
CrossTabReport_Source (Block?: String)
Description
Lets you specify the sheet and range of cells from which you want to generate the report.
Parameter
Block The range of cells.
Example
When creating a Cross Tab Report, the following macro command is used to specify the source for the report.
{CrossTabReport.Source A:A1..H145}
The report is generated from cells A1 to H145 on sheet A. For a more detailed example involving this macro, refer to the help for the {CrossTabReport_Create} macro.
