db files: sum

Showing posts with label sum. Show all posts

Wednesday, March 28, 2012

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 doesnt do anything useful for me

I put =RunningValue(Fields!FieldName.Value, Sum, Nothing) in a tables group
footer. It displays the same total on every page.
I put in a new details line on the table. It works fine. But prints itself
all over page rather than once at bottom of each page.
Help !
Chris BrooksbankChris,
At the bottom of this posting is an example of how to use a running value in
a group footer.
Please let me know if this is what you are attempting to achieve. This
sample counts the number of items in the group footer and the table footer.
Note that the Group footer accumulates as expected while the same expression
shows the total when use in the table footer.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Chris Brooksbank" <NoSpam@.Ta.com> wrote in message
news:eTUNnuBZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> I put =RunningValue(Fields!FieldName.Value, Sum, Nothing) in a tables
group
> footer. It displays the same total on every page.
> I put in a new details line on the table. It works fine. But prints itself
> all over page rather than once at bottom of each page.
> Help !
> Chris Brooksbank
>
RunningValueInGroupFooterSample.rdl
----
--
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>23</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Customer ID</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>22</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Company Name</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>21</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="ProductID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>ProductID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!ProductID.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>UnitPrice</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!UnitPrice.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CustomerID">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>20</ZIndex>
<rd:DefaultName>CustomerID</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=First(Fields!CustomerID.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>19</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>18</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!CompanyName.Value</GroupExpression>
</GroupExpressions>
<PageBreakAtEnd>true</PageBreakAtEnd>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="UnitPrice_1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>UnitPrice_1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Group Footer : Count in this group</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Count(Fields!UnitPrice.Value)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox16">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox16</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>textbox4</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Group Footer : Running Count of
Items</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox5</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value,
Count, "table1")</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox10">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox10</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox18">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<CanGrow>true</CanGrow>
<Value>Group Footer : Running Count of
Items</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox20">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value,
Count, Nothing)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox19">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox19</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>17</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>Table Footer : Running Count </Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<TextAlign>Right</TextAlign>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>16</ZIndex>
<CanGrow>true</CanGrow>
<Value>=RunningValue(Fields!UnitPrice.Value, Count,
Nothing)</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>15</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
<RepeatOnNewPage>true</RepeatOnNewPage>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.375in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
<TableColumn>
<Width>1.66667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>5.70834in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="OrderID">
<DataField>OrderID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="ProductID">
<DataField>ProductID</DataField>
<rd:TypeName>System.Int32</rd:TypeName>
</Field>
<Field Name="UnitPrice">
<DataField>UnitPrice</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
<Field Name="Quantity">
<DataField>Quantity</DataField>
<rd:TypeName>System.Int16</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT Customers.CustomerID, Customers.CompanyName,
Orders.OrderID, Orders.OrderDate, [Order Details].ProductID, [Order
Details].UnitPrice,
[Order Details].Quantity
FROM Customers INNER JOIN
Orders ON Customers.CustomerID = Orders.CustomerID
INNER JOIN
[Order Details] ON Orders.OrderID = [Order
Details].OrderID</CommandText>
<Timeout>30</Timeout>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
<BottomMargin>1in</BottomMargin>
</Report>|||Bruce:
Thank you so much for the sample but this sample does not sum at the end of
the PAGE. It summed by company_id on each page.
At the end of each page I need to have a running sum of that Page not just
only by company_id. Because one page may contains multiple company_id.
Thanks,
John
"Bruce Johnson [MSFT]" wrote:
> Chris,
> At the bottom of this posting is an example of how to use a running value in
> a group footer.
> Please let me know if this is what you are attempting to achieve. This
> sample counts the number of items in the group footer and the table footer.
> Note that the Group footer accumulates as expected while the same expression
> shows the total when use in the table footer.
> --
> Bruce Johnson [MSFT]
> Microsoft SQL Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no rights.
>
> "Chris Brooksbank" <NoSpam@.Ta.com> wrote in message
> news:eTUNnuBZEHA.3304@.TK2MSFTNGP09.phx.gbl...
> > I put =RunningValue(Fields!FieldName.Value, Sum, Nothing) in a tables
> group
> > footer. It displays the same total on every page.
> >
> > I put in a new details line on the table. It works fine. But prints itself
> > all over page rather than once at bottom of each page.
> >
> > Help !
> >
> > Chris Brooksbank
> >
> >
> RunningValueInGroupFooterSample.rdl
> ----
> --
> <?xml version="1.0" encoding="utf-8"?>
> <Report
> xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
> tion"
> xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
> <RightMargin>1in</RightMargin>
> <Body>
> <ReportItems>
> <Table Name="table1">
> <Style />
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>23</ZIndex>
> <rd:DefaultName>textbox1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Customer ID</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox2">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>22</ZIndex>
> <rd:DefaultName>textbox2</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Company Name</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox3">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>21</ZIndex>
> <rd:DefaultName>textbox3</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Details>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="ProductID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>2</ZIndex>
> <rd:DefaultName>ProductID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!ProductID.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="UnitPrice">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>1</ZIndex>
> <rd:DefaultName>UnitPrice</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!UnitPrice.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox6">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <rd:DefaultName>textbox6</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> </Details>
> <DataSetName>Northwind</DataSetName>
> <TableGroups>
> <TableGroup>
> <Header>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="CustomerID">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>20</ZIndex>
> <rd:DefaultName>CustomerID</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=First(Fields!CustomerID.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="CompanyName">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>19</ZIndex>
> <rd:DefaultName>CompanyName</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Fields!CompanyName.Value</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox12">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>18</ZIndex>
> <rd:DefaultName>textbox12</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Header>
> <Grouping Name="table1_Group1">
> <GroupExpressions>
> <GroupExpression>=Fields!CompanyName.Value</GroupExpression>
> </GroupExpressions>
> <PageBreakAtEnd>true</PageBreakAtEnd>
> </Grouping>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox13">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>5</ZIndex>
> <rd:DefaultName>textbox13</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="UnitPrice_1">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>4</ZIndex>
> <rd:DefaultName>UnitPrice_1</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox15">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>3</ZIndex>
> <rd:DefaultName>textbox15</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox11">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>8</ZIndex>
> <rd:DefaultName>textbox11</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Count in this group</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox14">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>7</ZIndex>
> <rd:DefaultName>textbox14</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=Count(Fields!UnitPrice.Value)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox16">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>6</ZIndex>
> <rd:DefaultName>textbox16</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox4">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>11</ZIndex>
> <rd:DefaultName>textbox4</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Running Count of
> Items</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox5">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>10</ZIndex>
> <rd:DefaultName>textbox5</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value,
> Count, "table1")</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox10">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>9</ZIndex>
> <rd:DefaultName>textbox10</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox18">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>14</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>Group Footer : Running Count of
> Items</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox20">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>13</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value,
> Count, Nothing)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox19">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>12</ZIndex>
> <rd:DefaultName>textbox19</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Footer>
> </TableGroup>
> </TableGroups>
> <Footer>
> <TableRows>
> <TableRow>
> <Height>0.25in</Height>
> <TableCells>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox7">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>17</ZIndex>
> <rd:DefaultName>textbox7</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value>Table Footer : Running Count </Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox17">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <TextAlign>Right</TextAlign>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>16</ZIndex>
> <CanGrow>true</CanGrow>
> <Value>=RunningValue(Fields!UnitPrice.Value, Count,
> Nothing)</Value>
> </Textbox>
> </ReportItems>
> </TableCell>
> <TableCell>
> <ReportItems>
> <Textbox Name="textbox9">
> <Style>
> <PaddingLeft>2pt</PaddingLeft>
> <PaddingBottom>2pt</PaddingBottom>
> <PaddingTop>2pt</PaddingTop>
> <PaddingRight>2pt</PaddingRight>
> </Style>
> <ZIndex>15</ZIndex>
> <rd:DefaultName>textbox9</rd:DefaultName>
> <CanGrow>true</CanGrow>
> <Value />
> </Textbox>
> </ReportItems>
> </TableCell>
> </TableCells>
> </TableRow>
> </TableRows>
> <RepeatOnNewPage>true</RepeatOnNewPage>
> </Footer>
> <TableColumns>
> <TableColumn>
> <Width>2.375in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.66667in</Width>
> </TableColumn>
> <TableColumn>
> <Width>1.66667in</Width>
> </TableColumn>
> </TableColumns>
> </Table>
> </ReportItems>
> <Style />
> <Height>2in</Height>
> </Body>
> <TopMargin>1in</TopMargin>
> <DataSources>
> <DataSource Name="Northwind">
> <rd:DataSourceID>32d95cbf-5e5b-4fb3-a37a-39b9506b8c80</rd:DataSourceID>
> <ConnectionProperties>
> <DataProvider>SQL</DataProvider>
> <ConnectString>data source=localhost;initial
> catalog=Northwind</ConnectString>
> <IntegratedSecurity>true</IntegratedSecurity>
> </ConnectionProperties>
> </DataSource>
> </DataSources>
> <Width>5.70834in</Width>
> <DataSets>
> <DataSet Name="Northwind">
> <Fields>
> <Field Name="CustomerID">
> <DataField>CustomerID</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="CompanyName">
> <DataField>CompanyName</DataField>
> <rd:TypeName>System.String</rd:TypeName>
> </Field>
> <Field Name="OrderID">
> <DataField>OrderID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="OrderDate">
> <DataField>OrderDate</DataField>
> <rd:TypeName>System.DateTime</rd:TypeName>
> </Field>
> <Field Name="ProductID">
> <DataField>ProductID</DataField>
> <rd:TypeName>System.Int32</rd:TypeName>
> </Field>
> <Field Name="UnitPrice">
> <DataField>UnitPrice</DataField>
> <rd:TypeName>System.Decimal</rd:TypeName>
> </Field>
> <Field Name="Quantity">
> <DataField>Quantity</DataField>
> <rd:TypeName>System.Int16</rd:TypeName>
> </Field>
> </Fields>
> <Query>
> <DataSourceName>Northwind</DataSourceName>
> <CommandText>SELECT Customers.CustomerID, Customers.CompanyName,
> Orders.OrderID, Orders.OrderDate, [Order Details].ProductID, [Order
> Details].UnitPrice,
> [Order Details].Quantity
> FROM Customers INNER JOIN
> Orders ON Customers.CustomerID = Orders.CustomerID
> INNER JOIN
> [Order Details] ON Orders.OrderID = [Order
> Details].OrderID</CommandText>
> <Timeout>30</Timeout>
> </Query>
> </DataSet>
> </DataSets>
> <LeftMargin>1in</LeftMargin>
> <rd:SnapToGrid>true</rd:SnapToGrid>
> <rd:DrawGrid>true</rd:DrawGrid>
> <rd:ReportID>4792d607-5639-4c89-ac36-2794e9e78a74</rd:ReportID>
> <BottomMargin>1in</BottomMargin>
> </Report>
>
>

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 Value

Hi all

I'm not sure if this is a BUG or if I'm missing something when using the Running value with Sum.

(WORKS FINE IF I DO NOT USE A SUBREPORT)

(=RunningValue(Fields!pervalue.Value,Sum,"chart1_SeriesGroup1")

I have a main report and a subreport, the key field (series group in the chart) that should reset the running value is passed by the main report, so is always different which should reset the running value sum to zero.

That is not working, the second time the subreport is invoked, seems like it takes the value from the previous last record and increase it by the same amount.

example

First call to subreport -

key field value = 1 (chart1_SeriesGroup1) subreport retrieve 4 records

rec 1 - pervalue 10

rec 2 - pervalue 5

rec 3 - pervalue 20

rec 4 - pervalue 15

everthing looks great

second call to subreport

key field value = 2 (this value is used in the running value reset series)

the subreport display

rec 1 - pervalue 15

rec 2 - pervalue 30

rec 3 - pervalue 45

instead of

rec 1 - pervalue 20

rec 2 - pervalue 50

rec 3 - pervalue 10

Thank in advance

I guess that the running value function, should not be used in a subreport, being that it does not work properly, seems like it saves the last value of the first group and add it to itself.

Thanks

Monday, March 26, 2012

Running Total: Summing on Distinct Amount

I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.

Example:

Month Year Goal Other Value

March 2007 500 5568

March 2007 500 5569

April 2007 600 5568

April 2007 700 5569

Total (I am receive)

March-April 2007 1600 5568

March-April 2007 1200 5569

Total (I excpect)

March-April 2007 1100 5568

March-April 2007 1200 5569

I haven't found anything online to help.

Thanks,

Sam

You could define you data set using the following SQL.

SELECT Month, Year, [Other Value], SUM(Goal)

FROM (

SELECT DISTINCT Month, Year, [Other Value], Goal

FROM TableName

)

GROUP BY Month, Year, [Other Value]

|||

I actually have more data being returned than what is being displayed in my example. I am already doing a group by in data set. Thanks for the thought.

Sam

|||

I hope this example is better:

Example:

Month Year Goal SalesID ActualSales

March 2007 500 5568 550

March 2007 500 5568 475

March 2007 500 5569 605

April 2007 600 5568 700

April 2007 700 5569 710

Friday, March 23, 2012

Running Total

I try to sum the weight by putting the expression
=Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "L", Fields!CONTAINER_WEIGHT.Value, 0))
in group footer section but #Error is returned. On the contrast if the expression: =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "L",1, 0))
No error is returned. Please help me !!!Can anyone help me ?
#Error is displayed in the cell.
"May Liu" wrote:
> I try to sum the weight by putting the expression
> =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "L", Fields!CONTAINER_WEIGHT.Value, 0))
> in group footer section but #Error is returned. On the contrast if the expression: =Sum(IIf(Fields!EQP_LEN.Value = 20 And Fields!EQP_TYPE.Value = "L",1, 0))
> No error is returned. Please help me !!!

