•ENHANCEMENT - New Script Command for Vertical Table Creation [PMID Ref No 21998] [Ver. 10.1.0.6]
A new script function tag entitled “<<CREATE_VERTICAL_TABLE:” was added to the MAR SQL Script Processor which will create a new vertical table from a one record summary table. The vertical table that is created will contain separate records for each field found in the summary table that contains a numeric field value (non numeric fields will NOT be included in the vertical table). Each vertical record will contain three fields:
•Field_Label - contains the field name from the summary table.
•Field_Value - contains the field value from the summary table.
•Field_Order - unique field number for each field in the table
For example, in the Job Cost reports for class 533, one of the summary tables that is created for the Account Receivable Grand Totals is called ReconstructedARGrandTotals and contains one summary record for ALL jobs with the following record layout:
If you wanted to create vertical table from this summary table, you would add the following line to the end of the 533 custom class script:
•<<CREATE_VERTICAL_TABLE:ReconstructedARGrandTotals
This command needs to be on a separate line of the script class so if you want to convert multiple tables, simply add a new line for each table. When the script is run for the report, a vertical table will be created with the same name as the summary table name followed by “_VERTICAL” at the end of the table name as shown:
•New Vertical Table Name: ReconstructedARGrandTotals_VERTICAL
The Vertical table will contain the same fields laid out in a vertical format:
The SQL Script process will check the summary table to make sure it is valid and will list any errors or field information in the SQL Script Log. NOTE: The summary table MUST contain only 1 record in order to create the vertical table. If the vertical table is not created, be sure to check the SQL Script Log for more information on the creation process. The following situations will be checked and logged:
•If NO records exist, the vertical table will not be created.
•If more than 1 record exists, the vertical table will not be created.
•If the table name does not exist, the vertical table cannot be created.
•If any non numeric fields are found in the summary table, they will NOT be included in the vertical table but will be noted in the SQL Script Log:
