Solved

Evaluate the first record of a group

Posted on 2010-08-26
16
1,165 Views
Last Modified: 2012-05-10
I have a database that has three columns: a date/time, a person's name and a ticket number.  I have grouped my report on the ticket number.  I need to evaluate only the first row of the group, based on the date/time, and count how many times each person's name shows up.   The database would look like this:

7/2/2010 11:35:25  JACK  12345
7/2/2010 11:40:25  JANE  12345
7/2/2010 11:45:25  JANE  12345

7/2/2010 11:50:25  EMILY  12346
7/2/2010 11:55:25  JACK  12346

7/2/2010 12:00:25  JACK  12347
7/2/2010 12:05:25  JANE  12347

7/2/2010 12:10:25  JANE  12348

and the results would look like this:
JACK  EMILY  JANE
  2          1           1


Any ideas?
0
Comment
Question by:rainfields
  • 5
  • 4
  • 3
  • +2
16 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 33535456
Do you need the "extra" records in the report?

I think you could use the TOP 1 option on the group

If you need all the data, is there a limit on the number of names?
You will need to do this with variables and arrays.

The other way would be to use a subreport that selects only the first record and do the table in the subreport

mlmcc
0
 

Author Comment

by:rainfields
ID: 33537365
I do not need the extra records, just the first one in each group.
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33537597
I would use a database command as the source and use the TOP 1 option to select only the first record in each group

mlmcc
0
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
LVL 2

Expert Comment

by:bischofb_CRNET
ID: 33546139
I've done this a hundred times. Use a command object and a simple SQL statement like this (insert your own field and table names):

SELECT person_name, Count(*) FROM your_table GROUP BY person_name

To print it out across the page, use a label format that goes from left to right. Should work fine.

If you find this helpful, please mark the question answered.  :-)
0
 
LVL 2

Expert Comment

by:bischofb_CRNET
ID: 33546158
Oh - I forgot that you are grouping by ticket number.

SELECT Ticket_number, person_name, Count(*) FROM your_table GROUP BY ticket_number, person_name

0
 

Author Comment

by:rainfields
ID: 33546746
mlmmc: could you give me more information on how you would use the TOP 1 to get rid of all but the first record in each group?
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 33547536
My SQL is rusty so I really can't

It is something like

SELECT TOP 1 name FROM database Group By somefield

mlmcc
0
 
LVL 2

Expert Comment

by:Venkatgvi
ID: 33548557
what database are you using, if it is oracle, just use this query

select ticket_no,name, count(name) over (partition by name) name_count from <table_name>
0
 
LVL 2

Expert Comment

by:bischofb_CRNET
ID: 33562580
Here is how I would do it. First off, you only want to see the first record in each group by date. So you will need to create a formula in the group header b/c that will let you isolate only the first record. Second, you want to do it by date, so you will need to add a sort field based on the date.

Now that you have isolated the first record, and you know it is the most recent one, you need to create a formula to track how many of each item there is. To do this you will need two arrays. The first array tracks the name and the second array tracks the count. Create a formula that loops through the first array and checks if the name is in there or not. If not, then add it to the end of the array. If it is already there, then don't do anything. You will need to store the location of the index in where the name is found in the first array.

The second part of the formula uses the index from above and increases the count in the second array of the proper element. Thus, if the index for Emily is 2 (NumberVar NameIndex), then the second formula would be

CountArray[NameIndex] := CountArray[NameIndex} + 1;

When the report is finished, the first array will be filled with all the names and the second array will be filled with their total counts. Then in the report footer print each element in the arrays to show each name and its count.

-Brian
0
 

Author Comment

by:rainfields
ID: 33570839
Brian, thanks for the input.  That gets me most of the way, but I am still having problems with the formula that would isolate the first record.  Any help would be appreciated.  The TOP 1 suggestion that mlmcc had isn't working for me.
0
 
LVL 2

Accepted Solution

by:
bischofb_CRNET earned 250 total points
ID: 33571453
Ok, I'm going to type this in off the top of my head since I don't have your report here. So there might be a syntax error, but for the most part it should work fine. Replace the dummy field names with yours where appropriate. Put this formula in the group header.

//create the array without losing older values
Global NumberVar Array NameArray;
Redim Preserve NameArray[100];  // I assume you wont' have more than 100 names?
Redim Preserve CountArray[100]
NumberVar SearchIndex;
NumberVar NameIndex;

StringVar NameToFind;
NameToFind := {yourtable.namefield};

SearchIndex := 1;
//Loop until we find a match
While NameIndex == 0
(
    If NameArray[SearchIndex] == NameToFind Then
        //Found the name!
        NameIndex=SearchIndex;
    If NameArray[SearchIndex] == "" Then
    (
        //We are at the end and didn't find it. So add it to the current postiion
        NameArray[SearchIndex] := NameToFind;
        NameIndex := SearchIndex;
    )
    SearchIndex := SearchIndex + 1;
)

//Increase the counter by one
CountArray[NameIndex] := CountArray[NameIndex] + 1;
0
 
LVL 2

Expert Comment

by:bischofb_CRNET
ID: 33571465
I forgot to declare the second array at the top. Here they are again:

Global NumberVar Array NameArray;
Global NumberVar Array CountArray;
0
 
LVL 34

Assisted Solution

by:James0628
James0628 earned 250 total points
ID: 33573873
Brian's basic idea should work.  I would write the formula a bit differently, but I think his will work too, but there are a few syntax and other issues.

//create the array without losing older values
Global NumberVar Array NameArray;
Global NumberVar Array CountArray;
Redim Preserve NameArray[100];  // I assume you wont' have more than 100 names?
Redim Preserve CountArray[100];       // --- Added a ; here
NumberVar SearchIndex;
NumberVar NameIndex;

StringVar NameToFind;
NameToFind := {yourtable.namefield};

SearchIndex := 1;
//Loop until we find a match
While NameIndex = 0       // --- Replaced == with =
(
    If NameArray[SearchIndex] = NameToFind Then       // --- Replaced == with =
        //Found the name!
        NameIndex := SearchIndex;       // --- Replaced = with :=
    If NameArray[SearchIndex] = "" Then       // --- Replaced == with =
    (
        //We are at the end and didn't find it. So add it to the current postiion
        NameArray[SearchIndex] := NameToFind;
        NameIndex := SearchIndex;
    );       // --- Added a ; here
    SearchIndex := SearchIndex + 1;
);       // --- Added a ; here

//Increase the counter by one
CountArray[NameIndex] := CountArray[NameIndex] + 1;

// --- Added "" at the end, so that the formula doesn't produce
// --- any actual output on the report.
// --- That's really just a personal preference
""


 In addition to that, you should create a separate formula like the following and put it in the report header to initially create those variables.  It seems that global variables sometimes don't work properly if they're not declared first in the report header.

Global NumberVar Array NameArray;
Global NumberVar Array CountArray;


 If you could have more than 100 names on the report, you could increase the array sizes accordingly, or the formula could be changed to expand the arrays if necessary.  However, CR arrays are limited to 1000 elements, so if you could have more than 1000 names on a report, that will be a problem.

 James
0
 
LVL 34

Expert Comment

by:James0628
ID: 33573879
Oops.  Forgot to end that second formula (the one that goes in the report header) with "".  Again, that's just a personal preference of mine.  You could also just suppress the formula, or the entire section, if there's nothing in it that you need to see.

 James
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 34308560
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now