types and hours. Each leave type record is unique(sicks on record and
vacation another). 2 fields of each record,I group on (Employee and
TimePeriod) and the Leave type hours I want to display in separate columns.
What I am trying to do is group by Employee with a page break after each
employee(this was easy), then group by period(same page, this was done too),
but I also need to put the hours in different columns depending on the Leave
type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
CREATED.
i.e)
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 8
1/8/2005-1/15/2005 4 0
BUT INSTEAD I AM GETTING THIS
NAME Time Sick Vacation
Employee Name
1/1/2005-1/8/2005 8 0
1/1/2005-1/8/2005 0 8
1/8/2005-1/15/2005 4 0
1/8/2005-1/15/2005 0 4
I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?Create the two text boxes for Sick and Vacation... For the values use an
expression like
for Sick
=Iif(Fields!LeaveType.Value="Sick",Hours,0)
for Leave textbox
=Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
Hope this helps...
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
too),
> but I also need to put the hours in different columns depending on the
Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||I used the Iif statement you wrote. It works fine, but what I don't want it
to do is to put the next record in the dataset onto the next row. I want the
next record to go into the next column as in previous post example. I only
want the next dataset record to go into the next row if the group changes.
See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values use an
> expression like
> for Sick
> =Iif(Fields!LeaveType.Value="Sick",Hours,0)
> for Leave textbox
> =Iif(Fields!LeaveTypeValue="Vacation",Hours,0)
> Hope this helps...
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees leave
> > types and hours. Each leave type record is unique(sicks on record and
> > vacation another). 2 fields of each record,I group on (Employee and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break after each
> > employee(this was easy), then group by period(same page, this was done
> too),
> > but I also need to put the hours in different columns depending on the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
> >
>
>|||This is a multi-part message in MIME format.
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/plain;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
Create a grouping on Employee and DateRange and drop the Employee name, =date range (week, whatever it's called), and the Sum() for Vacation and =Sick in the Group Footer. Make sure you use the group name as the scope =for the Sum (IOW, =3DSum(Fields!SickTime.Value, "GroupName") where ="GroupName" is the name fo the group).
You won't have any values dropping into the detail rows and you can =place the Employee Name in the group header row if you don't want it to =repeat for each row. Make sure you also sort the data set by Employee =and Time.
HTH.
-- TIM ELLISON
"dillig" <dillig@.discussions.microsoft.com> wrote in message =news:1C382976-466F-4AF5-9688-CCCAEB1460AB@.microsoft.com...
I used the Iif statement you wrote. It works fine, but what I don't =want it to do is to put the next record in the dataset onto the next row. I =want the next record to go into the next column as in previous post example. I =only want the next dataset record to go into the next row if the group =changes. See the example for clarification.
"Wayne Snyder" wrote:
> Create the two text boxes for Sick and Vacation... For the values =use an
> expression like
> for Sick
> =3DIif(Fields!LeaveType.Value=3D"Sick",Hours,0)
> for Leave textbox
> =3DIif(Fields!LeaveTypeValue=3D"Vacation",Hours,0)
> > Hope this helps...
> > -- > Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> > I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> > "dillig" <dillig@.discussions.microsoft.com> wrote in message
> news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
> > I have a dataset that is comprised of several records of employees =leave
> > types and hours. Each leave type record is unique(sicks on record =and
> > vacation another). 2 fields of each record,I group on (Employee =and
> > TimePeriod) and the Leave type hours I want to display in separate
> columns.
> > What I am trying to do is group by Employee with a page break =after each
> > employee(this was easy), then group by period(same page, this was =done
> too),
> > but I also need to put the hours in different columns depending on =the
> Leave
> > type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT =RECORD TO GO
> IN
> > THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I =WANT A NEW
> ROW
> > CREATED.
> > i.e)
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 8
> > 1/8/2005-1/15/2005 4 0
> >
> >
> > BUT INSTEAD I AM GETTING THIS
> > NAME Time Sick =Vacation
> > Employee Name
> > 1/1/2005-1/8/2005 8 0
> > 1/1/2005-1/8/2005 0 8
> > 1/8/2005-1/15/2005 4 0
> > 1/8/2005-1/15/2005 0 4
> > I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE =HELP'?
> >
> > >
--=_NextPart_000_01AB_01C4F4BE.37F23400
Content-Type: text/html;
charset="Utf-8"
Content-Transfer-Encoding: quoted-printable
=EF=BB=BF<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
Create a grouping on Employee and =DateRange and drop the Employee name, date range (week, whatever it's called), and the =Sum() for Vacation and Sick in the Group Footer. Make sure you use the =group name as the scope for the Sum (IOW, ==3DSum(Fields!SickTime.Value, "GroupName") where "GroupName" is the name fo the =group).
You won't have any values dropping into =the detail rows and you can place the Employee Name in the group header row if you =don't want it to repeat for each row. Make sure you also sort the data set by =Employee and Time.
HTH.
-- TIM ELLISON
"dillig"
--=_NextPart_000_01AB_01C4F4BE.37F23400--|||Have you tried using a Matrix instead of a table? Put time in the rows and
leave type in the columns.
--
Cheers,
'(' Jeff A. Stucker
\
Business Intelligence
www.criadvantage.com
---
"dillig" <dillig@.discussions.microsoft.com> wrote in message
news:889F62C5-3617-464D-BCEA-267787AAB37E@.microsoft.com...
>I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate
> columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done
> too),
> but I also need to put the hours in different columns depending on the
> Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO
> IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW
> ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>|||Thanks to all who replied to this post. Creating a second grouping from
timeperiod and putting the totals for the inside group in group2's footer
worked great.
Your guys are awesome.
"dillig" wrote:
> I have a dataset that is comprised of several records of employees leave
> types and hours. Each leave type record is unique(sicks on record and
> vacation another). 2 fields of each record,I group on (Employee and
> TimePeriod) and the Leave type hours I want to display in separate columns.
> What I am trying to do is group by Employee with a page break after each
> employee(this was easy), then group by period(same page, this was done too),
> but I also need to put the hours in different columns depending on the Leave
> type. BUT I DONT WANT IT TO CREATE A NEW ROW. I WANT THE NEXT RECORD TO GO IN
> THE NEXT COLUMN UNTIL THE NEXT GROUP IS ENCOUNTERED AND Then I WANT A NEW ROW
> CREATED.
> i.e)
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 8
> 1/8/2005-1/15/2005 4 0
>
> BUT INSTEAD I AM GETTING THIS
> NAME Time Sick Vacation
> Employee Name
> 1/1/2005-1/8/2005 8 0
> 1/1/2005-1/8/2005 0 8
> 1/8/2005-1/15/2005 4 0
> 1/8/2005-1/15/2005 0 4
> I CANT FOR THE LIFE OF ME FIGURE OUT HOW TO DO THIS. CAN ANYONE HELP'?
>
