Sorting with Matrix

Hi folks.

Im trying to get some sorting functionality in my matrix conrol. I have tried it with the interactive sorting but when clicking on the arrow in column header nothing happens.

Can some one help me out on this one.

Its a guite simple matrix that is filled up with data from a couple of access tables.

Thanks

Answer this question

Sorting with Matrix

Sowbhagya

I suppose you want to sort the matrix group instances e.g. alphabetically. In that case, you should not put the interactive sort on the textbox in the matrix group heading, but rather at the parent. If you want to sort the outermost grouping, put the interactive sort on a textbox in the matrix corner.

I attached a sample which is a RDL file for report designer and runs based on the Northwind database. Besides sorting the matrix groups (through textboxes in the matrix corner), it also allows you to dynamically select the grouping fields for the matrix. The sample will require some careful study.

-- Robert

< 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="northwind">
<DataSourceReference>northwind</DataSourceReference>
<rd:DataSourceID>66a72cd8-749c-4971-b5d6-05b2612a4d40</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>1in</BottomMargin>
<RightMargin>1in</RightMargin>
<ReportParameters>
<ReportParameter Name="RowGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ProductName</Value>
</Values>
</DefaultValue>
<Prompt>RowGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ProductName</Value>
<Label>By Product Name</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
<ParameterValue>
<Value>CategoryID</Value>
<Label>By Category ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
<ReportParameter Name="ColumnGroup">
<DataType>String</DataType>
<DefaultValue>
<Values>
<Value>ReorderLevel</Value>
</Values>
</DefaultValue>
<Prompt>ColumnGroup</Prompt>
<ValidValues>
<ParameterValues>
<ParameterValue>
<Value>ReorderLevel</Value>
<Label>By Reorder Level</Label>
</ParameterValue>
<ParameterValue>
<Value>UnitsInStock</Value>
<Label>By Stock</Label>
</ParameterValue>
<ParameterValue>
<Value>SupplierID</Value>
<Label>By Supplier ID</Label>
</ParameterValue>
</ParameterValues>
</ValidValues>
</ReportParameter>
</ReportParameters>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>8.5in</InteractiveWidth>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ReportItems>
<Rectangle Name="rectangle2">
<Left>0.125in</Left>
<ReportItems>
<Textbox Name="textbox3">
<Top>0.625in</Top>
<ZIndex>2</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix columns " & Parameters!ColumnGroup.Label</Value>
</Textbox>
<Textbox Name="textbox1">
<Top>0.375in</Top>
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>1</ZIndex>
<Width>3in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<Value>="Matrix rows " & Parameters!RowGroup.Label</Value>
</Textbox>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>2.125in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!RowGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!RowGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="ReorderLevel">
<rd:DefaultName>ReorderLevel</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields(Parameters!ColumnGroup.Value).Value</Value>
</Textbox>
</ReportItems>
<Sorting>
<SortBy>
<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
<Direction>Ascending</Direction>
</SortBy>
</Sorting>
<Grouping Name="matrix1_ColumnGroup">
<GroupExpressions>
<GroupExpression>=Fields(Parameters!ColumnGroup.Value).Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.5in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Top>1.125in</Top>
<Width>3.125in</Width>
<Corner>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Textbox Name="textbox2">
<Left>0.875in</Left>
<ZIndex>1</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<UserSort>
<SortExpression>=Fields(Parameters!ColumnGroup.Value).Value</SortExpression>
<SortExpressionScope>matrix1_ColumnGroup</SortExpressionScope>
</UserSort>
<Value>Sort columns</Value>
</Textbox>
<Textbox Name="textbox4">
<Top>0.25in</Top>
<rd:DefaultName>textbox4</rd:DefaultName>
<Width>1.25in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields(Parameters!RowGroup.Value).Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup</SortExpressionScope>
</UserSort>
<Value>Sort rows</Value>
</Textbox>
</ReportItems>
<ZIndex>3</ZIndex>
</Rectangle>
</ReportItems>
</Corner>
<Height>0.75in</Height>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ProductID">
<rd:DefaultName>ProductID</rd:DefaultName>
<Style>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!ProductID.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Top>0.125in</Top>
<Height>2.25in</Height>
</Rectangle>
</ReportItems>
<Height>4in</Height>
</Body>
<rd:ReportID>4614d21e-03f0-4b4b-8270-a40c31094d26</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from products</CommandText>
<DataSourceName>northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>ProductID</DataField>
</Field>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="SupplierID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>SupplierID</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
<Field Name="QuantityPerUnit">
<rd:TypeName>System.String</rd:TypeName>
<DataField>QuantityPerUnit</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="UnitsInStock">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsInStock</DataField>
</Field>
<Field Name="UnitsOnOrder">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>UnitsOnOrder</DataField>
</Field>
<Field Name="ReorderLevel">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>ReorderLevel</DataField>
</Field>
<Field Name="Discontinued">
<rd:TypeName>System.Boolean</rd:TypeName>
<DataField>Discontinued</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>3.875in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Pyush Kumar

Robert,

Many thanks for your helpfull post. That helped me to come forward, however only one problem remains at the moment.

The first column in my matrix contains productname, the second and following column contains properties of the the products(each product can have a unlimited number of properties), now I would like to sort this columns individually based open the property values.

Example of my reports

Product Duration Length
Xy 1 2000
yx 3 220

How can one do this

Best regards
Yozz
le_montmartre, you could use ILNumerics.Net for sorting your columns. The library comes with extended sorting functionality compatible to Matlab sort(). It is free of charge too.

ILArray<double> table = new double[]{{2,1},{2000,100}};
ILArray<double> sortCol1 = ILMath.sort(table[':;1']);
// evaluates to:
//sortCol1
//<Double> [2,1]
// 100.0
// 2000.0

Much more flexible sorting supported... (and lot of matrix manipulations, linear algebra, complex numbers, n-dimensions etc....)
Jim Webb

I have still not solved this issue and I would love to get some help on this one.
Sorting with Matrix

Answer this question

Sorting with Matrix

Hot Topic

Visual Studio

Sorting with Matrix

Sorting with Matrix

Sowbhagya

Pyush Kumar

Yozz

Jim Webb