| Author |
Message |
poongundran
Newbie
Joined: 02 Mar 2008
Online Status: Offline
Posts: 9
|
  
Topic: Earlier Record Field
Posted: 17 Mar 2008 at 12:43am |
|
Hi....how r u all ?I having problem with my reports.My record selection is based on the "Date"
Date ChildrenName Current Weight Previous Weight
15-02-2008
Hein 15.8 15.0 -->A
Adam 14.8 15.2 -->B
If i m selecting the Date 15-02-2008 ,all the Children's who weighed on 15-02-2008 should be displayed
so i grouped it based on Date.Here the "previous weight" denotes the earlier current weight of Hein and Adam ...
Date ChildrenName Current Weight Previous Weight
12-12-2007
Hein 15.0 -->A 13
Adam 15.2 -->B 14
How to make it using formula fields since i m new to Crystal Report XI,Please help me out....Its urgent
Edited by poongundran - 17 Mar 2008 at 8:47pm
|
 IP Logged |
|
|
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 17 Mar 2008 at 11:21am |
Is the weight information all in the same table but in different records?
-Dell
|
|
|
| IP Logged |
|
poongundran
Newbie
Joined: 02 Mar 2008
Online Status: Offline
Posts: 9
|
Posted: 17 Mar 2008 at 8:49pm |
|
Yes....All the weight information are in the same table but in different records
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 18 Mar 2008 at 6:25am |
Ok, here's what you need to do. You need to add another instance of the weight table to the report - when you add a table more than once, Crystal will ask if you're sure you want to add it and then will create an "alias" for it so that it has a different name than the original instance of the table. Then do the following:
- Link from the original weight table, which I'll call Weight, to the new aliased copy, which I'll call Weight_1, on whatever the key field is that identifies who the weight is for.
-Keep your select filter on date from the Weight table.
-Add a select filter on Weight.Date > Weight_1.Date
-Group by Date and then by Child Name.
-Add a descending sort on the date field in Weight_1
-Put your data in the Child Name group header section instead of in the details section.
This should give you the child's current weight as well as the weight that from the most recent prior date.
-Dell
|
|
|
| IP Logged |
|
poongundran
Newbie
Joined: 02 Mar 2008
Online Status: Offline
Posts: 9
|
Posted: 21 Mar 2008 at 12:39am |
|
Hi HILFY
Thanks for ur help.Your posting was greatly helpful,i did what u said and worked well. I m facing another problem in the same scenario.
Table Name:Weight
ChildName WeighedDate Weight
Adam 12/01/2008 00:00:00 15.40
Kein 25/02/2008 00:00:00 16.30
Adam 25/02/2008 00:00:00 15.80
Adam 21/03/2008 00:00:00 15.70
Kein 21/03/2008 00:00:00 16.00
Peter 21/03/2008 00:00:00 14.30
I made the copy of the same table
* Also linked the Weight.ChildName -->Weight_1.ChildName
* I made two groups from Weight.WeighedDate and Weight.ChildName
* Used the select expert Weight.WeighedDate>Weight_1.WeighedDate
and sort the field WeighedDate (Weight_1)in descending order and
also record selection made using Weight.WeighedDate.
If WeighedDate =12-01-2008
-------- Nothing is displayed-------
Needed Output
12-01-2008
ChildName CurrentWeight PreviousWeight
Adam 15.40 0
(Since this is first record of the child-->Adam ,Previous Weight =0)
If Weighed Date = 25-02- 2008
Obtained :
25-02-2008
Adam 15.80 15.40
Desired Output :
25-02-2008
Adam 15.80 15.40
Hein 16.30 0
(Since this is first record of the child-->Hein ,Previous Weight =0)
Please solve my problem
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 24 Mar 2008 at 12:40pm |
Change the join from Weight to Weight_1 to be a left outer join. Do this by selecting the join in the database expert, right-click on it, and there should be an option to modify, or it may be properties (I'm not on a computer that has Crystal right now, so I can't check it....) For most databases, you should have the option to change it.
-Dell
|
|
|
| IP Logged |
|
poongundran
Newbie
Joined: 02 Mar 2008
Online Status: Offline
Posts: 9
|
Posted: 26 Mar 2008 at 11:50pm |
|
hi Mr.HILFY
I worked out with what you said (left outer join),but not getting the desired output.Please help me out
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 27 Mar 2008 at 12:36pm |
Ok, one more piece that I missed. Change the weigheddate part of the selection formula to something like:
(IsNull(Weight_1.WeighedDate) or Weight.WeighedDate>Weight_1.WeighedDate)
You MUST put the "IsNull" first and, since you have other conditions in your select formula, you MUST use the parentheses around it to get it to filter correctly.
-Dell
(actually, it's "Ms." Hilfy!  )
|
|
|
| IP Logged |
|
poongundran
Newbie
Joined: 02 Mar 2008
Online Status: Offline
Posts: 9
|
Posted: 27 Mar 2008 at 11:48pm |
|
Ms.Hilfy
I changed the selection formula as per your instruction [---(IsNull(Weight_1.WeighedDate) or Weight.WeighedDate>Weight_1.WeighedDate)--] but getting the wrong output (same as previous).
I tried to change the link as below :
Link Options
wt.ChildName-->wt_1.ChildName
Join Type Enforce Join Link Type
---------------------------------------------------------------------------
Inner Join .Not Enforced-->a =
.Left Outer Join -->a Enforced From .> -->a
Right Outer Join Enforced To >=
Full Outer Join Enforced Both <
<=
!=
-----------------------------------------------------------------------------
'a' denotes selected things
It shows the output as
12-01-2008
ChildName CurrentWeight PreviousWeight
Adam 15.40 (Nothing is displayed)
25-02-2008
Adam 15.80 (Nothing is displayed)
Hein 16.30 15.40
21-03-2005
Adam 15.70 (Nothing is displayed)
Hein 16.00 15.80
Peter 14.30 15.80
Edited by poongundran - 28 Mar 2008 at 12:32am
|
| IP Logged |
|
hilfy
Admin Group
Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
|
Posted: 28 Mar 2008 at 6:23am |
Ok, you're much closer to what you need. Now, create a formula to use in place of Weight_1.Weight. It will look something like this:
if IsNull({Weight_1.Weight}) then 0 else {Weight_1.Weight}
-Dell
|
|
|
| IP Logged |
|
|
|
