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.
>> >
>> >
>>
>
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment