Showing posts with label calls. Show all posts
Showing posts with label calls. Show all posts

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>

Friday, February 24, 2012

Does a table REQUIRE a dataset?

Why is it not possible for me to create a static table in Reporting
services?
I have a table that will be making some calls to a custom assembly, but it
requires NO database queries to build itself. When i leave the "dataset"
area blank, the report will not compile.
Is it really impossible to create static tables in Reporting Services?
Any help would be appreciated.
Thanks!
BrianYou can create what you want, but you still need to have a "dataset" name.
Create a new dataset ( default microsoft sql server type ) then for your
query here's an example.
Select 1 as Col#, 'A' as Col$
Union
Select 2 as Col#, 'B' as Col$
Union
Select 3 as Col#, 'C' as Col$
Union
Select 4 as Col#, 'D' as Col$
Union
Select 5 as Col#, 'E' as Col$
or am i off track on what you want to accomplish?
"G" wrote:
> Why is it not possible for me to create a static table in Reporting
> services?
> I have a table that will be making some calls to a custom assembly, but it
> requires NO database queries to build itself. When i leave the "dataset"
> area blank, the report will not compile.
> Is it really impossible to create static tables in Reporting Services?
> Any help would be appreciated.
> Thanks!
> Brian
>
>