When we run the below code

TABLE FILE CAR
PRINT DEALER_COST AS 'Dealer Cost'
BY CAR AS 'Car'
BY RETAIL_COST AS 'Retail Cost'
BY SALES AS 'Sales'
ON TABLE COLUMN-TOTAL RETAIL_COST
ON TABLE PCHOLD FORMAT EXL2K PIVOT
PAGEFIELDS COUNTRY
CACHEFIELDS MODEL
END

we get output as below in Excel

CAR Retailcost Sales Total

ALFA ROMEO 5,925 4800 4,915
6,820 12400 5,660
13000 5,660
AUDI 5,970 7800 5,063
BMW 5,940 8950 5,800
6,355 8900 6,000
9,097 14000 8,300
9,495 15600 8,400
13,752 14000 10,000
14,123 18940 11,000
DATSUN 3,139 43000 2,626
JAGUAR 8,878 0 7,427
13,491 12000 11,194
JENSEN 17,850 0 14,940
MASERATI 31,500 0 25,000
PEUGEOT 5,610 0 4,631
TOYOTA 3,339 35030 2,886
TRIUMPH 5,100 0 4,292
Grand Total 143,794


But the requirement is I do not wanted Dealer cost to be printed in the report. When I tried giving NOPRINT it throws error.
The output should have CAR ,DealerCost and Sales printed with Grand total on Dealer cost and Sales.

Please let me know if it is possible to hide a column while exporting to Excel? And also how to achieve Grand total on BY fields.

To hide a column in a pivot table means having it as a CACHEFIELD or PAGEFIELD
or hiding it, once the pivot is opened.
That is either done manually or via a macro.

You cannot show totals for hidden fields in an Excel pivot table.

Excel Pivot tables do not allow totals/sub-totals on 'BY' fields, it is not logical from
a conceptual point of view.

It only allows totals/sub-totals on the columns/measures managed by the ‘BY’ fields.

Excel Pivot is not the solution for your requirement.

Hiding a column,I meant hiding it when opening the report.

Actually when we give the fields to be totaled in the PRINT stmnt , they get displayed one below the other in the excel report.But I want them to be displayed one after the other as they get printed in the html report.Thats why I tried giving the measures in BY stmnt.

Is it possible to makes the measures get printed one after the other in excel report.

Also is it possible to give a default selected value in PAGEFILED instead of All.

Sridevi, You obviously do not understand the way Excel pivot tables work.

As I said you cannot show a total for a column without its detail.

The only way that you can display the measures in columns is to change them into column data
from an Excel pivot perspective.

The only way that you can adjust the display of what is seen when the pivot table is opened
is to do it using a macro or if you are 7.1 a WebFocus template.
That includes showing a specific page value and not all which is the Excel default.

The alternative option is to have Excel populate the data from WebFocus into an existing spreadsheet.

Switching to columns for the data means that once in Excel the order is controlled by Excel.
The effect of that is even if you force the display order to be RETAIL_COST SALES DEALER_COST
Microsoft Excel thinking that it knows better than you will switch it to DEALER_COST RETAIL_COST SALES.
The only way to over come this is to introduce a dummy ACROSS to maintain the order.

The following generates an Excel pivot table that you could apply a macro/template to,
which would do most of what you want.


FILEDEF PIVDATA DISK PIVDATA.FTM (APPEND
-RUN
-SET &CNT=0;
-REPEAT ENDREPEAT 3 TIMES
-SET &CNT=&CNT+1;
TABLE FILE CAR
SUM
COMPUTE PIVCOLS/I1=&CNT;
COMPUTE PIVCOL/A12=IF &CNT EQ 1 THEN 'RETAIL_COST'
ELSE IF &CNT EQ 2 THEN 'SALES'
ELSE 'DEALER_COST';
COMPUTE PIVDATA/D7=IF &CNT EQ 1 THEN RETAIL_COST
ELSE IF &CNT EQ 2 THEN SALES
ELSE DEALER_COST;
BY CAR BY COUNTRY BY MODEL
ON TABLE HOLD AS PIVDATA
END
-RUN
-ENDREPEAT
TABLE FILE PIVDATA
PRINT PIVDATA
ACROSS PIVCOLS
ACROSS PIVCOL
ON TABLE PCHOLD FORMAT EXL2K PIVOT
PAGEFIELDS COUNTRY CAR
CACHEFIELDS MODEL
END