In this section: |
The commands SET ASNAMES, SET HOLDLIST, and SET HOLDATTR enable you to control the FIELDNAME, TITLE, and ACCEPT attributes in HOLD Master Files. These commands are issued prior to the report request and remain in effect for the duration of the session, unless you change them.
In addition, the SET HOLDSTAT command enables you to include comments and DBA information in the HOLD Master File. For more information about SET HOLDSTAT, see the Describing Data With TIBCO WebFOCUS® Language manual. For details about SET commands, see the TIBCO WebFOCUS® Developing Reporting Applications manual.
How to: |
Reference: |
When SET ASNAMES is set to ON, MIXED or FOCUS, the literal specified in an AS phrase in a report request is used as the field name in a HOLD Master File. This command also controls how ACROSS fields are named in HOLD files.
SET ASNAMES = [ON|OFF|MIXED|FOCUS|FLIP]
where:
Propagates the field names in the original Master File to the alias names in the HOLD Master File and the alias names in the original Master File to the field names in the HOLD Master File.
When you set the ASNAMES parameter to FLIP, for relational HOLD files, the field names from the original Master File or the AS names specified in the request become the alias names in the HOLD Master File as well as the column names in the generated relational table and the TITLE attributes in the HOLD Master File. The alias names in the original Master File become the field names in the HOLD Master File, except when there is an AS name, in which case the original field name becomes the HOLD field name.
PRINT COUNTRY AS 'PLACE,OF,ORIGIN'
If an AS phrase is used for the fields in the ACROSS phrase, each new column has a field name composed of the literal in the AS phrase concatenated to the beginning of the value of the first field used in the ACROSS phrase.
In the following example, SET ASNAMES=ON causes the text in the AS phrase to be used as field names in the HOLD1 Master File. The two fields in the HOLD1 Master File, NATION and AUTOMOBILE, contain the data for COUNTRY and CAR.
SET ASNAMES=ON TABLE FILE CAR PRINT CAR AS 'AUTOMOBILE' BY COUNTRY AS 'NATION' ON TABLE HOLD AS HOLD1 END
The request produces the following Master File:
FILE=HOLD1, SUFFIX=FIX SEGMENT=HOLD1, SEGTYPE=S01,$ FIELDNAME=NATION ,ALIAS=E01 ,USAGE=A10 ,ACTUAL=A12 ,$ FIELDNAME=AUTOMOBILE ,ALIAS=E02 ,USAGE=A16 ,ACTUAL=A16 ,$
The following request generates a HOLD Master File with one unique field name for SALES and one for AVE.SALES. Both SALES and AVE.SALES would be named SALES, if SET ASNAMES had not been used.
SET ASNAMES=ON TABLE FILE CAR SUM SALES AND AVE.SALES AS 'AVERAGESALES' BY CAR ON TABLE HOLD AS HOLD2 END
The request produces the following Master File:
FILE=HOLD2, SUFFIX=FIX SEGMENT=HOLD2, SEGTYPE=S01,$ FIELDNAME=CAR ,ALIAS=E01 ,USAGE=A16 ,ACTUAL=A16 ,$ FIELDNAME=SALES ,ALIAS=E02 ,USAGE=I6 ,ACTUAL=I04 ,$ FIELDNAME=AVERAGESALES ,ALIAS=E03 ,USAGE=I6 ,ACTUAL=I04 ,$
The following request produces a HOLD Master File with the literal CASH concatenated to each value of COUNTRY.
SET ASNAMES=ON TABLE FILE CAR SUM SALES AS 'CASH' ACROSS COUNTRY ON TABLE HOLD AS HOLD3 END
The request produces the following Master File:
FILE=HOLD3, SUFFIX=FIX SEGMENT=HOLD3, SEGTYPE=S01,$ FIELDNAME=CASHENGLAND ,ALIAS=E01 ,USAGE=I6 ,ACTUAL=I04 ,$ FIELDNAME=CASHFRANCE ,ALIAS=E02 ,USAGE=I6 ,ACTUAL=I04 ,$ FIELDNAME=CASHITALY ,ALIAS=E03 ,USAGE=I6 ,ACTUAL=I04 ,$ FIELDNAME=CASHJAPAN ,ALIAS=E04 ,USAGE=I6 ,ACTUAL=I04 ,$ FIELDNAME=CASHW GERMANY ,ALIAS=E05 ,USAGE=I6 ,ACTUAL=I04 ,$
Without the SET ASNAMES command, every field in the HOLD FILE is named COUNTRY.
To generate field names for ACROSS values that include only the field value, use the AS phrase followed by two single quotation marks, as follows:
SET ASNAMES=ON TABLE FILE CAR SUM SALES AS '' ACROSS COUNTRY ON TABLE HOLD AS HOLD4 END
The resulting Master File looks like this:
FILE=HOLD4, SUFFIX=FIX SEGMENT=HOLD4, SEGTYPE=S0,$ FIELDNAME=ENGLAND ,ALIAS=E01 ,USAGE=I6 ,ACTUAL=I04 ,$ FIELDNAME=FRANCE ,ALIAS=E02 ,USAGE=I6 ,ACTUALI04 ,$ FIELDNAME=ITALY ,ALIAS=E03 ,USAGE=I6 ,ACTUALI04 ,$ FIELDNAME=JAPAN ,ALIAS=E04 ,USAGE=I6 ,ACTUALI04 ,$ FIELDNAME=W GERMANY ,ALIAS=E05 ,USAGE=I6 ,ACTUALI04 ,$
The following request generates a HOLD file in ALPHA format using the OFF value for SET ASNAMES. The field CURR_SAL has the AS name SALARY in the request:
SET ASNAMES=OFF TABLE FILE EMPLOYEE SUM CURR_SAL AS SALARY PCT_INC BY DEPARTMENT ON TABLE HOLD FORMAT ALPHA END
In the HOLD Master File, AS names have not been propagated, the field names are from the original Master File, and default alias names are generated:
FILENAME=HOLD , SUFFIX=FIX , IOTYPE=STREAM, $ SEGMENT=HOLD, SEGTYPE=S1, $ FIELDNAME=DEPARTMENT, ALIAS=E01, USAGE=A10, ACTUAL=A10, $ FIELDNAME=CURR_SAL, ALIAS=E02, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=PCT_INC, ALIAS=E03, USAGE=F6.2, ACTUAL=A06, $
The following version of the request generates a relational table:
SET ASNAMES=OFF TABLE FILE EMPLOYEE SUM CURR_SAL AS SALARY PCT_INC BY DEPARTMENT ON TABLE HOLD FORMAT SQLMSS END
The field names from the original Master File have been propagated to the field names in the HOLD Maser File, and the alias names from the original Master File have been propagated to the HOLD Master File. The AS name for CURR_SAL has become the TITLE in the HOLD Master File:
FILENAME=HOLD , SUFFIX=SQLMSS , $ SEGMENT=SEG01, SEGTYPE=S0, $ FIELDNAME=DEPARTMENT, ALIAS=DPT, USAGE=A10, ACTUAL=A10, $ FIELDNAME=CURR_SAL, ALIAS=CSAL, USAGE=D12.2M, ACTUAL=D8, TITLE='SALARY', $ FIELDNAME=PCT_INC, ALIAS=PI, USAGE=F6.2, ACTUAL=F4, $
Changing SET ASNAMES to ON propagates the AS name SALARY to the field name in the HOLD Master File. The following is the Master File for the HOLD file in ALPHA format:
FILENAME=HOLD , SUFFIX=FIX , IOTYPE=STREAM, $ SEGMENT=HOLD, SEGTYPE=S1, $ FIELDNAME=DEPARTMENT, ALIAS=E01, USAGE=A10, ACTUAL=A10, $ FIELDNAME=SALARY, ALIAS=E02, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=PCT_INC, ALIAS=E03, USAGE=F6.2, ACTUAL=A06, $
The following is the Master File for the HOLD file in relational format:
FILENAME=HOLD , SUFFIX=SQLMSS , $ SEGMENT=SEG01, SEGTYPE=S0, $ FIELDNAME=DEPARTMENT, ALIAS=DPT, USAGE=A10, ACTUAL=A10, $ FIELDNAME=SALARY, ALIAS=CURR_SAL, USAGE=D12.2M, ACTUAL=D8, TITLE='SALARY', $ FIELDNAME=PCT_INC, ALIAS=PI, USAGE=F6.2, ACTUAL=F4, $
Changing SET ASNAMES to FLIP propagates the AS name SALARY to the alias name in the HOLD Master File. In the ALPHA HOLD file, the other field names have been propagated to the alias names in the HOLD Master File, and default field names have been generated:
FILENAME=HOLD , SUFFIX=FIX , IOTYPE=STREAM, $ SEGMENT=HOLD, SEGTYPE=S1, $ FIELDNAME=F01, ALIAS=DEPARTMENT, USAGE=A10, ACTUAL=A10, $ FIELDNAME=F02, ALIAS=SALARY, USAGE=D12.2M, ACTUAL=A12, $ FIELDNAME=F03, ALIAS=PCT_INC, USAGE=F6.2, ACTUAL=A06, $
In the relational HOLD file, changing SET ASNAMES to FLIP propagates the AS name SALARY to the alias name in the HOLD Master File. For that field, the field name from the original Master File becomes the field name in the HOLD Master File and the TITLE attribute. The other field names have been propagated to the alias names in the HOLD Master File, and the corresponding alias names from the original Master File have been propagated to the field names in the HOLD Master File:
FILENAME=HOLD , SUFFIX=SQLMSS , $ SEGMENT=SEG01, SEGTYPE=S0, $ FIELDNAME=DPT, ALIAS=DEPARTMENT, USAGE=A10, ACTUAL=A10, $ FIELDNAME=CURR_SAL, ALIAS=SALARY, USAGE=D12.2M, ACTUAL=D8, TITLE='SALARY', $ FIELDNAME=PI, ALIAS=PCT_INC, USAGE=F6.2, ACTUAL=F4, $
How to: |
You can use the SET HOLDLIST command to restrict fields in HOLD Master Files to those appearing in a request.
SET HOLDLIST = {PRINTONLY|ALL|ALLKEYS|EXPLICIT}
where:
Note: Vertical sort (BY) fields specified in the request with the NOPRINT option are not included in the HOLD file even if HOLDLIST=ALL.
The ALLKEYS setting enables caching of all of the data necessary for manipulating an active report.
Note that SET HOLDLIST may also be issued from within a TABLE request. When used with MATCH, SET HOLDLIST always behaves as if HOLDLIST is set to ALL.
When HOLDLIST is set to ALL, the following TABLE request produces a HOLD file containing all specified fields, including NOPRINT fields and values calculated with the COMPUTE command.
SET HOLDLIST=ALL TABLE FILE CAR PRINT CAR MODEL NOPRINT COMPUTE TEMPSEATS=SEATS+1; BY COUNTRY ON TABLE HOLD END ? HOLD
The output is:
NUMBER OF RECORDS IN TABLE= 18 |
LINE= |
18 |
||
DEFINITION OF HOLD FILE: HOLD |
||||
FIELDNAME |
ALIAS |
FORMAT |
||
COUNTRY |
E01 |
A10 |
||
CAR |
E02 |
A16 |
||
MODEL |
E03 |
A24 |
||
SEATS |
E04 |
I3 |
||
TEMPSEATS |
E05 |
D12.2 |
When HOLDLIST is set to PRINTONLY, the following TABLE request produces a HOLD file containing only fields that would appear in report output:
SET HOLDLIST=PRINTONLY TABLE FILE CAR PRINT CAR MODEL NOPRINT COMPUTE TEMPSEATS=SEATS+1; BY COUNTRY ON TABLE HOLD END ? HOLD
The output is:
NUMBER OF RECORDS IN TABLE= 18 |
LINES= |
18 |
||
DEFINITION OF HOLD FILE: HOLD |
||||
FIELDNAME |
ALIAS |
FORMAT |
||
COUNTRY |
E01 |
A10 |
||
CAR |
E02 |
A16 |
||
TEMPSEATS |
E03 |
D12.2 |
The following request against the GGSALES data source has two reformatted display fields (DOLLARS, UNITS). The DOLLARS field is also an explicit NOPRINT field. The BY field named CATEGORY is also an explicit NOPRINT field:
SET HOLDLIST=ALL TABLE FILE GGSALES SUM UNITS/I5 DOLLARS/D12.2 NOPRINT BY REGION BY CATEGORY NOPRINT ON TABLE HOLD FORMAT FOCUS END
Running the request with SET HOLDLIST=ALL generates the following HOLD Master File. Note that the DOLLARS and UNITS fields are included twice, once with the original format (which would have been implicitly NOPRINTed if the report had been printed rather than held) and once with the new format. However the NOPRINTed BY field (CATEGORY) is not included:
FILENAME=HOLD, SUFFIX=FOC , $ SEGMENT=SEG01, SEGTYPE=S1, $ FIELDNAME=REGION, ALIAS=E01, USAGE=A11, TITLE='Region', DESCRIPTION='Region code', $ FIELDNAME=UNITS, ALIAS=E02, USAGE=I08, TITLE='Unit Sales', DESCRIPTION='Number of units sold', $ FIELDNAME=UNITS, ALIAS=E03, USAGE=I5, TITLE='Unit Sales', $ FIELDNAME=DOLLARS, ALIAS=E04, USAGE=I08, TITLE='Dollar Sales', DESCRIPTION='Total dollar amount of reported sales', $ FIELDNAME=DOLLARS, ALIAS=E05, USAGE=D12.2, TITLE='Dollar Sales', $
Running the request with SET HOLDLIST=ALLKEYS generates the following HOLD Master File. Note that the DOLLARS and UNITS fields are included twice, once with the original format, which would have been implicitly NOPRINTed if the report had been printed rather than held, and once with the new format. The NOPRINTed BY field (CATEGORY) is included:
FILENAME=HOLD, SUFFIX=FOC , $ SEGMENT=SEG01, SEGTYPE=S2, $ FIELDNAME=REGION, ALIAS=E01, USAGE=A11, TITLE='Region', DESCRIPTION='Region code', $ FIELDNAME=CATEGORY, ALIAS=E02, USAGE=A11, TITLE='Category', DESCRIPTION='Product category', $ FIELDNAME=UNITS, ALIAS=E03, USAGE=I08, TITLE='Unit Sales', DESCRIPTION='Number of units sold', $ FIELDNAME=UNITS, ALIAS=E04, USAGE=I5, TITLE='Unit Sales', $ FIELDNAME=DOLLARS, ALIAS=E05, USAGE=I08, TITLE='Dollar Sales', DESCRIPTION='Total dollar amount of reported sales', $ FIELDNAME=DOLLARS, ALIAS=E06, USAGE=D12.2, TITLE='Dollar Sales', $
Running the request with SET HOLDLIST=PRINTONLY generates the following HOLD Master File. Only the fields that would have actually printed on the report output are included: REGION and UNITS with the new format (I5). All explicitly and implicitly NOPRINTed fields are excluded, including the NOPRINTed BY field (CATEGORY):
FILENAME=HOLD , SUFFIX=FOC , $ SEGMENT=SEG01, SEGTYPE=S1, $ FIELDNAME=REGION, ALIAS=E01, USAGE=A11, TITLE='Region', DESCRIPTION='Region code', $ FIELDNAME=UNITS, ALIAS=E02, USAGE=I5, TITLE='Unit Sales', $
Running the request with SET HOLDLIST=EXPLICIT generates the following HOLD Master File. The fields that would have actually printed on the report output are included and so are the explicitly NOPRINTed fields (the display field DOLLARS and the BY field CATEGORY). The implicitly NOPRINTed fields (DOLLARS and UNITS with their original formats) are omitted:
FILENAME=HOLD, SUFFIX=FOC , $ SEGMENT=SEG01, SEGTYPE=S2, $ FIELDNAME=REGION, ALIAS=E01, USAGE=A11, TITLE='Region', DESCRIPTION='Region code', $ FIELDNAME=CATEGORY, ALIAS=E02, USAGE=A11, TITLE='Category', DESCRIPTION='Product category', $ FIELDNAME=UNITS, ALIAS=E03, USAGE=I5, TITLE='Unit Sales', $ FIELDNAME=DOLLARS, ALIAS=E04, USAGE=D12.2, TITLE='Dollar Sales', $
How to: |
The SET HOLDATTR command controls whether the TITLE and ACCEPT attributes, as well as other attributes in the original Master File, are propagated to the HOLD Master File. SET HOLDATTR does not affect the way fields are named in the HOLD Master File.
Note that if a field in a data source does not have the TITLE attribute specified in the Master File, but there is an AS phrase specified for the field in a report request, the corresponding field in the HOLD file is named according to the AS phrase.
SET HOLDATTR =[ON|OFF|FOCUS|CUBE]
where:
Propagates folders and DV_ROLE attributes, as well as TITLE attributes to the HOLD Master File. It also propagates the field name as the alias value.
In this example, the Master File for the CAR data source specifies TITLE and ACCEPT attributes:
FILENAME=CAR2, SUFFIX=FOC SEGNAME=ORIGIN, SEGTYPE=S1 FIELDNAME =COUNTRY, COUNTRY, A10, TITLE='COUNTRY OF ORIGIN', ACCEPT='CANADA' OR 'ENGLAND' OR 'FRANCE' OR 'ITALY' OR 'JAPAN' OR 'W GERMANY', FIELDTYPE=I,$ SEGNAME=COMP, SEGTYPE=S1, PARENT=ORIGIN FIELDNAME=CAR, CARS, A16, TITLE='NAME OF CAR',$ . . .
Using SET HOLDATTR=FOCUS, the following request
SET HOLDATTR = FOCUS TABLE FILE CAR2 PRINT CAR BY COUNTRY ON TABLE HOLD FORMAT FOCUS AS HOLD5 END
produces this HOLD Master File:
FILE=HOLD5, SUFFIX=FOC SEGMENT=SEG01, SEGTYPE=S02 FIELDNAME=COUNTRY ,USAGE=E01 ,ACTUAL=A10 TITLE='COUNTRY OF ORIGIN', ACCEPT=CANADA ENGLAND FRANCE ITALY JAPAN 'W GERMANY',$ FIELDNAME=FOCLIST ,USAGE=E02 ,ACTUAL=I5 ,$ FIELDNAME=CAR ,USAGE=E03 ,ACTUAL=A16 , TITLE='NAME OF CAR' ,$