Monday, March 19, 2012

Interactive sorting in matrix

How could I accomplish interactive sorting inside the matrix control? I have matrix like this:

=Fields!Time_Calculations.Value (Current, Prior Year)
Forecast Sale
=Fields!Month xxxxx xxx

And I need to sort by Forecast and Sale.

Thnaks

The matrix region is somewhat special when it comes to sorting due to the way it aggregates data. In short, you need to use the corner textbox to set up your user sort, as this report definition demonstrates.

<?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">

<ConnectionProperties>

<IntegratedSecurity>true</IntegratedSecurity>

<ConnectString>Data Source=localhost;Initial Catalog=Northwind</ConnectString>

<DataProvider>SQL</DataProvider>

</ConnectionProperties>

<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>

<Textbox Name="textbox3">

<Left>0.125in</Left>

<Top>0.375in</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 " &amp; Parameters!ColumnGroup.Label</Value>

</Textbox>

<Textbox Name="textbox1">

<Left>0.125in</Left>

<Top>0.125in</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 " &amp; Parameters!RowGroup.Label</Value>

</Textbox>

<Matrix Name="matrix1">

<MatrixColumns>

<MatrixColumn>

<Width>1in</Width>

</MatrixColumn>

</MatrixColumns>

<Left>0.125in</Left>

<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.25in</Height>

</ColumnGrouping>

</ColumnGroupings>

<DataSetName>DataSet1</DataSetName>

<Top>0.875in</Top>

<Width>3.125in</Width>

<Corner>

<ReportItems>

<Textbox Name="textbox4">

<rd:DefaultName>textbox4</rd:DefaultName>

<ZIndex>3</ZIndex>

<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>

</Corner>

<Height>0.5in</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>

<Height>2in</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.375in</Width>

<InteractiveHeight>11in</InteractiveHeight>

<Language>en-US</Language>

<TopMargin>1in</TopMargin>

</Report>

Posted by bushsdfx at 7:52 PM
Labels: , , , , , , , , , , , , , , ,

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)