I am making a query for mailing labels that I will generate in Reporting
Services. I have a SQL Server stored procedure that returns the address using
SQL like this
SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
+ Zip AS FullAddress
FROM ...
Assume the data is:
Address: 123 Main Street
Address2: Suite 3
City: Bellevue
State: WA
Zip: 98111
I assign the FullAddress field to a textbox in Reporting Services. I resize
it to be several lines high. The result I get is something like this:
123 Main StreetSuite3Bellevue
WA 98111
I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
double spaces. Does RS handle CHAR() output? Do I need to use separate fields?I've done this before but in the actual field expression not in the query.
Use Fields!Address.Value + CHR(10) + Fields!Address.Value + ...
"Rick" wrote:
> I am making a query for mailing labels that I will generate in Reporting
> Services. I have a SQL Server stored procedure that returns the address using
> SQL like this
> SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
> + Zip AS FullAddress
> FROM ...
> Assume the data is:
> Address: 123 Main Street
> Address2: Suite 3
> City: Bellevue
> State: WA
> Zip: 98111
> I assign the FullAddress field to a textbox in Reporting Services. I resize
> it to be several lines high. The result I get is something like this:
> 123 Main StreetSuite3Bellevue
> WA 98111
> I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
> double spaces. Does RS handle CHAR() output? Do I need to use separate fields?|||Thanks. Your answer got me going in the right direction. I didn't get the
Chr(10) to work in the actual field expression, but chr(10) + chr(13) did.
For the benefit of future thread readers...I went a step further and created
a code block in the Report|Report Parameters command to make things a little
cleaner. It easily handles the common case where the second address line is
empty (Note: my query returns an empty string rather than a NULL if there is
no Address2). My code is this:
Function BuildAddress(ByVal A1 As String, ByVal A2 As String, ByVal C AS
String) As String
Dim strReturn As String
=Code.BuildAddress(Fields!Address.Value, Fields!Address2.Value,
Fields!CSZ.Value)
If A2.Length > 0 Then
strReturn += A2 + chr(10) + chr(13)
End If
strReturn += C
Return strReturn
End Function
And then I used the following expression in the textbox:
=Code.BuildAddress(
"David Bienstock" wrote:
> I've done this before but in the actual field expression not in the query.
> Use Fields!Address.Value + CHR(10) + Fields!Address.Value + ...
> "Rick" wrote:
> > I am making a query for mailing labels that I will generate in Reporting
> > Services. I have a SQL Server stored procedure that returns the address using
> > SQL like this
> >
> > SELECT Address + CHAR(13) + Address2 + CHAR(13) + City + ' ' + State + ' '
> > + Zip AS FullAddress
> > FROM ...
> >
> > Assume the data is:
> > Address: 123 Main Street
> > Address2: Suite 3
> > City: Bellevue
> > State: WA
> > Zip: 98111
> >
> > I assign the FullAddress field to a textbox in Reporting Services. I resize
> > it to be several lines high. The result I get is something like this:
> >
> > 123 Main StreetSuite3Bellevue
> > WA 98111
> >
> > I have tried using CHAR(10) + CHAR(13) instead of just CHAR(10), but I get
> > double spaces. Does RS handle CHAR() output? Do I need to use separate fields?
Showing posts with label returns. Show all posts
Showing posts with label returns. Show all posts
Thursday, March 22, 2012
Sunday, March 11, 2012
does Level() do anything?
enclosed is the complete rdl for a report that has a table, contains groups
and calls the level function.
Returns the current level of depth in a recursive hierarchy. it looks like
the recursive part does not work?
what am I missing here?
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1.25in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>group name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="unique_name">
<Style>
<PaddingLeft>30pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>unique_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!unique_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= iif( Fields!unique_name.Value is Nothing,
"Null","Not Null")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>6.83334in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="group_name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>group_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!group_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<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="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!group_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="sub_group_name">
<Style>
<PaddingLeft>10pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>sub_group_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!sub_group_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox18</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!group_id.Value</Parent>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="family_name">
<Style>
<PaddingLeft>20pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>family_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!family_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox23</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= iif(Fields!family_name.Value=Nothing,"Null","Not Null")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox24">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox24</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group3">
<GroupExpressions>
<GroupExpression>=Fields!family_id.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!sub_group_id.Value</Parent>
</Grouping>
<Visibility>
<Hidden>= iif(Fields!family_name.Value=Nothing,True,False)</Hidden>
</Visibility>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="datasource">
<rd:DataSourceID>6f542575-d325-4bbe-8889-0ce5d6439aa6</rd:DataSourceID>
<DataSourceReference>datasource</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.95833in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="group_name">
<DataField>group_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="group_id">
<DataField>group_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="sub_group_name">
<DataField>sub_group_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="sub_group_id">
<DataField>sub_group_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="family_name">
<DataField>family_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="family_id">
<DataField>family_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="unique_id">
<DataField>unique_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="unique_name">
<DataField>unique_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>datasource</DataSourceName>
<CommandText>select 'group 1' as group_name, 1 as group_id, 'sub
group 1' as sub_group_name, 1 as sub_group_id, null as family_name, null as
family_id, null as unique_id, null as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 1' as
sub_group_name, 1 as sub_group_id, 'family 1' as family_name, 1 as family_id,
1 as unique_id, '1' as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 1' as
sub_group_name, 1 as sub_group_id, 'family 1' as family_name, 1 as family_id,
2 as unique_id, '2' as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 2' as
sub_group_name, 2 as sub_group_id, 'family 2' as family_name, 2 as family_id,
3 as unique_id, '3' as unique_name
union
select 'group 2' as group_name, 2 as group_id, 'sub group 2' as
sub_group_name, 2 as sub_group_id, 'family 2' as family_name, 2 as family_id,
4 as unique_id, '4' as unique_name</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>eec2b812-624b-4368-8fcc-a696fe0d2f30</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>Level function is for recursive hierarchies, which are tables that link back
to themselves. If you create a table such as...
CategoryID CategoryParent
1 0
2 1
3 1
4 0
5 4
6 4
7 4
8 2
9 2
10 2
11 9
12 10
13 12
and set your query to be
Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
Then you could use the level function to tell you how far each category node
is nested.
"letuce dance" wrote:
> enclosed is the complete rdl for a report that has a table, contains groups
> and calls the level function.
> Returns the current level of depth in a recursive hierarchy. it looks like
> the recursive part does not work?
> what am I missing here?
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>1.25in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>group name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="unique_name">
> <Style>
> <PaddingLeft>30pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>unique_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!unique_name.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>= iif( Fields!unique_name.Value is Nothing,
> "Null","Not Null")</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <Width>6.83334in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="group_name">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>group_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!group_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <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="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!group_id.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="sub_group_name">
> <Style>
> <PaddingLeft>10pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>sub_group_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!sub_group_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox17</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox18</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> </GroupExpressions>
> <Parent>=Fields!group_id.Value</Parent>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="family_name">
> <Style>
> <PaddingLeft>20pt</PaddingLeft>
> <BackgroundColor>Gainsboro</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>family_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!family_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox23">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gainsboro</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>|||ya I think I provided that example? an example that shows level() does not
work?
"Jace" wrote:
> Level function is for recursive hierarchies, which are tables that link back
> to themselves. If you create a table such as...
> CategoryID CategoryParent
> 1 0
> 2 1
> 3 1
> 4 0
> 5 4
> 6 4
> 7 4
> 8 2
> 9 2
> 10 2
> 11 9
> 12 10
> 13 12
> and set your query to be
> Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
> outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
> Then you could use the level function to tell you how far each category node
> is nested.
>
> "letuce dance" wrote:
> > enclosed is the complete rdl for a report that has a table, contains groups
> > and calls the level function.
> >
> > Returns the current level of depth in a recursive hierarchy. it looks like
> > the recursive part does not work?
> >
> > what am I missing here?
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1.25in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>14</ZIndex>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>group name</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>13</ZIndex>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>12</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="unique_name">
> > <Style>
> > <PaddingLeft>30pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>unique_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!unique_name.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>= iif( Fields!unique_name.Value is Nothing,
> > "Null","Not Null")</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox6">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>textbox6</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>DataSet1</DataSetName>
> > <Width>6.83334in</Width>
> > <TableGroups>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="group_name">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>11</ZIndex>
> > <rd:DefaultName>group_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=First(Fields!group_name.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox11">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <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="textbox12">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>9</ZIndex>
> > <rd:DefaultName>textbox12</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> > <GroupExpression>=Fields!group_id.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="sub_group_name">
> > <Style>
> > <PaddingLeft>10pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>8</ZIndex>
> > <rd:DefaultName>sub_group_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=First(Fields!sub_group_name.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox17">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>7</ZIndex>
> > <rd:DefaultName>textbox17</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox18">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>6</ZIndex>
> > <rd:DefaultName>textbox18</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group2">
> > <GroupExpressions>
> > <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> > </GroupExpressions>
> > <Parent>=Fields!group_id.Value</Parent>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>|||What query in your example is joined back to itself? I didnt see one. I
believe you have to have this for the level function to work.
"letuce dance" wrote:
> ya I think I provided that example? an example that shows level() does not
> work?
> "Jace" wrote:
> > Level function is for recursive hierarchies, which are tables that link back
> > to themselves. If you create a table such as...
> >
> > CategoryID CategoryParent
> > 1 0
> > 2 1
> > 3 1
> > 4 0
> > 5 4
> > 6 4
> > 7 4
> > 8 2
> > 9 2
> > 10 2
> > 11 9
> > 12 10
> > 13 12
> >
> > and set your query to be
> > Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
> > outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
> >
> > Then you could use the level function to tell you how far each category node
> > is nested.
> >
> >
> > "letuce dance" wrote:
> >
> > > enclosed is the complete rdl for a report that has a table, contains groups
> > > and calls the level function.
> > >
> > > Returns the current level of depth in a recursive hierarchy. it looks like
> > > the recursive part does not work?
> > >
> > > what am I missing here?
> > >
> > > <?xml version="1.0" encoding="utf-8"?>
> > > <Report
> > > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> > > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > > <RightMargin>1in</RightMargin>
> > > <Body>
> > > <ReportItems>
> > > <Table Name="table1">
> > > <Height>1.25in</Height>
> > > <Style />
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox1">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>14</ZIndex>
> > > <rd:DefaultName>textbox1</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>group name</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox2">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>13</ZIndex>
> > > <rd:DefaultName>textbox2</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox3">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>12</ZIndex>
> > > <rd:DefaultName>textbox3</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Details>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="unique_name">
> > > <Style>
> > > <PaddingLeft>30pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>2</ZIndex>
> > > <rd:DefaultName>unique_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=Fields!unique_name.Value</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox5">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>1</ZIndex>
> > > <rd:DefaultName>textbox5</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>= iif( Fields!unique_name.Value is Nothing,
> > > "Null","Not Null")</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox6">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <rd:DefaultName>textbox6</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Details>
> > > <DataSetName>DataSet1</DataSetName>
> > > <Width>6.83334in</Width>
> > > <TableGroups>
> > > <TableGroup>
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="group_name">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>11</ZIndex>
> > > <rd:DefaultName>group_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=First(Fields!group_name.Value)</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox11">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <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="textbox12">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>9</ZIndex>
> > > <rd:DefaultName>textbox12</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Grouping Name="table1_Group1">
> > > <GroupExpressions>
> > > <GroupExpression>=Fields!group_id.Value</GroupExpression>
> > > </GroupExpressions>
> > > </Grouping>
> > > </TableGroup>
> > > <TableGroup>
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="sub_group_name">
> > > <Style>
> > > <PaddingLeft>10pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>8</ZIndex>
> > > <rd:DefaultName>sub_group_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=First(Fields!sub_group_name.Value)</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox17">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>7</ZIndex>
> > > <rd:DefaultName>textbox17</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox18">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>6</ZIndex>
> > > <rd:DefaultName>textbox18</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Grouping Name="table1_Group2">
> > > <GroupExpressions>
> > > <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> > > </GroupExpressions>
and calls the level function.
Returns the current level of depth in a recursive hierarchy. it looks like
the recursive part does not work?
what am I missing here?
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1.25in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>group name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="unique_name">
<Style>
<PaddingLeft>30pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>unique_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!unique_name.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= iif( Fields!unique_name.Value is Nothing,
"Null","Not Null")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>DataSet1</DataSetName>
<Width>6.83334in</Width>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="group_name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>group_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!group_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<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="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gray</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!group_id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="sub_group_name">
<Style>
<PaddingLeft>10pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>sub_group_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!sub_group_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox17</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Silver</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox18</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group2">
<GroupExpressions>
<GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!group_id.Value</Parent>
</Grouping>
</TableGroup>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="family_name">
<Style>
<PaddingLeft>20pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>family_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!family_name.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox23">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox23</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>= iif(Fields!family_name.Value=Nothing,"Null","Not Null")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox24">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BackgroundColor>Gainsboro</BackgroundColor>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox24</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=level()</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group3">
<GroupExpressions>
<GroupExpression>=Fields!family_id.Value</GroupExpression>
</GroupExpressions>
<Parent>=Fields!sub_group_id.Value</Parent>
</Grouping>
<Visibility>
<Hidden>= iif(Fields!family_name.Value=Nothing,True,False)</Hidden>
</Visibility>
</TableGroup>
</TableGroups>
<TableColumns>
<TableColumn>
<Width>2.5in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>1.75in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="datasource">
<rd:DataSourceID>6f542575-d325-4bbe-8889-0ce5d6439aa6</rd:DataSourceID>
<DataSourceReference>datasource</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.95833in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="group_name">
<DataField>group_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="group_id">
<DataField>group_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="sub_group_name">
<DataField>sub_group_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="sub_group_id">
<DataField>sub_group_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="family_name">
<DataField>family_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="family_id">
<DataField>family_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="unique_id">
<DataField>unique_id</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="unique_name">
<DataField>unique_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>datasource</DataSourceName>
<CommandText>select 'group 1' as group_name, 1 as group_id, 'sub
group 1' as sub_group_name, 1 as sub_group_id, null as family_name, null as
family_id, null as unique_id, null as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 1' as
sub_group_name, 1 as sub_group_id, 'family 1' as family_name, 1 as family_id,
1 as unique_id, '1' as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 1' as
sub_group_name, 1 as sub_group_id, 'family 1' as family_name, 1 as family_id,
2 as unique_id, '2' as unique_name
union
select 'group 1' as group_name, 1 as group_id, 'sub group 2' as
sub_group_name, 2 as sub_group_id, 'family 2' as family_name, 2 as family_id,
3 as unique_id, '3' as unique_name
union
select 'group 2' as group_name, 2 as group_id, 'sub group 2' as
sub_group_name, 2 as sub_group_id, 'family 2' as family_name, 2 as family_id,
4 as unique_id, '4' as unique_name</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>eec2b812-624b-4368-8fcc-a696fe0d2f30</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>Level function is for recursive hierarchies, which are tables that link back
to themselves. If you create a table such as...
CategoryID CategoryParent
1 0
2 1
3 1
4 0
5 4
6 4
7 4
8 2
9 2
10 2
11 9
12 10
13 12
and set your query to be
Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
Then you could use the level function to tell you how far each category node
is nested.
"letuce dance" wrote:
> enclosed is the complete rdl for a report that has a table, contains groups
> and calls the level function.
> Returns the current level of depth in a recursive hierarchy. it looks like
> the recursive part does not work?
> what am I missing here?
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Height>1.25in</Height>
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>group name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="unique_name">
> <Style>
> <PaddingLeft>30pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>unique_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!unique_name.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>= iif( Fields!unique_name.Value is Nothing,
> "Null","Not Null")</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>DataSet1</DataSetName>
> <Width>6.83334in</Width>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="group_name">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>group_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!group_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <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="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gray</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!group_id.Value</GroupExpression>
> </GroupExpressions>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="sub_group_name">
> <Style>
> <PaddingLeft>10pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>sub_group_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!sub_group_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox17</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Silver</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox18</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=level()</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Header>
> <Grouping Name="table1_Group2">
> <GroupExpressions>
> <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> </GroupExpressions>
> <Parent>=Fields!group_id.Value</Parent>
> </Grouping>
> </TableGroup>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="family_name">
> <Style>
> <PaddingLeft>20pt</PaddingLeft>
> <BackgroundColor>Gainsboro</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>family_name</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!family_name.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox23">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <BackgroundColor>Gainsboro</BackgroundColor>
> <PaddingBottom>2pt</PaddingBottom>|||ya I think I provided that example? an example that shows level() does not
work?
"Jace" wrote:
> Level function is for recursive hierarchies, which are tables that link back
> to themselves. If you create a table such as...
> CategoryID CategoryParent
> 1 0
> 2 1
> 3 1
> 4 0
> 5 4
> 6 4
> 7 4
> 8 2
> 9 2
> 10 2
> 11 9
> 12 10
> 13 12
> and set your query to be
> Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
> outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
> Then you could use the level function to tell you how far each category node
> is nested.
>
> "letuce dance" wrote:
> > enclosed is the complete rdl for a report that has a table, contains groups
> > and calls the level function.
> >
> > Returns the current level of depth in a recursive hierarchy. it looks like
> > the recursive part does not work?
> >
> > what am I missing here?
> >
> > <?xml version="1.0" encoding="utf-8"?>
> > <Report
> > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > <RightMargin>1in</RightMargin>
> > <Body>
> > <ReportItems>
> > <Table Name="table1">
> > <Height>1.25in</Height>
> > <Style />
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox1">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>14</ZIndex>
> > <rd:DefaultName>textbox1</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>group name</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox2">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>13</ZIndex>
> > <rd:DefaultName>textbox2</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox3">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>12</ZIndex>
> > <rd:DefaultName>textbox3</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Details>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="unique_name">
> > <Style>
> > <PaddingLeft>30pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>2</ZIndex>
> > <rd:DefaultName>unique_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=Fields!unique_name.Value</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox5">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>1</ZIndex>
> > <rd:DefaultName>textbox5</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>= iif( Fields!unique_name.Value is Nothing,
> > "Null","Not Null")</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox6">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <rd:DefaultName>textbox6</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Details>
> > <DataSetName>DataSet1</DataSetName>
> > <Width>6.83334in</Width>
> > <TableGroups>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="group_name">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>11</ZIndex>
> > <rd:DefaultName>group_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=First(Fields!group_name.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox11">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <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="textbox12">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Gray</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>9</ZIndex>
> > <rd:DefaultName>textbox12</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group1">
> > <GroupExpressions>
> > <GroupExpression>=Fields!group_id.Value</GroupExpression>
> > </GroupExpressions>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>
> > <TableRows>
> > <TableRow>
> > <Height>0.25in</Height>
> > <TableCells>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="sub_group_name">
> > <Style>
> > <PaddingLeft>10pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>8</ZIndex>
> > <rd:DefaultName>sub_group_name</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=First(Fields!sub_group_name.Value)</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox17">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>7</ZIndex>
> > <rd:DefaultName>textbox17</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value />
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > <TableCell>
> > <ReportItems>
> > <Textbox Name="textbox18">
> > <Style>
> > <PaddingLeft>2pt</PaddingLeft>
> > <BackgroundColor>Silver</BackgroundColor>
> > <PaddingBottom>2pt</PaddingBottom>
> > <PaddingTop>2pt</PaddingTop>
> > <PaddingRight>2pt</PaddingRight>
> > </Style>
> > <ZIndex>6</ZIndex>
> > <rd:DefaultName>textbox18</rd:DefaultName>
> > <CanGrow>true</CanGrow>
> > <Value>=level()</Value>
> > </Textbox>
> > </ReportItems>
> > </TableCell>
> > </TableCells>
> > </TableRow>
> > </TableRows>
> > </Header>
> > <Grouping Name="table1_Group2">
> > <GroupExpressions>
> > <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> > </GroupExpressions>
> > <Parent>=Fields!group_id.Value</Parent>
> > </Grouping>
> > </TableGroup>
> > <TableGroup>
> > <Header>|||What query in your example is joined back to itself? I didnt see one. I
believe you have to have this for the level function to work.
"letuce dance" wrote:
> ya I think I provided that example? an example that shows level() does not
> work?
> "Jace" wrote:
> > Level function is for recursive hierarchies, which are tables that link back
> > to themselves. If you create a table such as...
> >
> > CategoryID CategoryParent
> > 1 0
> > 2 1
> > 3 1
> > 4 0
> > 5 4
> > 6 4
> > 7 4
> > 8 2
> > 9 2
> > 10 2
> > 11 9
> > 12 10
> > 13 12
> >
> > and set your query to be
> > Select t1.CategoryID, t2.CategoryID as Subordinates from Sometable t1 left
> > outer join Sometable t2 on t1.Categoryid = t2.CategoryParent
> >
> > Then you could use the level function to tell you how far each category node
> > is nested.
> >
> >
> > "letuce dance" wrote:
> >
> > > enclosed is the complete rdl for a report that has a table, contains groups
> > > and calls the level function.
> > >
> > > Returns the current level of depth in a recursive hierarchy. it looks like
> > > the recursive part does not work?
> > >
> > > what am I missing here?
> > >
> > > <?xml version="1.0" encoding="utf-8"?>
> > > <Report
> > > xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
> > > xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> > > <RightMargin>1in</RightMargin>
> > > <Body>
> > > <ReportItems>
> > > <Table Name="table1">
> > > <Height>1.25in</Height>
> > > <Style />
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox1">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>14</ZIndex>
> > > <rd:DefaultName>textbox1</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>group name</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox2">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>13</ZIndex>
> > > <rd:DefaultName>textbox2</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox3">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>12</ZIndex>
> > > <rd:DefaultName>textbox3</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Details>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="unique_name">
> > > <Style>
> > > <PaddingLeft>30pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>2</ZIndex>
> > > <rd:DefaultName>unique_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=Fields!unique_name.Value</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox5">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>1</ZIndex>
> > > <rd:DefaultName>textbox5</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>= iif( Fields!unique_name.Value is Nothing,
> > > "Null","Not Null")</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox6">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <rd:DefaultName>textbox6</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Details>
> > > <DataSetName>DataSet1</DataSetName>
> > > <Width>6.83334in</Width>
> > > <TableGroups>
> > > <TableGroup>
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="group_name">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>11</ZIndex>
> > > <rd:DefaultName>group_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=First(Fields!group_name.Value)</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox11">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <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="textbox12">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Gray</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>9</ZIndex>
> > > <rd:DefaultName>textbox12</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Grouping Name="table1_Group1">
> > > <GroupExpressions>
> > > <GroupExpression>=Fields!group_id.Value</GroupExpression>
> > > </GroupExpressions>
> > > </Grouping>
> > > </TableGroup>
> > > <TableGroup>
> > > <Header>
> > > <TableRows>
> > > <TableRow>
> > > <Height>0.25in</Height>
> > > <TableCells>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="sub_group_name">
> > > <Style>
> > > <PaddingLeft>10pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>8</ZIndex>
> > > <rd:DefaultName>sub_group_name</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=First(Fields!sub_group_name.Value)</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox17">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>7</ZIndex>
> > > <rd:DefaultName>textbox17</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value />
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > <TableCell>
> > > <ReportItems>
> > > <Textbox Name="textbox18">
> > > <Style>
> > > <PaddingLeft>2pt</PaddingLeft>
> > > <BackgroundColor>Silver</BackgroundColor>
> > > <PaddingBottom>2pt</PaddingBottom>
> > > <PaddingTop>2pt</PaddingTop>
> > > <PaddingRight>2pt</PaddingRight>
> > > </Style>
> > > <ZIndex>6</ZIndex>
> > > <rd:DefaultName>textbox18</rd:DefaultName>
> > > <CanGrow>true</CanGrow>
> > > <Value>=level()</Value>
> > > </Textbox>
> > > </ReportItems>
> > > </TableCell>
> > > </TableCells>
> > > </TableRow>
> > > </TableRows>
> > > </Header>
> > > <Grouping Name="table1_Group2">
> > > <GroupExpressions>
> > > <GroupExpression>=Fields!sub_group_id.Value</GroupExpression>
> > > </GroupExpressions>
Sunday, February 19, 2012
Documented t-sql for file space ?
Hi There
I would like to write sql to check that space used by any given data file in SS2000.
sp_spaceused only returns the total space used by the DB or object in the DB, not for a specific data file.
DBCC SHOWFILESTATS does give me this information , but it is not documented in BOL, i would prefer not to use an undocumented command to ensure future use in later versions. Is DBCC SHOWFILESTATS an undocumented T-SQL command ? If so what documented t-sql command will provide me with this information ?
I could query system table's and work this out , but as mentioned this may not not work in future versions of SS.
Thanx
Dont worry, seems FILEPROPERTY function will do this for me , thanx
Subscribe to:
Posts (Atom)