Solved

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

Posted on 2011-02-25
9
1,064 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
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.

 
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
 

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

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.

Question has a verified solution.

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

Written by Valentino Vranken. A while ago I wrote an article called Chart Optimization Tips (http://www.experts-exchange.com/articles/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Chart-Optimization-Tips.html).  This article explained how …
Introduction As you'll probably know, a data region in a SQL Server Reporting Services report can be linked to only one dataset.  This makes it troublesome when you need to display data from more than one dataset in the same data region.  SQL Serve…
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

770 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