Status Report by using CR.

Hi,

May I know how can I get this report by using crystal Report. (VB + MySQL Database)

Data:
No.               File                            Status                Completed Date
========================================================
1                  File1                          Task A                 2-Jan-2007
2                  File1                          Task B                 3-Jan-2007
3                  File1                          Task C                 NULL
1                  File2                          Task A                1-Jan-2007
2                  File2                          Task D                NULL
1                  File3                          Task A                NULL

The Report I need
===============
No.               File                            Last Completed Task                Completed Date              Pending Task
======================================================================================
1                  File1                          Task B                                               3-Jan-2007                        Task C
1                  File2                          Task A                                               1-Jan-2007                        Task D                
1                  File3                                                                                         -                                    Task A                

I guess I need to play around with grouping...but i didn't have a clear image on how to solve this. Please advice.
LVL 1
cybehAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mlmccCommented:
The first 4 columns are easy

Create a group on the FILE field.
Add a sort on the date - Descending
You should be able to put the FIle, Status, and Completed date in the grouip header to get that part.

To get the pending task will be a little more difficult.

How are you selecting the database/table?

What version of Crystal?

mlmcc
0
cybehAuthor Commented:
I am using CR XI + SQL string @ the data expert.

Actually in our database, the incomplete task will have a completion date of "1601-01-01", maybe we can make use of that?

0
mlmccCommented:
Two ideas

First to join the table to itself

SELECT  T1.File, T1.Status, T1,CompletedDate, T2,Status, T2.CompletedDate
FROM YourTable T1  INNER JOIN YourTable T2
ON  T1.File = T2.File
WHERE T1.CompletedDate > Date(1601,01,01) AND T2.CompletedDate = Date(1601,01,01)

The other is to use variables and functions
Set the report up as I stated above.
The group header gets no fields but add a formula.
Name - SaveGroupInfo
Formula
WhilePrintingRecords;
Global StringVar strFile;
Global StringVar strStatus;
Global DateVar dtComplete;
strFile := {FileField};
stsStatus := {StatusField};
dtComplete := {DateCompletedField};

In the detail section
Name - GetNextTask
Formula
WhilePrintingRecords;
Global StringVar strNextTask;
If {DateCompletedField} = Date(1601,01,01) then
    strNextTask := {StatusField};

In the group footer add formulas to display the variables
Name - DispFile
Formula
WhilePrintingRecords;
Global StringVar strFile;
strFile

Name - DispStatus
Formula
WhilePrintingRecords;
Global StringVar strStatus;
strStatus

Name - DispDate
Formula
WhilePrintingRecords;
Global DateVar dtComplete;
dtComplete

Name - DispNextTask
Formula
WhilePrintingRecords;
Global StringVar strNextTask;
strNextTask

mlmcc
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

cybehAuthor Commented:
Thanks for the reply. But this will not show the report is all task are completed.

I think i didn't give a good test case in my previous post
Data:
No.               File                            Status                Completed Date
========================================================
1                  File1                          Task A                 2-Jan-2007
2                  File1                          Task B                 3-Jan-2007
3                  File1                          Task C                 NULL
1                  File2                          Task A                1-Jan-2007
1                  File3                          Task A                NULL


The Report I need
===============
File                            Last Completed Task                Completed Date              Pending Task
=========================================================================
File1                          Task B                                               3-Jan-2007                        Task C
File2                          Task A                                               1-Jan-2007                        -                
File3                           -                                                              -                                    Task A                

Please advice. Thanks !!!
0
cybehAuthor Commented:
Manage to do it by using the 2nd method. Thanks !!!
0
mlmccCommented:
Glad i could help

mlmcc
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.