Hi
I am trying to create a dynamic row. Say for example, I want to list all the "Employees" belonging to a particular department say "Physics". So, I would want every employee name to be in a separate row. How do I go about doing this? Any help/suggestions is appreciated.
Thanks
Using a matrix, this is possible if you add a group based on Department, a group based on Emeployee name, and data values in the data section of the matrix. Here is an example of the output:
Phisics | Joe Smith | 123 |
| Mike Jones | 222 |
| Kelly Roberts | 111 |
Chemistry | Sarah Paulson | 145 |
Kinestetics | Robert Paulson | 321 |
| Rob Sam | 235 |
You can also do this with a table. Add a group based on Department and add the the employee fields to the details row. The output would look something like this,
Physics | |
Joe Smith | 123 |
Mike Jones | 222 |
Kelly Roberts | 111 |
| | |
Chemistry | |
Sarah Paulson | 145 |
| | |
Kinestetics | |
Robert Paulson | 321 |
Jimmy James | 235 |
| | |
Below is an RDL that demonstrates this.
Ian
<?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="DataSource1">
<ConnectionProperties>
<IntegratedSecurity>true</IntegratedSecurity>
<ConnectString>Data Source=localhost; Initial Catalog="AdventureWorks"</ConnectString>
<DataProvider>SQL</DataProvider>
</ConnectionProperties>
<rd:DataSourceID>dafaddb3-d659-48f2-a1bc-6f040759edf9</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="textbox11">
<rd:DefaultName>textbox11</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="textbox12">
<rd:DefaultName>textbox12</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.25in</Height>
</TableRow>
</TableRows>
</Footer>
<DataSetName>DataSet2</DataSetName>
<Top>2.25in</Top>
<TableGroups>
<TableGroup>
<Footer>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<rd:DefaultName>textbox15</rd:DefaultName>
<ZIndex>3</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="textbox16">
<rd:DefaultName>textbox16</rd:DefaultName>
<ZIndex>2</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>
</Footer>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Department_1">
<rd:DefaultName>Department_1</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Red</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Department.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Red</Bottom>
</BorderColor>
<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!Department.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</TableGroup>
</TableGroups>
<ZIndex>1</ZIndex>
<Width>5.16667in</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="EmployeeName_1">
<rd:DefaultName>EmployeeName_1</rd:DefaultName>
<ZIndex>1</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="DataValue_1">
<rd:DefaultName>DataValue_1</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!DataValue.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.25in</Height>
</TableRow>
</TableRows>
</Details>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</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="textbox6">
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<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>
<TableColumns>
<TableColumn>
<Width>2.58333in</Width>
</TableColumn>
<TableColumn>
<Width>2.58333in</Width>
</TableColumn>
</TableColumns>
<Height>1.25in</Height>
</Table>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.375in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="Department">
<rd:DefaultName>Department</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Default>Red</Default>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!Department.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_Department">
<GroupExpressions>
<GroupExpression>=Fields!Department.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
<RowGrouping>
<Width>1.375in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="EmployeeName">
<rd:DefaultName>EmployeeName</rd:DefaultName>
<ZIndex>1</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>
<Grouping Name="matrix1_EmployeeName">
<GroupExpressions>
<GroupExpression>=Fields!EmployeeName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="textbox2">
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
<Grouping Name="matrix1_ColumnGroup1">
<GroupExpressions>
<GroupExpression />
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet2</DataSetName>
<Top>1in</Top>
<Width>3.75in</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</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>
</Corner>
<Height>0.5in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="DataValue">
<rd:DefaultName>DataValue</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!DataValue.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>6.125in</Height>
</Body>
<rd:ReportID>819bacc0-4ffc-4d72-90d6-8d01b9a574e8</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet2">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>Select 'Physics' As Department, 'Joe Smith' As EmployeeName, 123 As DataValue
Union All
Select 'Physics', 'Mike Jones', 222
Union All
Select 'Physics', 'Kelly Roberts', 111
Union All
Select 'Chemistry', 'Sarah Paulson', 145
Union All
Select 'Kinestetics', 'Robert Paulson', 321
Union All
Select 'Kinestetics', 'Jimmy James', 235</CommandText>
<DataSourceName>DataSource1</DataSourceName>
</Query>
<Fields>
<Field Name="Department">
<rd:TypeName>System.String</rd:TypeName>
<DataField>Department</DataField>
</Field>
<Field Name="EmployeeName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>EmployeeName</DataField>
</Field>
<Field Name="DataValue">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>DataValue</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<CodeModules>
<CodeModule>System.Xml, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089</CodeModule>
</CodeModules>
<Code />
<Width>7.75in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>|||
Ian
I'm getting the following error message: when I add a group by row on my matrix1.
[rsAggregateInGroupExpression] A group expression for the matrix 'matrix1' includes an aggregate function. Aggregate functions cannot be used in group expressions.
Build complete -- 1 errors, 0 warnings
The query is pretty tricky. In my report, I have a report parameter that takes in an employee id and does a select on the Department ID. After getting the Department ID, I select all the other employees corresponding to it. All the data is in one table. So, the SQL query would be like this
SELECT emp_id FROM emp_dept WHERE dept_id IN
(SELECT dept_id FROM emp_dept WHERE emp_id=@.emp_id_param)
In this report I'm trying to display all the emp_id's.
Your suggestions are greatly appreciated.
Thanks
preps.
|||
Ian
In addition, to the information in my previous mail. I'm building this report in Visual Studio2005 and not in Report Builder.
preps.
|||The error you are getting is caused by using an aggregate on a field in the group expression. Aggregates are not allowed in this context, and in your case does not seem to be needed.
If all you are displaying is the emp_id field values, then you don't really need to use a matrix. A table should give you what you need. If there is more information to display, including another dimension of data, and aggregations that need to be performed based on the dimensionality of the data, then a matrix would be the report item to use.
That being said, if you continue to use the matrix, make sure that the query includes the dept_id field in the field list, along with the emp_id. This value is needed for the first group.
The first group should have the group expression "=Fields!dept_id.Value" to first group the data by department. The second group should have the expression "=Fields!emp_id.Value" to group the data by employee. Based on the example query, this should give you the matrix described in the previous post.|||
Hi Ian
Thanks for you reply. As per your suggestion I used a table data region (in Report Designer). But for some reason only one emp_id is being display. Instead of three emp_ids which is the actual resultset.
I don't know why the other two emp_id's are not being displayed. Do you have any idea?
|||Which row in the table are you placing the emp_id field in? The one with the three horzontal lines in the box to the left of the row? Are you adding any groups to the table? If so, what fields are you grouping on?
Ian|||
Which row in the table are you placing the emp_id field in? The one with the three horzontal lines in the box to the left of the row? - Yes
Are you adding any groups to the table? - No
preps
|||On the data screen, you see three rows of data, but only one is being displayed in the table? Can you post the rdl, so that I can take a closer look? Make sure to remove any sensitive data from it before posting.
Ian|||
Ian
I figured out the problem. The table that I was trying to create was inside a List report item and hence it was not displaying the emp_ids. I put the table outside the List. It gave my the result I was looking for. If you know why or what caused this behaviour that would give me a better understanding. Otherwise, don't bother about it.
Thanks for all the help and quick response that drove me towards the right solution.
Cheers
preps
![Smile [:)]](http://pics.10026.com/?src=/emoticons/emotion-1.gif)
