Im trying to do something which I thought would be relevently simple but cant see the wood for the trees & havent been able to find a suitable answer so far,

Im basically trying to do a report with several over fields & a row-total (or across total) but I dont want to row-total all my fields or at least not display them.

Using the car file as an example.

TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES OVER
LENGTH
BY COUNTRY

ACROSS CAR
ON TABLE ROW-TOTAL
END

This would show row totals for all the fields within the SUM statement, but I would only want to display the row-total for DEALER_COST.

I have got round displaying the information by formatting the text to white for those totals I do not wish to see but they still print out. Therefore, i would like if possible to not display them at all.

Part of me hopes that I am just being a numpty as I can live with the shame

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

Well, I don't know what a numpty is but I surely wouldn't want to be one either! And your question is legitimate. Here is a quick take though not perfect:

SET ASNAMES=ON
DEFINE FILE CAR
TOTCAR/A16='ZTOTAL';
TOTRCOST/D7=0;
TOTSALES/I6=0;
TOTLEN/D5=0;
END
TABLE FILE CAR
SUM   DEALER_COST
      TOTRCOST AS RETAIL_COST
      TOTSALES AS SALES
      TOTLEN AS LENGTH
BY COUNTRY
BY TOTCAR AS CAR
ON TABLE HOLD AS TOTCAR FORMAT ALPHA
END
TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST OVER
SALES OVER
LENGTH 
BY COUNTRY
ACROSS CAR
-*ON TABLE ROW-TOTAL
MORE 
FILE TOTCAR
END

Now in the first TABLE, I made TOTCAR ZTOTAL so that it would sort at the end because I was being lazy and didn't want to code the Dialogue Manager -REPEAT loop necessary to do the ACROSS COLUMNS syntax to get them to sort in the correct order. But in my defense, that would take the fun out of it for you. And I think there are a number of posts related to that in the Forum. Search on ACROSS COLUMNS and see what you find. If you need a further explanation of what I did, let me know and I will supply.

This had me stumped and Ginny's solution is pretty good!

quote:

numpty

99 definitions (some of them cannot be posted as the formum filters will **** them)

http://www.urbandictionary.com/define.php?term=numpty

Thanks Jinny. My cup of morning coffee on across-total.

quote:

MORE
FILE TOTCAR

Hua

This is not very elegant, but offers a different approach. Converting amounts you don't want totalled into alpha format prevents them from being ROW-TOTALed.

TABLE FILE CAR
  SUM DEALER_COST
      RETAIL_COST
      SALES
      LENGTH
   BY COUNTRY
   BY CAR
   ON TABLE HOLD
END
-*
DEFINE FILE HOLD
 RCOSTA/A10  = FTOA(RETAIL_COST,'(D7)','A10');
 SALESD/D6   = SALES ;
 SALESA/A10  = FTOA(SALESD,'(D6)','A10');
 LENGTHA/A10 = FTOA(LENGTH,'(D5)','A10');
END
-*
TABLE FILE HOLD
   SUM DEALER_COST
  OVER RCOSTA  AS 'RETAIL_COST'
  OVER SALESA  AS 'SALES'
  OVER LENGTHA AS 'LENGTH'
    BY COUNTRY AS ''
ACROSS CAR     AS ''
    ON TABLE ROW-TOTAL
    ON TABLE SET STYLE *
       TYPE=DATA, JUSTIFY=RIGHT, $
    ENDSTYLE
END

This message has been edited. Last edited by: Dan Satchell,

Thanks for the replies folks!

As soon as I get some urgent year end stuff done I will take a look .

numpty in my book is just being a bit simple/stupid But i do love description no2 on the urban dictionary link (been so hectic in the office as of late its given me a nice 'out loud' laugh which had my colleagues asking what was so funny

Thanks all. This forum is a REAL wealth of knowledge!!

As feeback, Ginnys suggestion gave me what I wanted but gave me zero where I didnt want to display the data & Dans gave me what I wanted but I wasnt to keen on subjecting myself to inserting all the FTOA defines (theres a lot of data on these reports.)

I had tried doing a simple version of the more file before asking the question but Ginny put me on the right tracks. With the addition of S after the formats in the 1st sub request I got what I wanted. waahaay!

Example of finalised code below if it helps anyone else:


SET ASNAMES=ON

DEFINE FILE CAR
TOTCAR/A16='ZTOTAL';
TOTRCOST/D7=;
TOTSALES/I6=0;
TOTLEN/D5=0;
END
TABLE FILE CAR
SUM DEALER_COST
TOTRCOST AS RETAIL_COST
TOTSALES AS SALES
TOTLEN AS LENGTH
BY COUNTRY
BY TOTCAR AS CAR
ON TABLE HOLD AS TOTCAR FORMAT ALPHA
END

TABLE FILE CAR
SUM DEALER_COST OVER
RETAIL_COST/D7S OVER
SALES/I6S OVER
LENGTH/D5S
BY COUNTRY
ACROSS CAR
MORE
FILE TOTCAR
END