Hello

I have a report that I am working on. Below is the sample with CAR file.
I am trying to hide DEALER_COST and RETAIL_COST for Total. Only SALES should show up for Total. Is it possible?

DEFINE FILE CAR
ACROSS_FLD/A10 = COUNTRY;
END
TABLE FILE CAR
SUM
	SALES
	DEALER_COST
	RETAIL_COST
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H1
END 

DEFINE FILE CAR
ACROSS_FLD/A10 = 'Total';
END
TABLE FILE CAR
SUM
	SALES
	DEALER_COST
	RETAIL_COST
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H2
END

TABLE FILE H1
SUM
	SALES
	DEALER_COST
	RETAIL_COST
BY ACROSS_FLD
ON TABLE HOLD AS ALL_REC
MORE
FILE H2
END

TABLE FILE ALL_REC
SUM
	SALES/D20CM
	DEALER_COST/D20CM
	RETAIL_COST/D20CM
ACROSS ACROSS_FLD AS ''
END
 

Please suggest.
Thank you.

This message has been edited. Last edited by: FP Mod Chuck,

Could you show us what the output would look like?

quote:

Originally posted by BabakNYC:
Could you show us what the output would look like?

I changed my original post. Please check.

Just got a thought. If its difficult, I am thinking whether to create "Total" section as a different report with required fields and display it at end of individual report.
May be I will give a try and see. This will work for HTML report. But an excel version is also needed, and this won't work with excel.

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

Here two options for you
-1- That puts the Total on left side

DEFINE FILE CAR
DUMMY /I1 = 0;
END
TABLE FILE CAR
SUM SALES/D8CM AS 'Total,Sales'
BY DUMMY NOPRINT
SUM SALES/D8CM       AS 'Sales'
    DEALER_COST/D8CM AS 'Dealer,Cost'
    RETAIL_COST/D8CM AS 'Retail,Cost'
BY DUMMY NOPRINT
ACROSS COUNTRY AS ''
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
END 

-2- Where Total is on right

DEFINE FILE CAR
ACROSS_FLD/A10 = COUNTRY;
END
TABLE FILE CAR
SUM SALES/D8CM AS 'MEAS'
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 1;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Sales';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H10
END 
-RUN

TABLE FILE CAR
SUM DEALER_COST/D8CM AS 'MEAS'
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 2;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Dealer Cost';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H11
END 
-RUN

TABLE FILE CAR
SUM RETAIL_COST/D8CM AS 'MEAS'
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 3;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Retail Cost';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H12
END 
-RUN

DEFINE FILE CAR
ACROSS_FLD/A10 = 'Total';
END
TABLE FILE CAR
SUM SALES/D8CM AS 'MEAS'
BY TOTAL COMPUTE ACROSS_ID /I2  = 99;
BY TOTAL COMPUTE MEAS_ID   /I2  = 4;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Sales';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H2
END
-RUN

TABLE FILE H10
SUM MEAS
BY ACROSS_ID
BY MEAS_ID
BY MEAS_TXT
BY ACROSS_FLD
ON TABLE HOLD AS ALL_REC
MORE
FILE H11
MORE
FILE H12
MORE
FILE H2
END
-RUN

TABLE FILE ALL_REC
SUM MEAS
ACROSS ACROSS_FLD AS ''
ACROSS MEAS_ID    NOPRINT
ACROSS ACROSS_ID  NOPRINT
ACROSS MEAS_TXT   AS ''
END
-RUN

Martin, 2nd option worked well me. What can be done if 2 measures are currencies and 1 measure is a normal integer. I specified the format but its showing $ for all measures.

quote:

Originally posted by MartinY:
Here two options for you
-1- That puts the Total on left side

DEFINE FILE CAR
DUMMY /I1 = 0;
END
TABLE FILE CAR
SUM SALES/D8CM AS 'Total,Sales'
BY DUMMY NOPRINT
SUM SALES/D8CM       AS 'Sales'
    DEALER_COST/D8CM AS 'Dealer,Cost'
    RETAIL_COST/D8CM AS 'Retail,Cost'
BY DUMMY NOPRINT
ACROSS COUNTRY AS ''
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
END 

-2- Where Total is on right

DEFINE FILE CAR
ACROSS_FLD/A10 = COUNTRY;
END
TABLE FILE CAR
SUM SALES/D8CM AS 'MEAS'
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 1;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Sales';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H10
END 
-RUN

TABLE FILE CAR
SUM DEALER_COST/D8CM AS 'MEAS'
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 2;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Dealer Cost';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H11
END 
-RUN

TABLE FILE CAR
SUM RETAIL_COST/D8CM AS 'MEAS'
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 3;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Retail Cost';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H12
END 
-RUN

DEFINE FILE CAR
ACROSS_FLD/A10 = 'Total';
END
TABLE FILE CAR
SUM SALES/D8CM AS 'MEAS'
BY TOTAL COMPUTE ACROSS_ID /I2  = 99;
BY TOTAL COMPUTE MEAS_ID   /I2  = 4;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Sales';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H2
END
-RUN

TABLE FILE H10
SUM MEAS
BY ACROSS_ID
BY MEAS_ID
BY MEAS_TXT
BY ACROSS_FLD
ON TABLE HOLD AS ALL_REC
MORE
FILE H11
MORE
FILE H12
MORE
FILE H2
END
-RUN

TABLE FILE ALL_REC
SUM MEAS
ACROSS ACROSS_FLD AS ''
ACROSS MEAS_ID    NOPRINT
ACROSS ACROSS_ID  NOPRINT
ACROSS MEAS_TXT   AS ''
END
-RUN

Building on the first option from Martin, you could NOPRINT the aggregates at report level and then add a compute to aggregate those values to the end of the report like the code below, or just use the principal.

DEFINE FILE CAR
DUMMY /I1 = 0;
END
TABLE FILE CAR
SUM SALES NOPRINT
    DEALER_COST NOPRINT
	RETAIL_COST NOPRINT
BY DUMMY NOPRINT
SUM SALES/D8C        AS 'Sales'
    DEALER_COST/D8CM AS 'Dealer,Cost'
    RETAIL_COST/D8CM AS 'Retail,Cost'
BY DUMMY NOPRINT
ACROSS COUNTRY AS ''
COMPUTE TOTAL/D8CM = C1 + C2 + C3;  AS 'Total,Measures'
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  grid=off, $
ENDSTYLE
END

T

Using the solution that you've chosen, this is one way without too many chances to original code

DEFINE FILE CAR
ACROSS_FLD/A10 = COUNTRY;
END
TABLE FILE CAR
SUM COMPUTE MEAS/A20V = FPRINT(SEATS, 'D8C', 'A20V');
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 1;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Seats';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H10
END 
-RUN

TABLE FILE CAR
SUM COMPUTE MEAS/A20V = FPRINT(DEALER_COST, 'D8CM', 'A20V');
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 2;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Dealer Cost';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H11
END 
-RUN

TABLE FILE CAR
SUM COMPUTE MEAS/A20V = FPRINT(RETAIL_COST, 'D8CM', 'A20V');
BY TOTAL COMPUTE ACROSS_ID /I2  = 1;
BY TOTAL COMPUTE MEAS_ID   /I2  = 3;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Retail Cost';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H12
END 
-RUN

DEFINE FILE CAR
ACROSS_FLD/A10 = 'Total';
END
TABLE FILE CAR
SUM COMPUTE MEAS/A20V = FPRINT(SALES, 'D8CM', 'A20V');
BY TOTAL COMPUTE ACROSS_ID /I2  = 99;
BY TOTAL COMPUTE MEAS_ID   /I2  = 4;
BY TOTAL COMPUTE MEAS_TXT  /A20V= 'Sales';
BY ACROSS_FLD
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE HOLD AS H2
END
-RUN

