I do have a table like this

 

Department	Customer ID	Purchase ID	City
Laptops	A123	101	Los Angeles
Laptops	A123	101	Los Angeles
Tablets	A123	101	Los Angeles
Laptops	B123	102	Los Angeles
Laptops	A123	102	Dallas
Smart Phones	A123	101	Los Angeles
Smart Phones	B123	105	New York
Laptops	A123	107	New York
Tablets	A123	108	Dallas

 

I need a summary like this way

 

	Los Angeles	New York	Dallas	Totals
Laptops	2	1	1	4
Smart Phones	1	1	0	2
Tablets	1	0	1	1
Report Footer	4	2	2	8

 

Let see the scenerio

 

1) For Losangeles A customer named A123 bought two laptops with same Purchase ID and the same customer bought laptop in NY & Dallas with different PID's

2) For Los Angeles B123 bought another laptop with purchase ID 102

 

I grouped it by department and I am using Running totals to count the number at group footer

 

Field to Summarize -- > Department

Type of Summart --> Distinct Count

Evaluate : choose formula and I put city = "Los Angeles "

Reset : On change of group : Department

 

I am getting the correct number at group level but to get the totals at the report footer I have no clue how to write a formula/running totals for it.

 

To be precise all I need is just to sum all numbers at the group level to get a total at report footer.

 

Please give me suggestions on it,any help will be appreciated greatly.

 

 

 

HTH

 

I am getting something like this :

	Los Angeles	New York	Dallas	Totals
Laptops	2	1	1	4
Smart Phones	1	1	0	2
Tablets	1	0	1	2
Report Footer	3	2	2	7

 

but I need like the following :

 

	Los Angeles	New York	Dallas	Totals
Laptops	2	1	1	4
Smart Phones	1	1	0	2
Tablets	1	0	1	2
Report Footer	2	2	2	6

 

Because I am using distinct count I am not getting exact totals in the report footer

 

Could you please look into this one more time ?

 

The rule is to do a distinct count on purchase ID by department wise and

 

For report footer I need the sum of all the values at the group level not by distinct count of all purchase ID's

shared numbervar LA;

local numbervar dLA:=distinctcount({table.field}, {group});

LA := LA + dLA;

dLA

 

I am just not sure how you will get the LA specific entities, but this will display the distinct count in the department footer and in the report footer you would have something like:

shared numbervar LA;

LA

 

and this will display the sum of the distinct counts in the report footer.

Your formula is working really well in getting the totals by department however I just forgot to include a condition at the department level I need to be break down by each city

 

 

Does this work or will that be a separate condition

 

if you are displaying the count, by department and city, you probably just need to have a formula like:

most of the time when you do a running total it's a total for a group, but here you want to have individual running totals by item, which is a bit more complex.

 

I have 2 possible solutions, neither of which is completely simple:

1) create a delimited string listing the item and its total which is maintained by adding/modifying the string

2) creating 2 arrays, 1 to hold the item and the second to holds it total

 

you may need to reset these methods as well, but from the snippet above, you wouldn't.

 

the delimited string would be something like |itemID^currentTotal|item^currentTotal|...

 

so you would use instr(totalString, "|" + {table.itemID} + "^") if this is > 0 then you would want to retrieve from initial pipe (|) to the next pipe and string current value, update it, display it, and then re-encode the value and insert it back into the string.  If the instr() = 0 , then you would just encode and add to the string.

 

if you are using arrays, you would initially dim 2 arrays to a size that is large enough to hold the items, say 100 or so to start.  (we can dynamically resize the array, but initially I would try to stay away from that).  Then you would search one of the arrays for the itemID, and if you find it, you can look in the other array at the same index for the value, increment it, display it, and just update the array.  If you don't find the itemID, you update the 'last' array element to hold the new itemID and update the count in the other array for the same index.

 

Neither is simple, but neither is it impossible.

 

HTH

 

I used the formula in my report