Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Select Data based on previous day Post Reply Post New Topic
Author Message
addk
Newbie
Newbie


Joined: 08 Nov 2011
Online Status: Offline
Posts: 4
Quote addk Replybullet Topic: Select Data based on previous day
    Posted: 09 Nov 2011 at 1:50am
Have searched hi and low for an answer to do this but have not found anything suitable.

I am trying to select data based on the previous day, I have turned the Unix time stamp field into a usable date from a formula.

I need to create parameters that will let the user select either previous Day or allow them to select a date range. To make this even more complicated a working day starts from 6am to 6am and NOT 12am until 12am

I have got into a muddle with the date add function to such an extent I have really confused myself.

Can anyone help a relative newbie.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Nov 2011 at 4:29am

it will help if you more clearly define what you want the user to enter and what you want the data returned.

as a start I would just use date type parameters and make the user enter the actual dates regardless if it is for 'yesterday' or any other 'range'
From there you need to be a little more clear on how you want to handle the 6am-6am instead of 12-12. if the user inputs 'yesterday' in the date range
?Begin Date = '11/8/2011' and ?End Date = '11/8/2011'
you want your data to treturn anything from
11-8-2011 6:00am through 11-9-2011 6:00am, correct?
 
IMO the easiest way is to 'shift your non standard days' to 'standard days' with dateadd.
so the select statement looks like
dateadd('h',-6,{@datetimeformula}) in {?Begin Date} to {?End Date}
 


Edited by DBlank - 09 Nov 2011 at 4:30am
IP IP Logged
addk
Newbie
Newbie


Joined: 08 Nov 2011
Online Status: Offline
Posts: 4
Quote addk Replybullet Posted: 09 Nov 2011 at 7:41am
Without trying to confuse the situation , I would like the user to select 'yesterday' from a drop down list.

The data returned would be anything from yesterday between 6am and 6am today . Example if today is 9/11/2011 at 7am ( although could be anytime that day) and the user selects 'yesterday' the data returned would be between 8/11/2011 6am and 9/11/2011 6am.

To make matters worse I would like to add to the drop down list ' custom date range' then the user would select a start date and end date and the data returned would be between that period.

Hope the makes sense but not sure how to explain it better.

Thanks
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 09 Nov 2011 at 9:28am
if you are using version 2008 then maybe i would recommend your desired process but if you are using any other version of crystal you cannot make optional parameters. This means even if the user picks 'yesterday' from a drop down they still have to enter some dates into the 'custom date range'. If they have to enter dates regardless of the pick list why make them do both? It is a waste of time and confusing?
Thsi is why i recommended the other approach.
are you using 2008?
 
IP IP Logged
addk
Newbie
Newbie


Joined: 08 Nov 2011
Online Status: Offline
Posts: 4
Quote addk Replybullet Posted: 09 Nov 2011 at 7:23pm
What I am trying to do is give them an option, in the drop down box it's either 'yesterday' which will quickly give them yesterdays data or if they pick 'custom date range' in the drop down box they would then use another parameters which will be the date picker.The idea of the date picker would be for a custom range say for 6 month period.

I was then hoping to use both parameters in some sort of logic formula within the select expert.

I understand where your coming from, I could just have the user select yesterdays date from the date picker. I am simply trying to make things quicker for the end user and yesterday's data will be run far more than the custom date range.

I am currently using XI and would prefer a solution in that, although I do have access to 2008 should I need to.

The main problem is this 6am to 6am working day which ranges over two dates.

once again thanks for the help



IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 10 Nov 2011 at 3:45am
I believe I understand what you want to do. However your primary reason for using the drop down was for quicker entry. What I was trying to explain to you is that in any version of crysal lower than 2008 you cannot have an optional parameter. If the parameter is in the report design the user must enter data into it (even if you as the designer ignore what they enter). Therefore your primary design reson for the 2 layered date entry approach, quick data entry, is no longer valid. It makes the entry process more clumsy and time consuming.
If you still choose to do this,
add one parameter
name='Date Type' (or what ever you want)
type = string
List of values=static
In the value column add your 2 desired options of 'Yesterday' and 'Other Dates'
OK
add another param
name= 'Custom Dates' (or whatever)
type =date
in Options mark 'allow range values' as true
your select statment will be
 
(
{?Date Type} = 'Yesterday' and
datediff('d',(dateadd('h',-6,{@datetimeformula})),currentdate)=1
)
or
(
{?Date Type} = 'Other Dates' and
dateadd('h',-6,{@datetimeformula}) in Minimum({?Custom Dates}) to Maximum({?Custom Dates})
)
 
IP IP Logged
addk
Newbie
Newbie


Joined: 08 Nov 2011
Online Status: Offline
Posts: 4
Quote addk Replybullet Posted: 15 Nov 2011 at 4:49am
Thanks for your help on the subject.

I see exactly what you mean in XI, it just makes it even more confusing and looks a complete mess. I will try it on 2008 when I can gain access to it in a few days.

I need to get some live data to work with now to verify the formula is doing as expected.

once again thanks for your expert help, this is an excellent crystal forum. keep up the great work.

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.018 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.