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

Status Report by using CR.

Posted on 2007-04-04
6
157 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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
Introduction Since I wrote the original article about Handling Date and Time in PHP and MySQL (http://www.experts-exchange.com/articles/201/Handling-Date-and-Time-in-PHP-and-MySQL.html) several years ago, it seemed like now was a good time to updat…
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

839 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