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.

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!!!

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.

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

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?

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.

Featured Post

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes. We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…