filtering problem

[azwaan]

i'm using CR 8.5 and i have a detail report displaying records from the database.

the table has call logging info ..such as dialledno,date,time,duration ,charge etc.

i need to filter all dialled no's which have been taken within 10 minutes (variable) of each other.

how can i do this?

 

[pgtek]

hi

read this

http://support.crystaldecisions.com...ndexq2=&indexq3=&indexq4=&btnG.x=40&btnG.y=13

cheers


pgtek

 

[synapsevampire]

The datediff function allows you to determine which datetimes are within 10 minutes of each other, but you might want to share technical information, such as the database used, and how the data is stored, otherwise people will have to guess and you'll end up going through a discovery process.

And once this data is "filtered", what is to be done with it?

-k

 

[azwaan]

ok..i'm using sql server as the backend but my question is showing records with times within 10 minutes of each other.

so..for this purpose i'm NOT passing any parameters for the report so i can use it in the selection formula.

for example if there are 2 records such as

dialled no- 1234, time :10.00
dialled no- 1234 time :10.05

these records should be displayed

these the following should not be

dialled no- 1234, time :13.00
dialled no- 1234 time :15.05


i know how to use the datediff function but how in this situation?..

 

[Ngolem]

hmmmm...I don't think you can do it in the record collection stage....use dateDiff() after the records are clooected and grouped/sorted...should be easy to suppress records that are not withing the timeframe

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[synapsevampire]

The reason why I asked for the database is because you might use a Query on the database side as opposed to relying on a Crystal based solution.

Still pretty vague, but here's the method:

Create a datetime variable from your date and time fields, then use the next() and previous functions to determine if a given row should be displayed, as in:

whileprintingrecords;
datetimevar currdt:= datetime(date({table.date})+time({table.date});
datetimevar prevdt:= datetime(date(previous({table.date}))+time(previous({table.date}));
datetimevar nextdt:= datetime(date(next({table.date}))+time(next({table.date}));

BTW, consider storing the date and time together in a datetime field in SQL Server, since SQL Server does NOT have just a date type, it probably already is and your example data of a time field is either wrong or useless.

Which also means you can just use the fields directly as opposed to converting 2 seperate fields into a meaningful field.

Now you can use the datediff function as the suppression formula at the detail level (right click the details and select X 2 next to the suppress) using something like:

if not(onfirstrecord) then
datediff("n",prevdt,currdt) > 10
else
datediff("n",currdt,nextdt) > 10

-k