October 17, 2017 at 8:20 am
My report consists of a list of patients, location in the hospital, and a column called "TobaccoUseWithin30Days. The values in this column are 'Yes' or 'No'. I need to create a summary by location for the report. In other words how many Yes's and how many No's for each location. Any thoughts?
October 17, 2017 at 8:36 am
Here is some test data.
create table #Test
(
Location varchar(10),
PatientNumber varchar(100),
TobaccoUseInLast30Days varchar(5)
)
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p1','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p2','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p3','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p4','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p5','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p6','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p7','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p8','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p9','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p10','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p11','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p12','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p13','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p14','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p15','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p16','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p17','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p18','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p19','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p20','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p21','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p22','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p23','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p24','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p25','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A2','p26','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p27','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p28','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p29','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p30','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p31','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p32','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p33','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p34','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p35','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p36','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p37','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p39','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p40','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p41','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p42','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p43','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p44','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p45','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p46','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p47','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p48','Yes')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p49','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p50','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p51','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('A5','p52','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('B4','p53','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('B4','p54','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('B4','p55','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('B4','p56','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('B4','p57','No')
insert into #Test(Location, PatientNumber, TobaccoUseInLast30Days) values('B4','p58','No')
October 17, 2017 at 10:59 am
So my first question to you, what have you tried so far? What sort of a query have you tried to accomplish this with?
October 17, 2017 at 11:09 am
I added a Matrix to the report using the same dataset. Then, tried to group by Location and use an expression to get the counts for Yes and No's. I get this error.
The Value expression for the textrun 'Textbox26.Paragraphs[0].TextRuns[0]' has a scope parameter that is not valid for an aggregate function. The scope parameter must be set to a string constant that is equal to either the name of a containing group, the name of a containing region, or the name of a dataset.
I don't understand any of the error.
October 17, 2017 at 11:14 am
My apologies, I didn't notice when I replied that this was in the SSRS forum. I've got a solution but it just generates the data so I don't know if it'll be useful for you or not, I've included the query below:select location
, TobaccoUseInLast30Days
, count(PatientNumber)
from #Test
group by Location, TobaccoUseInLast30Days
order by Location;
October 17, 2017 at 11:18 am
Thanks but, I know how to do that. I need assistance with adding another matrix then, aggregating the dataset.
October 18, 2017 at 11:20 am
Yes, this can be done. This is how I would do it:
1) In your dataset, add 2 Fields (it will prompt you if they are calculated fields or query fields - choose calculated) - call them ResponseYes or ResponseNo (or whatever you want to name them). These will be used to store a 0 or 1 depending on the response, so that they can be added up to get the total number of responses.
2) Under Field Source, click on the formula button and use this formula for ResponseYes:
=IIf(Fields!TobaccoUseInLast30Days.Value="Yes",1,0)
3) Under Field Source, click on the formula button and use this formula for ResponseNo:
=IIf(Fields!TobaccoUseInLast30Days.Value="No",1,0)
4) In your tablix, group by location, then in the group total, you can use the Sum function to add up the counts (I'm assuming your group name is Location if you are grouping by Location):
=Sum(Fields!ResponseYes.Value,"Location")
=Sum(Fields!ResponseNo.Value,"Location")
That should do it - hope that gives you what you need.
October 18, 2017 at 11:37 am
Thanx. That's exactly what I needed.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply