Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Evaluate the first record of a group

Posted on 2010-08-26
16
1,169 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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Crystal Reports  Formula to Command 4 86
New and Previous Value in Crystal Report 8 61
Crystal Reports Server 2016 Installation, setup and use 5 312
Crystal Reports 13 5 71
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. …
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …

791 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