Group Totals

[R7Dave]

Hello

I am trying to create a report that will show all of the requests made by a manager and their current status. At the end I want to total the # for each status

My SQL looks like this

select upper(ac.contact) as 'Manager',
AL2.Lookup_Description, count(*) as 'Count'
from AT_date AD
join AT_request AR 
	on AD.projectno=AR.projectno
	and AD.requestno=AR.requestno
left join AT_Lookup AL2
	on AR.Status = AL2.Lookup_value
	and AL2.fieldname='Date_Type'	
join AT_Contact AC
on AC.projectno=AR.projectno
	and AC.requestno=AR.requestno
	and AC.contact_type='MM'

where AD.Date_type='SO'
and AD.date_enter >= @STARTDATE
and AD.date_enter <= @ENDDATE
group by upper(ac.contact), Lookup_Description
order by manager, case LOOKUP_Description
when 'In Production' then 'A'
when 'Dev & Test Complete' then 'B'
when 'In Development' then 'C'
else LOOKUP_Description end

Which gives me data that looks like...

BJONES | New Request | 5
BJONES | Waiting on Requestor | 3
JSMITH | New Request | 2
JSMITH | Waiting on Requestor | 4

The purpose of this report is to show the volume and current status of requests by a manager submited within a date range

I have the report grouped by manager

I would like to put a total for each status at the end of the report - how can I do this? - I have tried groups and Switch statements but I think I am going the wrong way - any help is appreciated.

Thanks in advance
Dave

 

[RiverGuy]

Just add a new Table below your current table, using the same DataSet, and group by status.

 

[R7Dave]

Thank you - that worked nicely.

For some reason, the second table is ignoring the sort order - the first table is working fine - I changed the Table2.datasetname = dataset1 which had no affect.

Please let me know if you have ideas for that one, thanks again.

Dave

 

[RiverGuy]

You can sort on many different levels. Try sorting on the second table itself. The sort should be in the properties of the table.