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

Posted on 2011-04-22
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

    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 100

    Expert Comment

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

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    Join & Write a Comment

    After much searching across the internet I have found that you could not set the name of the file you were attaching to dynamic report subscriptons in Microsoft Reporting Services. I did manage to find one article showing you how your could make a s…
    Introduction Earlier I wrote an article about the new lookup functions ( that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
    Hi everyone! This is Experts Exchange customer support.  This quick video will show you how to change your primary email address.  If you have any questions, then please Write a Comment below!
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    23 Experts available now in Live!

    Get 1:1 Help Now