?
Solved

Status Report by using CR.

Posted on 2007-04-04
6
Medium Priority
?
164 Views
Last Modified: 2010-05-18
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.
0
Comment
Question by:cybeh
  • 3
  • 3
6 Comments
 
LVL 101

Expert Comment

by:mlmcc
ID: 18850516
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
 
LVL 1

Author Comment

by:cybeh
ID: 18850546
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
 
LVL 101

Accepted Solution

by:
mlmcc earned 2000 total points
ID: 18851214
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 1

Author Comment

by:cybeh
ID: 18851452
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
 
LVL 1

Author Comment

by:cybeh
ID: 18854703
Manage to do it by using the 2nd method. Thanks !!!
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 18855174
Glad i could help

mlmcc
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month13 days, 13 hours left to enroll

757 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