Asked by:
Column grouping in Reporting Services Matrix report

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.CategoryDesign 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