| Author |
Message |
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
   Topic: Splitting datetime field
Posted: 12 Sep 2012 at 4:32am |
I have a report that I am pulling from our timekeeping system. In the schedule table it has a datetime field and I want to use the date portion in my parameter and display the start and end times in the report. I tried just pulling parts of it and I tried converting it to text and using the different parts but I cant get any results. The table is: {VP_SCHEDULE.SHIFTSTARTDATE}
with the following info:
2/16/2012 4:00:00 AM
So I want to use 2/16/2012 as my parameter and I want to display 4:00.
Thanks for any help
KevV
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Sep 2012 at 4:44am |
ae you talking about using a dyanimc LOV or just a date type param?
if a date type param use this in the select expert
{?date} = date({VP_SCHEDULE.SHIFTSTARTDATE})
and for you display
time({VP_SCHEDULE.SHIFTSTARTDATE})
|
|
IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 12 Sep 2012 at 5:49am |
That didnt seem to work either. I still do not get any results. I fdid find another field to pull from that has just the date and changed the select statement to:
{VP_SCHEDULE.CURRPAYPERIODSTART} = {?Date}
I also tried:
date({VP_SCHEDULE.CURRPAYPERIODSTART}) = {?Date}
but still nothing. Any other suggestions
KevV
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Sep 2012 at 6:00am |
dont filter and start using some formulas to look at your data in crystal
try
date({VP_SCHEDULE.SHIFTSTARTDATE})
place it side by side with the original {VP_SCHEDULE.SHIFTSTARTDATE} field...what show up for this?
your sample date should show as
original formula
2/16/2012 4:00:00 AM 2/16/2012
|
|
IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 12 Sep 2012 at 7:23am |
I started all over and got it to show the time right. It currently shows the one day. Is there a way to show a full week on the one line like:
Sunday Monday Tuesday Wednesday Thursday
10AM-6PM 10AM-6PM OFF OFF 10AM-6PM
or would I need to do a subreport. I do have the fields in the table named:
{VP_SCHEDULE.CURRPAYPERIODSTART} and
{VP_SCHEDULE.CURRPAYPERIODEND}
Which is the current pay period.
Is it possible to use that time frame as my paramater in a formula so they dont have to enter one?
KevV
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Sep 2012 at 7:34am |
|
the easist way to show rows as columsn is a crosstab but I am not sure I understand your data well enought to anseer the questions exactly. Can you show sample data nad how you want it to present?
|
|
IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 12 Sep 2012 at 8:13am |
Currently they have to enter the date range. I would like to use the:
{VP_SCHEDULE.CURRPAYPERIODSTART} and
{VP_SCHEDULE.CURRPAYPERIODEND}
as the parameter to show everything in that timeframe.
As for the layout this is the way it currently shows:
7:00AM 3:30PM
7:00AM 3:30PM
5:00AM 1:30PM
5:00AM 1:30PM
7:00AM 3:30PM
but I want it show :
Sunday Monday Tuesday
7:00AM -3:30PM 7:00AM-3:30PM 5:00AM-1:30PM
I looked at the crosstab but I am not sure about what to put in the summarize area
Thanks
KevV
Edited by KevV - 12 Sep 2012 at 8:15am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Sep 2012 at 8:30am |
I am not sure what you mean by use the fields...? Do you mena have a dynamic LOV that they pick from?
if so use a param, change i tot dynamic and point the value to the field you want to use.
to change it top a row there are a few ways to do this.
easiest is probably make your formulas to use an if then based on the weekday, one per day you want to show.
//Sunday
if weekday(table.datefield)=1 then totext(starttime,"h:mmtt") + "-" + totext(endtime,"h:mmtt")
I assume you have this grouped on a worker id or something so you would do a max of weekday formula for each worker and plac in the group footer
maximum(@sunday,groupfield)
|
|
IP Logged |
|
KevV
Senior Member
Joined: 19 May 2011
Online Status: Offline
Posts: 106
|
Posted: 12 Sep 2012 at 11:45am |
Thanks for your paitence this one has been a little rough for me. The fields I was reffering to are:
{VP_SCHEDULE.CURRPAYPERIODSTART} and
{VP_SCHEDULE.CURRPAYPERIODEND}.
They are in the table and refer to the current pay period and are populated for example if I look at the table today it shows:
9/9/2012 under {VP_SCHEDULE.CURRPAYPERIODSTART} and
9/15/2012 under {VP_SCHEDULE.CURRPAYPERIODEND}
so I am wondering about using those in a select statement rather than asking them to enter anything in a parameter. I tried putting those in the select statemnet but it didnt seem to work.
I was able to get the hours to show the one thing I cant seem to get working is if there is nothing in the field I wantt it to show "OFF".
Thanks
KevV
Edited by KevV - 12 Sep 2012 at 11:46am
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 12 Sep 2012 at 12:05pm |
YOu can use them in the select expert but I do not know if you alwasy just want the current or last week or if the use still needs to be able to choose which week they want to look at.
If things are null they are more likely an empty string as tehre would be one row to evalaute try:
if maximum(@sunday,groupfield)= "" then "Off" else maximum(@sunday,groupfield)
|
|
IP Logged |
|
|
|