Running sum?

Is there running sum function in MS SQL Server 2000 like a property in MS
Access?
In advance thank you,
Stanko Milosev
work:
stanko@.nospam--netcomp.co.yu
www.netcomp.co.yu
home:
stanko@.nospam.milosev.co.yu
www.milosev.co.yuStanko Milosev,

> Is there running sum function in MS SQL Server 2000 like a property in MS
> Access?
No, there is not such function in SQL Server, but you can calculate it.
Example:
use northwind
go
create table t1 (
c1 int not null identity unique,
c2 numeric(9, 2)
)
go
insert into t1(c2) values(193.51)
insert into t1(c2) values(194.5)
insert into t1(c2) values(202.71)
insert into t1(c2) values(192.79)
insert into t1(c2) values(197.6)
insert into t1(c2) values(192.9)
insert into t1(c2) values(192.76)
insert into t1(c2) values(191.91)
insert into t1(c2) values(187.9)
go
select
a.c1,
a.c2,
sum(b.c2) as running_sum_c2
from
t1 as a
left join
t1 as b
on a.c1 >= b.c1
group by a.c1, a.c2
order by a.c1
go
drop table t1
go
AMB
"Stanko Milosev" wrote:

> Is there running sum function in MS SQL Server 2000 like a property in MS
> Access?
> In advance thank you,
> Stanko Milosev
> work:
> stanko@.nospam--netcomp.co.yu
> www.netcomp.co.yu
> home:
> stanko@.nospam.milosev.co.yu
> www.milosev.co.yu
>
>|||I think that the running sum is a function of Access, not the Jet database.
Alejandro gave you a working solution.
You can test it out but you should also test doing the calculations client
side.
You may find (I'm tempted to say "probably") that it will perform better
doing it client side.
"Stanko Milosev" <stanko@.nothing.com> wrote in message
news:Ou7qBQH4FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Is there running sum function in MS SQL Server 2000 like a property in MS
> Access?
> In advance thank you,
> Stanko Milosev
> work:
> stanko@.nospam--netcomp.co.yu
> www.netcomp.co.yu
> home:
> stanko@.nospam.milosev.co.yu
> www.milosev.co.yu
>|||Thank you all, we solve the problem,
Stanko.
"Stanko Milosev" <stanko@.nothing.com> wrote in message
news:Ou7qBQH4FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Is there running sum function in MS SQL Server 2000 like a property in MS
> Access?
> In advance thank you,
> Stanko Milosev
> work:
> stanko@.nospam--netcomp.co.yu
> www.netcomp.co.yu
> home:
> stanko@.nospam.milosev.co.yu
> www.milosev.co.yu
>

Running Sum Query ?

Following query is calculating running sum and running average of an Invento
ry Item, positive quantities are receipts and negative quantities are issued
, last two columns are running sum of quantity and amount column.
While running sum of quantity is ok, but Amt running sum is not correct, any
idea what am I missing ?
My table data is as under:-
Please note, negative quantites have no rates and no amounts, this will be c
alculated on average basis in above query.
Sno field is just an auto numbering field for query purpose.
PRITC QTY RATE PRAMT SNO
-- -- -- -- --
1111-034 200 1494.5 298900 1
1111-034 218 1559 339862 2
1111-034 -150 3
1111-034 -5 4
1111-034 -200 5
1111-034 -5 6
1111-034 -50 7
Best Regards,
Luqman
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
sum(case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end) Amt
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
SNO QTY RATE AMT SUM(B.QTY) AMT
-- -- -- -- --
--
1 200 1494.5 298900 200 298900
2 218 1559 339862 418 679724
3 -150 1528.13876 -229220.81 268 -687662.44
4 -5 1528.13876 -7640.6938 263 -30562.775
5 -200 1528.13876 -305627.75 63 -1528138.8
6 -5 1528.13876 -7640.6938 58 -45844.163
7 -50 1528.13876 -76406.938 8 -534848.56Hello, Luqman
Please post DDL (as "CREATE TABLE" statements) and sample data (as
"INSERT INTO ... VALUES ..." statements), like this:
CREATE TABLE test (
PRITC varchar(10) NOT NULL,
QTY numeric(10,3) NOT NULL,
RATE numeric(18,4) NULL,
PRAMT numeric(18,4) NULL,
SNO int IDENTITY PRIMARY KEY,
CHECK (PRAMT=QTY*RATE)
)
INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
INSERT INTO test VALUES ('1111-034',218,1559,339862)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
The above DDL is just a guess; please correct it if necessary.
Your current query returns the following error: "Cannot perform an
aggregate function on an expression containing an aggregate or a
subquery." The reason is obvious: you cannot use SUM() for an
expression that contains a subquery (for the last column in your
query).
You probably want something like this:
SELECT a.sno, a.qty, a.rate, a.pramt, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qty_sum, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as avg_rate
FROM test a
If you want something else, please post the expected results.
Razvan|||The following query will output something like this.
select a.sno,a.qty,
case when a.qty>0 then a.rate
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Amt,sum(b.qty),
case when a.qty>0 then a.pramt
else
(select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
end Bal
from test a, test b
where a.sno>=b.sno
group by a.sno,a.qty,a.pramt,a.rate
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 339862.000000
3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
running sum of Amount Field in AmtTotal.
The correct display should be:
sno qty rate Amount qtyTotal AmtTotal
1 200.000 1494.500000 298900.000000 200.000 298900.000000
2 218.000 1559.000000 339862.000000 418.000 638762.000000
3 -150.000 1528.138755 -229220.813250 268.000 409541.19
4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
Any idea please ?
Best Regards,
Luqman
"Razvan Socol" <rsocol@.gmail.com> wrote in message
news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
> Hello, Luqman
> Please post DDL (as "CREATE TABLE" statements) and sample data (as
> "INSERT INTO ... VALUES ..." statements), like this:
> CREATE TABLE test (
> PRITC varchar(10) NOT NULL,
> QTY numeric(10,3) NOT NULL,
> RATE numeric(18,4) NULL,
> PRAMT numeric(18,4) NULL,
> SNO int IDENTITY PRIMARY KEY,
> CHECK (PRAMT=QTY*RATE)
> )
>
> INSERT INTO test VALUES ('1111-034',200,1494.5,298900)
> INSERT INTO test VALUES ('1111-034',218,1559,339862)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-150)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-200)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-5)
> INSERT INTO test (PRITC,QTY) VALUES ('1111-034',-50)
> The above DDL is just a guess; please correct it if necessary.
> Your current query returns the following error: "Cannot perform an
> aggregate function on an expression containing an aggregate or a
> subquery." The reason is obvious: you cannot use SUM() for an
> expression that contains a subquery (for the last column in your
> query).
> You probably want something like this:
> SELECT a.sno, a.qty, a.rate, a.pramt, (
> SELECT SUM(qty) FROM test b
> WHERE b.sno<=a.sno
> ) as qty_sum, (
> SELECT SUM(pramt) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as pramt_sum, (
> SELECT SUM(pramt)/SUM(qty) FROM test b
> WHERE b.sno<=a.sno AND qty>0
> ) as avg_rate
> FROM test a
> If you want something else, please post the expected results.
> Razvan
>|||Luqman wrote:
> The correct display should be: [...]
OK, then let's save the SELECT statement in my previous post as a view:
create view myview as
SELECT a.sno, a.qty,
ISNULL(a.rate, (
SELECT SUM(pramt)/SUM(qty) FROM test b
WHERE b.sno<=a.sno AND qty>0
)) as rate, (
SELECT SUM(qty) FROM test b
WHERE b.sno<=a.sno
) as qtyTotal, (
SELECT SUM(pramt) FROM test b
WHERE b.sno<=a.sno AND qty>0
) as pramt_sum
FROM test a
And now we can use the following query:
SELECT x.sno, x.qty, x.rate, x.qty*x.rate as Amount,
x.qtyTotal, x.pramt_sum+isnull((
select sum(qty*rate) from myview y
where y.sno<=x.sno and y.qty<0
),0) as AmtTotal
FROM myview x
The above query returns the expected results (I'm not sure what is the
number of decimals that you want; the number of decimals in the result
changes according to the data types in the table).
However, to be sure that the above query meets all your requirements,
you should also post some sample data where there is a positive value
AFTER some negative values and expected results for this new sample
data.
Razvan|||Hi,
Did you work on my queries, please ?
Best Regards,
Luqman
"Luqman" <pearlsoft@.cyber.net.pk> wrote in message
news:%232Kn49ZiGHA.3884@.TK2MSFTNGP04.phx.gbl...
> The following query will output something like this.
> select a.sno,a.qty,
> case when a.qty>0 then a.rate
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0) end rate,
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Amt,sum(b.qty),
> case when a.qty>0 then a.pramt
> else
> (select sum(pramt)/sum(qty) from test where sno<a.sno and qty>0)*a.qty
> end Bal
> from test a, test b
> where a.sno>=b.sno
> group by a.sno,a.qty,a.pramt,a.rate
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 339862.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 -229220.813250
> 4 -5.000 1528.138755 -7640.693775 263.000 -7640.693775
> 5 -200.000 1528.138755 -305627.751000 63.000 -305627.751000
> 6 -5.000 1528.138755 -7640.693775 58.000 -7640.693775
> 7 -50.000 1528.138755 -76406.937750 8.000 -76406.937750
> Currently the Qtytotal is ok, but AmtTotal is not ok, I want the correct
> running sum of Amount Field in AmtTotal.
> The correct display should be:
> sno qty rate Amount qtyTotal
> AmtTotal
> 1 200.000 1494.500000 298900.000000 200.000 298900.000000
> 2 218.000 1559.000000 339862.000000 418.000 638762.000000
> 3 -150.000 1528.138755 -229220.813250 268.000 409541.19
> 4 -5.000 1528.138755 -7640.693775 263.000 401900.4962
> 5 -200.000 1528.138755 -305627.751000 63.000 96272.7462
> 6 -5.000 1528.138755 -7640.693775 58.000 88632.0524
> 7 -50.000 1528.138755 -76406.937750 8.000 12225.1144
> Any idea please ?
> Best Regards,
> Luqman
>
> "Razvan Socol" <rsocol@.gmail.com> wrote in message
> news:1149586142.846012.210890@.f6g2000cwb.googlegroups.com...
>|||Razvan Socol wrote:
Luqman wrote:
> Did you work on my queries, please ?
My last message was:
http://groups.google.com/group/micr...br />
5b133039
Did you read it ?
If yes, you didn't post any additional information. If you need more
help, post sample data AND EXPECTED RESULTS where there is a positive
value after some negative values.
Razvan

Running Sum on Query

Hye Guys,

I am new in this forums and programming..

I have data like

S.N Item Qty
1 ABC 10
2 ABC 20
3 ABC 5
4 XYZ 2
5 XYZ 10
6 XYZ 1

And I want the result

S.N Item Qty RunTot
1 ABC 10 10
2 ABC 20 30
3 ABC 5 35
4 XYZ 2 2
5 XYZ 10 12
6 XYZ 1 13
Plz Help meAre you going to be presenting these rows in a client application? If so, it will be VASTLY more efficient to do these sub-totals there. It is easy as you're looping one by one through the individual rows to keep a tally of Qty for like Item values. With T-SQL, you will have to use a sub-query that is evaluated for every row, and this is going to put your performance into the toilet. wrote in message news:d85d7cec-a74b-47be-a43d-6b4c56306acd@.discussions.microsoft.com...
> Hye Guys, >
> I am new in this forums and programming.. >
> I have data like >
> S.N Item Qty
> 1 ABC 10
> 2 ABC 20
> 3 ABC 5
> 4 XYZ 2
> 5 XYZ 10
> 6 XYZ 1 >
> And I want the result >
> S.N Item Qty RunTot
> 1 ABC 10 10
> 2 ABC 20 30
> 3 ABC 5 35
> 4 XYZ 2 2
> 5 XYZ 10 12
> 6 XYZ 1 13 > >
> Plz Help me
>|||Try this one:

select t.[S.N], t.Item, t.Qty, sum(t1.Qty) as RunTot
from
YourTable as t
inner join YourTable as t1
on t.Item = t1.Item and t.[S.N]>=t1.[S.N]
group by t.[S.N], t.Item, t.Qty|||Here is an article on it. I have not tested it, I just came across it once and bookmarked it..

http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=23349&DisplayTab=Article

Hope that helps!

BobP|||Hye friends,
The replies you have posted had helped me but as one of our friend said I tried it in a database containing large volumes of records.. and the result is ...... 2 slow. So need some other technique..

Always Ready 2 Learn
Kabindra Bakey|||Are there any reasons why it has to be in one select statement?

If it is for a report, let the reporting application calculate it.
If it is for an application, let the app calc it.

The other thing is to pre-process the statement, and store the results in a table, and just select from that table when you need the data.

You could setup the pre-process to happen at night, or every hour, etc... You would just need to dump the results to a temp table, start a transaction, delete from the processed table, populate the processed table from the temp table, and then commit the transaction.

To the users it is transparent, except that you dont have "real-time" data.

BobP|||

Dear guys,

Previously For such running totals I use to have a seperate column in a transaction table. And I used to calculate the value during the insertion of the record and store it in the respective column. that was quite easy way but by any means my rows get unsorted or the order of the transaction has to be changed manually I have w calculate the values again and maintain the column sum once more. which was totally manual calculation based. So I am trying to find the way out fo it...

Always there 2 learn
Kabindra Bakey

Running Sum on Query

Hye Guys,

I am new in this forums and programming..

I have data like

S.N Item Qty
1 ABC 10
2 ABC 20
3 ABC 5
4 XYZ 2
5 XYZ 10
6 XYZ 1

And I want the result

S.N Item Qty RunTot
1 ABC 10 10
2 ABC 20 30
3 ABC 5 35
4 XYZ 2 2
5 XYZ 10 12
6 XYZ 1 13
Plz Help meAre you going to be presenting these rows in a client application? If so,

it will be VASTLY more efficient to do these sub-totals there. It is easy

as you're looping one by one through the individual rows to keep a tally of

Qty for like Item values. With T-SQL, you will have to use a sub-query that

is evaluated for every row, and this is going to put your performance into

the toilet.

wrote in message

news:d85d7cec-a74b-47be-a43d-6b4c56306acd@.discussions.microsoft.com...

> Hye Guys,

> I am new in this forums and programming..

> I have data like

> S.N Item Qty

> 1 ABC 10

> 2 ABC 20

> 3 ABC 5

