distinct count not working

[Kakoivini]

Greetings all again,

CR9, Informix procedure, Unix database, different report, same CR newbie

This report is supposed to total up trailers and give that total at the end of the report. These are the two formulas we have in the report counting trailers:

countTrailers:

whileprintingrecords;
shared numbervar trailers:=
if ({sp_yard.(expression):10})="HA" then
If Not IsNull({sp_yard.(expression):2}) and Not (Trim({sp_yard.(expression):2})="")then
trailers := trailers +1
else
trailers;

fldTrailerCount:

if shared numbervar trailers=0 then
(0)
else
shared numbervar trailers;

It is counting all of the duplicate trailers if there is different information associated with that trailer, like several shipments on the same trailer. I need it to count the trailer only once, but still display the trailer and info. All of the report info is in the details section, (amazingly enough there are only 5 groups, but everything is still in the details). Group 5 has the location expression in it and is the main sort for the report, the other groups are empty. My guess is that this repot has been worked over many times. I tried to run a distinctcount in both of the above formulas and it either stayed the same or came back with zero.

Thanks!

 

[Kakoivini]

Figured it out-I put the counttrailer field into group 5 and it is now counting them correctly. Yay!!!!

 

[Madawc]

Glad you solved your problem, but you'd find life a lot easier if you learned to use running totals and summary totals, which do a lot of the work for you.

Madawc Williams (East Anglia, UK)

 

[Kakoivini]

Will a running totals count them only once if they are duplicate?

 

[Madawc]

Options include Distinct Count. If you can, write a test report and explore just what running totals can do. You'll then find you can solve business problems a lot faster.

Right-click on a field and choose Insert to get a choice of Running Total, Summary and Grand Total.. Or else use the Field Explorer, the icon that is a grid-like box.
It is also possible to get get totals using a Formula Field, which can contain a Variable or a Directly Calculated Total.
Running totals allow you to do clever things with grouping and formulas. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.
Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say.
Grand totals are much like summary totals, but for the whole report rather than groups. Note that summary totals include an option to have a grand total calculated on the same basis.
Variables are user-defined fields. I don't use them, except for shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.
Directly Calculated Totals within a Formula Field can be coded directly, with commands like Sum ({ADV01.Advance}, {ADV01.AccType}). The same result can be achieved by picking up an existing Variable, and will keep the code even if the Variable itself is later deleted. Formula fields can also include Running Totals and other Formula Fields, with some limits depending on when the values are calculated.

Madawc Williams (East Anglia, UK)

 

[Kakoivini]

I tried the running totals-those are great, but I am having a problem. Unfortunately the way the database is written and records data there can be duplicate quantities-the only way to distinguish them is in the report. Example- we have an order for 43 pieces, 3 pallets are pulled, qty 5, 29 & 9), however because 3 pallets were pulled the order qty is in the database 3 times-once per pallet. So a simple sum won't work, I need to do a sum of exp 7 like this: (7 is an order qty, 5 is the lot number and 12 is the pallet tag number, 5 & 12 are the only unique entries for each pallet)
Were I to write this in SQL I would use this:
SUM ({sp_hold.(expression):7}) WHERE
{sp_hold.(expression):5} <> PREVIOUS ({sp_hold.(expression):5})
AND {sp_hold.(expression):12} <> PREVIOUS ({sp_hold.(expression):12})

Unfortunately as far as I can tell CR has no WHERE syntax..or does it?

 

[Madawc]

Running counts include tests for a formula. This can include testing a formula field for true or false.

Madawc Williams (East Anglia, UK)