I have looked at many of the threads on this site, have searched for possible solutions, but was unable to find anything that might be able to help me with this problem.

I have a report that currently displays 30 items from 12 tables. My users now want to be able to pick the columns that they want displayed on the report. I want to leave the select alone if I can, because it is working great. Is there a way to only print the columns that a user has selected? If there is such an animal some sample code would be appreciated.

Here are a couple of ideas for you:

1) Take a look at OLAP reporting this should do what you want.
2) You could just have the GUI pass a variable containing the field names.
Here is an exmaple of #2

-SET &FIELD00 = 'RETAIL_COST';
-SET &FIELD01 = 'DEALER_COST';
-SET &FIELD02 = '';
-RUN

TABLE FILE CAR
PRINT
     &FIELD00
     &FIELD01
     &FIELD02
BY
     COUNTRY
BY
     CAR
END
-RUN

We did this same thing by creating dynamic sql and placing the user inputs into a sql table. The user selects all the fields they want to display, write those to a table. They select there criteria, write that to a table. After there done. Build your sql based on the contents of the table and you have it. You will need to fluent in SQL, not sure what DB you're using.

I have looked at the OLAP reporting and can not make the connection of how to make that work for me. The other suggestions that were made, would require many IF tests, including in the print section, and I have not found any examples of how to do that. Can anyone send me a code example or suggest another way to make this happen?

Thanks All.

The OLAP should work. When you have all columns from the select statement, you can have the users hide each column they don't want to print and even save the report into a different format to print.

I have gone through the tutorial on OLAP reports, and I found they do not run on my machine. I had someone else do the report on his pc and at least it worked for him.

I will say that the tutorial was fine, but it was nice that every field they wanted to print was in 1 table. Is that the way it must be for OLAP to work? I could not find a way to sort my 3 BY fields in order, because the fields all come from different tables. I could also add only 1 table into the Dimensions area on the form.

Is there some trick to this that the tutorial did not bring up? I would appreciate any replies.

Thanks in advance folks.

I would try joining the 3 tables together and then creating the OLAP on that combined table, displaying all the fields, and having the 3 By fields.

I have the tables joined together and the data that it has is correct, but how do I now get that data into the OLAP report. The data is in a HOLD table because I am in a FEX to get the tables joined. Is it possible within the FEX to enter the HOLD table into an OLAP report? OLAP only gives you the option of selecting a table that belongs to the project. If in fact OLAP is the way to go, can anyone send me an example of how to do this.

Thanks all.

This message has been edited. Last edited by: Big Guy,

I 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?