> 4 XYZ 2

> 5 XYZ 10

> 6 XYZ 1

> And I want the result

> S.N Item Qty RunTot

> 1 ABC 10 10

> 2 ABC 20 30

> 3 ABC 5 35

> 4 XYZ 2 2

> 5 XYZ 10 12

> 6 XYZ 1 13

> Plz Help me

>|||Try this one:

http://www.windowsitpro.com/Articles/Index.cfm?ArticleID=23349&DisplayTab=Article

Hope that helps!

Always Ready 2 Learn
Kabindra Bakey|||Are there any reasons why it has to be in one select statement?

If it is for a report, let the reporting application calculate it.
If it is for an application, let the app calc it.

The other thing is to pre-process the statement, and store the results in a table, and just select from that table when you need the data.

To the users it is transparent, except that you dont have "real-time" data.

BobP|||

Dear guys,

Always there 2 learn
Kabindra Bakey

Running Sum on DataReport

Hye Guys,
I am a beginer programmer.. I have started 2 build a simple database application. I am now in the stage of showing the data in reports 2 print. I am suggested to use datareport available in VB. I also did my some of the report in it. It worked fine. But now I am stucked in one step..of displaying the calculateable field in the detail section..

Eg. in database:

Item Type Qty
A Pur 10
A Sal 3
A Pur 6
A Pur 2
A Sal 5

In Report

Item Type Qty BALQty
A Pur 10 10
A Sal 3 7
A Pur 6 13
A Pur 2 15
A Sal 5 10

Plz Help me...

Always ready 2 learn..
Kabindra Bakey

BALQty expression could be

=RunningValue(iif(Fields!Type.Value="Pur",Fields!Qty.Value,-Fields!Qty.Value),Sum,Nothing)

|||

Hi,
Thanks for your king reply. but I am confused that where should I write that expression Code ... Either in the query Builder of VB or the View of SQL Server

Hoping for your kind information..

Always Ready 2 Learn
Kabindra Bakey

|||In Report Designer (Layout view)|||

Hi,
I am really sorry that I couldn't get the Layout view of the report designer. As I already said I am a beginer, I performed the following steps. So if there is any mistake u plz help me.

Steps:
1.) Added New Dataenvironment to my project
2.) Managed the properties of the connection and pointed to my database. Test Connection also preceeded.
3.) Added New Command object to the connection.
4.) In Properties of the Command Object Managed its Name to TestObj and Its Database object to table and then ObjectName to the Name of Table.
5.) Save and Quit.
6.) Added New DataReport to my Project
7.) Set its DataSource to DataEnvironment
8.) Set its DataMember to the TESTObj
9.) Now Right CLicked on the DataReport and Click on Retreive Structure.
10.) Created text Controls for the Each of the Columns to be displayed.
11.) Now but I couldn't draw the Function control in the detail section.

So plz Help me..

|||Are you using Report Designer or something else?|||I am using Data Report Designer that is available in VB6sql

Running Sum on DataReport

Eg. in database:

Item Type Qty
A Pur 10
A Sal 3
A Pur 6
A Pur 2
A Sal 5

In Report

Item Type Qty BALQty
A Pur 10 10
A Sal 3 7
A Pur 6 13
A Pur 2 15
A Sal 5 10

Plz Help me...

Always ready 2 learn..
Kabindra Bakey

BALQty expression could be

=RunningValue(iif(Fields!Type.Value="Pur",Fields!Qty.Value,-Fields!Qty.Value),Sum,Nothing)

|||

Hi,
Thanks for your king reply. but I am confused that where should I write that expression Code ... Either in the query Builder of VB or the View of SQL Server

Hoping for your kind information..

Always Ready 2 Learn
Kabindra Bakey

|||In Report Designer (Layout view)|||

Hi,
I am really sorry that I couldn't get the Layout view of the report designer. As I already said I am a beginer, I performed the following steps. So if there is any mistake u plz help me.

|||Are you using Report Designer or something else?|||I am using Data Report Designer that is available in VB6

Running Sum on DataReport

Eg. in database:

Item Type Qty
A Pur 10
A Sal 3
A Pur 6
A Pur 2
A Sal 5

In Report

Item Type Qty BALQty
A Pur 10 10
A Sal 3 7
A Pur 6 13
A Pur 2 15
A Sal 5 10

Plz Help me...

Always ready 2 learn..
Kabindra Bakey

BALQty expression could be

=RunningValue(iif(Fields!Type.Value="Pur",Fields!Qty.Value,-Fields!Qty.Value),Sum,Nothing)

|||

Hi,
Thanks for your king reply. but I am confused that where should I write that expression Code ... Either in the query Builder of VB or the View of SQL Server

Hoping for your kind information..

Always Ready 2 Learn
Kabindra Bakey

|||In Report Designer (Layout view)|||

Hi,
I am really sorry that I couldn't get the Layout view of the report designer. As I already said I am a beginer, I performed the following steps. So if there is any mistake u plz help me.

|||Are you using Report Designer or something else?|||I am using Data Report Designer that is available in VB6

Running Sum In View (sql server 2000)

Please can anyone help me ....

how to do running sum in view in sql server 2000

