| Author |
Message |
a4chan
Newbie
Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21
|
   Topic: Using AND in crystal reports
Posted: 08 Aug 2010 at 4:31pm |
Hello every one,
I have encountered a problem while working on crystal reports.I have a sample table with the following data
| TID |
Date |
Item |
| T100 |
8/1/2010 |
Laptop |
| T100 |
8/1/2010 |
Desktop |
| T101 |
8/1/2010 |
Laptop |
| T102 |
8/1/2010 |
Desktop |
| T103 |
8/2/2010 |
Laptop |
| T103 |
8/2/2010 |
Desktop |
| T104 |
8/2/2010 |
Laptop |
I need the data when a person bought laptop & desktop on the sameday i,e the rows that are in green only .I used a formula in the record select but its not working,it is returning no rows.Can any one please help me to resolve this issue ?
Formula :
table.date in date() to date() and table.item = "laptop" and table.item = "Desktop"
Thanks
Edited by a4chan - 13 Aug 2010 at 6:41am
|
|
a4chan
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Aug 2010 at 4:21am |
group on tid (assuming that is customer id)
group on date set to day
create a formula for flagging laptop as LTflag
if table.item='laptop' then 1 else 0
create a formual field to flag desktop as DTflag
if table.item='desktop' then 1 else 0
Insert a summary of @LTFlag as a SUM in the datefield group (group2)
Insert a summary of @DTFlag as a SUM in the datefield group (group2)
go in the select expert
click on the expand it so you can toggle to the Group Selection option
make your select criteria ont he group as both your sums have to be >0. It will look somthing like this:
and
NOTE: Group record selection will leave the groups that do NOT meet the condition int he group tree (it youa re uing it) but will excldue them fromt he report canvas.
|
|
IP Logged |
|
a4chan
Newbie
Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21
|
Posted: 09 Aug 2010 at 11:12am |
Thank you for your post,its fantastic and working fine it returned the exact data i am looking for.But when I used distinct count for TID it should return only the count of the TID (i,e 2) but it is returning the distinct count of all TID's (i,e 5).I have a report something like following :
Laptop Desktop Laptop or desktop Laptop and Desktop
Distinctcount(TID) Distinctcount(TID) Distinctcount(TID) Distinctcount(TID)
it possible to use only formula fields without grouping the data or is there anyother way to resolve this issue so that the count of other fields should not be effected.
Thanks in advance
|
|
a4chan
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Aug 2010 at 11:16am |
interesting.
try using a running total set as a distinctcount of TID
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 09 Aug 2010 at 11:26am |
I just tested the RT process and it should work.
The Running Total is calculated after the select criteria is applied
right click on Runnning Totals (in Field Explorer) and select NEW
Name=CountAfterGroup
Field to summarize=TID
type=DistinctCount
Evaluate=For each Record
Reset=Never
Place in Report footer (will not work in the header)
Edited by DBlank - 09 Aug 2010 at 11:32am
|
|
IP Logged |
|
a4chan
Newbie
Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21
|
Posted: 10 Aug 2010 at 11:28am |
This works for a separate report you are simply superb thanks a bunch.how about both or & and in a single report
Laptop or desktop Laptop and desktop
Distinctcount(TID i,e 5) Distinctcount(TID i,e 2)
If I use group by I get the count but it will effect the number of rows.In the report I need all the corresponding rows (where item is laptop or desktop) and the distinct count of laptop or desktop & laptop and desktop in a single report.Please help its sounds like tricky to me.
|
|
a4chan
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Aug 2010 at 11:43am |
|
So you need to display all rows but get a count of customers that bought a laptop or desktop but not both and also a distinctcount if they bought both?
|
|
IP Logged |
|
a4chan
Newbie
Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21
|
Posted: 10 Aug 2010 at 11:46am |
|
|
|
a4chan
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 10 Aug 2010 at 11:53am |
|
remove any of the group filter from your select expert
create 2 running totals, 1 per count you want
Name=Both
Field to summarize=TID
type=DistinctCount
Evaluate=use a formula
SUM ({ @DTflag},{table.date},"daily")>0
and
SUM ({ @LTflag},{table.date},"daily")>0
Reset=Never
Place in Report footer (will not work in the header)
same thing for the other except use a different formula
(SUM ({ @DTflag},{table.date},"daily")>0
or
SUM ({ @LTflag},{table.date},"daily")>0
)
and not
(
SUM ({ @DTflag},{table.date},"daily")>0
and
SUM ({ @LTflag},{table.date},"daily")>0
)
Edited by DBlank - 10 Aug 2010 at 11:56am
|
|
IP Logged |
|
a4chan
Newbie
Joined: 07 Aug 2010
Location: United States
Online Status: Offline
Posts: 21
|
Posted: 11 Aug 2010 at 10:45am |
That certainly answers my question,thank you once again.
I have one more question,If the report is break down by two groups Location and by Date.I need the distinct count of customers that bought a laptop or desktop but not both & distinctcount if they bought both for that particular location in the group header/footer.Same for a particular date.
For total count we can use running totals in the report footer.
|
|
a4chan
|
|
IP Logged |
|
|
|
