Need help recreating an excel lieu time sheet in SSRS

I'm trying to recreate a lieu time report currently in excel.  I have a table storing hours entered, the code (billable, personal, vacation, etc.) against which it is entered, the date and the employee id.  I can easily pull out all the data for a given employee beginning with the start of our fiscal year (May 1) but I'm not sure about how to group it by the week ending.  Plus I have a separate table where I've generated all the days of the year and marked it as either a work day, weekend or holiday (as well as other information).  

This is the sheet I'm trying to recreate:

And here is the code I used to create the calendar table (I didn't include the last part where I mark the holidays).  Though I think perhaps I should have included the week ending day.
IF EXISTS(SELECT * FROM sys.tables WHERE name='WorkCalendar') 
	DROP TABLE WorkCalendar

CREATE TABLE WorkCalendar (
	PKDate date primary key, 
	-- Years
	calendar_year smallint,
	-- Quarters 
	calendar_quarter tinyint, 
	calendar_quarter_desc varchar(10),  
	-- Months
	calendar_month tinyint, 
	calendar_month_name_long varchar(30), 
	calendar_month_name_short varchar(10), 
	-- Weeks
	calendar_week_in_year tinyint, 
	calendar_week_in_month tinyint, 
    -- added
	pay_week_in_month tinyint,  
	-- Days
	calendar_day_in_year smallint, 
	calendar_day_in_week tinyint,  -- The first of the month 
	calendar_day_in_month tinyint, 
	mdy_name_long varchar(30), 
	mdy_name_long_with_suffix varchar(30), 
	day_name_long varchar(10), 
	day_name_short varchar(10), 
	description varchar(30), 
	is_weekend tinyint,            -- Tinyint and not bit so you can add the 1's. 
	is_holiday tinyint,            -- Tinyint and not bit so you can add the 1's. 
	is_workday tinyint,            -- Tinyint and not bit so you can add the 1's. 
	is_event tinyint               -- Used to indicate any special event days. 

DECLARE @dt_start date = '20120101'
      , @dt_end date   = '20160430'
      , @total_days int
      , @i int = 0
      , @start_Month int = 5 -- added

SELECT @total_days = DATEDIFF(d, @dt_start, @dt_end) 

WHILE @i <= @total_days
	INSERT INTO WorkCalendar (PKDate) 
	SELECT CAST(DATEADD(d, @i, @dt_start) as DATE) 

	SET @i = @i + 1

UPDATE WorkCalendar
      calendar_year = YEAR(PKDate), 
      calendar_quarter = DATEPART(q, PKDate),
      calendar_month = DATEPART(m, PKDate), 
      calendar_week_in_year = DATEPART(WK, PKDate), 
      calendar_day_in_year = DATEPART(dy, PKDate), 
      calendar_day_in_week = DATEPART(Weekday, PKDate),
      calendar_day_in_month = DATEPART(d, PKDate),
      day_name_long = datename(weekday, PKDate)

UPDATE WorkCalendar
      is_weekend = CASE DATEPART(weekday, PKDate) WHEN 1 THEN 1 WHEN 7 THEN 1 ELSE 0 END, 
      calendar_quarter_desc =  'Q' + CAST(calendar_quarter as char(1)) + ' ' + CAST(calendar_year as char(4)), 
      calendar_month_name_long = DATENAME(m, PKDate) + ' ' + CAST(calendar_year as CHAR(4)),
      mdy_name_long = DATENAME(m, PKDate) + ' ' + CAST(DATEPART( d, PKDate ) as varchar(2)) + ', '  + CAST(calendar_year as CHAR(4)),
      day_name_short = LEFT(datename(weekday, PKDate),3)
UPDATE WorkCalendar
SET calendar_week_in_month = 
    ELSE 5

-- added
update WorkCalendar
set pay_week_in_month = sq.PAY_WEEK_IN_MONTH
from WorkCalendar
inner join (
                      dense_rank() over (partition by fy_start
                                                    , month(paywk_end)
                                         order by paywk_start)         as PAY_WEEK_IN_MONTH
                    , pkdate
              FROM WorkCalendar AS cal
              cross apply (
                           /* calc the number of days to reach Saturday of this "week" */
                           select abs((datediff(day,6,pkdate) % 7) - 6) 
                          ) ca1 (to_sat)
              cross apply (
                                , dateadd(day,to_sat,pkdate)
                                , dateadd(month,@start_month-1,
                                          case when month(pkdate) >= @start_month
                                                    then cast(dateadd(year,year(pkdate)-1900,0) as date)
                                               else cast(dateadd(year,year(pkdate)-1901,0) as date)
                          ) ca2 (pay_start,pay_end, fy_start)
              cross apply (
                                   case when month(pay_start) < @start_Month and
                                             month(pay_end)   = @start_Month and
                                             datediff(day,fy_start, pay_end) < 8
                                               then fy_start
                                        else pay_start
                                 ,  case when month(pay_start) < @start_Month and
                                             month(pay_end)   = @start_Month and
                                             datediff(day,fy_start, pay_end) > 365
                                               then dateadd(day,-1,dateadd(year,1,fy_start))
                                        else pay_end
                          ) ca3 (paywk_start,paywk_end)
              WHERE cal.pkdate BETWEEN @dt_start AND @dt_end
              ) sq on WorkCalendar.pkdate = sq.pkdate

Open in new window

I am not sure how to generate this report.  Should it be a Matrix with the codes at the top?  Should I group codes together in the ds query, such as the PE3 and PE6 Personal Time?  Perhaps I need the initial query to return results for each week with grouped codes?  

Any suggestions on how to get this up and running would be greatly appreciated!!!
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
You've given the WorkCalendar table, but not the actual data table.

I know what you have in Excel, but presumably have you just transferred the Excel table into SQL Server as is, or have you normalised it, or what?

A bit more information there is essential.

I would suggest starting with a Matrix, and then see if there is anything you need to add afterwards (such as Parameters). Start small, and work up.
HSI_guelphAuthor Commented:
Here are the results from the tables
Work Calendar dataWIP table data
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Are these tables related? I can see you have a "Week number" in "Work Calendar data", but do you have a date there for the start of the Week? That seems to be the thing you are lacking (unless it is hidden from your screenshots).

It can be added with a little SQL if you haven't got it using the WEEKDAY function. It's easier if you have SQL Server 2008R2 or greater, because then you can use the DATE function, i.e.

DATE(year(MyDate), month(MyDate), day(MyDate) - WEEKDAY(MyDate,2) + 1)

Once that is in place, so do the Matrix, with Service going across, and Week Ending going down.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

HSI_guelphAuthor Commented:
No there is no start of the week but should I put a start of the week or end of the week?  This was originally set up because I was going to make a report that would show the hours per week of the month so dividing the weeks up where one month ends and another begins was a goal.  We are using SQL Server 2008 R2 so I can add that column to the table. I'm just not sure how to get the rows to group by week.  Does this have to be done in the query or can it be done in the row groups pane?
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Two answers:

1. Yes, it can be done in the row groups pane. However, you will need to pull in all the data from SQL Server for SSRS to give you the report you need, and that will increase network traffic, processor usage etc.
2. You can do it in the query if you want, which will reduce the amount of data you need.

So, either way.

Start of the week or the end of the week? Your initial spreadsheet using week ending dates, so if you are trying to recreate that, using W/E dates seems sensible.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
HSI_guelphAuthor Commented:
Got something working for now but I'll continue to work with it to achieve the result I want.  Thanks for helping me work through this!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.