[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


How can I have 2 groups in a Matrix with different time range?

Posted on 2011-04-22
Medium Priority
Last Modified: 2012-05-11
I need a matrix for 2 groups (Department - ‘Nursing’ and ‘Other’) What I am doing is showing the Days/Hours they worked for the time frame they select. If they fall into the Nursing Group I need the hours Grouped into 3 shifts but if they fall into the ‘Other’ group the hours are grouped by the day only. Each group needs subtotals and I need a grand total for both Groups.

I also need 2 empty rows after each Group subtotal which will be used for calculations once the report is exported to Excel.

Please see attached spreadsheet.

Question by:JS56
LVL 22

Accepted Solution

Nico Bontenbal earned 2000 total points
ID: 35455360
Start with your query. Use a case statement that makes the shift always shift 2 when the department is other. This piece of code shows how you could do this.
--create table and fille with sample data
create table #hours(Department varchar(20), HourType varchar(20), Shift varchar(10), Hours decimal(6,2))
insert into #hours values('Nursing','MDS','Shift1',8)
insert into #hours values('Nursing','MDS','Shift2',0.03)
insert into #hours values('Nursing','RN','Shift1',8)
insert into #hours values('Nursing','RN','Shift2',11.45)
insert into #hours values('Nursing','RN','Shift3',10)
insert into #hours values('Other','Don','Shift1',2)
insert into #hours values('Other','Don','Shift1',6)
insert into #hours values('Other','Housekeeping','Shift1',5.5)
insert into #hours values('Other','Housekeeping','Shift2',20)
insert into #hours values('Other','Social Services',null,6)

--actual example
    case Department when 'Nursing' then Shift else 'Shift2' end as Shift, 
    SUM([Hours]) as Hours
group by
    case Department when 'Nursing' then Shift else 'Shift2' end 

drop table #hours

Open in new window

If you create a report based on a query like this the hours for the other departments will always end up in the shift2 column.
Next you can use  expressions in SSRS that hide border lines and captions to replicate the design of the Excel sheet as much as possible.
(Sub)totals and blank rows are basic tablix functionality.
LVL 101

Expert Comment

ID: 35859764
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Hi, I have heard from my friends that it’s not possible to create Label Printing report using SSRS. I am amazed after hearing this words not possible in SSRS. I googled lot and found that it is possible to some of people know about the Report Bui…
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…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Loops Section Overview
Suggested Courses
Course of the Month18 days, 23 hours left to enroll

834 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