Solved

date range display question

Posted on 2006-06-15
22
308 Views
Last Modified: 2008-02-01
if the user enters a date range via a parameter & i'm displaying it on a report using:

totext(minimum({?DateRange})) + ' - ' + totext(maximum({?DateRange}))

how can I diplay whether either of those were marked inclusive?
0
Comment
Question by:trs28
  • 10
  • 6
  • 4
  • +1
22 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 16914993
Hi trs28,

I don't know whether it is possible to do that, but just a caution on using date ranges in this way.
If your database fields are datetime fields rather than date fields CR is basing its include/exclude rules on the exact second.
(So don't assume it is working in whole days).

Pete
0
 

Author Comment

by:trs28
ID: 16915154
wouldn't the range start at the very beginning of the start date & the very end of the end date?    It's important for the data to be in date/time format, but for reporting purposes, time is insignificant.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16916682
Look at the values in the parameter prompt dialog.
If the end datetime is 01-jun-2006 00:00:00 then that is quite precise.

Pete
0
 

Author Comment

by:trs28
ID: 16916749
for anyone that looks at this ..... i figured it out ..... (too bad you can't give points to yourself!  lol)  ....by the way, this is in a formula, for displaying the selected range on the report, that i have partially done so far - the code below is how i trapped whether or not a start date was entered.  i'm sure the other half of the date range is trapped in a similar manner.

if (year(minimum({?DateRange})) = 0)
    then
       ('EVERYDAY' + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy"))
    else
        (totext(minimum({?DateRange}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy"));



anyone know how to give points to yourself?   HA!   I jacked the points up to 500 for anyone that can answer that one!
0
 

Author Comment

by:trs28
ID: 16916767
oops ... i didn't trap the "include" value ... i trapped the "no lower bounds" value .... kindly disregard that last posting :)
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 16917691
Do you still require help?

mlmcc
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 100 total points
ID: 16918165
I still don't believe you can detect the include/exclude settings unless you can somehow read the SQL statement being used.

The Include/exclude option are used by CR to set the SQL statement used to select records (assuming that is how you are using the parameter).

It affects the creation of the SQL 'Where' clause and might change to >= and/or add 1 minimum time unit to the range to exclude the start point.
the 'minimum time unit' is 1 second for a datetime field.  If you set a start point of 01/01/2006 00:00:00 in the parameter and exclude the start point then CR changes the start time to 01/01/2006 00:00:01 in the SQL statement.

Pete
0
 
LVL 34

Accepted Solution

by:
James0628 earned 400 total points
ID: 16919356
Checking whether "include value" was checked is actually quite simple.  Just test if the minimum and maximum are in the range.  For example:

 minimum({?DateRange}) in {?DateRange}

 That will be False if the "include value" box was not checked on the minimum.

 You'll have to decide how you want to reflect that on the report.  I ended up adding ">" (eg. "100 - 200" became ">100 - 200").

 James
0
 
LVL 77

Expert Comment

by:peter57r
ID: 16919376
Good thinking Batman!

Pete
0
 

Author Comment

by:trs28
ID: 16919828
that was very good thinking indeed!  James, you're definitely going to be getting a bunch of points for that one, but this now brings me to another dilemma ....  oh and pete, i'm planning on giving you points too buddy  ;-)

i want to adjust the diplay date to account for that.  in other words, if you select 01/01/2006 (not inclusive) as the max value, I want to be able to diplay the end value as 12/31/2005, but I'd like to be able to do it EASILY.  :)  

I was just going to create variables for the 3 numbers, parse out the values, subtract day value by 1, and then piece the 3 together for output, but then i realized how many conditions i would need to account for different lengths of the various months, etc.    

There wouldn't happen to be a PreviousDay or PriorDay function or something along those lines would there !?!?!?!  

...oh as far as the "giving points to yourself" thing goes, that was just a little late night sarcasm/humor.   :)

- travis
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:trs28
ID: 16920922
I figured it out ... for anyone that's interested, here's the code that traps both the "include this value" and "no lower/upper bounds" options for the start and end dates .....



local booleanVar minInc := (minimum({?DateRange}) in {?DateRange});
local booleanVar maxInc := (maximum({?DateRange}) in {?DateRange});

if (year(minimum({?DateRange})) = 0) then   //....if no lower bound date
(
    if (maxInc) then   //....if end date falls within actual queried date range
    (
        totext(minimum({AchReport.AchDate}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy")
    )                                                               //...prints lowest order date as start date (end inclusive)
    else
    (
        totext(minimum({AchReport.AchDate}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange})-1,"MM/dd/yyyy")
    )                                                               //...prints lower order date as start date (end NOT inclusive)
)
else
(    
    if (year(maximum({?DateRange})) = 0) then        //....if no upper bound date
    (
        if (minInc) then    //....if start date falls within actual queried date range
        (
            totext(minimum({?DateRange}),"MM/dd/yyyy") + ' - ' + totext(maximum({AchReport.AchDate}),"MM/dd/yyyy")
        )                                                        //...prints most recent order date as end date (start inclusive)
        else
        (
            totext(minimum({?DateRange})+1,"MM/dd/yyyy") + ' - ' + totext(maximum({AchReport.AchDate}),"MM/dd/yyyy")
        )                                                       //....prints most recent order date as end date (start NOT inclusive)  
    )
    else
    (
        if (minInc and maxInc) then
        (
            totext(minimum({?DateRange}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy")
        )                                                       //...prints user selected range as is (both inclusive)
        else
        (
            if (minInc and not maxInc) then
            (
                totext(minimum({?DateRange}),"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange})-1,"MM/dd/yyyy")
            )                                                   //...end date not inclusive
            else
            (
                if (not minInc and maxInc) then
                (
                    totext(minimum({?DateRange})+1,"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange}),"MM/dd/yyyy")
                )                                               //...start date not inclusive
                else
                (
                    totext(minimum({?DateRange})+1,"MM/dd/yyyy") + ' - ' + totext(maximum({?DateRange})-1,"MM/dd/yyyy")
                )                                               //...neither dates inclusive
            )
        )
    )
);

0
 
LVL 34

Expert Comment

by:James0628
ID: 16926326
Heh heh.  Thanks Robin.  :-)  I went through this same thing a while back, trying to figure out how to tell whether "include value" was unchecked, so I could show that on a report.  I did some research and couldn't find anything about it.  Not even a suggestion that it was possible.  It was looking like this was going to be one of those "you can't get there from here" kind of things, but then a day or two later it suddenly occurred to me to try that and it worked.


 Travis,

 Glad I could help.
 As you've figured out, you can just add or subtract a day to get the date after or before the minimum or maximum date.

 However, I think there's a problem with the formula you posted.  As I read it, you're checking for:

if (year(minimum({?DateRange})) = 0) then   //....if no lower bound date
 ...
else
  if (year(maximum({?DateRange})) = 0) then        //....if no upper bound date


 So, if both "include value" boxes are unchecked, your formula would see that the minimum was excluded and go through the first block of code and never get to the maximum check in the "else".

 Also, while it may not be a "problem", per se, your formula is somewhat redundant in that it repeatedly checks the same conditions.  You can simplify it greatly using variables.  The following is untested, but I think it should work (it may need some minor tweaking):

Local DateVar mindate;
Local DateVar maxdate;

if year (minimum ({?DateRange})) = 0 then         //....if no lower bound date
  mindate := minimum ({AchReport.AchDate})
else
  if minimum ({?DateRange}) in {?DateRange} then  //....if start date falls within date range
    mindate := minimum ({?DateRange})
  else                                            //...start date not inclusive
    mindate := minimum ({?DateRange}) + 1;

if year (maximum ({?DateRange})) = 0 then         //....if no upper bound date
 maxdate := maximum ({AchReport.AchDate})
else
  if maximum ({?DateRange}) in {?DateRange} then  //....if end date falls within date range
    maxdate := maximum ({?DateRange})
  else                                            //...end date not inclusive
    maxdate := maximum ({?DateRange}) - 1;

totext (mindate,"MM/dd/yyyy") + ' - ' + totext (maxdate,"MM/dd/yyyy")


 James
0
 

Author Comment

by:trs28
ID: 16927553
You definitely are correct about my code being redundant!   Initially I was going to somehow "mark" one of the dates if inclusive rather than adjusting the date itself, but then I got to thinking about it and realized that'd be more trouble than it's worth in the long run and frankly just bad practice.   ....so I started testing out some logic and well, what i had above worked!  

After reading your comment though, I started to wonder whether or not I did have that possible flaw that you pointed out.  The first thing it does is test for the lower bound date .... if it isn't, it tests to see if the end date is inclusive via the maxInc boolean variable ...doing it this way is how this little snippet of code became redundant, since i had to do that for all situations ....  so i think my code is okay.

Regardless, I'm a much bigger fan of your logic ... I had intentions on tweaking my horrible "if then" nightmare, but whether I would actually get the time to go over it again is another story .... so I do really appreciate your suggestion, especially because it's probably going to take the place of my original code!  :)    

You know, I was actually very surprised to see that more people haven't come across this same problem!   I was even more surprised to see how almost no one knew how to handle it !!!

Thanks again!

- Travis
0
 

Author Comment

by:trs28
ID: 16927581
There was a tiny bit of tweaking that needed done James on two lines, but I'll be honest with you, I'm a little confused as to exactly "why" it needed the adjustments ... here is the modified (fully tested and working) variation of your code - the adjustments were done in the line after the start of your two "if" blocks - for some reason, I had to encapsulate the variable assignment with date(...), but if i did that for the other assignments, crystal would bitch and say it wants a DateTime field regardless of them being identical assignments  !!?!??!    The only thing I can think of is that Crystal must only be picky about the assignment when a value is first assigned & then just disregard the time if it needs to be reassigned.   Other than that, it works great buddy!   :-D


Local DateVar mindate;
Local DateVar maxdate;

if year (minimum ({?DateRange})) = 0 then         //....if no lower bound date
     mindate := date(minimum ({AchReport.AchDate}))
else
  if minimum ({?DateRange}) in {?DateRange} then  //....if start date falls within date range
    mindate := minimum ({?DateRange})
  else                                            //...start date not inclusive
    mindate := minimum ({?DateRange}) + 1;

if year (maximum ({?DateRange})) = 0 then         //....if no upper bound date
 maxdate := date(maximum ({AchReport.AchDate}))
else
  if maximum ({?DateRange}) in {?DateRange} then  //....if end date falls within date range
    maxdate := maximum ({?DateRange})
  else                                            //...end date not inclusive
    maxdate := maximum ({?DateRange}) - 1;

totext (mindate,"MM/dd/yyyy") + ' - ' + totext (maxdate,"MM/dd/yyyy")

0
 
LVL 34

Expert Comment

by:James0628
ID: 16928885
You're welcome again.  :-)

 It still seems to me that when the minimum "include value" box was unchecked, your original code would have not checked if the maximum box was checked, but I guess it's a fairly moot point if you're not using that code anymore.

 As for having to add Date(), I have a couple questions.

 Is your ?DateRange parameter type Date or DateTime?

 Is the AchReport.AchDate field type Date or DateTime?

 If the parameter is Date and the field is DateTime, that would explain it.

 James
0
 

Author Comment

by:trs28
ID: 16929342
Oh wow!  I must be slipping b/c for some reason I was thinking that each of the 3 assignment were to the same thing -> maximum({?DateRange}) when I was writing that up!   You were right though, the AchReport.AchDate field is a DateTime and I created the parameter as a Date parameter.  

 //***The first thing looked at is whether the start date is unbound.
if (year(minimum({?DateRange})) = 0) then

     //***if it isn't, then it checks to see if the end date is unbound.
     if (year(maximum({?DateRange})) = 0) then

          //***if neither are unbound, it first checks to see if BOTH include values are checked
          if (minInc and maxInc) then

          //***if minimum is checked and max is unchecked
          if (minInc and not maxInc) then
       
          //***if maximum is checked and minimum is unchecked
          if (not minInc and maxInc) then

keep in mind, my boolean variables at the beginning of the code will tell me whether or not both are inclusive.

local booleanVar minInc := (minimum({?DateRange}) in {?DateRange});
local booleanVar maxInc := (maximum({?DateRange}) in {?DateRange});

you're right though about it being a moot point, especially since i'm using your code!   :)
0
 
LVL 34

Expert Comment

by:James0628
ID: 16929448
Oops.  I got stuck on the "include value" boxes I guess.  The tests I was referring to were actually for the lower and upper bounds.  If the "no lower bound" and "no upper bound" boxes were both checked, the first "if" would have been true, so you wouldn't have gotten to the "else", which is where it tested to see if there was an upper bound.  Sorry for the confusion.

 James
0
 

Author Comment

by:trs28
ID: 16929701
Well James,

I must say .... I'm impressed - you got me!   My code doesn't account for that whatsoever, but tell me ... what version of crystal are you using that allows you to select both "no lower bound" and "no upper bound" ??????   ha ha ha ha ha!  :-D   looks like i may have got you on that one buddy, huh !?!   lol


- Travis
0
 
LVL 34

Expert Comment

by:James0628
ID: 16932321
Heh heh.  You're completely right.  CR won't let you select both.  I don't use range parameters very often and had completely forgotten about that.  So your code was OK after all.  Well, that was a big waste of time.  :-)

 I don't know why CR won't let you select both.  Seems like that would be the way to select "all".  Hmm.

 James
0
 

Author Comment

by:trs28
ID: 16933962
A waste of time?   Nah.   Well, at least not for me!  :)   Besides, I enjoyed our little back and forth mini-chat.   Take care James.

- Travis
0
 
LVL 34

Expert Comment

by:James0628
ID: 16944954
Thanks.  You too.

 James
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now