Solved

Status Report by using CR.

Posted on 2007-04-04
6
160 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
[X]
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
  • 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 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

Question has a verified solution.

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

If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Come and listen to Percona CEO Peter Zaitsev discuss what’s new in Percona open source software, including Percona Server for MySQL (https://www.percona.com/software/mysql-database/percona-server) and MongoDB (https://www.percona.com/software/mongo-…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

724 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