Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: How to add formulas in SQL Expression Field Post Reply Post New Topic
Author Message
SIddhu
Newbie
Newbie
Avatar

Joined: 27 Feb 2008
Location: India
Online Status: Offline
Posts: 22
Quote SIddhu Replybullet Topic: How to add formulas in SQL Expression Field
     Posted: 07 May 2008 at 2:46am
Hi,
I have a doubt in adding a formula into the Record selection field.
 
The task i got is to display the datas whose
First(due_date) is >= 10/01/2006
 
The first(due_date) returns the Minimum(due_date) since the records are ordered in ascending order.
What i tried is, i wrote a formula to get the Minimum(Due_date) and tried to use it in the Record selection as
 
@Due_Date >= Date (2006,01,10)
 
But this cannot be done. so can anyone help me out with this.
 
Thank you ,
Siddhu
IP IP Logged
JRAJCR
Newbie
Newbie
Avatar

Joined: 07 May 2008
Location: India
Online Status: Offline
Posts: 4
Quote JRAJCR Replybullet Posted: 07 May 2008 at 4:21am
please provide ur inputs to my posts
Regards,
Jayaraj
IP IP Logged
SIddhu
Newbie
Newbie
Avatar

Joined: 27 Feb 2008
Location: India
Online Status: Offline
Posts: 22
Quote SIddhu Replybullet Posted: 07 May 2008 at 5:37am
I dono about the specific data for this. But its like
 
user_code    due_date        Activity     Lead_status
AKA10SD       02/12/06            BMP          QUAL
SDI34SD       12/03/06            CST           REPL
SDF54DG       23/04/07           SDF           QUAL
ER232DS       13/05/08           DFD           SDFG
DFD44FF        03/06/07          BMP           REPL
DS343SS       15/06/07           DFD           DJSM
A3K40SD       30/12/05           BMP           NONQ
 
These are the few datas required, From this i have to order the data by due date in ascending..........
From that ordered datas, i have to take the First due_date and then display only the datas whose due_date is greater than 10/01/2006
 
These details are enuf or you need some more on this.... ?!?!


Edited by SIddhu - 07 May 2008 at 5:39am
Thanks,
Siddhu.
IP IP Logged
Lugh
Senior Member
Senior Member
Avatar

Joined: 14 Nov 2007
Online Status: Offline
Posts: 377
Quote Lugh Replybullet Posted: 08 May 2008 at 8:59am
First, you cannot use a formula in the Selection Criteria.  The Criteria get evaluated before any formulas do.

Second, um, I'm a little confused as to what you are attempting to achieve.  I mean, if the first due_date is 10/01/2006, then aren't all the other due_dates greater than 10/01/2006 pretty much by definition?

If you want to do complicated self-referential selection criteria like this, you pretty much need to do it in SQL.  Use the SQL Command object to build your data source (where you normally select tables to add to the report, just select Add Command instead).  Here, you can do things like create sub-queries and self-joins to achieve the effect that I think you want.

You may also benefit from using parameters to specify the dates of your report.  It would require the user knowing what the first date is, though.


IP IP Logged
SIddhu
Newbie
Newbie
Avatar

Joined: 27 Feb 2008
Location: India
Online Status: Offline
Posts: 22
Quote SIddhu Replybullet Posted: 26 May 2008 at 11:07pm
okey... !!
I can achieve my task as SQL scripts, but not able to do the same using crystal reports..
I will explain the task clearly again:
 
For one contact there are many Due_dates... from which i have to select the oldest due date and display for each contact in the crystal reports...  
EX:   contact name  :James, Mathew
        Due_date        : 10/06/2006
                                  16/05/2005
                                  30/07/2006 and so on.....
From this i hv to display the contact name and the oldest due_date in the report page like.
 
           CONTACT NAME            OLDEST DUE DATE
 
            James, Mathew                  16/05/2005
 
 
Like this i have to display the oldest due_dates for all the contacts......
 
Hope this time atleast am clear with the explanation.... 
Thanks,
Siddhu.
IP IP Logged
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