RunningValue In Matrix

Hello, I was wonder if it is possible to have a RunningValue per row on a
matrix? I want to Sum a number onto itself and show in each columnfor the
first row and reset for the second row and start all over again. I am going
to use conditional statements to control what value is being summed based on
the rowgroup I am at, but that number will have to sum onto itself for every
column in each row.
Ie. 38, 76, 114, 152, 190 in first row
second row: 38, 76, 114, 152, 190
These are based on the variable Fields!WC_TOTALS_DIVIDEND_ROUNDED.Value
Thanks,
BJYes reporting services literally provides a runningvalue function
called runningvalue. In your case runningvalue(Sum(Fields!
Wc_TOTALS_DIVIDEND_ROUNDED)). Put it in the data field of the matrix.
Make sure to use the appropriate column group.

RunningValue + Aggregate = ERROR?

I am trying to do alternative color row for my report,
1. On Table - it works
=iif(RowNumber(Nothing) Mod 2 , "#F9F9F9","#EBEBEB" )
2. On Matrix with Value - it works too
=iif(RunningValue(Fields!Name.Value, CountDistinct, Nothing) Mod 2,
"#EEEEEE", "#FFFFFF")
3. On Matrix with Aggregate function - it DOESN'T WORK
=iif(RunningValue(Fields!OrderAmount.Value, Sum, Nothing) Mod 2, "#EEEEEE",
"#FFFFFF")
=iif(RunningValue(Sum(Fields!OrderAmount.Value), CountDistinct, Nothing) Mod
2, "#EEEEEE", "#FFFFFF")
It said scope parameter is not for RunningValue?The "Nothing" scope is not supported in matrix cells - because otherwise it
is not clear if the RunningValue should run horizontally or vertically
through the matrix cells. The scope has to be a specific matrix row group or
column group.
In your case, it sounds like you want to achieve alternating colors for
matrix rows. Take a look at the following blog article:
http://blogs.msdn.com/chrishays/archive/2004/08/30/GreenBarMatrix.aspx
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"Zean Smith" <nospam@.nospamaaamail.com> wrote in message
news:Wv2dnQkV7dBsD__eRVn-qw@.rogers.com...
>I am trying to do alternative color row for my report,
> 1. On Table - it works
> =iif(RowNumber(Nothing) Mod 2 , "#F9F9F9","#EBEBEB" )
> 2. On Matrix with Value - it works too
> =iif(RunningValue(Fields!Name.Value, CountDistinct, Nothing) Mod 2,
> "#EEEEEE", "#FFFFFF")
> 3. On Matrix with Aggregate function - it DOESN'T WORK
> =iif(RunningValue(Fields!OrderAmount.Value, Sum, Nothing) Mod 2,
> "#EEEEEE", "#FFFFFF")
> =iif(RunningValue(Sum(Fields!OrderAmount.Value), CountDistinct, Nothing)
> Mod 2, "#EEEEEE", "#FFFFFF")
> It said scope parameter is not for RunningValue?
>
>

Running Value in Matrix

