Report Design
 Crystal Reports Forum : Crystal Reports 9, X, XI, 2008, 2011 : Report Design
Message Icon Topic: Report by group formula Post Reply Post New Topic
Author Message
Zuzanna
Groupie
Groupie


Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
Quote Zuzanna Replybullet Topic: Report by group formula
    Posted: 17 Nov 2011 at 12:47pm

Hi, 

I hope someone can help. I am creating a crystal report and the data coming in looks as follows

 

Id              Course                  Course Completed

 

100           ABC                         09/25/2011

100           DEF                          09/16/2011

101           ABC                         10/01/2011

103           DEF                          09/14/2011

105 

I created a crystal report to group on ID. I need the report to look as follows

 

Id            Course ABC                                     Course DEF

 

100         ABC completed on 09/25/2011   DEF completed on 09/16/2011

101        ABC completed on 10/01/2011    DEF Not completed

103        ABC Not completed                         DEF completed on 09/14/2011

105        ABC Not  completed                        DEF Not completed

 

How do I set up the formula to do this ?

 

Thanks in advance

IP IP Logged
lockwelle
Senior Member
Senior Member


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4183
Quote lockwelle Replybullet Posted: 18 Nov 2011 at 9:28am
create a formula like:
{table.id} + {table.courseid} + {table.completeDate}
 
in the group (change group) instead of selecting a field in a table, select the formula you just created.
 
All done.
 
HTH
IP IP Logged
Zuzanna
Groupie
Groupie


Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
Quote Zuzanna Replybullet Posted: 18 Nov 2011 at 10:46am
If a person completed both courses, 2 rows are read. I need to display just 1 line per person showing if they completed or not.
 
ID         Course ABC                          Course DEF
 
100        completed on 09/12/2011     Completed on 10/12/2011
 
If a person completed only 1 course, I want to display
 
ID    Course ABC                        Course DEF
100 completed on 09/12/2011     Not completed
 
I tried to set up RT count of ABC and DEF courses and then a formula to check the counts and format the message, but it doesn't work.
 
If someone completed both courses, it shows ABC count as 1, but DEF count as nothing.


Edited by Zuzanna - 19 Nov 2011 at 8:05am
IP IP Logged
lockwelle
Senior Member
Senior Member


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4183
Quote lockwelle Replybullet Posted: 21 Nov 2011 at 4:59am
what you want to do is display in the summary, suppress the details.
in the details you will want to put a formula that collects the data that you are after.
 
if you want an arbritary set of classes to display, you are going to need to come up with some system to gather/display them.  If the classes are fixed, you can use shared variables to populate what you need.
 
If I was doing this, and it was for an arbitary set of classes, I would use 2 arrays and populate them at the same time: one for the class, one for the complete date.
 
HTH
IP IP Logged
Zuzanna
Groupie
Groupie


Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
Quote Zuzanna Replybullet Posted: 21 Nov 2011 at 6:02am
Hi,
 
can you provide a simple example of the formula. I am fairly new to crystal and have not used arrays in crystal.
I added 2 formulas, ABC count and DEF count as follows
 
If {table.Course_Id} = 'ABC'
Then 1
Else 0
If {table.Course_Id} = 'DEF'
Then 1
Else 0
 
Then I set up RT to do the following
 
If {@ABC_CNT} = 0 Then
'ABC not completed' Else
'Completed on ' + CStr ({Table.DT_ISSUED})
 
I placed the RT on the Group, but I wasn't getting the correct results.
IP IP Logged
kostya1122
Senior Member
Senior Member
Avatar

Joined: 13 Jun 2011
Online Status: Offline
Posts: 468
Quote kostya1122 Replybullet Posted: 21 Nov 2011 at 6:44am
try sum not count
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 22 Nov 2011 at 1:00am
Hi,
 
maybe you can use a crosstab.
put a crosstab in you report.
 
row=ID
column=course
summarized fields= mode of the date field.
 
 
IP IP Logged
Zuzanna
Groupie
Groupie


Joined: 10 Sep 2010
Location: Canada
Online Status: Offline
Posts: 56
Quote Zuzanna Replybullet Posted: 25 Nov 2011 at 9:04am

Hi,

I changed my query to give me rows as follows.
 

Id              Course                  Course Completed

 

100           ABC                          09/25/2011

100           DEF                          11/22/2011

101           ABC                         10/01/2011
101           DEF                        
103           ABC
103           DEF                          09/14/2011
 
So I always get 2 rows per ID. I want to display my report as follows
 
Id           ABC Training                         DEF training
100        Completed on 09/25/2011    Completed on 11/22/2011
101        Completed on 10/01/2011    Not completed
 
I group on Id. Problem is the report displays as follows
 
Id           ABC Training                         DEF training
100        Completed on 09/25/2011    Completed on
101        Completed on 10/01/2011    Not completed
 
How do I save the date 11/22/2011, so it shows in the group instead of showing as blank.
 
Thanks in advance.
  
IP IP Logged
Memoli28
Groupie
Groupie
Avatar

Joined: 10 Apr 2009
Online Status: Offline
Posts: 58
Quote Memoli28 Replybullet Posted: 28 Nov 2011 at 7:27am
create 2 formulas:
 
1 for course ABC : if field Course=ABC then field course completed else ''
1 for course DEF : if field Course=DEF then field course completed else ''
 
and put a summary in your group header :
maximum of formula ABC  and one for  maximum of formula DEF.
 
 
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.