Getting the correct totals

I am trying to create a report and get a specific total, but it is not working correctly. I have a group of workers and I am able to count how many Work Orders they have worked on in a given month, however when I try to get a total of how many they completed, I run into a problem because it is counting every date that they worked as 1 instead of counting the # of work orders. Someone mentioned using a Global variable, but I am unsure how to use this.Try a running total, or if your count is in your details area, you should be able to right click it and insert a summary (group level if desired and report level)

Getting summary table with category based totals

I have a table containing data as illustrated below.

create table #temp(
ProjectNumber varchar(50),
ProjectName varchar(50),
ProjectCategory varchar(50),
ProjectDepartment varchar(50),
ProjectDivision varchar(50),
Hours int,
Date datetime,
Resource varchar(50)
)

Insert Into #temp Values('Project1', 'Rocket', 'ShortTerm', 'Engineering', 'Research', 5,'01-01-2007', 'John')
Insert Into #temp Values('Project1', 'Rocket', 'ShortTerm', 'Engineering', 'Research', 3,'01-02-2007', 'Mark')
Insert Into #temp Values('Project1', 'Rocket', 'ShortTerm', 'Engineering', 'Research', 8,'01-02-2007', 'John')
Insert Into #temp Values('Project1', 'Rocket', 'ShortTerm', 'Engineering', 'Research', 8,'01-02-2007', 'Mark')

Insert Into #temp Values('Project2', 'LaunchPad', 'MediumTerm', 'Constructions', 'Constructions and Infrastructure', 3,'01-01-2007', 'Mark')
Insert Into #temp Values('Project2', 'LaunchPad', 'MediumTerm', 'Constructions', 'Constructions and Infrastructure', 4,'01-02-2007', 'John')
Insert Into #temp Values('Project2', 'LaunchPad', 'MediumTerm', 'Constructions', 'Constructions and Infrastructure', 5,'01-02-2007', 'John')

Insert Into #temp Values('Project3', 'TakeOff', 'LongTerm', 'Operations', 'Research', 5,'01-01-2007', 'John')
Insert Into #temp Values('Project3', 'TakeOff', 'LongTerm', 'Operations', 'Research', 5,'01-02-2007', 'Mark')
Insert Into #temp Values('Project3', 'Takeoff', 'LongTerm', 'Operations', 'Research', 5,'01-02-2007', 'John')

I would like an output that displays :

ProjectNumber, ProjectName, ProjectCategory, ProjectDivision, Total Hours on Day 1, Total Hours on Day 2, Total Hours on Day3

And vertically at the end of each division to have a total of all the hours spent within the division like so:

Project1, Rocket, ShortTerm, Engineering, Research, 5, 19
Project3, TakeOff, LongTerm, Operations, Research, 5, 10
DivisionTotals Research, 10, 29
Project2, LaunchPad, MediumTerm, Constructions, Constructions and Infrastructure, 3, 9
DivisionTotals Constructions and Infrastructure, 3, 9

Could you enlighten me as to how I can do this?

I do know how to generate the data for the Day1, Day2, etc..columns.
And I had a pretty strong feeling I could get the vertical division totals by using a Group By Clause with Rollup. However my output continues on having multiple entries for each project.

Help?

Try:

Code Snippet

createtable #t(

ProjectNumber varchar(50),

ProjectName varchar(50),

ProjectCategory varchar(50),

ProjectDepartment varchar(50),

ProjectDivision varchar(50),

Hours int,

Date datetime,

Resource varchar(50)

)

go

InsertInto #t Values('Project1','Rocket','ShortTerm','Engineering','Research', 5,'01-01-2007','John')

InsertInto #t Values('Project1','Rocket','ShortTerm','Engineering','Research', 3,'01-02-2007','Mark')

InsertInto #t Values('Project1','Rocket','ShortTerm','Engineering','Research', 8,'01-02-2007','John')

InsertInto #t Values('Project1','Rocket','ShortTerm','Engineering','Research', 8,'01-02-2007','Mark')

InsertInto #t Values('Project2','LaunchPad','MediumTerm','Constructions','Constructions and Infrastructure', 3,'01-01-2007','Mark')

InsertInto #t Values('Project2','LaunchPad','MediumTerm','Constructions','Constructions and Infrastructure', 4,'01-02-2007','John')

InsertInto #t Values('Project2','LaunchPad','MediumTerm','Constructions','Constructions and Infrastructure', 5,'01-02-2007','John')

InsertInto #t Values('Project3','TakeOff','LongTerm','Operations','Research', 5,'01-01-2007','John')

InsertInto #t Values('Project3','TakeOff','LongTerm','Operations','Research', 5,'01-02-2007','Mark')

InsertInto #t Values('Project3','Takeoff','LongTerm','Operations','Research', 5,'01-02-2007','John')

go

select

ProjectNumber,