Is is possible to have a running value in a matrix? The application has:
-- Weeks --
! 1 2 3 4 Total(Month)
!Jan x x x x Sum
Months!Feb
!...
!Total x x x x Sum
I would like to have a column after the Total(Month) that would represent
the running total for the calendar year. (Jan = Jan) (Feb = Jan + Feb)...
Thank you.Rs has a function by that very name Runningvalue... It is documented in
Rerporting Services books..
See if that is what you need.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Gary Murrish" <gmush@.swbell.net> wrote in message
news:u3chDqEYEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Is is possible to have a running value in a matrix? The application has:
> -- Weeks --
> ! 1 2 3 4 Total(Month)
> !Jan x x x x Sum
> Months!Feb
> !...
> !Total x x x x Sum
> I would like to have a column after the Total(Month) that would represent
> the running total for the calendar year. (Jan = Jan) (Feb = Jan + Feb)...
> Thank you.
>|||Yes, just use the RunningValue function and your column group name as the
scope.
Fang Wang (MSFT)
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Gary Murrish" <gmush@.swbell.net> wrote in message
news:u3chDqEYEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Is is possible to have a running value in a matrix? The application has:
> -- Weeks --
> ! 1 2 3 4 Total(Month)
> !Jan x x x x Sum
> Months!Feb
> !...
> !Total x x x x Sum
> I would like to have a column after the Total(Month) that would represent
> the running total for the calendar year. (Jan = Jan) (Feb = Jan + Feb)...
> Thank you.
>|||The problem is how do I drop it into the Matrix after the Total Column?
Gary Murrish
"Fang Wang (MSFT)" <fangw@.microsoft.com> wrote in message
news:eqcny2IYEHA.2964@.TK2MSFTNGP10.phx.gbl...
> Yes, just use the RunningValue function and your column group name as the
> scope.
> Fang Wang (MSFT)
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> "Gary Murrish" <gmush@.swbell.net> wrote in message
> news:u3chDqEYEHA.2544@.TK2MSFTNGP10.phx.gbl...
> > Is is possible to have a running value in a matrix? The application has:
> >
> > -- Weeks --
> > ! 1 2 3 4 Total(Month)
> > !Jan x x x x Sum
> > Months!Feb
> > !...
> > !Total x x x x Sum
> >
> > I would like to have a column after the Total(Month) that would
represent
> > the running total for the calendar year. (Jan = Jan) (Feb = Jan +
Feb)...
> >
> > Thank you.
> >
> >
>|||Since the value in your matrix is different from the one you want to
subtotal, you can use a different value based on the the InScope function.
=IIF(InScope("matrix1_weeks"),Sum(Fields!sales.Value),RunningValue(Fields!sales.Value,
Sum, "matrix1_weeks"))
I've posted a full example using the pubs database below.
--
Brian Welcker
Group Program Manager
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
<?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="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>
<Style />
<MatrixRows>
<MatrixRow>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="ytd_sales_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>ytd_sales_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=IIF(InScope("matrix1_pub_name"),Sum(Fields!ytd_sales.Value),
RunningValue(Fields!ytd_sales.Value, Sum, "matrix1_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="matrix1_pub_name">
<GroupExpressions>
<GroupExpression>=Fields!pub_name.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<ReportItems>
<Textbox Name="pub_name">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>pub_name</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!pub_name.Value</Value>
</Textbox>
</ReportItems>
<Subtotal>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
<FontWeight>700</FontWeight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Total</Value>
</Textbox>
</ReportItems>
</Subtotal>
</DynamicColumns>
<Height>0.25in</Height>
</ColumnGrouping>
</ColumnGroupings>
<Width>3.375in</Width>
<Top>0.625in</Top>
<Left>0.25in</Left>
<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>
</Matrix>
</ReportItems>
<Style />
<Height>2.5in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="MyReports5">
<rd:DataSourceID>adedf68c-bc44-419e-945a-ceb24c77c2e2</rd:DataSourceID>
<DataSourceReference>MyReports5</DataSourceReference>
</DataSource>
</DataSources>
<Width>6.25in</Width>
<DataSets>
<DataSet Name="DataSet1">
<Fields>
<Field Name="title_id">
<DataField>title_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="title">
<DataField>title</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="type">
<DataField>type</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="pub_id">
<DataField>pub_id</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="price">
<DataField>price</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="advance">
<DataField>advance</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="royalty">
<DataField>royalty</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="ytd_sales">
<DataField>ytd_sales</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="notes">
<DataField>notes</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="pubdate">
<DataField>pubdate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="pub_name">
<DataField>pub_name</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>MyReports5</DataSourceName>
<CommandText>SELECT titles.*, publishers.pub_name
FROM titles INNER JOIN
publishers ON titles.pub_id = publishers.pub_id
WHERE (titles.ytd_sales > 0)</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>6e1b5581-c1e8-493d-a6e5-4ff74ef05171</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>
"Gary Murrish" <gmush@.swbell.net> wrote in message
news:urUFKbKYEHA.3156@.TK2MSFTNGP12.phx.gbl...
> The problem is how do I drop it into the Matrix after the Total Column?
> Gary Murrish
> "Fang Wang (MSFT)" <fangw@.microsoft.com> wrote in message
> news:eqcny2IYEHA.2964@.TK2MSFTNGP10.phx.gbl...
>> Yes, just use the RunningValue function and your column group name as the
>> scope.
>> Fang Wang (MSFT)
>> Microsoft SQL Server Reporting Services
>> This posting is provided "AS IS" with no warranties, and confers no
> rights.
>> "Gary Murrish" <gmush@.swbell.net> wrote in message
>> news:u3chDqEYEHA.2544@.TK2MSFTNGP10.phx.gbl...
>> > Is is possible to have a running value in a matrix? The application
>> > has:
>> >
>> > -- Weeks --
>> > ! 1 2 3 4 Total(Month)
>> > !Jan x x x x Sum
>> > Months!Feb
>> > !...
>> > !Total x x x x Sum
>> >
>> > I would like to have a column after the Total(Month) that would
> represent
>> > the running total for the calendar year. (Jan = Jan) (Feb = Jan +
> Feb)...
>> >
>> > Thank you.
>> >
>> >
>>
>

Running totals in matrix?

Is it possible to create a row in a matrix that represents the running total
over time? Can I do this in the matrix itself or does this need to be
calculated in the dataset? If it's the latter, any ideas for how to do this
simply?
Example:
Jan Feb Mar
Monthly Sales 150 59 124
Cumulative Sales 150 209 333
Thanks!there is a running value in the aggregation expression...
On Jan 5, 1:36=A0am, jmann <jm...@.discussions.microsoft.com> wrote:
> Is it possible to create a row in a matrix that represents the running tot=al
> over time? =A0Can I do this in the matrix itself or does this need to be
> calculated in the dataset? =A0If it's the latter, any ideas for how to do =this
> simply?
> Example:
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0Jan =A0= =A0 =A0 Feb =A0 =A0 =A0 Mar
> Monthly Sales =A0 =A0 =A0 =A0 =A0 150 =A0 =A0 =A0 =A059 =A0 =A0 =A0 =A0 ==A0124
> Cumulative Sales =A0 =A0 =A0150 =A0 =A0 =A0 =A0209 =A0 =A0 =A0 =A0333
> Thanks!|||On Jan 5, 4:53 am, Sridar K <srid...@.gmail.com> wrote:
> there is a running value in the aggregation expression...
> On Jan 5, 1:36 am, jmann <jm...@.discussions.microsoft.com> wrote:
> > Is it possible to create a row in a matrix that represents the running total
> > over time? Can I do this in the matrix itself or does this need to be
> > calculated in the dataset? If it's the latter, any ideas for how to do this
> > simply?
> > Example:
> > Jan Feb Mar
> > Monthly Sales 150 59 124
> > Cumulative Sales 150 209 333
> > Thanks!
The Running Total/Value expression is explained here:
http://msdn2.microsoft.com/en-us/library/aa255824.aspx
Just as an FYI, you would normally use a cursor or while loop to
provide this type of functionality in the stored procedure/query that
is sourcing the report. Of course, this option adds complexity and
poor performance, so I would suggest using the Running Value
expression. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||I'm trying the RunningTotal expression (Fields!Sales.Value, Sum, Nothing) and
getting the following error:
"The value expression for the textbox â'textbox6â' has a scope parameter that
is not valid for RunningValue, RowNumber or Previous. The scope parameter
must be set to a string constant that is equal to the name of a containing
group within the matrix â'matrix1â'." Not sure what this means. Any ideas?
Thanks!
"EMartinez" wrote:
> On Jan 5, 4:53 am, Sridar K <srid...@.gmail.com> wrote:
> > there is a running value in the aggregation expression...
> >
> > On Jan 5, 1:36 am, jmann <jm...@.discussions.microsoft.com> wrote:
> >
> > > Is it possible to create a row in a matrix that represents the running total
> > > over time? Can I do this in the matrix itself or does this need to be
> > > calculated in the dataset? If it's the latter, any ideas for how to do this
> > > simply?
> >
> > > Example:
> >
> > > Jan Feb Mar
> > > Monthly Sales 150 59 124
> > > Cumulative Sales 150 209 333
> >
> > > Thanks!
>
> The Running Total/Value expression is explained here:
> http://msdn2.microsoft.com/en-us/library/aa255824.aspx
> Just as an FYI, you would normally use a cursor or while loop to
> provide this type of functionality in the stored procedure/query that
> is sourcing the report. Of course, this option adds complexity and
> poor performance, so I would suggest using the Running Value
> expression. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant
>

Running sum

I'm not quite sure if i'm aiming for the impossible, but:
In a matrix, i have time (weeks) as columns, items as rows and qty as detail
and i would like to have a running sum for the columns.
This problems arise because i need to columns to be dynamic.
Any suggestions would be more than welcome
Example:
Records:
Item 1, week 1, qty 5
Item 1,week 2, qty 3
Item 1, week 3, qty 4
Matrix
week 1 week 2 week 3
Item 1 5 8 12I seem to have found the solution (RunningValue(Expression, Function,
Scope)).
Sorry for the inconvenience
"Claus Aaberg" <caa@.phaseone.com> wrote in message
news:%23TPAKwKcEHA.368@.TK2MSFTNGP10.phx.gbl...
> I'm not quite sure if i'm aiming for the impossible, but:
> In a matrix, i have time (weeks) as columns, items as rows and qty as
detail
> and i would like to have a running sum for the columns.
> This problems arise because i need to columns to be dynamic.
> Any suggestions would be more than welcome
> Example:
> Records:
> Item 1, week 1, qty 5
> Item 1,week 2, qty 3
> Item 1, week 3, qty 4
> Matrix
> week 1 week 2 week 3
> Item 1 5 8 12
>sql