Select field as aggregates

[adonis547]

Hello group,

Imagine the following two tables:

accounts:

account# Name CreateDate Salesrep
=====================================================
0101 ABC 01-02-05 CARLOS
0102 ACB 02-02-05 JOHN
0104 CBA 01-12-04 BEN


orders:

account# doc_date
=====================
0101 02-02-05
0104 01-04-05
0101 05-02-05


Ok - I want to create a report, that lists new accounts from a given period. In addition, I'd also like to show when the newly created client was first invoiced (from the "orders" table).

When I create my report, I can easily display the accounts, but when I add the orderdate - it will list the same account for each order in the "orders" table.

fx. with the above stated data, it will list the account '0101' twice. When in fact, I only want to show the first order date.

I can do it in pure SQL, but I do not know how to do it in Crystal. In SQL I would use the min() aggregate function on the doc_date field.

Any help would be much appreciated.

/mich

 

[Madawc]

Group by account. You can do summary totals showing minimum or maximum for a field.

There are several ways to find totals: running totals, summary totals, grand totals and variables. 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. They also accumulate for each line, hence the name. 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. One useful variant are 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.

To get yourself familiar with the idea, try doing a test report with a summary total and a running total for the same field, placed on the detail line. You'll find that the running total increases as each line is printed, whereas the summary total has the final value all along.

Madawc Williams (East Anglia, UK)

 

[adonis547]

Hmm.. Thanks.

I tried out "Running totals" and "Summary total" - both of them can give me the "minimum" date - but none of them addresses the problem at hand: the accounts are listed as many times, as there's a hit in the "orders" table.

Is there no way of changing the SELECT statement that is being issued ? I looked at "Database" -> "Show SQL query" -- however, I can only make changes to FROM, WHERE and ORDER BY syntax.

Basically, I'd like the report to simply show on line pr. account (grouped by salesrep) with the date of the first order in the system.

Thanks.

 

[lbass]

You could try creating a SQL expression {%minorder}:

(select min(AKA.`doc_date`) from Orders AKA where
AKA.`account#` = Orders.`account#`)

Make sure the field names and table name are accurate. Leave "AKA" as is since it represents an alias table name.

Then go to edit selection formula->RECORD and enter:

isnull({Orders.doc_date}) or
{Orders.doc_date} = {%minorder}

-LB

 

[adonis547]

Thanks.

Though meanwhile I solved the problem.

From the "orders" table I pulled out, doc_type and set that up as a group - and then moved contents in the report from the detail section to the nested group.