Descending RunningValue? -- sql server reporting services

Robert Bruckner [MSFT]
4/20/2005 5:47:04 PM

You could try the following expression:
=IIF(InScope("matrix3_RowGroup1"), Sum(Fields!MonthAmount.Value),
Sum(Fields!MonthAmount.Value) - RunningValue(Fields!MonthAmount.Value, Sum,
"matrix3_RowGroup1"))

Note: the third argument of the IIF function would get used if your not in
the scope of RowGroup1. Therefore, the Sum() function should calculate the
Sum of all cells of the entire RowGroup1 and you can just subtract the
RunningValue.

-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.

[quoted text, click to view]

twbanks
4/21/2005 12:18:34 PM

Thanks for the suggestion, Robert. Unfortunately, that subtracted the
runningvalue from the sum for that month only. Below is an example rdl
with first just the sum, then your suggestion, based on the pubs db.
Is there a way to reference the entire sum rather than just the
monthly?

RDL below:

<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefinition"
xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Matrix Name="matrix2">
<Corner>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.46875in</Height>
<ZIndex>1</ZIndex>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<CanGrow>true</CanGrow>

<Value>=IIF(InScope("matrix2_pub_name"),Sum(Fields!ytd_sales.Value),Sum(Fields!ytd_sales.Value)
- RunningValue(Fields!ytd_sales.Value, Sum,
"matrix2_pub_name"))</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
<Height>0.21875in</Height>
</MatrixRow>
</MatrixRows>
<MatrixColumns>
<MatrixColumn>
<Width>1.125in</Width>
</MatrixColumn>
</MatrixColumns>
<DataSetName>DataSet1</DataSetName>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<Grouping Name="matrix2_pub_name">
<GroupExpressions>

<GroupExpression>=Fields!pub_name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!pub_name.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.375in</Width>
<Top>1.25in</Top>
<Left>0.25in</Left>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix2_RowGroup1">
<GroupExpressions>
<GroupExpression>=Fields!type.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<CanGrow>true</CanGrow>
<Value>=Fields!type.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1.125in</Width>
</RowGrouping>
</RowGroupings>
</Matrix>
<Matrix Name="matrix1">
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<Height>0.46875in</Height>
<RowGroupings>
<RowGrouping>
<DynamicRows>
<Grouping Name="matrix1_type">
<GroupExpressions>
<GroupExpression>=Fields!type.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="type">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>type</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!type.Value</Value>
</Textbox>
</ReportItems>
</DynamicRows>
<Width>1.125in</Width>
</RowGrouping>
</RowGroupings>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ytd_sales_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>

sql server reporting services : Descending RunningValue?