ssrs Group By on multiple columns together
-
Saturday, December 29, 2012 4:41 PM
Hello All,
I am not sure how to design the report for the following sample requirement:
The query looks very simple : Select Dept,SubDept,Name,Technology,PNum From tblEmployee
I could Group by only Dept but I am not able to group by on Dept and SubDept at same level.
The Report should look like as below:
Thanks.
All Replies
-
Saturday, December 29, 2012 5:46 PM
Hi Rik,
You can always group the by using And condition in the Group. for example screen shot for the same:
I have attached the same sql code and rdl code for your reference - > rdl code is designed in VS 2010.
T-SQL:
IF OBJECT_ID('dbo.tblEmployee') IS NOT NULL DROP TABLE dbo.tblEmployee GO CREATE TABLE tblEmployee ( Dept VARCHAR(100), SubDept VARCHAR(100), Name VARCHAR(100), Technology VARCHAR(100), PNum INT ) GO INSERT INTO tblEmployee(Dept,SubDept,Name,Technology,PNum) VALUES ('MFG','HiTech','Joey','Flex',12) ,('MFG','HiTech','Rooney','Java',14) ,('MFG','HiTech','Saini','Dot Net',17) ,('MFG','HiTech','Kitzie','Dot Net',19) ,('MFG','Free','Lorry','Flex',11) ,('MFG','Free','Marie','Java',13) ,('MFG','Free','Zinta','Dot Net',15) ,('MFG','Free','Fairy','Dot Net',16) GO
RDL Code:
<?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <Body> <ReportItems> <Tablix Name="Tablix2"> <TablixBody> <TablixColumns> <TablixColumn> <Width>1in</Width> </TablixColumn> <TablixColumn> <Width>1in</Width> </TablixColumn> <TablixColumn> <Width>1in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Dept1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Dept</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Dept1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Dept"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Dept.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Dept</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox22"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox22</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="SubDept1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>SubDept</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>SubDept1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="SubDept"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!SubDept.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>SubDept</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox46"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox46</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox41"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Name</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox41</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox42"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Technology</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox42</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox43"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>PNum</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox43</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Name"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Name.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Name</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Technology"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Technology.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Technology</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="PNum"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!PNum.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>PNum</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember /> <TablixMember /> <TablixMember /> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <Group Name="SubDept"> <GroupExpressions> <GroupExpression>=Fields!Dept.Value</GroupExpression> <GroupExpression>=Fields!SubDept.Value</GroupExpression> </GroupExpressions> </Group> <TablixMembers> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <Group Name="Details" /> <TablixMembers> <TablixMember /> </TablixMembers> </TablixMember> </TablixMembers> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>TestDS</DataSetName> <Top>0.125in</Top> <Left>0.10417in</Left> <Height>1in</Height> <Width>3in</Width> <Style> <Border> <Style>None</Style> </Border> </Style> </Tablix> </ReportItems> <Height>1.45834in</Height> <Style /> </Body> <Width>3.49875in</Width> <Page> <LeftMargin>1in</LeftMargin> <RightMargin>1in</RightMargin> <TopMargin>1in</TopMargin> <BottomMargin>1in</BottomMargin> <Style /> </Page> <AutoRefresh>0</AutoRefresh> <DataSources> <DataSource Name="TestDS"> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>Data Source=.;Initial Catalog=TimePassTest</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> <rd:SecurityType>Integrated</rd:SecurityType> <rd:DataSourceID>099d2d77-40ea-4e1b-9a7c-7a654bfea85f</rd:DataSourceID> </DataSource> </DataSources> <DataSets> <DataSet Name="TestDS"> <Query> <DataSourceName>TestDS</DataSourceName> <CommandText>SELECT tblEmployee.* FROM tblEmployee</CommandText> </Query> <Fields> <Field Name="Dept"> <DataField>Dept</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="SubDept"> <DataField>SubDept</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Name"> <DataField>Name</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Technology"> <DataField>Technology</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="PNum"> <DataField>PNum</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> </Fields> </DataSet> </DataSets> <rd:ReportUnitType>Inch</rd:ReportUnitType> <rd:ReportID>6a6adc92-64c1-42b2-89da-cf9357c67f1e</rd:ReportID> </Report>
Output Text:
Regards Harsh
-
Saturday, December 29, 2012 5:46 PM
Hi Rik,
You can always group the by using And condition in the Group. for example screen shot for the same:
I have attached the same sql code and rdl code for your reference - > rdl code is designed in VS 2010.
T-SQL:
IF OBJECT_ID('dbo.tblEmployee') IS NOT NULL DROP TABLE dbo.tblEmployee GO CREATE TABLE tblEmployee ( Dept VARCHAR(100), SubDept VARCHAR(100), Name VARCHAR(100), Technology VARCHAR(100), PNum INT ) GO INSERT INTO tblEmployee(Dept,SubDept,Name,Technology,PNum) VALUES ('MFG','HiTech','Joey','Flex',12) ,('MFG','HiTech','Rooney','Java',14) ,('MFG','HiTech','Saini','Dot Net',17) ,('MFG','HiTech','Kitzie','Dot Net',19) ,('MFG','Free','Lorry','Flex',11) ,('MFG','Free','Marie','Java',13) ,('MFG','Free','Zinta','Dot Net',15) ,('MFG','Free','Fairy','Dot Net',16) GO
RDL Code:
<?xml version="1.0" encoding="utf-8"?> <Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"> <Body> <ReportItems> <Tablix Name="Tablix2"> <TablixBody> <TablixColumns> <TablixColumn> <Width>1in</Width> </TablixColumn> <TablixColumn> <Width>1in</Width> </TablixColumn> <TablixColumn> <Width>1in</Width> </TablixColumn> </TablixColumns> <TablixRows> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Dept1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Dept</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Dept1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Dept"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Dept.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Dept</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox22"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox22</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="SubDept1"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>SubDept</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>SubDept1</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="SubDept"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!SubDept.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>SubDept</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox46"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value /> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox46</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Textbox41"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Name</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox41</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox42"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>Technology</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox42</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Textbox43"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>PNum</Value> <Style> <FontWeight>Bold</FontWeight> </Style> </TextRun> </TextRuns> <Style> <TextAlign>Center</TextAlign> </Style> </Paragraph> </Paragraphs> <rd:DefaultName>Textbox43</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> <TablixRow> <Height>0.25in</Height> <TablixCells> <TablixCell> <CellContents> <Textbox Name="Name"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Name.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Name</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="Technology"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!Technology.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>Technology</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> <TablixCell> <CellContents> <Textbox Name="PNum"> <CanGrow>true</CanGrow> <KeepTogether>true</KeepTogether> <Paragraphs> <Paragraph> <TextRuns> <TextRun> <Value>=Fields!PNum.Value</Value> <Style /> </TextRun> </TextRuns> <Style /> </Paragraph> </Paragraphs> <rd:DefaultName>PNum</rd:DefaultName> <Style> <Border> <Color>LightGrey</Color> <Style>Solid</Style> </Border> <PaddingLeft>2pt</PaddingLeft> <PaddingRight>2pt</PaddingRight> <PaddingTop>2pt</PaddingTop> <PaddingBottom>2pt</PaddingBottom> </Style> </Textbox> </CellContents> </TablixCell> </TablixCells> </TablixRow> </TablixRows> </TablixBody> <TablixColumnHierarchy> <TablixMembers> <TablixMember /> <TablixMember /> <TablixMember /> </TablixMembers> </TablixColumnHierarchy> <TablixRowHierarchy> <TablixMembers> <TablixMember> <Group Name="SubDept"> <GroupExpressions> <GroupExpression>=Fields!Dept.Value</GroupExpression> <GroupExpression>=Fields!SubDept.Value</GroupExpression> </GroupExpressions> </Group> <TablixMembers> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <KeepWithGroup>After</KeepWithGroup> </TablixMember> <TablixMember> <Group Name="Details" /> <TablixMembers> <TablixMember /> </TablixMembers> </TablixMember> </TablixMembers> </TablixMember> </TablixMembers> </TablixRowHierarchy> <DataSetName>TestDS</DataSetName> <Top>0.125in</Top> <Left>0.10417in</Left> <Height>1in</Height> <Width>3in</Width> <Style> <Border> <Style>None</Style> </Border> </Style> </Tablix> </ReportItems> <Height>1.45834in</Height> <Style /> </Body> <Width>3.49875in</Width> <Page> <LeftMargin>1in</LeftMargin> <RightMargin>1in</RightMargin> <TopMargin>1in</TopMargin> <BottomMargin>1in</BottomMargin> <Style /> </Page> <AutoRefresh>0</AutoRefresh> <DataSources> <DataSource Name="TestDS"> <ConnectionProperties> <DataProvider>SQL</DataProvider> <ConnectString>Data Source=.;Initial Catalog=TimePassTest</ConnectString> <IntegratedSecurity>true</IntegratedSecurity> </ConnectionProperties> <rd:SecurityType>Integrated</rd:SecurityType> <rd:DataSourceID>099d2d77-40ea-4e1b-9a7c-7a654bfea85f</rd:DataSourceID> </DataSource> </DataSources> <DataSets> <DataSet Name="TestDS"> <Query> <DataSourceName>TestDS</DataSourceName> <CommandText>SELECT tblEmployee.* FROM tblEmployee</CommandText> </Query> <Fields> <Field Name="Dept"> <DataField>Dept</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="SubDept"> <DataField>SubDept</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Name"> <DataField>Name</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="Technology"> <DataField>Technology</DataField> <rd:TypeName>System.String</rd:TypeName> </Field> <Field Name="PNum"> <DataField>PNum</DataField> <rd:TypeName>System.Int32</rd:TypeName> </Field> </Fields> </DataSet> </DataSets> <rd:ReportUnitType>Inch</rd:ReportUnitType> <rd:ReportID>6a6adc92-64c1-42b2-89da-cf9357c67f1e</rd:ReportID> </Report>
Output Text:
Regards Harsh
-
Sunday, December 30, 2012 8:59 AM
Thanks. Its Works.
But I have one more question on the design part.
How did you drag the table and then put the groups , When I do so group gets added in the first column as parent.
Let me know if my steps are wrong:
1.Create the Datasource.
2.Create the Dataset.
3.Drag table to the designer.
4.Delete the Header portion and assign the Name Technology and PNum.
5.Drag Dept to the row group above Details. and add the Sub Dept in the group properties.
The 5 step ruins the design all together :(
Kindly Help.
-
Sunday, December 30, 2012 11:24 AM
Hi Rik,
Here are the steps:
1.) Drag table to the desiger. Delete the Header Portion.
2.) Select on the detail row, Right Click and select "Add Group" from Menu and select Parent Group. In Tablix group window in Group By drop Down select the "Dept" and check the check box "Add group header". This will add a parent group and a extra column to Left of Tablix Dept. Select that newly column added column "Dept" and delete it, and now are left with two rows one blank and a detail row below it.
3.)Select the top blank row and right click and select Insert row from context menu and choose option "Inside Group - Below". Repeat it two times.
4.) now design your report as "show in screen shot."
5.) In bottow left corner, you see row groups, select the Dept Group, right click select group properties, In General Menu , Under Group expression you will Dept. Now click on the add button this add another drop down and select SubDept there.
6.) Preview report.
Regards Harsh
- Marked As Answer by Rikz Sunday, December 30, 2012 11:36 AM
