Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: manipulation issues? Post Reply Post New Topic
Author Message
bobsn
Newbie
Newbie


Joined: 19 Mar 2007
Online Status: Offline
Posts: 27
Quote bobsn Replybullet Topic: manipulation issues?
    Posted: 17 Jul 2007 at 12:36pm
Hello 
     I have a report based on a temp table.
The table consists of
cl num, cont date, code.
 
eg: 51c23  11/21/2006  p1
      51c23  11/22/2006  p2
      51c23   11/21/2006 p3
      51c24    10/1/2006  p1
      51c12   1/1/2006     p2
      51c12    1/2/2006    p3
 
I want it show up in the report as
 
cl num  p1 date            p2date           p3 date
51c23   11/21/2006     11/22/2006    11/21/2006
51c24   10/1/2006       none               none
51c12    none               1/1/2006        1/2/2006
 
any ideas??  I tried to create 3 formula p1date, p2date, p3date which have cont date when the code  = p1, p2,  p3 respectively.
 
but they still appear on 3 different lines.
 
thanks
IP IP Logged
jkwrpc
Senior Member
Senior Member


Joined: 19 Jun 2007
Location: United States
Online Status: Offline
Posts: 432
Quote jkwrpc Replybullet Posted: 17 Jul 2007 at 2:05pm
You could try grouping on the clnumber then create 3 formulas to populate the date field.
 
The formulas would look something like this
 
If p1 then {cont datefield}
 
The next formula would look something like this..
If p2 then {cont datefield}
 
and so forth
 
the design would look something like this
 
Page Header
              cl num  p1 Date     p2 Date     p3 Date
Group 1 clnum  p1 formula p2 formula p3 formula
 
There are different design or layout variations that may also work.
 
Hope this helps
 
Regards,
 
John W.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 17 Jul 2007 at 2:06pm

You might be able to do this using running totals (which can be max or min values instead of "totals"), or you may have to use variables.

To use Running Totals:

- Group on Cl_num.
- Create a new running total field for each date (p1, p2, p3).
- Field to Summarize:  your date field.
- Type of Summary: maximum.
- Evaluate based on a formula (change for each "p" number): {table.ptype} = 'p1'
- Reset on Change Of Group: cl_num.
 
Put your data in the cl_num group footer instead of on the details line.
 
-Dell
(I haven't tried this but it should work....)


Edited by hilfy - 17 Jul 2007 at 2:07pm
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
bobsn
Newbie
Newbie


Joined: 19 Mar 2007
Online Status: Offline
Posts: 27
Quote bobsn Replybullet Posted: 18 Jul 2007 at 2:19pm
I did the same and it worked out to an extent. When I do a count of the individual date fields in the group footer, i get weird results.  Like in the example I gave
 
PH   cl num  p1 date            p2date           p3 date
GF2 51c23   11/21/2006     11/22/2006    11/21/2006
       51c24   10/1/2006       none               none
       51c12    none               1/1/2006        1/2/2006
GF1                 2                     2                     2
 
Instead i get          3      3      3
 
I need to count the number of occurrences of dates in each colummn.
 
is there a way to do it?  counting the number of occurrences in the group footer section...
 
I tried the running total method too and I face the same problem. It wont allow me to do a distinct count or count on the fields in GF2
 
thanks
 
 
Originally posted by jkwrpc

You could try grouping on the clnumber then create 3 formulas to populate the date field.
 
The formulas would look something like this
 
If p1 then {cont datefield}
 
The next formula would look something like this..
If p2 then {cont datefield}
 
and so forth
 
the design would look something like this
 
Page Header
              cl num  p1 Date     p2 Date     p3 Date
Group 1 clnum  p1 formula p2 formula p3 formula
 
There are different design or layout variations that may also work.
 
Hope this helps
 
Regards,
 
John W.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 18 Jul 2007 at 2:26pm
Create a formula for each date that looks something like this:
 
if {table.code} = 'P1' and not IsNull({table.cont_date}) then 1 else 0
 
Do a SUM of each formula at the Group1 level to get the count of dates.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
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.016 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.