Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Beginning Date / End Date in Report Header Post Reply Post New Topic
Author Message
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Topic: Beginning Date / End Date in Report Header
    Posted: 20 Jul 2016 at 11:49am
I use a purchase order report that utilizes a field called 'po.orderdate'. Is it possible to create two fields in the report header that's based on the 'po.orderdate', that shows the dates of the beginning and end dates?

I'd like to do something like this:
Report date from (beginning date) to (end date)

Thank you

Edited by jgarner - 20 Jul 2016 at 11:50am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 20 Jul 2016 at 12:05pm
you can use
MINIMUM(po.orderdate)
and
MAXIMUM(po.orderdate)
If you are using a parameter to get your date range consider using that instead as it would likely be more accurate in case your data results do not have any values that fell on the start or end dates of the parameter values.
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 20 Jul 2016 at 12:22pm
Thanks for your suggestion. However, it's been quite a while since I've used CR, and I have a couple of questions.

I've used the Select Expert to specify the date range. Here's what is in the formula:

{PO.ORDERDATE} > DateTime (2015, 01, 01, 0, 0, 0) and
not ({PO.STATUS} like ["CAN", "REVISD"]) and
{PO.DESCRIPTION} like ["*Locker*", "*LOCKER*", "*locker*"]

As I mentioned, I'm terribly rusty with CR at the moment, and am wondering where and with which tool would I use your suggestion MINIMUM(po.orderdate) and MAXIMUM(po.orderdate)? In a formula or parameter field?

Edited by jgarner - 20 Jul 2016 at 12:22pm
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 20 Jul 2016 at 12:25pm
Do I apply your suggestion in the Running Total Field?
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 20 Jul 2016 at 12:29pm
Solved it with the Running Total Field. Thanks for your help
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Jul 2016 at 3:59am
I was suggesting that you use two formula fields in the header not a running total.
You can also use one formula field that is a string something like
"Report date from "+ totext(MINIMUM(po.orderdate),"mm/dd/yyyy")+ " to " + totext(MAXIMUM(po.orderdate),"mm/dd/yyyy")

I would caution using the RT as they do not "function correctly" when used in a header.
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 21 Jul 2016 at 4:49am
You are absolutely correct with mentioning that using an RT doesn't function correctly in headers, as I was having issues the more I worked with it.

I have put to use your formula. In order to eliminate errors when saving it, it ended up like this:

"Report Date From "+ totext(MINIMUM({PO.ORDERDATE}),"mm/dd/yyyy")+ " to " + totext(MAXIMUM({PO.ORDERDATE}),"mm/dd/yyyy")

I tested this on a 2-page report that didn't have a lot of data. The earliest date on my report is 10/23/15 and the latest date is 7/15/16.

However, when I bring the formula into the header, it looks like this:

Report Date From 39/23/2015 to 56/15/2016

Any ideas why it would show up like that? In the Format Editor / Date and Time, I have my dates formatted as 03/01/1999

Also, I am using the Select Expert to filter my dates (PO.ORDERDATE) as 'is greater than 1/1/2015 12:00:00AM

Edited by jgarner - 21 Jul 2016 at 4:54am
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Jul 2016 at 5:45am
oops. sorry but i used lower case 'mm' in the totext which uses the minute value. use upper case MM to get the month...
totext(MINIMUM({PO.ORDERDATE}),"MM/dd/yyyy")
IP IP Logged
jgarner
Senior Member
Senior Member


Joined: 23 Jan 2009
Location: United States
Online Status: Offline
Posts: 159
Quote jgarner Replybullet Posted: 21 Jul 2016 at 5:50am
Thanks for the correction. I would never have guessed. I really appreciate your help with this, and this question has been resolved!.

Thanks again.
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.031 seconds.