As an example there are two department stores in the given city. The are some customers that shop in both stores. So:

TABLE FILE STORES
SUM ONE AS 'CUST PER CITY'
BY CITY
SUM ONE AS 'CUST PER STORE'
BY CITY
BY STORE
WHERE CITY EQ 'MYCITY'

Results are:

CITY STORE CUST PER CITY CUST PER STORE
------ ----- ------------- --------------
MYCITY STORE1 5 2
STORE2 0 4

TOTAL MYCITY 5 6

Is there anyway I can total the 'CUST PER CITY' in to the 'CUST PER STORE' column as follows:


CITY STORE CUST PER CITY CUST PER STORE
------ ----- ------------- --------------
MYCITY STORE1 5 2
STORE2 0 4

TOTAL MYCITY 5 5

I tried it with subfoot but in my real report it seems to be very difficult to do.

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

I don't see a difference between the two examples of what you have and what you want?

I need to show the true number of customers per store - which is 2 + 4 =6. But in the total line in this column should show a unique number of customers which is 5. (1 customer shops in both stores).

You have to use the DST prefix and in fact build two separate reports. One to totalize number of clients per shop and one per city. So it is not a sum.

I can do it using separate columns. Unfortunately the requirements are to use the same column.

Create two hold files first and combine that to one report
This is asked many times and there are several examples available.
Else look for mcguyver it is an elegant solution