Print Page | Close Window

Report by group formula

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14969
Printed Date: 07 May 2024 at 11:03am


Topic: Report by group formula
Posted By: Zuzanna
Subject: Report by group formula
Date 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


Replies:
Posted By: lockwelle
Date 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

Posted By: Zuzanna
Date 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.

Posted By: lockwelle
Date 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

Posted By: Zuzanna
Date 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 mailto:%7b@ABC_CNT - {@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.

Posted By: kostya1122
Date Posted: 21 Nov 2011 at 6:44am
try sum not count

Posted By: Memoli28
Date 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.
 
 

Posted By: Zuzanna
Date 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.
  

Posted By: Memoli28
Date 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.
 
 


Print Page | Close Window