• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Filter Records Using Previous

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
cehrnow
Asked:
cehrnow
  • 9
  • 6
  • 5
  • +3
2 Solutions
 
OutinCommented:
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
 
OutinCommented:
Soz, replace >= with <=

--
Outin
0
 
OutinCommented:
lol
I already did that :-)

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

--
Outin
0
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
cehrnowAuthor Commented:
I tried that but get "this function cannot be used as it must be evaluated later".
0
 
OutinCommented:
Did you use that in selection criteria? or in suppress condition?

I didn't use WhilePrintingRecords btw..

--
Outin
0
 
cehrnowAuthor Commented:
Report : Selection Formula : Record
0
 
OutinCommented:
Don't use the formula in the selection formula.
Use the formula to suppress the details section...

--
Outin
0
 
cehrnowAuthor Commented:
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
 
OutinCommented:
Hmm okay...

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

--
Outin
0
 
GJParkerCommented:
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
 
mlmccCommented:
Agree with Gary.  I think you will have build a view/stored procedure in the database

mlmcc
0
 
davetheravesmithCommented:
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
 
SaxonWicaCommented:
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
 
OutinCommented:
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
 
SaxonWicaCommented:
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
 
cehrnowAuthor Commented:
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
 
SaxonWicaCommented:
Is the report you're developing have groups in it?
0
 
cehrnowAuthor Commented:
Yes two groups.
0
 
OutinCommented:
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
 
SaxonWicaCommented:
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
 
OutinCommented:
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
 
cehrnowAuthor Commented:
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
 
SaxonWicaCommented:
No problem
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 9
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now