Self-Publishing
 Crystal Reports Forum : General Information : Self-Publishing
Message Icon Topic: Complex Date Formula Post Reply Post New Topic
Author Message
aolfloppydisk
Newbie
Newbie
Avatar

Joined: 18 Apr 2013
Location: United States
Online Status: Offline
Posts: 1
Quote aolfloppydisk Replybullet Topic: Complex Date Formula
    Posted: 18 Apr 2013 at 10:29am
So I have a dillema, I'm trying to create a Crystal Report that will return records based on a specific date function. So what I'm trying to do is return results on invoices that have early pay discounts due in 3 "Working" days. So I have an InvoiceDate field that represents the start of the discount count, i.e. 10/NET 30. So in order to get the early pay discount you need to pay by 10 days from the InvoiceDate. But I want to be alerted 3 working days before that date, so if that date would happen to be a Tuesday then I would want to be alerted on Friday (excluding weekends). In my DB I have the InvoiceDate, the early terms are defined in a DB field called DiscountDays which would have 10 for example in it.

So basically I want the report to give me everything that is due in 3 days to make the early term discount.

InvoiceDate + DiscountDays = DiscountDueDate
DiscountDueDate - 3 (excluding weekends) = When I want it to show up on the report, if it's due 3 days from when I run the report. Not concerned with holidays or anything.

Thus far I can create a formula that gives me a comparison date that is 3 working days from now...

if WeekDay(currentdate, crMonday) < 3 then currentdate + 3
else if WeekDay(currentdate, crMonday) > 5 then currentdate + 3 + (7 - WeekDay(currentdate, crMonday))
else currentdate + 5;

I'm not sure what else I need to do to make this compare to the DiscountDueDate to get the result. Not sure what to do next. Thanks for any help.
Floppy
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3701
Quote hilfy Replybullet Posted: 19 Apr 2013 at 9:38am
How much data do you expect to be going through in this report?  I'm concerned because using a Crystal formula for this type of filter will probably cause Crystal to pull ALL of the data into memory and then apply the filter instead of pushing the filter to the database for processing.  This means that lots of data will be travelling over the network and into the memory of the computer where the report is running where Crystal will process it to potentially return just a few rows.  So, this will slow down your report.
 
However, there are a couple of ways around this.  What type of database are you connecting to?  How are you getting the data - are you linking tables in Crystal, using a universe, using a command or a stored procedure?
 
-Dell
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.030 seconds.