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

Answer 1

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/

Answer 2

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

Answer 3

User1162414655 posted

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

thanks

Answer 4

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.

Answer 5

User1162414655 posted

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

Answer 6

User1162414655 posted

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