ProjectName,

ProjectCategory,

ProjectDivision,

sum(casewhen [Date] ='20070101'then Hours else 0 end)as'20070101',

sum(casewhen [Date] ='20070102'then Hours else 0 end)as'20070102',

sum(casewhen [Date] ='20070103'then Hours else 0 end)as'20070103'

from

#t

groupby

ProjectDivision,

ProjectNumber,

ProjectName,

ProjectCategory withrollup

having

grouping(ProjectNumber)+grouping(ProjectName)+grouping(ProjectCategory)+grouping(ProjectDivision)in(0, 3)

andgrouping(ProjectDivision)= 0

orderby

grouping(ProjectDivision),

ProjectDivision,

grouping(ProjectNumber),

ProjectNumber,

grouping(ProjectName),

ProjectName,

grouping(ProjectCategory),

ProjectCategory

go

droptable #t

go

AMB

|||
Thanks Hunchback.

Getting sub totals in groups

Hi,
i have a table and a group in it..
i need to have subtotal column..
Im using the cost filed for grouping ...
i need to sum this filed and show it...
it seems that when i use the sum function alone,it sums all the cost values
ignoring the group
example
Project Reg ot Cost total cost
1 2 0 4 8
2 3 0 5 15
totals 5 o 13
isince there are 2 projects with cost 4 ,it sums incorrectly as 13,i
expect it to be 9...
any help please...
Thanks,Try adding a scope to your aggregate =Sum(Fields!Cost.Value, "Group_Name")
--
Douglas McDowell douglas@.nospam.solidqualitylearning.com
"CCP" <CCP@.discussions.microsoft.com> wrote in message
news:D582E6A9-B408-458C-AFC1-EFE709B0BD1D@.microsoft.com...
> Hi,
> i have a table and a group in it..
> i need to have subtotal column..
> Im using the cost filed for grouping ...
> i need to sum this filed and show it...
> it seems that when i use the sum function alone,it sums all the cost
> values
> ignoring the group
> example
> Project Reg ot Cost total cost
> 1 2 0 4 8
> 2 3 0 5 15
> totals 5 o 13
> isince there are 2 projects with cost 4 ,it sums incorrectly as
> 13,i
> expect it to be 9...
> any help please...
> Thanks,
>|||CCP,
If you've got you're group set properly (to project) you shouldn't need
to worry about scope. The sum should be in the group footer. In fact
you shouldn't have to specify 'Sum' at all, just dragging the Cost
field from the field list into a cell on the Group footer will sort the
syntax out for you.
What you are getting looks like you have it set in the TABLE footer,
not the GROUP footer.
Chris
Douglas McDowell wrote:
> Try adding a scope to your aggregate =Sum(Fields!Cost.Value,
> "Group_Name") --
> Douglas McDowell douglas@.nospam.solidqualitylearning.com
>
> "CCP" <CCP@.discussions.microsoft.com> wrote in message
> news:D582E6A9-B408-458C-AFC1-EFE709B0BD1D@.microsoft.com...
> > Hi,
> > i have a table and a group in it..
> > i need to have subtotal column..
> > Im using the cost filed for grouping ...
> > i need to sum this filed and show it...
> > it seems that when i use the sum function alone,it sums all the
> > cost values
> > ignoring the group
> > example
> > Project Reg ot Cost total cost
> > 1 2 0 4 8
> > 2 3 0 5 15
> >
> > totals 5 o 13
> >
> > isince there are 2 projects with cost 4 ,it sums incorrectly
> > as 13,i
> > expect it to be 9...
> >
> > any help please...
> >
> > Thanks,
> >

getting similar fields in same output row for grouping totals

I have a table similar to the below:

Field1 Field2
EXE1 Age
EXE2 Child Under 10
EXE3 Not a citizen
WEB1 Cares for a child under 10
WEB2 Be a US Citizen

and I am getting the totals for the records grouped by Field1 - not a
problem. So, output is something like:

Field2 Total %
Age 10 .4
Child under 10 15 .5
Not a citizen 15 .5
Cares for < 10 20 .6
Be a US citizen 25 .7

However, IF there is an EXE# basically the same as a WEB#, then I need
to put them together in my report output, like so:

Field EXEtotal WEBtotal Total %
Age 10 0 10 .4
Child <10 15 20 35 1.0
Citizen 15 25 40 1.1

So, I'm getting the data I need returned, but don't know how to sort it
in my report to give me the new output I need.

I think I need to do this in my report output rather than my SQL
because I need to pull the individual information. Is there a way to
say "if Field 1 = 'WEB2' and Field 1 = 'EXE3', list in same row, then
total? I just don't know how to get them in the same row...

Thanks!

I would create a new column in the table to keep track of the parent group, ie.. your Web1 and Exe1 would be in the same Parent group.

Let me know if you can't make sense of it.

sql