locked
Column grouping in Reporting Services Matrix report RRS feed

  • Question

  • User1127983234 posted

    hi all, i have a aproblem designing my sql serever reporting services matrix report, i have a table with following fields:

    • id (int, unique value)
    • date
    • Category
    • problem
    • off-duty (true or false)
    • Referred (true or false)

    for a particualar date, i want to show the total count of records for a particualr Category

    For example, for date "8/10/2009", i want to show the following 

    • total number of records where problem field value is "Malaria"
    • total number of records where off-duty field value is "True"
    • total number of records where referred field value is "True"

    So far i have only managed to get daily totals for only unique categories( Category 1, Category 2, more....) but i would prefer totals further grouped by malaria, off-duties & referred) for a particualr date. Please help.

    I basically want some thing that looks like this.


    Category 1

    Category 2


    Malaria

    Off-duties

    Referred

    Malaria

    Off-duties

    Referred

    8/10/2009

    1

    3

    7

    4

    5

    5

    8/09/2009

    9

    2

    0

    8

    45

    3

    8/08/2009

    0

    0

    1

    0

    0

    0

    8/07/2009

    3

    5

    1

    9

    7

    3

    Monday, August 10, 2009 3:11 AM

All replies

  • User387424128 posted

    Hi nick

    kindly post some sample data to test your requirement

    kindly go through the below link to get best and quick answer in forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537/

    Monday, August 10, 2009 5:52 AM
  • User1127983234 posted
                    ID
    Date Category Problem Off-Duty Referred
    1  
    8/10/2009        
    Category 1 Some Problem TRUE          
    FALSE         
    2  
    8/10/2009        
    Category 2 Malaria FALSE        
    FALSE         
     3  
    8/10/2009        
    Category2 Malaria TRUE          
    TRUE           
    4  
    8/9/2009          
    Category 1 different problem TRUE          
    FALSE         

        with th above data, i would like my matrix report to appear as below


    Category 1

    Category 2


    Malaria

    Off-duties

    Referred

    Malaria

    Off-duties

    Referred

    8/10/2009

    0

    2

    1

    2

    1

    1

    8/09/2009

    0

    1

    0

    0

    0

    0

    as you can see, if the problem field has any other value that is not malaria, then i dont care about it.






    as
































    Monday, August 10, 2009 10:03 AM
  • User1162414655 posted

    did you figure this out, i am trying to do something similar.

    thanks

    Thursday, May 12, 2011 10:21 AM
  • User-830595639 posted

    Hi,

    I think you need to work on query side and then it should be simple on Report side. Following is the query I used to design the Report.

    Select Date,Category,
    (Select COUNT(Problem) From Sample P Where P.Category = M.Category And P.Date = M.Date And Problem = 'Malaria') as Maleria,
    (Select COUNT(OffDuty) From Sample O Where O.Category = M.Category And O.Date = M.Date And OffDuty = 'True') as OffDuty,
    (Select COUNT(Referred) From Sample T Where T.Category = M.Category And T.Date = M.Date And Referred = 'True') as Refered
    from Sample M
    Group By M.Date,M.Category

    Design you Report as following

    1. Date should be Row Group

    2. Category should be Column Group

    3. Rest columns are detail value

    I hope this will help you.

    Thursday, May 26, 2011 5:52 AM
  • User1162414655 posted

    actually it is pretty simple on report side, just got to group it on the ssrs side.

    Tuesday, June 21, 2011 11:52 AM
  • User1162414655 posted

    thanks for your reply though, would keep ur solution in mind for future, thanks.

    Tuesday, June 21, 2011 11:53 AM