SSRS SQL help with multiple tables showing all employees
-
I have an employee summary report that works well but doesn't show employees when they haven't submitted hours for the defined time period.
SELECT EmpCustom.EmpCustValue AS Team, Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, WIP.WCltName AS Project, WIP.WSCDesc AS Service,ISNULL(WIP.Whours, 0) AS Hours, WIP.Wfee AS Amount, WIP.Wdate AS Date, Department.DeptName AS TeamDept, WIP.WCodeCat AS ServiceCategory,
EmpCustom.EmpCustValue AS TeamColour, Employee.Emplevel AS Level
FROM WIP RIGHT OUTER JOIN
Employee ON WIP.WempID = Employee.ID INNER JOIN
Department ON Employee.Empdept = Department.DeptID INNER JOIN
EmpCustom ON Employee.ID = EmpCustom.EmpCustEmpId
WHERE (WIP.Wdate BETWEEN @StartDate AND @StartDate + 6) AND (WIP.WCodeCat IN ('Admin', 'Bill')) AND (Employee.ID > 1) AND
(EmpCustom.EmpCustValue = @TeamColour)
GROUP BY EmpCustom.EmpCustValue, Employee.Emplevel, Employee.Empfname, WIP.WEmpLName, WIP.WCltName, WIP.WSCDesc, WIP.Wdate, WIP.Whours, WIP.Wfee,
Department.DeptName, WIP.WCodeCat
ORDER BY TeamColour, Level, Employee, ServiceCategory, Date
I need all the employees in table Employee with a Empstatus of Active to show up on my summary even if they don't have any records of time in WIP table. I would think that all I should have to do is change WIP INNER JOIN Employee to WIP RIGHT OUTER JOIN Employee to show all the records from the right table (Employee) even if there are no matching records in WIP. I've posted repeatedly on another forum and have had lots of help but no luck. I want the employees who have not entered their time to show up with 0 for hours and I'll highlight them red to stand out. Team leaders will see the report and know who needs to enter time and can remind those employees to post their time. Here is my current report design and layout.
If I can get one query to work great. If I can use two datasets awesome! If I can find a way to put a list grouped by team with tables for details (multiple datasets) perfect! I am new to SSRS but worked with jsp/sql so I don't need step-by-step but an experienced opinion of how I should do this. I can't used stored procedures at this time and our database is relational (and honestly a mess). We have a virtual MS SQL Server 2005 server holding the database and I'm developing on MS SQL Server 2008 R2 virtual computer using BIDS. I need something quick and easy to show the power of SSRS and can refine my knowledge and skill with time.
-
off the top of my head this should do it
SELECT EmpCustom.EmpCustValue AS Team, Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, WIP.WCltName AS Project, WIP.WSCDesc AS Service,
ISNULL(WIP.Whours, 0) AS Hours, WIP.Wfee AS Amount, WIP.Wdate AS Date, Department.DeptName AS TeamDept, WIP.WCodeCat AS ServiceCategory,
EmpCustom.EmpCustValue AS TeamColour, Employee.Emplevel AS Level
FROM WIP RIGHT OUTER JOIN
Employee ON WIP.WempID = Employee.ID INNER JOIN
Department ON Employee.Empdept = Department.DeptID INNER JOIN
EmpCustom ON Employee.ID = EmpCustom.EmpCustEmpId
and (WIP.Wdate BETWEEN @StartDate AND @StartDate + 6) AND (WIP.WCodeCat IN ('Admin', 'Bill'))
WHERE (Employee.ID > 1) AND (EmpCustom.EmpCustValue = @TeamColour)
and WIP.WempID is null
GROUP BY EmpCustom.EmpCustValue, Employee.Emplevel, Employee.Empfname, WIP.WEmpLName, WIP.WCltName, WIP.WSCDesc, WIP.Wdate, WIP.Whours, WIP.Wfee,
Department.DeptName, WIP.WCodeCat
ORDER BY TeamColour, Level, Employee, ServiceCategory, Date
you need to move any criteria for WIP that is in the where clause into the join and then check for NULL in one of the WIP fields
MVDBA
-
Is there a way I can create a template (ie. declare the stuff from wip like hours and dates) and test with an if statement where if the employee exists in WIP (for the date range) then fill in hours, date, etc., and if the employee doesn't then skip to next employee/row (because I would set the employee details from the employee table before testing for WIP)? But then would that work with multiple records in the WIP? I want to show the sum but allow the user to expand to see the dates they worked and what they booked the time as (service rendered). I'd love to use a stored procedure but my supervisor does not have the background to know how to give me permissions to make stored procedures plus he's nervous about what might happen to the data in the database as we don't have a seperate development side for me to play with.
Or is that what the ISNULL( ,0) does? Is there a way to do that for dates?
-
ljvarga (5/11/2012)
Is there a way I can create a template (ie. declare the stuff from wip like hours and dates) and test with an if statement where if the employee exists in WIP (for the date range) then fill in hours, date, etc., and if the employee doesn't then skip to next employee/row (because I would set the employee details from the employee table before testing for WIP)? But then would that work with multiple records in the WIP?Yes, but I think that would involve changing the code to a loop/cursor based solution, I think you're better off with the set-based solution you have now.
ljvarga (5/11/2012)
I want to show the sum but allow the user to expand to see the dates they worked and what they booked the time as (service rendered). I'd love to use a stored procedure but my supervisor does not have the background to know how to give me permissions to make stored procedures plus he's nervous about what might happen to the data in the database as we don't have a seperate development side for me to play with.Not sure I'm right on what you're after, but typically in SSRS for something like this I'd return all the detailed data from the query to the report and then use Grouping & Visibilities in the tablix to give you this functionality.
-
ljvarga (5/11/2012)
Is there a way I can create a template (ie. declare the stuff from wip like hours and dates) and test with an if statement where if the employee exists in WIP (for the date range) then fill in hours, date, etc., and if the employee doesn't then skip to next employee/row (because I would set the employee details from the employee table before testing for WIP)? But then would that work with multiple records in the WIP? I want to show the sum but allow the user to expand to see the dates they worked and what they booked the time as (service rendered). I'd love to use a stored procedure but my supervisor does not have the background to know how to give me permissions to make stored procedures plus he's nervous about what might happen to the data in the database as we don't have a seperate development side for me to play with.Or is that what the ISNULL( ,0) does? Is there a way to do that for dates?
where to start ?
firstly - i'm sorry, but i don't understand your question based on
the fact that i have no sample data and no tables to query against
you haven't really explained too clearly what you are trying to acheive that wasn't defined in the original post (i think my last response answered the original question)
as for stored procedures - well frankly if your manager doesn't know how to give you permissions to add stored procedures then he should not be in charge of a sql server.
and if you're working on a live server and not a dev box then why not install sql express on your laptop/dev machine and do it there - you don't need all the data , just enough for you to be able to put some sample data in and know what you are trying to acheive - and then be able to prove your solution works.....
when you get theere you can prove to your manager that it is fit for purpose - to be honest i'm not keen to help anyone who is playing on a live SQL server as it only makes me culpable if you follow my instructions and get it wrong
MVDBA
-
(small background info to see where I'm coming from)
Right now I'm only doing Select queries to the database (is it possible to screw up the live data with a select? I didn't think so.) The database is set up to work with the accounting/time entry program the company is using. The database is not optimal and my boss wants reports to be highly customizable. Unfortunately this is what I've got to work with. I'm here on contract and my future here probably relies a big part on the reports I can develop and their usefullness so I'm kinda stuck and have to work with what's here. Its a small company with 40 people so my IT supervisor is required to handle everything but an outside company has set up the database and helped with servers so I think he's also having to learn new tricks.
I worked as a Web Developer using JSP and SQL on tomcat (this was a few years ago). I wrote stored procedures and gave them to the Web Master who would create them on the server for me to use. I'd use JSP to structure what I got back, programming statements, assigning meaningful information to IDs, looping to display only parts of the data, that sort of thing. I'm adapting to new technology but sometimes my brain goes back to what I've known in the past.
But basically I'm stuck with what's here and except for a couple of issues it's working out for me. So I swear I would not hold anyone responsible for any advice I take here or anywhere. I am solely responsible for my attempts at reporting services. Having said that I don't need anyone to write the code for me, I just need to know what to do. Like if ISNULL(whours,0) tests the field whours and finds a null then it puts 0 in as default. Can I do this with dates? Can I put an if statement (and where) to test for null in WIP in nested select statements? I could get all employees with hours using JOINS but when I tried to grab dates then I lost those employees. I can do the research but I'd need a nudge in the right direction.
What I'm trying to do is to display all the employees, grouped by Teams, and the hours they worked within a given period (the default range is the previous pay period). My boss wants to see how many hours an employee logged and what they were doing (service codes, descriptions) and the totals by employee and team. He also wants to be able to see employees who have not posted their hours for that week so they know who's not posted time in the system (there are a couple of people who take a longer time to post than others).
I have two main tables: Employee and WIP (rest are to convert IDs to meaningful words).
Table Employee
Table WIP
Any help is greatly appreciated and no one is liable for advice given!!!
-
ljvarga (5/11/2012)
(small background info to see where I'm coming from)Right now I'm only doing Select queries to the database (is it possible to screw up the live data with a select? I didn't think so.)
The data, no. But you can hose the server if it's a bad query 🙂
Like if ISNULL(whours,0) tests the field whours and finds a null then it puts 0 in as default. Can I do this with dates?
Yep!
Can I put an if statement (and where) to test for null in WIP in nested select statements?
Not exactly. You're heading towards row-by-row with that, bad performance wise and overly complicated for this I think.
I could get all employees with hours using JOINS but when I tried to grab dates then I lost those employees. I can do the research but I'd need a nudge in the right direction.
Right idea using an outer join, but as Michael said, you need to remove and criteria based on the WIP table from the WHERE clause to the join condition. Think the query will read better as a LEFT join too, something like: (untested)
SELECT EmpCustom.EmpCustValue AS Team, Employee.Empfname + ' ' + WIP.WEmpLName AS Employee, WIP.WCltName AS Project, WIP.WSCDesc AS Service,
ISNULL(WIP.Whours, 0) AS Hours, WIP.Wfee AS Amount, WIP.Wdate AS Date, Department.DeptName AS TeamDept, WIP.WCodeCat AS ServiceCategory,
EmpCustom.EmpCustValue AS TeamColour, Employee.Emplevel AS Level
FROM
Employee INNER JOIN
Department ON Employee.Empdept = Department.DeptID INNER JOIN
EmpCustom ON Employee.ID = EmpCustom.EmpCustEmpId
LEFT OUTER JOIN WIP ON WIP.WempID = Employee.ID AND (WIP.Wdate BETWEEN @StartDate AND @StartDate + 6) AND (WIP.WCodeCat IN ('Admin', 'Bill')) AND (Employee.ID > 1)
WHERE (EmpCustom.EmpCustValue = @TeamColour)
GROUP BY EmpCustom.EmpCustValue, Employee.Emplevel, Employee.Empfname, WIP.WEmpLName, WIP.WCltName, WIP.WSCDesc, WIP.Wdate, WIP.Whours, WIP.Wfee,
Department.DeptName, WIP.WCodeCat
ORDER BY TeamColour, Level, Employee, ServiceCategory, Date
Hope that helps.
G
-
Thank you so much for the replies! Unfortunately the server is down (I didn't hose it honest!!! :-P) so I might have to wait until Monday to give this a go. I wonder, is it possible to create an Access database to mimic the SQL server so I can play around at home.
-
No problem, let us know how you get on!
You could use Access, I think it'd make more sense to grab SQL Server Express (it's free!) and play with that, much more applicable.
Appreciate there'd be a learning curve there though.
[/URL]Viewing 9 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply