Hi,
I have an issue with reporting services report. I am including the rdl code
at the bottom of this email. In that report there are two levels of data
shown. The top level is manager id and the level below that is some info
about employees belonging to this manager id. The initial visibility of top
level is visible and second level is hidden with manager id as toggle item.
Now my question comes.
If for some reason in the database all entries for managerid column is null,
then the above mentioned report will have one plus sign alone and all the
employees will show up if we click on that plus sign. (This situation can be
achieved by changing our query to have all nulls for manager id column). Now
Please tell me if such a situation occurs how should I write the conditonal
expression such that the top level row is totally invisible(i.e plus sign
should not be shown as I want the group table1_group1 to be hidden under this
situation) and all employees info should show up directly when the report is
rendered.
Is this possible? if so how it should be done?
Thanks.
RDL File
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="dsrcEmployee">
<DataSourceReference>dsrcEmployee</DataSourceReference>
<rd:DataSourceID>c497ccbe-747c-4454-8e32-bb33161f9921</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Table Name="table1">
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<rd:DefaultName>textbox7</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<rd:DefaultName>textbox8</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<rd:DefaultName>textbox9</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
</Footer>
<Left>0.5in</Left>
<DataSetName>DataSet1</DataSetName>
<Top>0.375in</Top>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ManagerID">
<rd:DefaultName>ManagerID</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<TextAlign>Left</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!ManagerID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<rd:DefaultName>textbox10</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<rd:DefaultName>textbox12</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!ManagerID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="EmployeeName">
<rd:DefaultName>EmployeeName</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmployeeName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="EmailAddress">
<rd:DefaultName>EmailAddress</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!EmailAddress.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Phone">
<rd:DefaultName>Phone</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Phone.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="Gender">
<rd:DefaultName>Gender</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Gender.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
<Visibility>
<ToggleItem>ManagerID</ToggleItem>
<Hidden>true</Hidden>
</Visibility>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>15</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Employee Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>14</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Email Address</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>13</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Phone</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>12</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Gender</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.33333in</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
<Height>1.25in</Height>
</Table>
</ReportItems>
<Height>2in</Height>
</Body>
<rd:ReportID>3b3ab633-ae51-4bcf-8abd-bf6113721db1</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select ManagerID,isnull(ct.Title,' ') + ' ' +
isnull(ct.FirstName,' ') + ' ' + isnull(ct.MiddleName,' ') + ' ' +
isnull(ct.LastName,' ') as EmployeeName,ct.EmailAddress,ct.Phone,Gender=case
emp.Gender when 'M' then 'Male' when 'F' then 'Female' else 'Unknown' end
from HumanResources.Employee emp inner join Person.Contact ct on
emp.ContactID = ct.ContactID</CommandText>
<DataSourceName>dsrcEmployee</DataSourceName>
</Query>
<Fields>
<Field Name="ManagerID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ManagerID</DataField>
</Field>
<Field Name="EmployeeName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmployeeName</DataField>
</Field>
<Field Name="EmailAddress">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmailAddress</DataField>
</Field>
<Field Name="Phone">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Phone</DataField>
</Field>
<Field Name="Gender">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Gender</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>7.16667in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>I don't think you can toggle the expand/collapse for each toggle item,
in this case the manager ID. Since it is basically working for you,
except when there isn't a manger ID, why don't you modify your dataset
so that a no manger ID message is outputed instead of nulls?
On Mon, 5 Jun 2006 08:47:01 -0700, msdnuser <ringt@.nospam.nospam>
wrote:
>Hi,
>I have an issue with reporting services report. I am including the rdl code
>at the bottom of this email. In that report there are two levels of data
>shown. The top level is manager id and the level below that is some info
>about employees belonging to this manager id. The initial visibility of top
>level is visible and second level is hidden with manager id as toggle item.
>Now my question comes.
>If for some reason in the database all entries for managerid column is null,
>then the above mentioned report will have one plus sign alone and all the
>employees will show up if we click on that plus sign. (This situation can be
>achieved by changing our query to have all nulls for manager id column). Now
>Please tell me if such a situation occurs how should I write the conditonal
>expression such that the top level row is totally invisible(i.e plus sign
>should not be shown as I want the group table1_group1 to be hidden under this
>situation) and all employees info should show up directly when the report is
>rendered.
>Is this possible? if so how it should be done?
>Thanks.
>RDL File (snipped by responder)|||Hello Ringt,
You can try selecting the certain table row (which contains the group
header ) and right click it and choose "Edit Group" menu item. In the
displayed dialog, we choose the "visibility" tab, we can find that we can
define some expression to control the visibility of it.
Regards,
Steven Cheng
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! www.microsoft.com/security
(This posting is provided "AS IS", with no warranties, and confers no
rights.)
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment