Solved

Status Report by using CR.

Posted on 2007-04-04
6
154 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 100

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 100

Accepted Solution

by:
mlmcc earned 500 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
Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

 
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 100

Expert Comment

by:mlmcc
ID: 18855174
Glad i could help

mlmcc
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
join tables 4 51
vb.net checkbox 7 41
Amazon Redshift 2 28
Variable Event ? 3 23
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
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…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

863 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

24 Experts available now in Live!

Get 1:1 Help Now