SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


How to create a summary for a dataset


How to create a summary for a dataset

Author
Message
NineIron
NineIron
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9208 Visits: 843

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?


NineIron
NineIron
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9208 Visits: 843
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')



jasona.work
jasona.work
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38586 Visits: 15460
So my first question to you, what have you tried so far? What sort of a query have you tried to accomplish this with?
NineIron
NineIron
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9208 Visits: 843
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.
jasona.work
jasona.work
SSC-Dedicated
SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)SSC-Dedicated (38K reputation)

Group: General Forum Members
Points: 38586 Visits: 15460
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;

NineIron
NineIron
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9208 Visits: 843
Thanks but, I know how to do that. I need assistance with adding another matrix then, aggregating the dataset.
hcDBA
hcDBA
Old Hand
Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)Old Hand (373 reputation)

Group: General Forum Members
Points: 373 Visits: 447
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.
NineIron
NineIron
SSCrazy Eights
SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)SSCrazy Eights (9.2K reputation)

Group: General Forum Members
Points: 9208 Visits: 843
Thanx. That's exactly what I needed.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum








































































































































































SQLServerCentral


Search