Solved

Convert date time to show letters for shifts in SSRS report builder 3.0 (Take 2)

Posted on 2011-02-25
9
1,056 Views
Last Modified: 2012-05-11
I think i confused the last person to try to help me with this one. we were very close using public function code. They were very helpful I just think I could not explain clearly what I am trying to show. (Sorry) this is take 2

I need to convert a datetime field to represent a shift letter. I used a formula in crystal that works well. Just not sure how to do it in SSRS.

I need to convert with a start date of 12-31-2010 for the first day of "A" shift. From there it rotates every 48 hours to the next shift. This pattern will repeat every 6 days. The time period is that the shifts start at 0800 each day.

from 12-31-2010 @ 0800 to 1-1-2011 @0800 would = "A"
from 1-1-2011  @ 0800 to 1-2-2011 0800 would = "A"
from 1-2-2011 @0800 to 1-3-2011 0800 would = "B"
from 1-3-2011 @0800 to 1-4-2011 0800 would = "B"
from 1-4-2011 @0800 to 1-5-2011 0800 would = "C"
from 1-5-2011 @0800 to 1-6-2011 0800 would = "C"
then back to
from 1-6-2011 @0800 to 1-7-2011 0800 would = "A"

This pattern repeats every 6 days forever

This is the formula in crystal that works Example

Example of the report i am working on with shift letters in datetime field
Example 2
0
Comment
Question by:srodgers45
  • 6
  • 2
9 Comments
 
LVL 5

Assisted Solution

by:robertg34
robertg34 earned 334 total points
ID: 34987635
I suggest building a lookup table that contains the shift name, a beginning datetime and an ending datetime.  After you have the lookup table, you simply query it for your shift name within your report.  This takes the complexity out of your report and makes your historical shift information consistent.  

Here's the code to build the lookup table:

--step 1
create table shiftlookup
(shift varchar(1), start_dtm datetime, end_dtm datetime)


--step 2 this is the initial inserts
insert into shiftlookup
values ('A', '12/31/2010 8:00:00 am', dateadd(hh,48,'12/31/2010 8:00:00 am') )

insert into shiftlookup
select 'B', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup

insert into shiftlookup
select 'C', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup

--step 3 and going forward run this loop and extend the loop counter out as far as you like
declare @timestoloop int;
declare @loopcntr int;
set @loopcntr = 0;
set @timestoloop = 20;
while @loopcntr < @timestoloop
BEGIN
      insert into shiftlookup
      select 'A', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup;

      insert into shiftlookup
      select 'B', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup;

      insert into shiftlookup
      select 'C', max(end_dtm), dateadd(hh, 48, max(end_dtm)) from shiftlookup;
      
      set @loopcntr = @loopcntr + 1
END

select * from shiftlookup
0
 

Author Comment

by:srodgers45
ID: 34988237
Do I do this in SQL or in the report as a dataset? I created the shift part in a SQL Table, not sure how to do the step 3 forward? This looks good, I am not really a programmer, just a report person, mainly Crystal, the SSRS is all new to me.
Right now there are no shift selections in the tables. I have to convert the datetime field to represent the shift instead of a date.
 If I can use the excellent solution you provided how does it tie into the corresponding date in the report? The Crystal formula was tied into the "Created" date field. I just created the formula and dropped it in the area I needed it in. Unfortunately I have nultiple reports I need to see the shifts in.
It's probably not as complicated as I think it is.
THANKS.
Look Up Table
I was given this example, it almost works right, just could not figure out how to insert a start date for the first shift to loop from:
____________________________________________________________________________________
Place this code in your Reports properties Code Tab.
Then have this code in your cell or textbox
=Code.ShiftChange(Fields!MyFirstShiftDate.Value, Fields!MyComp_StatusDate.Value)

 Public Function ShiftChange(ByVal FirstShift As DateTime, ByVal CompStatus As DateTime) As String
        Dim DayDiff As Integer = Nothing

        DayDiff = Microsoft.VisualBasic.DateDiff(DateInterval.Day, FirstShift, CompStatus) Mod 6
        Select Case DayDiff
            Case 0
                If CompStatus.Hour < 8 Then
                    Return "C"
                Else
                    Return "A"
                End If
            Case 1              
                Return "A"
            Case 2
                If CompStatus.Hour < 8 Then
                    Return "A"
                Else
                    Return "B"
                End If
            Case 3
                Return "B"
            Case 4
                If CompStatus.Hour < 8 Then
                    Return "B"
                Else
                    Return "C"
                End If
            Case 5
                Return "C"
        End Select
        Return Nothing
    End Function
0
 

Author Comment

by:srodgers45
ID: 34988362
I was able to create the rest, I think I had originally left something out? Now how do I insert to report to associate the shift for the dates in the report?  

Look Up 2 Report Setup
0
 
LVL 27

Assisted Solution

by:planocz
planocz earned 166 total points
ID: 34988779
Will the Last Code that I give you I just changed the Mod 7 back to mod 6 and
I get the correct answer each time.
0
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 

Author Comment

by:srodgers45
ID: 34988833
Looking at the Tdate field on the DB it is not a 'datetime' but a 'varchar'. can that be causing the problem? I tried to change it in the code and it said error "Type 'varchar' is not defined. The only datetime is the "created", but i cannot use that for both in the expression. is there a set value that can be used instead?
Thanks (Again!)
Table Expression
0
 
LVL 5

Accepted Solution

by:
robertg34 earned 334 total points
ID: 34988995
This should be done with sql code.  Something like:

select shiftlookup.shift
from shiftlookup, yourtable
where yourtable.CREATED between shiftlookup.start_dtm and shiftlookup.end_dtm

I don't know what is in the tdate column or the name of that table..(yourtable).  If you want to post some screen shots of sample data for that table I might be able to help more.  



0
 

Author Comment

by:srodgers45
ID: 34989071
Here is the shot. Do i need to create a view?

Data
0
 

Author Comment

by:srodgers45
ID: 34989367
robertg34:
After looking at it further, I ran the last query you gave me to get  the data in the first screen shot. How do I get that to the report? The second one shows what the report is using for data in SSRS, some how I need to merge the 2 together? I think? Query for Look up[ Completion Query
0
 

Author Closing Comment

by:srodgers45
ID: 34989916
I was able to use this and the other table and information provided to create a view that included the shift, I have successfully created a report with the information needed. Thanks
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
With the power of JIRA, there's an unlimited number of ways you can customize it, use it and benefit from it. With that in mind, there's bound to be things that I wasn't able to cover in this course. With this summary we'll look at some places to go…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).

920 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

15 Experts available now in Live!

Get 1:1 Help Now