Solved

Evaluate the first record of a group

Posted on 2010-08-26
16
1,160 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
Comment Utility
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
Comment Utility
I do not need the extra records, just the first one in each group.
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 2

Expert Comment

by:bischofb_CRNET
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
My SQL is rusty so I really can't

It is something like

SELECT TOP 1 name FROM database Group By somefield

mlmcc
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:Venkatgvi
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

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…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

728 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

14 Experts available now in Live!

Get 1:1 Help Now