May 12, 2006, 02:31 PM
Big GuyI have finally found a way to get the report to run. Here is how the fex now looks:
APP PATH BASEAPP PCMS
-RUN
-* File Region Contract.FEX
-* Default Mode: Report FEX
ENGINE SQLORA SET DEFAULT_CONNECTION WEBD
SET ASNAMES = ON
SQL SQLORA SET DATETIME ON
FILEDEF HOLD2 DISK HOLD2.FTM (APPEND
-RUN
SQL
SELECT R.REGION_NAME AS RegionName,
M.MARKET_NAME AS MarketName,
M.MANAGER_FNAME AS FirstName,
M.MANAGER_LNAME AS LastName,
E.EXCH_NAME AS ExchangeName,
E.COST_CENTER AS CostCenter,
TC.TCOMP_NAME AS TCompanyName,
TC.TCOMP_NUMBER AS TCompanyNumber,
EC.NUM_CTL_POLES AS CTLPoles,
EC.NUM_CTL_SEC_POLES AS CTLSecPoles,
EC.NUM_FCOMP_POLES AS FCoPoles,
EC.NUM_FCOMP_SEC_POLES AS FCoSecPoles,
EC.NUM_FCOMP_ATTACH AS FCoAttach,
EC.NUM_FCOMP_SEC_ATTACH AS FCoSecAttach,
MT.MUNICIP_NAME AS MunicipalityName,
MT.TAX AS MunicipalityTax,
MT.ACCT_NUM AS MunicipalityAccount,
CT.COUNTY_NAME AS CountyName,
CT.TAX AS CountyTax,
CT.ACCT_NUM AS CountyAccount,
ST.STATE_NAME AS StateName,
ST.TAX AS StateTax,
ST.ACCT_NUM AS StateAccount,
FC.FCOMPANY_NAME AS ForeignCoName,
FCT.DESCRIP AS ForeignCoType,
C.CONTRACT_NUM AS ContractNumber,
C.CTL_CUR_RATE AS CTLCurrentRate,
C.CTL_SEC_RATE AS CTLSecondaryRate,
C.REV_TAXABLE AS RevenueTaxable,
C.EXP_TAXABLE AS ExpenseTaxable,
C.VEND_CUR_RATE AS VendorCurrentRate,
C.VEND_SEC_RATE AS VendorSecondaryRate,
C.LAST_CHANGED AS LastChanged,
BF.DESCRIP AS Description,
C.NOTES AS ContractNotes
FROM PCMSREGION R,
PCMS.MARKET M,
PCMS.EXCHANGE E,
PCMS.EXCH_CONTRACT EC,
PCMS.MUNICIP_TAX MT,
PCMS.COUNTY_TAX CT,
PCMS.STATE_TAX ST,
PCMS.TCOMPANY TC,
PCMS.FCOMP_CONTRACT FCC,
PCMS.FCOMPANY_DETAIL FCD,
PCMS.FCOMPANY FC,
PCMS.FCOMPANY_TYPE FCT,
PCMS.CONTRACT C,
PCMS.BILLING_FREQ BF
WHERE R.REGION_PK = ®ION_PK
AND M.REGION_FK = R.REGION_PK
AND E.MARKET_FK = M.MARKET_PK
AND E.TCOMPANY_FK = TC.TCOMPANY_PK
AND EC.EXCHANGE_FK = E.EXCHANGE_PK
AND E.MUNICIP_TAX_FK = MT.MUNICIP_TAX_PK
AND E.COUNTY_TAX_FK = CT.COUNTY_TAX_PK
AND E.STATE_TAX_FK = ST.STATE_TAX_PK
AND EC.FCOMP_CONTRACT_FK = FCC.FCOMP_CONTRACT_PK
AND FCC.FCOMPANY_DETAIL_FK = FCD.FCOMPANY_DETAIL_PK
AND FCD.FCOMPANY_FK = FC.FCOMPANY_PK
AND FC.FCOMPANY_TYPE_FK = FCT.FCOMPANY_TYPE_PK
AND FCC.CONTRACT_FK = C.CONTRACT_PK
AND C.BILLING_FREQ_FK = BF.BILLING_FREQ_PK
ORDER BY RegionName, MarketName, ExchangeName;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLD2
WHERE LASTCHANGED LT DT(&theDate)
END
-RUN
SQL
SELECT R.REGION_NAME AS RegionName,
M.MARKET_NAME AS MarketName,
M.MANAGER_FNAME AS FirstName,
M.MANAGER_LNAME AS LastName,
E.EXCH_NAME AS ExchangeName,
E.COST_CENTER AS CostCenter,
TC.TCOMP_NAME AS TCompanyName,
TC.TCOMP_NUMBER AS TCompanyNumber,
ECH.NUM_CTL_POLES AS CTLPoles,
ECH.NUM_CTL_SEC_POLES AS CTLSecPoles,
ECH.NUM_FCOMP_POLES AS FCoPoles,
ECH.NUM_FCOMP_SEC_POLES AS FCoSecPoles,
ECH.NUM_FCOMP_ATTACH AS FCoAttach,
ECH.NUM_FCOMP_SEC_ATTACH AS FCoSecAttach,
MT.MUNICIP_NAME AS MunicipalityName,
MT.TAX AS MunicipalityTax,
MT.ACCT_NUM AS MunicipalityAccount,
CT.COUNTY_NAME AS CountyName,
CT.TAX AS CountyTax,
CT.ACCT_NUM AS CountyAccount,
ST.STATE_NAME AS StateName,
ST.TAX AS StateTax,
ST.ACCT_NUM AS StateAccount,
FC.FCOMPANY_NAME AS ForeignCoName,
FCT.DESCRIP AS ForeignCoType,
C.CONTRACT_NUM AS ContractNumber,
C.CTL_CUR_RATE AS CTLCurrentRate,
C.CTL_SEC_RATE AS CTLSecondaryRate,
C.REV_TAXABLE AS RevenueTaxable,
C.EXP_TAXABLE AS ExpenseTaxable,
C.VEND_CUR_RATE AS VendorCurrentRate,
C.VEND_SEC_RATE AS VendorSecondaryRate,
C.LAST_CHANGED AS LastChanged,
BF.DESCRIP AS Description,
C.NOTES AS ContractNotes
FROM PCMSREGION R,
PCMS.MARKET M,
PCMS.EXCHANGE E,
PCMS.EXCH_CONTRACT_HISTORY ECH,
PCMS.MUNICIP_TAX MT,
PCMS.COUNTY_TAX CT,
PCMS.STATE_TAX ST,
PCMS.TCOMPANY TC,
PCMS.FCOMP_CONTRACT FCC,
PCMS.FCOMPANY_DETAIL FCD,
PCMS.FCOMPANY FC,
PCMS.FCOMPANY_TYPE FCT,
PCMS.CONTRACT C,
PCMS.BILLING_FREQ BF
WHERE R.REGION_PK = ®ION_PK
AND M.REGION_FK = R.REGION_PK
AND E.MARKET_FK = M.MARKET_PK
AND E.TCOMPANY_FK = TC.TCOMPANY_PK
AND ECH.EXCHANGE_FK = E.EXCHANGE_PK
AND E.MUNICIP_TAX_FK = MT.MUNICIP_TAX_PK
AND E.COUNTY_TAX_FK = CT.COUNTY_TAX_PK
AND E.STATE_TAX_FK = ST.STATE_TAX_PK
AND ECH.FCOMP_CONTRACT_FK = FCC.FCOMP_CONTRACT_PK
AND FCC.FCOMPANY_DETAIL_FK = FCD.FCOMPANY_DETAIL_PK
AND FCD.FCOMPANY_FK = FC.FCOMPANY_PK
AND FC.FCOMPANY_TYPE_FK = FCT.FCOMPANY_TYPE_PK
AND FCC.CONTRACT_FK = C.CONTRACT_PK
AND C.BILLING_FREQ_FK = BF.BILLING_FREQ_PK
ORDER BY RegionName, MarketName, ExchangeName;
TABLE FILE SQLOUT
PRINT *
ON TABLE HOLD AS HOLD2
WHERE LASTCHANGED LT DT(&theDate)
END
-RUN
TABLE FILE HOLD2
PRINT *
ON TABLE HOLD FORMAT FOCUS AS DDD
END
DEFINE FILE DDD
TODAYDATE/A10 = '&theDate';
FULLNAME/A41 = FIRSTNAME || (' ' | LASTNAME);
EXPTOTAL1/D11.2 = FCOPOLES * VENDORCURRENTRATE;
EXPTOTAL2/D11.2 = FCOSECPOLES * VENDORSECONDARYRATE;
EXPTOTAL3/D11.2 = FCOATTACH * VENDORCURRENTRATE;
EXPTOTAL4/D11.2 = FCOSECATTACH * VENDORSECONDARYRATE;
REVTOTAL1/D11.2 = CTLPOLES * CTLCURRENTRATE;
REVTOTAL2/D11.2 = CTLSECPOLES * CTLSECONDARYRATE;
LEN2/I3 = ARGLEN(500, CONTRACTNOTES, LEN2);
EXPTOTAL/D11.2 =
IF EXPENSETAXABLE = 'Y' THEN
EXPTOTAL1 + (EXPTOTAL1 * STATETAX/100) + (EXPTOTAL1 * COUNTYTAX/100) + (EXPTOTAL1 * MUNICIPALITYTAX/100) + EXPTOTAL2 + (EXPTOTAL2 * STATETAX/100) + (EXPTOTAL2 * COUNTYTAX/100) + (EXPTOTAL2 * MUNICIPALITYTAX/100) + EXPTOTAL3 + (EXPTOTAL3 * STATETAX/100) + (EXPTOTAL3 * COUNTYTAX/100) + (EXPTOTAL3 * MUNICIPALITYTAX/100) + EXPTOTAL4 + (EXPTOTAL4 * STATETAX/100) + (EXPTOTAL4 * COUNTYTAX/100) + (EXPTOTAL4 * MUNICIPALITYTAX/100)
ELSE
EXPTOTAL1 + EXPTOTAL2 + EXPTOTAL3 + EXPTOTAL4;
REVTOTAL/D11.2 = REVTOTAL1 + REVTOTAL2;
END
TABLE FILE DDD
PRINT
REGIONNAME AS REGIONNAME
TODAYDATE AS TODAYDATE
COSTCENTER AS COSTCENTER
FULLNAME AS FULLNAME
FCOPOLES AS FCOPOLES
FCOATTACH AS FCOATTACH
VENDORCURRENTRATE AS VENDORCURRENTRATE
FCOSECPOLES AS FCOSECPOLES
FCOSECATTACH AS FCOSECATTACH
VENDORSECONDARYRATE AS VENDORSECONDARYRATE
EXPTOTAL AS EXPTOTAL
CTLPOLES AS CTLPOLES
CTLCURRENTRATE AS CTLCURRENTRATE
CTLSECPOLES AS CTLSECPOLES
CTLSECONDARYRATE AS CTLSECONDARYRATE
REVTOTAL AS REVTOTAL
CONTRACTNUMBER AS CONTRACTNUMBER
DESCRIPTION AS DESCRIPTION
CONTRACTNOTES AS CONTRACTNOTES
BY TCOMPANYNUMBER AS TCOMPANYNUMBER
BY FOREIGNCONAME AS FOREIGNCONAME
BY EXCHANGENAME AS EXCHANGENAME
ON TABLE SET NODATA ''
ON TABLE HOLD FORMAT FOCUS AS HABC
END
-RUN
-OLAP ON
OLAP DIMENSIONS
-* DIMENSIONS FILE DDD
Market Name: MARKETNAME;
Exchange Name: EXCHANGENAME, COSTCENTER;
TCompany: TCOMPANYNAME, TCOMPANYNUMBER;
Exch Contract: CTLPOLES, CTLSECPOLES, FCOPOLES, FCOSECPOLES, FCOATTACH, FCOSECATTACH;
Foreign Company: FOREIGNCONAME, FOREIGNCOTYPE;
Billing Frequency: DESCRIPTION;
Contract: CONTRACTNUMBER, CTLCURRENTRATE, CTLSECONDARYRATE, VENDORCURRENTRATE, VENDORSECONDARYRATE, CONTRACTNOTES;
END
APP FI HABC BASEAPP/HABC.FTM
TABLE FILE HABC
PRINT
COSTCENTER AS 'Cost Center'
FULLNAME AS 'Market Manager'
FCOPOLES AS '# Vendor Poles'
FCOATTACH AS '# Vendor Attach'
VENDORCURRENTRATE AS 'Vendor Rate'
FCOSECPOLES AS '# Vendor Sec. Poles'
FCOSECATTACH AS '# Vendor Sec. Attach'
VENDORSECONDARYRATE AS 'Vendor Sec. Rate'
EXPTOTAL AS 'Pole Exp'
CTLPOLES AS '# CTL Poles'
CTLCURRENTRATE AS 'CTL Rate'
CTLSECPOLES AS '# CTL Sec. Poles'
CTLSECONDARYRATE AS 'CTL Sec. Rate'
REVTOTAL AS 'CTL Pole Rev'
CONTRACTNUMBER AS 'Contract Number'
DESCRIPTION AS 'Bill Cycle'
CONTRACTNOTES AS 'Contract Notes'
BY TCOMPANYNUMBER AS 'Company'
BY FOREIGNCONAME AS 'Vendor Name'
BY EXCHANGENAME AS 'Exchange Name'
HEADING
"
Region Contract Report"
"Date: "
" "
ON TABLE COLUMN-TOTAL AS 'Totals'
ON TABLE SET PAGE-NUM OFF
ON TABLE SET ONLINE-FMT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET AUTODRILL ON
ON TABLE SET OLAPPANE TOP
ON TABLE SET STYLE *
UNITS=IN,
PAGESIZE='SCREEN',
LEFTMARGIN=0.000000,
RIGHTMARGIN=0.000000,
TOPMARGIN=0.000000,
BOTTOMMARGIN=0.000000,
SQUEEZE=ON,
ORIENTATION=PORTRAIT,
$
TYPE=REPORT,
GRID=OFF,
FONT='ARIAL',
SIZE=9,
COLOR='BLACK',
BACKCOLOR='NONE',
STYLE=NORMAL,
RIGHTGAP=0.125000,
TOPGAP=0.013889,
BOTTOMGAP=0.027778,
$
ENDSTYLE
END
I have a problem when I drill down in any field though, or when I hide a field on the report. That problem is the second heading line that contains the date has disappeared. Can anyone suggest anything to help me?