Please see the example below on how to do this.
/*

This table tracks the quantity bought for a particular item.

The problem is to calculate the moving average of sales over the last 3 months

including the current month. The logic can be extended to any moving average problem.

CREATE TABLE #ItemSales (

ItemID int ,

DateBought datetime ,

Quantity int ,

CONSTRAINT PK_ItemSales_ID_Bought PRIMARY KEY CLUSTERED( ItemID , DateBought )

);

INSERT INTO #ItemSales

SELECT 1 , '2000-10-20' , 62

UNION ALL

SELECT 1 , '2000-09-01' , 13

UNION ALL

SELECT 1 , '2000-11-01' , 45

UNION ALL

SELECT 1 , '2000-06-01' , 89

UNION ALL

SELECT 2 , '2000-06-01' , 37

UNION ALL

SELECT 2 , '2000-10-24' , 81

UNION ALL

SELECT 2 , '2000-10-12' , 56;

-- How the data looks, from the most recent sale for eacn item.

SELECT * FROM #ItemSales ORDER BY ItemID , DateBought DESC;

ItemID DateBought Quantity

-- --

1 2000-11-01 00:00:00.000 45

1 2000-10-20 00:00:00.000 62

1 2000-09-01 00:00:00.000 13

1 2000-06-01 00:00:00.000 89

2 2000-10-24 00:00:00.000 81

2 2000-10-12 00:00:00.000 56

2 2000-06-01 00:00:00.000 37

Corelated query for calculating the moving average of each item's sale over the

last 3 months including the current date.

SELECT i1.ItemID , i1.DateBought ,

( SELECT AVG( i2.Quantity )

FROM #ItemSales AS i2

WHERE i2.ItemID = i1.ItemID And

DATEDIFF( mm , i2.DateBought , i1.DateBought ) Between 0 And 3

) AS MovingAverageOver3Months

FROM #ItemSales AS i1

ORDER BY i1.ItemID , i1.DateBought DESC;

-- Expected Output:

ItemID DateBought MovingAverageOver3Months

1 2000-11-01 00:00:00.000 40

1 2000-10-20 00:00:00.000 37

1 2000-09-01 00:00:00.000 51

1 2000-06-01 00:00:00.000 89

2 2000-10-24 00:00:00.000 68

2 2000-10-12 00:00:00.000 68

2 2000-06-01 00:00:00.000 37

DROP TABLE #ItemSales;

Running Sum in a Query

Need Help with the "Running Sum" Column in my Query.
Thanks alot - Joe
sSARC No Of sSARC Running Sum
1/12/2005 2 2
1/13/2005 2 4
1/14/2005 3 7
1/18/2005 2 9
1/20/2005 1 10
My SQL statement is:
SELECT Tbl_FunctionSiteAquisition.[sSA Release to Construction],
Count(Tbl_SiteCriteria.BechtelSiteID) AS [No Of sSARC]
FROM Tbl_SiteCriteria INNER JOIN Tbl_FunctionSiteAquisition ON
Tbl_SiteCriteria.BechtelSiteID =
Tbl_FunctionSiteAquisition.BechtelSiteID
GROUP BY Tbl_FunctionSiteAquisition.[sSA Release to Construction],
Tbl_SiteCriteria.Status, Tbl_SiteCriteria.Scope
HAVING (((Tbl_SiteCriteria.Status)="Active") AND
((Tbl_SiteCriteria.Scope)="UMTS"))
ORDER BY Tbl_FunctionSiteAquisition.[sSA Release to Construction];Joe,
Try this: It should be close.
Select A.[sSA Release to Construction] sSarc,
Count(S.BechtelSiteID) [No Of sSARC],
(Select Count(*) From Tbl_SiteCriteria iS
Join Tbl_FunctionSiteAquisition iA
On iS.BechtelSiteID = iA.BechtelSiteID
Where iS.Status = 'Active'
And iS.Scope = 'UMTS'
And iA.[sSA Release to Construction] <=
A.[sSA Release to Construction]) RunningSum
From Tbl_SiteCriteria S
JOIN Tbl_FunctionSiteAquisition A
ON S.BechtelSiteID = A.BechtelSiteID
Where S.Status = 'Active'
And S.Scope = 'UMTS'
Group By A.[sSA Release to Construction]
Order By A.[sSA Release to Construction]
"JoeC" wrote:

> Need Help with the "Running Sum" Column in my Query.
> Thanks alot - Joe
>
> sSARC No Of sSARC Running Sum
> 1/12/2005 2 2
> 1/13/2005 2 4
> 1/14/2005 3 7
> 1/18/2005 2 9
> 1/20/2005 1 10
>
> My SQL statement is:
> SELECT Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Count(Tbl_SiteCriteria.BechtelSiteID) AS [No Of sSARC]
> FROM Tbl_SiteCriteria INNER JOIN Tbl_FunctionSiteAquisition ON
> Tbl_SiteCriteria.BechtelSiteID =
> Tbl_FunctionSiteAquisition.BechtelSiteID
> GROUP BY Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Tbl_SiteCriteria.Status, Tbl_SiteCriteria.Scope
> HAVING (((Tbl_SiteCriteria.Status)="Active") AND
> ((Tbl_SiteCriteria.Scope)="UMTS"))
> ORDER BY Tbl_FunctionSiteAquisition.[sSA Release to Construction];
>|||--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
Perhaps this:
SELECT FSA.[sSA Release to Construction],
Count(SC.BechtelSiteID) AS [No Of sSARC],
(SELECT Count(*)
FROM Tbl_SiteCriteria As S1
INNER JOIN Tbl_FunctionSiteAquisition AS F1
ON S1.BechtelSiteID = F1.BechtelSiteID
WHERE S1.BechtelSiteID = SC.BechtelSiteID
AND F1.[sSA Release to Construction] <=
FSA.[sSA Release to Construction]) As RunningCount
FROM Tbl_SiteCriteria AS SC INNER JOIN Tbl_FunctionSiteAquisition AS FSA
ON SC.BechtelSiteID = FSA.BechtelSiteID
WHERE SC.Status='Active' AND SC.Scope='UMTS'
GROUP BY FSA.[sSA Release to Construction], SC.Status, SC.Scope
ORDER BY FSA.[sSA Release to Construction]
Naming a column "BechtelSiteID" is strange & indicates possible problems
w/ your DB design: as in - you've indicated that some table(s) will
only hold data for one company. This is attribute splitting (re: -
CELKO--). Perhaps it would be best if you reviewed a book or articles
on database design, particularly Normalization.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjzYcYechKqOuFEgEQKgcgCdEHMJRSQ08anS
JF4K0QGUABWc4xMAoLZw
n9bt/brqWtgkhJa+nJLfkhB6
=ZcrJ
--END PGP SIGNATURE--
JoeC wrote:
> Need Help with the "Running Sum" Column in my Query.
> Thanks alot - Joe
>
> sSARC No Of sSARC Running Sum
> 1/12/2005 2 2
> 1/13/2005 2 4
> 1/14/2005 3 7
> 1/18/2005 2 9
> 1/20/2005 1 10
>
> My SQL statement is:
> SELECT Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Count(Tbl_SiteCriteria.BechtelSiteID) AS [No Of sSARC]
> FROM Tbl_SiteCriteria INNER JOIN Tbl_FunctionSiteAquisition ON
> Tbl_SiteCriteria.BechtelSiteID =
> Tbl_FunctionSiteAquisition.BechtelSiteID
> GROUP BY Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Tbl_SiteCriteria.Status, Tbl_SiteCriteria.Scope
> HAVING (((Tbl_SiteCriteria.Status)="Active") AND
> ((Tbl_SiteCriteria.Scope)="UMTS"))
> ORDER BY Tbl_FunctionSiteAquisition.[sSA Release to Construction];
>|||Was unable to get these statements to work - (just learning SQL)
can we try with this statement.
SELECT Tbl_FunctionSiteAquisition.sSARC,
Count(Tbl_FunctionSiteAquisition.BechtelSiteID) AS [No Of sSARC]
FROM Tbl_FunctionSiteAquisition
GROUP BY Tbl_FunctionSiteAquisition.sSARC
HAVING (((Tbl_FunctionSiteAquisition.sSARC) Is Not Null))
ORDER BY Tbl_FunctionSiteAquisition.sSARC;
I thank you all for your help and timely responces.
The SiteID is multi unique locations of job sites.
JoeC wrote:
> Need Help with the "Running Sum" Column in my Query.
> Thanks alot - Joe
>
> sSARC No Of sSARC Running Sum
> 1/12/2005 2 2
> 1/13/2005 2 4
> 1/14/2005 3 7
> 1/18/2005 2 9
> 1/20/2005 1 10
>
> My SQL statement is:
> SELECT Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Count(Tbl_SiteCriteria.BechtelSiteID) AS [No Of sSARC]
> FROM Tbl_SiteCriteria INNER JOIN Tbl_FunctionSiteAquisition ON
> Tbl_SiteCriteria.BechtelSiteID =
> Tbl_FunctionSiteAquisition.BechtelSiteID
> GROUP BY Tbl_FunctionSiteAquisition.[sSA Release to Construction],
> Tbl_SiteCriteria.Status, Tbl_SiteCriteria.Scope
> HAVING (((Tbl_SiteCriteria.Status)="Active") AND
> ((Tbl_SiteCriteria.Scope)="UMTS"))
> ORDER BY Tbl_FunctionSiteAquisition.[sSA Release to Construction];|||First of all, ending queries with a semi-colon is done in Access not SQL
Server... Where are you running this ?
Second, Can't help any more w/o more information. What error are getting
from running the following?
Select A.[sSA Release to Construction] sSarc,
Count(S.BechtelSiteID) [No Of sSARC],
(Select Count(*) From Tbl_SiteCriteria iS
Join Tbl_FunctionSiteAquisition iA
On iS.BechtelSiteID = iA.BechtelSiteID
Where iS.Status = 'Active'
And iS.Scope = 'UMTS'
And iA.[sSA Release to Construction] <=
A.[sSA Release to Construction]) RunningSum
From Tbl_SiteCriteria S
JOIN Tbl_FunctionSiteAquisition A
ON S.BechtelSiteID = A.BechtelSiteID
Where S.Status = 'Active'
And S.Scope = 'UMTS'
Group By A.[sSA Release to Construction]
Order By A.[sSA Release to Construction
"JoeC" wrote:

> Was unable to get these statements to work - (just learning SQL)
> can we try with this statement.
> SELECT Tbl_FunctionSiteAquisition.sSARC,
> Count(Tbl_FunctionSiteAquisition.BechtelSiteID) AS [No Of sSARC]
> FROM Tbl_FunctionSiteAquisition
> GROUP BY Tbl_FunctionSiteAquisition.sSARC
> HAVING (((Tbl_FunctionSiteAquisition.sSARC) Is Not Null))
> ORDER BY Tbl_FunctionSiteAquisition.sSARC;
> I thank you all for your help and timely responces.
> The SiteID is multi unique locations of job sites.
> JoeC wrote:
>|||On Sun, 20 Mar 2005 08:03:05 -0800, CBretana wrote:

>First of all, ending queries with a semi-colon is done in Access not SQL
>Server... Where are you running this ?
(snip)
Hi Charlie,
Semicolons are not required in SQL Server, but they're not prohibited
either. IIRC, they will be required in some specific cases in SQL Server
2005. I wouldn't be surprised if the number of situations where
semicolons are required will rise even more in future versions.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

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

Wednesday, March 28, 2012

RunningValue In Matrix

RunningValue doesnt do anything useful for me

Running Value in Matrix

Running Value

Monday, March 26, 2012

Running Total: Summing on Distinct Amount

Friday, March 23, 2012

Running Total

Running sum?

Running Sum Query ?

Running Sum on Query

Running Sum on Query

Running Sum on DataReport

Running Sum on DataReport

Running Sum on DataReport

Running Sum In View (sql server 2000)

Running Sum in a Query

Running sum

db files

Blog Archive

About Me