Print Page | Close Window

Complex Date Formula

Printed From: Crystal Reports Book
Category: General Information
Forum Name: Self-Publishing
Forum Discription: Thinking about writing your own book or already have something to sell? Let me give you advice from my experiences.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=19444
Printed Date: 24 Apr 2024 at 3:23pm


Topic: Complex Date Formula
Posted By: aolfloppydisk
Subject: Complex Date Formula
Date 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


Replies:
Posted By: hilfy
Date 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


-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window