Hi,

I've created a hold file through the use of an ACROSS query with some variables.

 
TABLE FILE DWH_CLEARING_AND_SETTLEMENT
SUM 
     &V_MEASURES.(<Nbr,DWH.FIELDS.COUNT>,<Amt,DWH.FIELDS.AMOUNT>.Please select sum field(s).
BY  LOWEST DWH.FIELDS.YEAR_MONTH
ACROSS LOWEST &V_ACROSS.(<Mcc,DWH.FIELDS.MCC>,<Type ,DWH.FIELDS.TYPE>).Please select across field(s).  
ON TABLE SET ASNAMES ON
ON TABLE HOLD 

This works fine and I can use the hold file to create a report on it.
Considering the number of fields can be different however (depending on chosen across field), I need to print a variable number of fields:

 
TABLE FILE HOLD
SUM *
BY HOLD.HOLD.YEAR_MONTH 
ON TABLE PCHOLD FORMAT FLEX

My question: How can I prevent the YEAR_MONTH to show up twice? (once as BY, once as SUM)
Note: PRINT * doesn't work as the end report is shown as a FLEX chart --> for the chart to show correctly, it needs to be in the BY clause

This message has been edited. Last edited by: Kerry,

Here's one possible solution. Use CHECK FILE HOLD to get a list of fields in your HOLD file and use this list to exclude your BY field from the final report query.

TABLE FILE CAR
 PRINT
  SALES
  RETAIL_COST
  DEALER_COST
 BY COUNTRY
 ON TABLE HOLD AS CARHOLD
END
-*
CHECK FILE CARHOLD HOLD
-*
TABLE FILE HOLD
 PRINT FIELDNAME
 WHERE FIELDNAME NE 'COUNTRY';
 ON TABLE SAVE AS FIELDS
END
-RUN
-*
TABLE FILE CARHOLD
 SUM
-INCLUDE FIELDS
 BY COUNTRY
END

What about

SET HOLDLIST=PRINTONLY

and

BY YEAR_MONTH NOPRINT

?

A late reply on a closed topic as I was on vacation (yay for me)

@Dan: Thanks for the tip, it worked like a charm after I removed any quotes from the column names in the hold file

@Francis: Unless I misunderstood your suggestion, the NOPRINT option would remove YEAR_MONTH from the hold file, but I need this data to show on my x-axis.

Thanks to you both!