First Record Count/sum

[Steve95]

Hi All

I am compiling a report which is grouped by (datatype time)time by the hour. Each hour can have many unique people logging on, but a lenghth of stay can drop into other hours. All I am interested in is the hour of their unique login ie. first record of the login, indicating the hour they logged in.

I have used a formula
{lDate} = MINIMUM({lDate},{uniquelogin})
which bring back a boolean and where true placed another formula saying put 1 if true and 0 if not.

What I want to be able to do sum per hour the amount of uniquelogins for that hour.The second formula I have place saying

IF {First Record} = TRUE THEN 1 ELSE 0

comes back as data type number but Iam unable to sum on this.

Any one any ideas....

Many Thanks in advance

 

[Dimst]

So far so good,

to make a summary you could use the following formula:
Whileprintingrecords;
if {first record} = true then
numbervar counter := counter + 1;
counter;

Place this formula in the section and make also a reset formula, because most likely you want to reset this at the end of the group. If not, you do not need to use a reset formula.

whileprintingrecords;
counter := 0;

When not using a reset formula, a new formula for counter (where you use then numbervar again to fetch) can be placed at the report footer. All records where the first one is true will be count and at the end of the report you will have a grand total.

When using a reset formula, the new formula for fetching the counter can be placed in the group footer. Per group the amount of first records then will be showed.

My english is not great, so i hope it is or was of help to you.

 

[Steve95]

Hi Dimst

thanks for the above, but it is quite not what Iam looking for. It might be I have not come across correclty as I have only discovered some things now.

I have 2 groups in my report first one is by time and the second one is by uniquelogin. Everytime a user logs in they are allocated a uniquelogin. So a UL can fall into more than hour group but I only want to report on their first record of entry. Also i need to be able to sum the number iof UL per hour.

If i have missed anything please do ask.

Many Thanks

 

[lbass]

I think you could create a command like:

select `table`.`uniquelogin`, min(`table`.`ldate`) as mintime
from `table`
group by `table`.`uniquelogin`

Then link the command to your main table on the uniquelogin and link mintime to the ldate field, and choose "Enforce both" for the join, so that the only ldate that appears on the report will be the first per uniquelogin.

Then you should be able to simply insert a distinctcount on the uniquelogin field at the time group level.

-LB

 

[Dimst]

The option of command line is not familiar to me, because as far as i know this is not an option within crystal reports 8.5

 

[lbass]

Yes, it isn't available in CR 8.5, but the poster uses XI, where commands are available (database expert->your datasource->add command). In 8.5, you would create a SQL expression to get the minimum date instead and then you would set the date equal to the expression in the record selection formula.

-LB