I am having problems specifying a running total within a matrix. I have
monthly values in the columns of the matrix so that I am displaying Month To
Date (MTD) sales numbers in the data. I would like to use a running total
across months to get a YTD report. I am trying to add a row group to the
matrix and define an expression referencing the RunningValue of the
salesamout field.
I get errors about running values cannot be used in group expressions. How
would I solve this problem?You can use the RunningValue function in matrix headers and in matrix cells
(which seems what you want to achieve), but not in the matrix grouping
expression. Therefore, just use a grouping expression like
=Fields!Month.Value for the column grouping and use a function in the matrix
cell similar to: =RunningValue(Fields!Sales.Value, Sum,
"CategoryYearGrouping") - where CategoryYearGrouping would represent the
parent group of the month grouping. See also:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/rscreate/htm/rcr_creating_expressions_v1_5tt1.asp
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mark" <Mark@.discussions.microsoft.com> wrote in message
news:6B23ECD0-8C7F-4AF1-A414-1A712E686303@.microsoft.com...
> I am having problems specifying a running total within a matrix. I have
> monthly values in the columns of the matrix so that I am displaying Month
To
> Date (MTD) sales numbers in the data. I would like to use a running total
> across months to get a YTD report. I am trying to add a row group to the
> matrix and define an expression referencing the RunningValue of the
> salesamout field.
> I get errors about running values cannot be used in group expressions. How
> would I solve this problem?
Showing posts with label matrix. Show all posts
Showing posts with label matrix. Show all posts
Friday, March 30, 2012
Wednesday, March 21, 2012
Question about Interactive Sort
I use a matrix to display data,and I find the Interactive Sort seems no use in the matrix. I am confused with the "SortExpressionScope". What should it been when there are multigroup in a table or in a matrix.
Thanks for help.
In matrices, data is always grouped (even in cells), so detail sort wouldn't do anything. You can set the "SortExpressionScope" to one of the group in the matrix rows or column if you want to sort data by that row or column. Here is an example of how to do that:
<?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>30d8ee62-a72d-48fa-ad77-66fdebc3f620</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>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1in</Width>
</MatrixColumn>
</MatrixColumns>
<RowGroupings>
<RowGrouping>
<Width>1.5in</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="CategoryID">
<rd:DefaultName>CategoryID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CategoryName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_RowGroup1">
<GroupExpressions>
<GroupExpression>=Fields!CategoryID.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="CompanyName">
<rd:DefaultName>CompanyName</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_CompanyName">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.5in</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>DataSet1</DataSetName>
<Width>2.5in</Width>
<Corner>
<ReportItems>
<Rectangle Name="rectangle1">
<ReportItems>
<Textbox Name="textbox12">
<Top>0.25in</Top>
<ZIndex>1</ZIndex>
<Width>0.75in</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<UserSort>
<SortExpression>=Fields!CategoryName.Value</SortExpression>
<SortExpressionScope>matrix1_RowGroup1</SortExpressionScope>
</UserSort>
<Value>Rows</Value>
</Textbox>
<Textbox Name="textbox11">
<Left>0.75in</Left>
<rd:DefaultName>textbox11</rd:DefaultName>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.25in</Height>
<UserSort>
<SortExpression>=Fields!CompanyName.Value</SortExpression>
<SortExpressionScope>matrix1_CompanyName</SortExpressionScope>
</UserSort>
<Value>Cols</Value>
</Textbox>
</ReportItems>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</Rectangle>
</ReportItems>
</Corner>
<MatrixRows>
<MatrixRow>
<Height>0.25in</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="Quantity">
<rd:DefaultName>Quantity</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Sum(Fields!Quantity.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>0.75in</Height>
</Body>
<rd:ReportID>87afdd90-7b33-49bc-8880-0df212c6637a</rd:ReportID>
<LeftMargin>1in</LeftMargin>
<DataSets>
<DataSet Name="DataSet1">
<Query>
<CommandText>SELECT TOP 30 Products.ProductName, Products.UnitPrice, Customers.CompanyName, [Order Details].Quantity, Categories.CategoryName,
Products.CategoryID
FROM Products INNER JOIN
[Order Details] ON Products.ProductID = [Order Details].ProductID INNER JOIN
Orders ON [Order Details].OrderID = Orders.OrderID INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID INNER JOIN
Categories ON Products.CategoryID = Categories.CategoryID</CommandText>
<DataSourceName>Northwind</DataSourceName>
</Query>
<Fields>
<Field Name="ProductName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>ProductName</DataField>
</Field>
<Field Name="UnitPrice">
<rd:TypeName>System.Decimal</rd:TypeName>
<DataField>UnitPrice</DataField>
</Field>
<Field Name="CompanyName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CompanyName</DataField>
</Field>
<Field Name="Quantity">
<rd:TypeName>System.Int16</rd:TypeName>
<DataField>Quantity</DataField>
</Field>
<Field Name="CategoryName">
<rd:TypeName>System.String</rd:TypeName>
<DataField>CategoryName</DataField>
</Field>
<Field Name="CategoryID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>CategoryID</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Code />
<Width>6.50001in</Width>
<InteractiveHeight>11in</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>1in</TopMargin>
</Report>
Subscribe to:
Posts (Atom)