TABLE FILE H10
SUM MEAS
BY ACROSS_ID
BY MEAS_ID
BY MEAS_TXT
BY ACROSS_FLD
ON TABLE HOLD AS ALL_REC
MORE
FILE H11
MORE
FILE H12
MORE
FILE H2
END
-RUN

TABLE FILE ALL_REC
SUM MEAS
ACROSS ACROSS_FLD AS ''
ACROSS MEAS_ID    NOPRINT
ACROSS ACROSS_ID  NOPRINT
ACROSS MEAS_TXT   AS ''
ON TABLE SET STYLE *
TYPE=DATA, COLUMN=MEAS, JUSTIFY=RIGHT, $
ENDSTYLE
END
-RUN

quote:

Originally posted by MartinY:
Using the solution that you've chosen, this is one way without too many chances to original code

Thanks Martin. FPRINT did the trick for HTML.
When I use same code for Excel format, excel is displaying them fine but as text. There is a small arrow appearing in all cells and tooltip says "The number in this cell is formatted as text". An option in that menu is provided to "convert to number". Only after converting, excel is treating them as numbers and calculations are working. Customer is requesting to display them as numbers directly. I tried using XLSX and XLSX FORMULA as output formats. Any suggestions on this?

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

Don't forget that dynamic formatting is possible. This will ensure that your numerics in MS Excel are still numbers.

I know that Martin has put a lot of effort into his examples, and aplaud him for that, such a helpful Guy. But Martin, you're working the data too hard

DEFINE FILE CAR
DUMMY /I1 = 0;
END
TABLE FILE CAR
SUM SALES NOPRINT
    SEATS NOPRINT
    DEALER_COST NOPRINT
	RETAIL_COST NOPRINT
BY DUMMY NOPRINT
SUM COMPUTE FMT/A8 = DECODE COUNTRY ('ENGLAND' 'D8C!L' 'JAPAN' 'D8CM' ELSE 'D8C!E'); NOPRINT
    SEATS/D8C        AS 'Sales'
    DEALER_COST/FMT  AS 'Dealer,Cost'
    RETAIL_COST/FMT  AS 'Retail,Cost'
BY DUMMY NOPRINT
ACROSS COUNTRY AS ''
COMPUTE TOTAL/D8CM = C1;  AS 'Total,Sales'
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  grid=off, $
ENDSTYLE
END

T

Tony, this is the similar to the first version of code I wrote. But there is lot of styling and formatting needed as per requirements (very specific) and this made it difficult. Martin's code provided flexibility for the styling. So, I followed that.

quote:

Originally posted by Tony A:
Don't forget that dynamic formatting is possible. This will ensure that your numerics in MS Excel are still numbers.

I know that Martin has put a lot of effort into his examples, and aplaud him for that, such a helpful Guy. But Martin, you're working the data too hard

DEFINE FILE CAR
DUMMY /I1 = 0;
END
TABLE FILE CAR
SUM SALES NOPRINT
    SEATS NOPRINT
    DEALER_COST NOPRINT
	RETAIL_COST NOPRINT
BY DUMMY NOPRINT
SUM COMPUTE FMT/A8 = DECODE COUNTRY ('ENGLAND' 'D8C!L' 'JAPAN' 'D8CM' ELSE 'D8C!E'); NOPRINT
    SEATS/D8C        AS 'Sales'
    DEALER_COST/FMT  AS 'Dealer,Cost'
    RETAIL_COST/FMT  AS 'Retail,Cost'
BY DUMMY NOPRINT
ACROSS COUNTRY AS ''
COMPUTE TOTAL/D8CM = C1;  AS 'Total,Sales'
WHERE COUNTRY EQ 'ENGLAND' OR 'ITALY' OR 'JAPAN';
ON TABLE PCHOLD FORMAT XLSX
ON TABLE SET PAGE NOLEAD
ON TABLE SET STYLE *
  grid=off, $
ENDSTYLE
END

T

quote:

Originally posted by Tony A:
Don't forget that dynamic formatting is possible.

I had issues in the pass using dynamic formatting reason why I tend to stay away from this option.
But you are right Tony, this is also an option.