Report Design: Splitting datetime field

Print Page | Close Window

Splitting datetime field

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17547
Printed Date: 02 May 2024 at 4:48pm

Topic: Splitting datetime field

Posted By: KevV
Subject: Splitting datetime field
Date 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

Replies:

Posted By: DBlank
Date 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})

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

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

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

Posted By: DBlank
Date 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?

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

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

Posted By: DBlank
Date 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.

http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17549 - http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=17549

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)

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

Posted By: DBlank
Date 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)

Posted By: KevV
Date Posted: 17 Sep 2012 at 2:35am

I do want it to always be the current week. Here is the statment I am using in the select statement but I get different results than if I enter the actual dates.

CurrentDate in {VP_SCHEDULE.CURRPAYPERIODSTART} to {VP_SCHEDULE.CURRPAYPERIODEND}

Thanks

KevV

Posted By: DBlank
Date Posted: 17 Sep 2012 at 7:31am

you would get different resyults only if there are variations in the data hat allow one row to be included in one statemetn but exclude in the other statement (e.g. do some rows have a different end date)?

what exactly do you want to pull in?

records that have a start date int he last 14 days?

Records where the start and end date are exactly last monday and last saturday?

records where the end date is more than 7 days ago?

Posted By: KevV
Date Posted: 17 Sep 2012 at 9:40am

I checked all the rows in the table and they all have the same start date and end date so I am not sure why the results are different. I thought maybe there was something wrong with my statment. What I am looking for is to show the current week from Sunday thru Saturday.

Thanks

KevV

Posted By: DBlank
Date Posted: 17 Sep 2012 at 9:44am

odd.

CurrentDate in {VP_SCHEDULE.CURRPAYPERIODSTART} to {VP_SCHEDULE.CURRPAYPERIODEND}

should be fine

Posted By: KevV
Date Posted: 17 Sep 2012 at 10:39am

I think I see the issue, In the table there is {VP_SCHEDULE.CURRPAYPERIODSTART} and {VP_SCHEDULE.CURRPAYPERIODEND} and they all show the same start and end date but in another column is the {shiftstartdate} and {Shiftenddate}and it list all times since we installed the system. So the table shows:

{shiftstartdate} {Shiftenddate} {shiftstartime}

{9/17/2008 9:00:00 AM} {9/17/2008 2:00:00 PM} {1/1/1900 9:00:00 AM}

{Shiftendtime} {CURRPAYPERIODSTART} {CURRPAYPERIODEND}

{1/1/1900 2:00:00PM} {9/16/2012} {9/22/2012}

{PREVPAYPERIODSTART} {PREVPAYPERIODEND}

{9/9/2012} {9/15/2012}

So I pulled all recods that have a shift start date and shift end date in the current pay period rather than stating that the current date is in the time period and it worked. One last thing Is there a way thet if the time is on the hour it can show just the hour. I tried to format the field but that does not work. I bascially want it to show something like:

7AM - 3:30PM.

2:30AM - 10PM

and formatting the field only gives me:

7:0AM-3:30PM

2:30PM-10:0PM

Thanks

KevV

Posted By: DBlank
Date Posted: 17 Sep 2012 at 10:48am

i assume you are using some version of totext on the time as posted earlier. just use a replace on it

if weekday(table.datefield)=1 then replace(totext(starttime,"h:mmtt"),":00","") + "-" + replace(totext(endtime,"h:mmtt"),":00","")

Posted By: KevV
Date Posted: 17 Sep 2012 at 12:22pm

That did it You are Awesome.

Thanks

KevV

Print Page | Close Window