?
Solved

Filter Records Using Previous

Posted on 2005-05-10
23
Medium Priority
?
425 Views
Last Modified: 2012-06-27
How can I filter out records having a datetime value within 15 seconds of a previous record.
Have played around with WhilePrintingRecords etc.. in the record selection formula but keep getting cannot be evaluated etc..etc..
0
Comment
Question by:cehrnow
  • 9
  • 6
  • 5
  • +3
23 Comments
 
LVL 9

Expert Comment

by:Outin
ID: 13966782
Supress records or details section using this condition:

{DATETIMEFIELD} <= DateAdd ('s',15 ,previous({DATETIMEFIELD}))

This will supress everything that's within 15 secs of previous record.

--
Outin
0
 
LVL 9

Expert Comment

by:Outin
ID: 13966800
Soz, replace >= with <=

--
Outin
0
 
LVL 9

Expert Comment

by:Outin
ID: 13966804
lol
I already did that :-)

nm, it's moday for me, jsut back from a weeks @home :-)

--
Outin
0
Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

 

Author Comment

by:cehrnow
ID: 13966847
I tried that but get "this function cannot be used as it must be evaluated later".
0
 
LVL 9

Expert Comment

by:Outin
ID: 13966864
Did you use that in selection criteria? or in suppress condition?

I didn't use WhilePrintingRecords btw..

--
Outin
0
 

Author Comment

by:cehrnow
ID: 13966878
Report : Selection Formula : Record
0
 
LVL 9

Expert Comment

by:Outin
ID: 13966908
Don't use the formula in the selection formula.
Use the formula to suppress the details section...

--
Outin
0
 

Author Comment

by:cehrnow
ID: 13966940
Trouble is by the time it filters in the detail section the records have been grouped into 2 other sections. Need the datetime filter to apply prior to the grouping.
0
 
LVL 9

Expert Comment

by:Outin
ID: 13967155
Hmm okay...

Don't know if that's possible...will think about it...

--
Outin
0
 
LVL 19

Expert Comment

by:GJParker
ID: 13967828
Just to clarify

You want to sort the data one way and remove records within 15 seconds of each other and then re sort the records another way to display on the report ?

This isn't possible using just crystal without using a command as the datasource (even then it may not be possible it depends on the db)

another alternative is to do the initial sort and filtering on the db using a view/query.stored procedure and use this as the datasource for the report.

HTH

Gary

0
 
LVL 101

Expert Comment

by:mlmcc
ID: 13967957
Agree with Gary.  I think you will have build a view/stored procedure in the database

mlmcc
0
 
LVL 1

Expert Comment

by:davetheravesmith
ID: 13973664
Can I just join in here???

This is similar to something you experts advised me about a short while ago.  What we're all looking for here is a simple way to query and hold a dataset which has been extracted and manipulated one way, while "displaying" the data in a completely different way, but relative to the first pull!.

Could it be that we could attach to the same datasource twice for this, as opposed to attaching to two disparate datasources, and control one from the other?

I sometime get people to use a primary data source, let's say on a SQL box, while manipulating this against a completey different datasource, say in Excel or Access which supplies the "control" for the final result.  

An example would be a complete set of student records in the primary data source, filtered by data held in a secondary file for expelled pupils only.  In this case I lookup and validate records in the secondary file using the primary, and turn it around on the reports.  Is there a separate area where we can discuss this?

Regards

DAVE THE RAVE SMITH
0
 
LVL 5

Expert Comment

by:SaxonWica
ID: 13975383
Try to create a formula and put it on each detail you want to evaluate. For example, I create a formula named "OVER" :

if abs(DateDiff ("s",previous({DATETIMEFIELD}),{DATETIMEFIELD})) > 15 then
   "OVER"
else
   ""

And then add suppress formula on detail section :
   {@OVER} <> "OVER"

Note that this formula only works for records that you put in detail section, not a grouped one. You can suppress the formula so it doesn't show up in your report, but still evaluated.
0
 
LVL 9

Expert Comment

by:Outin
ID: 13975470
Dave,
This would not be a solution in this case. It might be possible that this works with datafiles (like Excel) when setting the 'Look up all of one, then all of others' option, but it will not work when using 'real' databases (like Oracle).
Imagine adding this same table twice, then link them correctly...and sorting one table will sort the other one too...

Saxon,
Afaik this will not work as the formula is evaluated *after* the data is sorted into groups. If no groups were added, your solution would work (it's kinda the same I already proposed).

--
Outin
0
 
LVL 5

Expert Comment

by:SaxonWica
ID: 13976046
Outin,
Yes, it's almost the same but when I tried your solution with a sample database from crystal report, it didn't return desired output. For example, you can try viewing all order details from extreme database within 1 day.

And you are correct on the evaluation after data sorting. That's why I also mention that this wouldn't work on a grouped one.


Chernow,
You can try to modify the report from database level as mlmcc and Gary suggested, then you can use the formula that Outin and I mentioned before.
0
 

Author Comment

by:cehrnow
ID: 13976075
The report must stand-alone without any outside code. How about creating a sub-report which does the initial time filter and then querying against the data held there for the display ? Not sure how to access the sub-report data though.
0
 
LVL 5

Expert Comment

by:SaxonWica
ID: 13976120
Is the report you're developing have groups in it?
0
 

Author Comment

by:cehrnow
ID: 13976161
Yes two groups.
0
 
LVL 9

Accepted Solution

by:
Outin earned 1200 total points
ID: 13976198
There is a way...but with limitations.
Maybe some others can comment on suggestions below:

- Create a main report that filters only the desired rows.
- Add all unique keys (like some row_id or so) to a global variable (eg stringvar ROWS look like "1,5,6,9,11,15,17").
- Create a subreport (in main report footer) that displays and groups data the way you want.
- Create a formula in the subreport that just returns the global variable.
- In the subreport selection formula, write something like 'row_id in {@ROWS}'.
- Hide all useless mainreport sections.

I can explain in more detail if you like....

--
Outin
0
 
LVL 5

Assisted Solution

by:SaxonWica
SaxonWica earned 800 total points
ID: 13976201
I see...

I think there's not much option now, except by writing a new view or sql command to group the database first or to calculate the time difference before being used in the report.

CMIIW
0
 
LVL 9

Expert Comment

by:Outin
ID: 13976299
Addition to my previous suggestion:
When using a stringvar it is (in CR 8.5) only possible to create strings with max. 254 bytes.
It's better to use an array of strings, which can hold up to 1000 elements...

So that's the limitation of the suggested solution: only up to 1000 rows can be selected...hope it is enough for you...

--
Outin
0
 

Author Comment

by:cehrnow
ID: 13978625
Very impressive solution but I think 1000 would be a dangerous limitation in this case AND it's getting a bit complicated. Have decided to add a further column to the source data table and set a (within 15 seconds) flag at source to enable easier filtering later. Not ideal but I think better in the long run.
Many thanks for help.
0
 
LVL 5

Expert Comment

by:SaxonWica
ID: 13983193
No problem
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This video shows how to quickly and easily deploy an email signature for all users in Office 365 and prevent it from being added to replies and forwards. (the resulting signature is applied on the server level in Exchange Online) The email signat…
Loops Section Overview
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question