Hi I've been puzzling over this one for ages, Imagine a dataset like:
ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
1-Jan 07:00 1-Jan 15:00 00001 Fred S
2-Jan 07:00 2-Jan 15:00 00001 Fred S
3-Jan 07:00 3-Jan 15:00 00001 Fred
4-Jan 07:00 4-Jan 15:00 00001 Fred S
5-Jan 07:00 5-Jan 15:00 00001 Fred S
6-Jan 07:00 6-Jan 15:00 00001 Fred
1-Jan 07:00 1-Jan 15:00 00002 Jane
2-Jan 07:00 2-Jan 15:00 00002 Jane S
3-Jan 07:00 3-Jan 15:00 00002 Jane S
4-Jan 07:00 4-Jan 15:00 00002 Jane S
5-Jan 07:00 5-Jan 15:00 00002 Jane S
6-Jan 07:00 6-Jan 15:00 00002 Jane
I would like to group by consecutive absence codes to generate a report
like:
Fred had 2 occurrences of AbsenceCode S
1-Jan 07:00 to 2-Jan 15:00
4-Jan 07:00 to 5-Jan 15:00
Jane had 1 occurrence of AbsenceCode S
2-Jan 07:00 to 5-Jan 15:00
I have many more codes, and many more records, How can I group by each
occurrence like this?
I've tried using RunningValue with Previous(AbsenceCode) but RS won't allow
nested aggregate functions.
Thanks in advance,
N.Create two groups - one on Name and another one on AbsenceCode. See the
attached example that goes against local pubs database.
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>state</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>city</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextDecoration>Underline</TextDecoration>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>address</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="state">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>state</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="city">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>city</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="au_fname">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>au_fname</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!au_fname.Value & " " &
Fields!au_lname.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="address">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>address</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!address.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>pubs</DataSetName>
<Width>5.375in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!state.Value & " [" &
RunningValue(Fields!state.Value, Count, "table1_Group1") & "
authors]"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!state.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!city.Value & " [" &
RunningValue(Fields!city.Value, Count, "table1_Group2") & "
authors]"</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<FontSize>8pt</FontSize>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!city.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!state.Value</Parent>
</Grouping>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>1.625in</Width>
</TableColumn>
<TableColumn>
<Width>1.375in</Width>
</TableColumn>
<TableColumn>
<Width>1.25in</Width>
</TableColumn>
<TableColumn>
<Width>1.125in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>4.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="pubs">
<rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=pubs</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>7.125in</Width>
<DataSets>
<DataSet Name="pubs">
<Fields>
<Field Name="au_id">
<DataField>au_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_lname">
<DataField>au_lname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="au_fname">
<DataField>au_fname</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="phone">
<DataField>phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="address">
<DataField>address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="city">
<DataField>city</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="state">
<DataField>state</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="zip">
<DataField>zip</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="contract">
<DataField>contract</DataField>
<rd:TypeName>System.Boolean</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>pubs</DataSourceName>
<CommandText>select * from authors</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nytr0x" <nytr0x@.yahoo.com> wrote in message
news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> Hi I've been puzzling over this one for ages, Imagine a dataset like:
> ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> 3-Jan 07:00 3-Jan 15:00 00001 Fred
> 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> 6-Jan 07:00 6-Jan 15:00 00001 Fred
> 1-Jan 07:00 1-Jan 15:00 00002 Jane
> 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> 6-Jan 07:00 6-Jan 15:00 00002 Jane
> I would like to group by consecutive absence codes to generate a report
> like:
> Fred had 2 occurrences of AbsenceCode S
> 1-Jan 07:00 to 2-Jan 15:00
> 4-Jan 07:00 to 5-Jan 15:00
> Jane had 1 occurrence of AbsenceCode S
> 2-Jan 07:00 to 5-Jan 15:00
> I have many more codes, and many more records, How can I group by each
> occurrence like this?
> I've tried using RunningValue with Previous(AbsenceCode) but RS won't
allow
> nested aggregate functions.
> Thanks in advance,
> N.
>|||I haven't had chance to test your example yet. But wouldn't this just group
each AbsenceCode for each Name? In the example given I want two groups for
the AbsenceCode S for Fred and 1 group for Jane.
Thanks,
N
"Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
news:%23VsZSq0aEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Create two groups - one on Name and another one on AbsenceCode. See the
> attached example that goes against local pubs database.
> <?xml version="1.0" encoding="utf-8"?>
> <Report
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>1in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>15</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>state</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>city</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextDecoration>Underline</TextDecoration>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> <FontWeight>700</FontWeight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>address</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="state">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>state</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="city">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>city</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="au_fname">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>au_fname</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!au_fname.Value & " " &
> Fields!au_lname.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="address">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>address</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!address.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>pubs</DataSetName>
> <Width>5.375in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!state.Value & " [" &
> RunningValue(Fields!state.Value, Count, "table1_Group1") & "
> authors]"</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!state.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox13</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!city.Value & " [" &
> RunningValue(Fields!city.Value, Count, "table1_Group2") & "
> authors]"</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox15">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox15</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox16">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <FontSize>8pt</FontSize>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>textbox16</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!city.Value</GroupExpression>
> </GroupExpressions>
> <Parent>=Fields!state.Value</Parent>
> </Grouping>
> </TableGroup>
> </TableGroups>
> <TableColumns>
> <TableColumn>
> <Width>1.625in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.375in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.25in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.125in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>4.75in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="pubs">
> <rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>initial catalog=pubs</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>7.125in</Width>
> <DataSets>
> <DataSet Name="pubs">
> <Fields>
> <Field Name="au_id">
> <DataField>au_id</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="au_lname">
> <DataField>au_lname</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="au_fname">
> <DataField>au_fname</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="phone">
> <DataField>phone</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="address">
> <DataField>address</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="city">
> <DataField>city</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="state">
> <DataField>state</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="zip">
> <DataField>zip</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="contract">
> <DataField>contract</DataField>
> <rd:TypeName>System.Boolean</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>pubs</DataSourceName>
> <CommandText>select * from authors</CommandText>
> <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> <Language>en-US</Language>
> </Report>
> --
> Ravi Mumulla (Microsoft)
> SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Nytr0x" <nytr0x@.yahoo.com> wrote in message
> news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > Hi I've been puzzling over this one for ages, Imagine a dataset like:
> >
> > ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> > 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> > 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> > 3-Jan 07:00 3-Jan 15:00 00001 Fred
> > 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> > 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> > 6-Jan 07:00 6-Jan 15:00 00001 Fred
> > 1-Jan 07:00 1-Jan 15:00 00002 Jane
> > 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> > 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> > 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> > 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> > 6-Jan 07:00 6-Jan 15:00 00002 Jane
> >
> > I would like to group by consecutive absence codes to generate a report
> > like:
> >
> > Fred had 2 occurrences of AbsenceCode S
> > 1-Jan 07:00 to 2-Jan 15:00
> > 4-Jan 07:00 to 5-Jan 15:00
> > Jane had 1 occurrence of AbsenceCode S
> > 2-Jan 07:00 to 5-Jan 15:00
> >
> > I have many more codes, and many more records, How can I group by each
> > occurrence like this?
> > I've tried using RunningValue with Previous(AbsenceCode) but RS won't
> allow
> > nested aggregate functions.
> >
> > Thanks in advance,
> > N.
> >
> >
>|||Yes, ths will first group by name and then by absense code. If you want to
do one group per absense code for each name, you'd have to do that in SQL.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Nytr0x" <nytr0x@.yahoo.com> wrote in message
news:OCioZO9aEHA.3692@.TK2MSFTNGP09.phx.gbl...
> I haven't had chance to test your example yet. But wouldn't this just
group
> each AbsenceCode for each Name? In the example given I want two groups
for
> the AbsenceCode S for Fred and 1 group for Jane.
> Thanks,
> N
> "Ravi Mumulla (Microsoft)" <ravimu@.online.microsoft.com> wrote in message
> news:%23VsZSq0aEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Create two groups - one on Name and another one on AbsenceCode. See the
> > attached example that goes against local pubs database.
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> >
>
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> > tion"
> >
>
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox4">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>15</ZIndex>
> > <rd:DefaultName>textbox4</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>state</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>14</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>city</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>13</ZIndex>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>Name</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <TextDecoration>Underline</TextDecoration>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > <FontWeight>700</FontWeight>
> > </Style>
> > <ZIndex>12</ZIndex>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>address</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="state">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>3</ZIndex>
> > <rd:DefaultName>state</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="city">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>city</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="au_fname">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>au_fname</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!au_fname.Value & " " &
> > Fields!au_lname.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="address">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>address</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!address.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>pubs</DataSetName>
> > <Width>5.375in</Width>
> > <TableGroups>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox12">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>11</ZIndex>
> > <rd:DefaultName>textbox12</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!state.Value & " [" &
> > RunningValue(Fields!state.Value, Count, "table1_Group1") & "
> > authors]"</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox11">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>10</ZIndex>
> > <rd:DefaultName>textbox11</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>9</ZIndex>
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox6">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>8</ZIndex>
> > <rd:DefaultName>textbox6</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> > <GroupExpression>=Fields!state.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox13">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>7</ZIndex>
> > <rd:DefaultName>textbox13</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox14">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>6</ZIndex>
> > <rd:DefaultName>textbox14</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!city.Value & " [" &
> > RunningValue(Fields!city.Value, Count, "table1_Group2") & "
> > authors]"</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox15">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>5</ZIndex>
> > <rd:DefaultName>textbox15</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox16">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <FontSize>8pt</FontSize>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>4</ZIndex>
> > <rd:DefaultName>textbox16</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group2">
> > <GroupExpressions>
> > <GroupExpression>=Fields!city.Value</GroupExpression>
> > </GroupExpressions>
> > <Parent>=Fields!state.Value</Parent>
> > </Grouping>
> > </TableGroup>
> > </TableGroups>
> > <TableColumns>
> > <TableColumn>
> > <Width>1.625in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.375in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.25in</Width>
> > </TableColumn>
> > <TableColumn>
> > <Width>1.125in</Width>
> > </TableColumn>
> > </TableColumns>
> > </Table>
> > </ReportItems>
> > <Style />
> > <Height>4.75in</Height>
> > </Body>
> > <TopMargin>1in</TopMargin>
> > <DataSources>
> > <DataSource Name="pubs">
> >
> > <rd:DataSourceID>d743d813-756c-4688-850b-f2e6deda54b0</rd:DataSourceID>
> > <ConnectionProperties>
> > <DataProvider>SQL</DataProvider>
> > <ConnectString>initial catalog=pubs</ConnectString>
> > <IntegratedSecurity>true</IntegratedSecurity>
> > </ConnectionProperties>
> > </DataSource>
> > </DataSources>
> > <Width>7.125in</Width>
> > <DataSets>
> > <DataSet Name="pubs">
> > <Fields>
> > <Field Name="au_id">
> > <DataField>au_id</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="au_lname">
> > <DataField>au_lname</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="au_fname">
> > <DataField>au_fname</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="phone">
> > <DataField>phone</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="address">
> > <DataField>address</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="city">
> > <DataField>city</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="state">
> > <DataField>state</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="zip">
> > <DataField>zip</DataField>
> > <rd:TypeName>System.String</rd:TypeName>
> > </Field>
> > <Field Name="contract">
> > <DataField>contract</DataField>
> > <rd:TypeName>System.Boolean</rd:TypeName>
> > </Field>
> > </Fields>
> > <Query>
> > <DataSourceName>pubs</DataSourceName>
> > <CommandText>select * from authors</CommandText>
> > <rd:UseGenericDesigner>true</rd:UseGenericDesigner>
> > </Query>
> > </DataSet>
> > </DataSets>
> > <LeftMargin>1in</LeftMargin>
> > <rd:SnapToGrid>true</rd:SnapToGrid>
> > <rd:DrawGrid>true</rd:DrawGrid>
> > <rd:ReportID>7a57b47c-d471-411b-abea-bc7acc3f1a16</rd:ReportID>
> > <BottomMargin>1in</BottomMargin>
> > <Language>en-US</Language>
> > </Report>
> >
> > --
> > Ravi Mumulla (Microsoft)
> > SQL Server Reporting Services
> >
> > This posting is provided "AS IS" with no warranties, and confers no
> rights.
> > "Nytr0x" <nytr0x@.yahoo.com> wrote in message
> > news:%23iNlXG0aEHA.3792@.TK2MSFTNGP09.phx.gbl...
> > > Hi I've been puzzling over this one for ages, Imagine a dataset like:
> > >
> > > ShiftStartDate, ShiftEndDate, NameID, Name, AbsenceCode
> > > 1-Jan 07:00 1-Jan 15:00 00001 Fred S
> > > 2-Jan 07:00 2-Jan 15:00 00001 Fred S
> > > 3-Jan 07:00 3-Jan 15:00 00001 Fred
> > > 4-Jan 07:00 4-Jan 15:00 00001 Fred S
> > > 5-Jan 07:00 5-Jan 15:00 00001 Fred S
> > > 6-Jan 07:00 6-Jan 15:00 00001 Fred
> > > 1-Jan 07:00 1-Jan 15:00 00002 Jane
> > > 2-Jan 07:00 2-Jan 15:00 00002 Jane S
> > > 3-Jan 07:00 3-Jan 15:00 00002 Jane S
> > > 4-Jan 07:00 4-Jan 15:00 00002 Jane S
> > > 5-Jan 07:00 5-Jan 15:00 00002 Jane S
> > > 6-Jan 07:00 6-Jan 15:00 00002 Jane
> > >
> > > I would like to group by consecutive absence codes to generate a
report
> > > like:
> > >
> > > Fred had 2 occurrences of AbsenceCode S
> > > 1-Jan 07:00 to 2-Jan 15:00
> > > 4-Jan 07:00 to 5-Jan 15:00
> > > Jane had 1 occurrence of AbsenceCode S
> > > 2-Jan 07:00 to 5-Jan 15:00
> > >
> > > I have many more codes, and many more records, How can I group by each
> > > occurrence like this?
> > > I've tried using RunningValue with Previous(AbsenceCode) but RS won't
> > allow
> > > nested aggregate functions.
> > >
> > > Thanks in advance,
> > > N.
> > >
> > >
> >
> >
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment