asked
Report Builder grouping and getting row totals
![alt text][1]I am very new to this so please forgive me if I this is a simple question. My group consists of clinic, service, program, protocol, and last name. As an example, the same person (last name) could fall in 2 different groups. One with clinic, service=adult, program, protocol, last name=MILLER. They could also be in clinic, service=child, program, protocol, last name MILLER. When this occurs, I get 0 for the row total. As long as the combination is unique in all respects, the total is correct. I have researched and researched and I still cannot seem to find a solution. I saw something about entities that could work but then i could not see how to do this?? Any ideas on how to solve this??? Thanks for any help someone could provide. [1]: /storage/temp/2948-example.jpg
You have some answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
Hi You might want to check your query please. Take the results into excel and try to calculate the total. You might be able to find the solution... or else ping the query here with some sample data . Thanks Lokesh
The query is ... declare @cspp_clinic char(12) declare @cspp_service char(12) declare @cspp_program char(12) declare @cspp_protocol char(12) declare @start_date datetime declare @end_date datetime set @cspp_clinic = 'ALL' set @cspp_service = 'ALL' set @cspp_program = 'OP' set @cspp_protocol = 'ALL' set @start_date = '20150801' set @end_date = '20150804' select newid(), pct.patient_id, pct.clinic_id, Case When @cspp_service = 'ALL' then 'ADULT/C&Y' Else pct.service_id End as 'service_id', Case When @cspp_program = 'INTAKE' and pct.program_id = 'INTAKE' then 'INTAKE' Else pct.program_id End as 'program_id', Case When @cspp_program = 'INTAKE' and pct.program_id = 'INTAKE' then 'GENERAL' Else pct.protocol_id End as 'protocol_id', pct.appt_date, pct.proc_code, pct.proc_duration, Case When pct.protocol_id = 'CM' and pct.status = 'CO' then ceiling((cast(pct.proc_duration as decimal))/15) Else 0 End as CM_CO, Case When pct.protocol_id = 'CM' and pct.status = 'CA' then 0 Else 0 End as CM_CA, Case When pct.protocol_id = 'CM' and pct.status = 'NS' then 0 Else 0 End as CM_NS, Case When pct.program_id = 'INTAKE' and pct.protocol_id = 'GENERAL' and pct.status = 'CO' then CAST(1.25 as decimal(12,2) ) When pct.protocol_id = 'THERAPY' and pct.proc_code = 'TXGROUP' and pct.status = 'CO' then CAST(ROUND(1*1.0/4,2) as decimal(12,2) ) When pct.protocol_id = 'THERAPY' and pct.proc_code <> 'TXGROUP' and pct.status = 'CO' then 1 Else 0 End as TX_CO, Case When pct.program_id = 'INTAKE' and pct.protocol_id = 'GENERAL' and pct.status = 'CA' then 1.25 When pct.protocol_id = 'THERAPY' and pct.proc_code = 'TXGROUP' and pct.status = 'CA' then CAST(ROUND(1*1.0/4,2) as numeric(36,2) ) When pct.protocol_id = 'THERAPY' and pct.proc_code <> 'TXGROUP' and pct.status = 'CA' then 1 Else 0 End as TX_CA, Case When pct.program_id = 'INTAKE' and pct.protocol_id = 'GENERAL' and pct.status = 'NS' then 1.25 When pct.protocol_id = 'THERAPY' and pct.proc_code = 'TXGROUP' and pct.status = 'NS' then CAST(ROUND(1*1.0/4,2) as numeric(36,2) ) When pct.protocol_id = 'THERAPY' and pct.proc_code <> 'TXGROUP' and pct.status = 'NS' then 1 Else 0 End as TX_NS, Case When pct.program_id = 'CRISIS' and pct.status = 'CO' then 1 Else 0 End as CRISIS_CO, Case When pct.program_id = 'CRISIS' and pct.status = 'CA' then 1 Else 0 End as CRISIS_CA, Case When pct.program_id = 'CRISIS' and pct.status = 'NS' then 1 Else 0 End as CRISIS_NS, Case When pct.program_id = 'SR' and pct.status = 'CO' then 1 Else 0 End as SR_CO, Case When pct.program_id = 'SR' and pct.status = 'CA' then 1 Else 0 End as SR_CA, Case When pct.program_id = 'SR' and pct.status = 'NS' then 1 Else 0 End as SR_NS, Case When pct.protocol_id = 'MEDMGMT' and pct.status = 'CO' then 1 Else 0 End as MEDMGMT_CO, Case When pct.protocol_id = 'MEDMGMT' and pct.status = 'CA' then 1 Else 0 End as MEDMGMT_CA, Case When pct.protocol_id = 'MEDMGMT' and pct.status = 'NS' then 1 Else 0 End as MEDMGMT_NS, Case When pct.protocol_id = 'PSR' and pct.status = 'CO' then 1 Else 0 End as PSR_CO, Case When pct.protocol_id = 'PSR' and pct.status = 'CA' then 1 Else 0 End as PSR_CA, Case When pct.protocol_id = 'PSR' and pct.status = 'NS' then 1 Else 0 End as PSR_NS, s.lname, pct.status from patient_clin_tran pct inner join staff s on s.staff_id = pct.clinician_id where pct.appt_date >= @start_date and pct.appt_date <= @end_date and pct.program_id <> 'IP' and pct.hosp_status_code <> 'IP' and (pct.status = 'NS' or pct.status = 'CO' or pct.status = 'CA' ) and (pct.clinic_id = @cspp_clinic or @cspp_clinic = 'ALL') and (pct.service_id = @cspp_service or @cspp_service = 'ALL') and (pct.program_id = @cspp_program or @cspp_program = 'ALL') and (pct.protocol_id = @cspp_protocol or @cspp_protocol = 'ALL') /* and s.lname = 'Robinson' and pct.status = 'CO' */ order by appt_date, clinic_id, service_id, program_id, protocol_id, s.lname, status ![alt text][1] [1]: /storage/temp/2951-capture.png The problem is when the I have: Clinic Service Program Protocol Lname Anderson adult op therapy miller Anderson c&y op therapy miller Because it is grouping on lname (the last field of the group) and there are 2 miller's the totals of the row are incorrect or blank. It is unique when all 5 fields are considered. I need it to total the unique row described by all 5 fields: clinic_id, service_id, program_id, protocol_id, and lname. I am getting desperate ... any help would be appreciated! Thanks.
Why dont you create a new column say fullname - firstname+lastname and group it using this. Just for displaying add the lastname on report.
I think I understand your scenario, please shout if not. Here goes the explanation:- I assume the resultset set would be something like following :- ![alt text][1] [1]: /storage/temp/2958-test1.png So your final query should be something like :- select Clinic,[service],Program,Protocol,lname,SUM(value) as value from haji_test group by Clinic,[service],Program,Protocol,lname That means you need to calculate the aggregated value at the query level to find cummulative sum of non unique records.
22 People are following this question.
