Solved

Convert date time to show letters for shifts in SSRS report builder 3.0

Posted on 2011-02-24
16
829 Views
Last Modified: 2012-05-11
I am trying to convert dates to show as a shift letter. I.E.  based on a 48 hour rotation beginning at 08:00 (am) for each day it would show the corresponding shift based on the date/time.
1-2-2011 before 08:00 = "A"
1-3-2011 before 08:00 = "A"  
1-4-2011 before 08:00 = "B"  
1-5-2011 before 08:00 = "B"
1-6-2011 before 08:00 = "B"  
1-7-2011 before 08:00 = "C"
1-8-2011 before 08:00 = "C"  
then back to
1-9-2011 before 08:00 = "A"  
And repeats from there.

The screen shot I have shows how I have it set up for a formula in Crystal, it works great. I am new to SSRS and not sure how to reproduce it for those reports? Any help is appreciated.

Shift ID Formula
0
Comment
Question by:srodgers45
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
16 Comments
 
LVL 27

Expert Comment

by:planocz
ID: 34972927
You place this code in your Reports properties Code Tab.
You will need to test it. I think I got it pretty close.
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

Open in new window

0
 

Author Comment

by:srodgers45
ID: 34973159
In this area?  

Query
0
 
LVL 27

Expert Comment

by:planocz
ID: 34974196
No. If you are using 2008 just right click on the report design area and pick Code tab.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:srodgers45
ID: 34975221
I think i almost there - i believe I need to add a date value in the expression, correct? Forgive my ignorance

 Code  
Shift-ID-Formula-3.bmp
0
 
LVL 27

Expert Comment

by:planocz
ID: 34975906
you need two different values in the expression, not both CREATED
0
 

Author Comment

by:srodgers45
ID: 34976500
I think I am still missing something - doesn't there need to be a date for the shift starting point  in the expression field? It should start over after 6 days  "AABBCCAABBCC............FOREVER"

I changed the expression to 2 different values:

 =Code.ShiftChange(Fields!Tdate.Value, Fields!CREATED.Value)  - ---- Should there be a shift start date value set in this expression? It would be the Tdate field if so.The screen shot example at the top from crystal formula has a static value for the shift start.

And should the code have a table value 'CREATED' to use for the shft start and stop time as well?

Sorry to be a pain, just not familiar with this application, I am going to be sooner than later. I know I am close to getting it right. Thank You for the help so far.

what I am trying to show in the screen shot is:
The 4th should be C prior to 0800 on the 5th,
Then
The 5th and 6th after 0800 are A,
The 7th and 8th after 0800 are B,
The 9th and 10th after 0800 are C,
Then it starts over again

 Shift
This is the report I am showing results for:
 Report
0
 
LVL 27

Expert Comment

by:planocz
ID: 34976648
You need to get me a start point.
Like the shift start is 1/4/2011 and the created date is 1/4/2011
You only show me the fininshed date.
0
 
LVL 27

Expert Comment

by:planocz
ID: 34976668
In other words I need to see what you want going in and what the real answer is suppose to be outing out.
0
 

Author Comment

by:srodgers45
ID: 34976765
The start date would be 2010-12-31 would be the first A shift  date, the created date has the date and time for which the shift would correspond to. So on 12-31-2010 @ 0800 A shift starts for 48 hours. What I show on the report is any created date within that 48 hours has the corresponding shift. On 1-2-2011 at 0800 B shift starts, and so on.Does that make sense? The date and setup on the screen shot at the top of the page for the crystal formula is accurate for the current report I have in crystal. I just cannot use it for what I need now. I am trying to basically duplicate that formula for SSRS report.

Thanks
0
 
LVL 27

Expert Comment

by:planocz
ID: 34976931
Ok the code is showing the same now as the very top code of
 '1-2-2011 before 08:00 = "A"
    '1-3-2011 before 08:00 = "A"  
    '1-4-2011 before 08:00 = "B"  
    '1-5-2011 before 08:00 = "B"
    '1-6-2011 before 08:00 = "B"  
    '1-7-2011 before 08:00 = "C"
    '1-8-2011 before 08:00 = "C"  
    'then back to
    '1-9-2011 before 08:00 = "A"  
    'And repeats from there.

Let me know if that still does not work.
0
 
LVL 27

Accepted Solution

by:
planocz earned 500 total points
ID: 34976935

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

        DayDiff = Microsoft.VisualBasic.DateDiff(DateInterval.Day, FirstShift, CompStatus) Mod 7
        Select Case DayDiff
            Case 0, 6
                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, 5
                If CompStatus.Hour <= 8 Then
                    Return "B"
                Else
                    Return "C"
                End If
        End Select
        Return Nothing
    End Function

Open in new window

0
 

Author Comment

by:srodgers45
ID: 34977101
Still the same result?

Forgive me, I must be explaining it wrong? I dont understand how the code knows what to use for a start date for the first A shift date? is it based on this expression formula? Should there be a date defined for the value? I am trying to understand it

     =Code.ShiftChange(Fields!Tdate.Value, Fields!CREATED.Value)

    12-31-2010 @ 0800 is the "First A Shift day" (for the next 48 hours it is A shift) based on Fire Department Shifts, I am reporting on incomplete reports for any incident generated from then until:    

     1-1-2011 before 08:00 = "A"  
Then
    '1-2-2011 before 08:00 = "A"  
Then
    '1-3-2011 before 08:00 = "B"  
Then
    '1-4-2011 before 08:00 = "B"
Then
    '1-5-2011 before 08:00 = "B"
Then
    '1-6-2011 before 08:00 = "C"
Then
    '1-7-2011 before 08:00 = "C"  
        'And then repeats from there.

In this crystal formula it has a start date for A shift of 1-5-2010
 Crystal formula

This is what the report from crystal looks like, The colored box shows the shift working on the date shown on the left, which is the from "CREATED"  date/time table
 Crystal Report
0
 
LVL 27

Expert Comment

by:planocz
ID: 34979403
the firstshift date was the 12-31-2010
0
 

Author Comment

by:srodgers45
ID: 34980446
Correct, first A shift is 12-31-2010 Thanks
0
 

Author Comment

by:srodgers45
ID: 34985083
Did you give up on me? I know I have. Sorry for any confusion
0
 

Author Closing Comment

by:srodgers45
ID: 34985586
Thanks, I think I wore you out on this. I reposted hopefully with a better expalnation of what I am trying to do.
0

Featured Post

Is Your Team Achieving Their Full Potential?

74% of employees feel they are not achieving their full potential. With Linux Academy, not only will you strengthen your team's core competencies but also their knowledge of of the newest IT topics.

With new material every week, we'll make sure that you stay ahead of the game.

Question has a verified solution.

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

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…
There's a multitude of different network monitoring solutions out there, and you're probably wondering what makes NetCrunch so special. It's completely agentless, but does let you create an agent, if you desire. It offers powerful scalability …

726 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