run-time changeable queries

I need to extract and store a value from a table (or from a MS Access file with OpenDataSource) which is not always the same and it is therefore stored in the @.openfile variable. Something like this:
...
declare @.standardselect nvarchar(4000)
declare @.value int
select @.standardSelect='select top 1 @.value=val from ' + @.openfile
exec (@.standardSelect)
...

It obviously doesn't work because the variable @.value is not declared within the sql string.
However, since @.openfile is always different, I need to pass it through a string and the only way I know is within a variable. If I declare @.value inside the @.standardselect it is not accessible to the rest of the procedure, which is not acceptable for me.

Any suggestions?I have solved it using a temporary table, rather than a variable, where to store the value val. But if you have a better idea...|||

You can use EXEC statement

@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL

However this is a bad practice.

|||Andranik Khachatryan, I guess you haven't read the code in my first message?

Andranik Khachatryan wrote:

You can use EXEC statement

@.sSQL = 'select...from ' + @.tablename + ' where ...'
Exec @.sSQL

However this is a bad practice.

|||

Oops sorry :)

My bad, I am a bit careless today.

|||

You could use sp_executesql with output parameters to do this.

Declare @.StandardSelect nvarchar(4000)
Declare @.Value Int

Select @.StandardSelect = 'Select top 1 @.Value=Id From ' + @.OpenFile
exec sp_executesql @.StandardSelect, N'@.Value int output', @.value OUTPUT

Select @.value

|||

You don't really need dynamic SQL for doing this. You can do below instead:

declare @.source varchar(30)

-- ... initialize based on whether you want to query table or Access

set @.source =

declare @.value int

set @.value = (

select top 1 val from (

select val from your_table where @.source = 'table'

union all

select val from opendatasource(...) where @.source = 'access'

) as t

order by ...

)

RunningValue()

I have a report that I has a dataset that has a column that can have high and low values. If a row has a value that exceeds either of these values I would like to do a running value on these values. For example, 300 records , 10 high , 3 low.

I know I can do a SQL query that can grab that data and then do a countrows on that dataset but I thought there should be a way of doing a runningvalue for each value.

Anyone have an idea on what the syntax is to do this?

I have also tried using 2 functions in custom code that are doing a simple if else to return the += 1 count for high and low. The value is displayed in a column that I'm hiding but I'm not able to display that variable anywhere else. I tried =Code.DisplayHigh() and it always returns nothing but in my columns I'm getting the right values.

Dim HighCount
Dim LowCount

Public Function CheckHigh(x As Double) As Integer
If x > 120.5 Then
HighCount += 1
Return HighCount
Else
Return 0
End If
End Function

Public Function CheckLow(x As Double) As Integer
If x < 116.2 Then
LowCount += 1
Return LowCount
Else
Return 0
End If
End Function

Public Function DisplayHigh()
Return HighCount
End Function

use properties (set and get ) and set the value and access the value using those. I cant say whats not working, because dont know whats the class structure. But try that first, is to convert these to properties.

RunningValue in Drilldown problem

HI,
We are using RunningValue function to alternate color for displaying rows.
Here is the property value for backgrond color:
=iif(RunningValue(Fields!Row_Date.Value, CountDistinct,Nothing) Mod 2,
"white", "#E4E7E6")
We are having a problem with using that and a drill down at the same time.
The first drill down works fine, but the second does not. Any thoughts?
MichaelI am in the middle of installing the gold bits, so I don';t have books on
line, but we use the rownumber function instead of runningvalue... See if
that works for you.
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"Michael" wrote:
> HI,
> We are using RunningValue function to alternate color for displaying rows.
> Here is the property value for backgrond color:
> =iif(RunningValue(Fields!Row_Date.Value, CountDistinct,Nothing) Mod 2,
> "white", "#E4E7E6")
> We are having a problem with using that and a drill down at the same time.
> The first drill down works fine, but the second does not. Any thoughts?
> Michaelsql

RunningValue help!!

the running value works on the preview of reporting services but when
i run the report on the server it displays zero all the time. plz tell
me how to solve it
thanx in advancei figured out the problem !!!

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() Problem in S-Curve

Hi Everyone,

I have a dataset like below

X15ForecastsCounts X16ActualsCounts WeekEnding 38 18 7/23/07 38 14 7/30/07 38 35 8/6/07 37 NULL 8/13/07 37 NULL 8/20/07 37 NULL 8/27/07 37 NULL 9/3/07 36 NULL 9/10/07 36 NULL 9/17/07 22 NULL 9/24/07

I am plotting this data set as an S-Curve using the Running Value funtion.

The value and data point value is =RunningValue(Fields!X15ForecastsCounts.Value,Sum,"Series") similar code for X16Actuals Counts.

Unfortunately I am getting the curve like this http://tinypic.com/view.php?pic=4lyiuko

I dont want my X16ActualsCounts to extend beyond 8/6/07 in this case....and next week I dont want it to extend beyond 8/13/07.

How can I fix this issue?

Just to add more clarity to what I said above...I want the Running Value to stop calculating when it encounters NULL in the dataset.....If this is not possible ...Can I conditionally format the line curve...asking to color the line "Transparent" or "Maroon" based on a condtion....in this way my Actuals line will show until 8/6/2007...and the rest will be transparent.

Can someone please help me on this....

|||

Is anybody experiencing the same problem as mine

Running Value with Subtotals and Grand Total

I have a Division parameter (select one or many) and I am grouping my Sales
Goal row by Division with columns for months. I can show the running year to
date total across the columns ok, but I can't figure out how to reset the
running value when I get to a new Division grouping. It is adding the second
division to the results from the first grouping which is not what I want.
How do I this?
I also want a grand total below all the groupings
TIA
DeanI have been looking for help with doing the subtotals and the grand totals of
a report and I came acrross the Running value aggregate and I am no expert
but it seems to me you need to specify the scope for your runningvalue and
that will reset it if the scope changes.
"Dean" wrote:
> I have a Division parameter (select one or many) and I am grouping my Sales
> Goal row by Division with columns for months. I can show the running year to
> date total across the columns ok, but I can't figure out how to reset the
> running value when I get to a new Division grouping. It is adding the second
> division to the results from the first grouping which is not what I want.
> How do I this?
> I also want a grand total below all the groupings
> TIA
> Dean
>
>

Running Value Problem

Hi,
I used RunningValue function within a table and returned the following
result. I have two questions.
1) How can I remove (not just hide but remove) the first row that
contains "0"?
2) How can I set values beyond Month 3 to blank fields instead of all
of these same "$87,736,926"? This is important because I also need to
create a line chart that contains these two value series and I want the
2006 YTD Sales line to stop at the current point (Month 3 in this case)
and not display any values beyond Month3.
Month 2006 YTD Sales 2005 YTD Sales
0 0
1 $34,934,297 $31,324,975
2 $67,138,492 $66,663,663
3 $87,736,926 $102,636,271
4 $87,736,926 $135,353,722
5 $87,736,926 $170,478,711
6 $87,736,926 $213,173,715
7 $87,736,926 $247,820,997
8 $87,736,926 $285,560,660
9 $87,736,926 $323,379,168
10 $87,736,926 $357,457,266
11 $87,736,926 $395,755,855
12 $87,736,926 $446,351,252
Here is the RunningValue formula I used.
=RunningValue(Fields!Month_Sales.Value, Sum, Nothing)
Any help is much appreciated.
Thanks,
KeithHi,
Get all these things trimmed using your query itself, since these things are
limited in reporting server. anyways.
1. Supressing 0.00 you can use filters.
2. check "Hide Duplicate"
Amarnath
"Keith" wrote:
> Hi,
> I used RunningValue function within a table and returned the following
> result. I have two questions.
> 1) How can I remove (not just hide but remove) the first row that
> contains "0"?
> 2) How can I set values beyond Month 3 to blank fields instead of all
> of these same "$87,736,926"? This is important because I also need to
> create a line chart that contains these two value series and I want the
> 2006 YTD Sales line to stop at the current point (Month 3 in this case)
> and not display any values beyond Month3.
> Month 2006 YTD Sales 2005 YTD Sales
> 0 0
> 1 $34,934,297 $31,324,975
> 2 $67,138,492 $66,663,663
> 3 $87,736,926 $102,636,271
> 4 $87,736,926 $135,353,722
> 5 $87,736,926 $170,478,711
> 6 $87,736,926 $213,173,715
> 7 $87,736,926 $247,820,997
> 8 $87,736,926 $285,560,660
> 9 $87,736,926 $323,379,168
> 10 $87,736,926 $357,457,266
> 11 $87,736,926 $395,755,855
> 12 $87,736,926 $446,351,252
> Here is the RunningValue formula I used.
> =RunningValue(Fields!Month_Sales.Value, Sum, Nothing)
> Any help is much appreciated.
> Thanks,
> Keith
>|||Thanks for your reply, Amarnath!
I tried "Hide Duplicate" and it did work within a table. Do you know
how to apply this to a line chart? Let the line stop at the Month 3 and
not display a straight line beyond Month 3 point.
I also tried to use filter to remove the row with "0". But it always
gave me the following error message.
"...The processing of filter expression for the table can not be
performed. The comparison failed. Please check the data type returned
by filter expression."
Any idea?
Thanks,
Keith

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 and NULL in a dataset

Hi Everyone,

I have a dataset like below

X15ForecastsCounts X16ActualsCounts WeekEnding 38 18 7/23/07 38 14 7/30/07 38 35 8/6/07 37 NULL 8/13/07 37 NULL 8/20/07 37 NULL 8/27/07 37 NULL 9/3/07 36 NULL 9/10/07 36 NULL 9/17/07 22 NULL 9/24/07

I am plotting this data set as an S-Curve using the Running Value funtion.

The value and data point value is =RunningValue(Fields!X15ForecastsCounts.Value,Sum,"Series") similar code for X16Actuals Counts.

Unfortunately I am getting the curve like this http://tinypic.com/view.php?pic=4lyiuko

I dont want my X16ActualsCounts to extend beyond 8/6/07 in this case....and next week I dont want it to extend beyond 8/13/07.

How can I fix this issue?

Just to add more clarity to what I said above...I want the Running Value to stop calculating when it encounters NULL in the dataset.....If this is not possible ...Can I conditionally format the line curve...asking to color the line "Transparent" or "Maroon" based on a condtion....in this way my Actuals line will show until 8/6/2007...and the rest will be transparent.

Can someone please help me on this....

|||

Is anybody experiencing the same problem as mine

|||You are correct using the functions but not on the colors.

use the iif statement to coherce the line to dissappear

Right click on the variable; click on the appearance tab,
Click on the series style button.

On the Border and lines tab click on the function button and add the code (changing the YourVariable to the variable name of the data in the runningvalue)

=iif(isnothing(Fields!YourVariable.Value),"None","Solid")

Running Value (Chart in a Table)

Hello

I have a report with a Table, in the table I have SubReport1, in Subreport1 I have a Table1, inside table1 I have another table2 and Subreport2.

Subreport2 has 4 different type of charts, depending on the passing parameters from the second subreport 1 of the 4 charts is displayed.

In one of the chart I'm using the Running Value function, works fine ONLY the first time the chart is displayed, after that the charts shows no values. I'm using a series group, which changes for every row, for what I understood the value should be reset everytime the series changes (100,101,102), but that is not the case.

Group level values are 1/10, 2/10, 3/10 etc up to 10/10.

Series level values 100,101,102 etc.

Thank you

Hi

I guess I was not clear,

seems like that the Series group ID never reset the running value even when the value changes from 100 to 101, when the chart is invoked by a subreport.

welcome any suggestion.

Running Value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the expression is:

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

But i am getting this error:

The Value expression for the textbox '*** %' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

How to get the cm % for the Cumulative total

Please help me

Thanks in advance

You can not 'sum' inside of other aggregate, you need to remove the sum function

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

Try without "Sum"

(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value/Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100

Ham

|||

Hi,

I want the SUM inside the runningValue,It is necessary.

Thanks

Running Value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the "AgeByGender")

But i am getting this error:

The Value

I solved the problem with the following expression.

For Cumulative %:The formula i used is:

Round(RunningValue((fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value),sum,"AgeByGender") /Sum((fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value), "AgeByGender") *100,2)

This expression solved the problem.

Thanks

Running Value

I currently have a report that salesmen gets a negitve or plus points on the sales that he/she makes. I need to keep a running total of these points. In addition I need this total to never to get over 10 points but if I need the points to minus from 10 if they get a negitive points on a sale.

Example:

Account 1: 4 points Running total: 4

Account 2: 4 points Running total: 8

Account 3: 4 points Running total: 10

Account 4: -2 points Running Total: 8

I have tried Iif statement but I don't know how to keep the value at 10. I also need to have the final value to show in a group footer.

Thanks for any help.

RunningValue(Fields!Cost.Value, Sum, Nothing)

check books on line for more info about RunningValue

sql

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

Running value

Hi

In my report I have the total column,under the total i have two sub fields:no , Row%and i have another column Cumulative total sub fields are no,***%

For the Row % under total i write like this:

=Round((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,2)

For the *** % under cumulative total the expression is:

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

But i am getting this error:

The Value expression for the textbox '*** %' contains an aggregate function (or RunningValue or RowNumber functions) in the argument to another aggregate function (or RunningValue). Aggregate functions cannot be nested inside other aggregate functions.

How to get the cm % for the Cumulative total

Please help me

Thanks in advance

Mahima

You will need to remove the "SUM" inside the running value statement.

=RunningValue((Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)/(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value)*100,sum,"AgeByGender")

Ham

|||

Hi,

I need the cum%(Cumulative %) thatswhy i added SUM inside.Without using Sum inside Running value,How to get the Cumulative %,Any work around .

Thanks in advance

|||

Mahima,

I'm missing a piece of Info to help you. Where are you trying to place your cumulative total - Is this a group footer, table footer, or Matrix?

Thks

Ham

|||

Hi,

This Cumulative Total % is a column in a Table.

|||

Hi,

Any one there,Please help me on this issue.

Thanks

|||

Mahima,

I meant to answer but I'm in the middle of a SSRS production release with my client. I did want to say that you could use the ReportItem!Field.value to replace the SUM(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.Value+Fields!Invalid.Value) - if you are not using the them in a report column the created a calculated field then use the calculated field in replace the SUM values.

|||

Hi,

Thanks for replying.Can you explain a bit clear.I tried in this way,We have a % column i.e textbox67,In Cum%,i used like that:(ReportItems!TextBox67.Value,Sum,"dataset1").But iam getting error.report items use only in header footer.

Where to calculate that value separately.

Thanks

|||

Okay,

My bad, I now remember why I asked if it was in the header. On the Dataset, on click, Add new calculated field, place your expression, then use in your runningvalue statement.

Ham

|||

Hi,

I did the same before.I created a calculated field and using those calculated field in the RunningValue,But the problem is When i click on View report button,It is displaying the following message,An internal error occured,See ebetlog for details.And closing the application(Visual Studio).Here what is the problem.

The exception is Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogexception occured in devenv.exe.

Thanks

|||

WOW,

It looks like a lot of things are not going right here. Is your data valid?

Ham

|||

Hi,

My data is valid.It is working fine before adding the Calculated field.Any other alternative.

Thanks

|||Please post your expression, I would like to try an verify it.|||

Hi,

In the Dataset named "DataSet1" fields are Male,Female,Unknown.

In the report,i have the following columns

Male Female Unknown Total Total% CumulativeTotal Cumulative%

Total=Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value

Total%=Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value)

CumulativeTotal=RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value,Sum,"Dataset1")

CumulativeTotal%=RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value),Sum,"DataSet1")

this Cumulativetotal% is giving error,So i created the 'calculated filed' named as "Percent" with the expression:Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value)

and try to use the Percent value like this:

RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Fields!Percent.Value,Sum,"DataSet1"),This is closing the Visual Studio and giving the exception.

Thanks

|||

In your calculated field,

Only place this Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value in calculated fields

in your expression use RunningValue(Fields!Male.Value+Fields!Female.Value+Fields!Unknown.value/Sum(mycalculatedfields),

I hope that this works for you.

Running Totals in a Report Builder Column Chart

I've created a Report Builder Column chart with months as the x-axis
(category) and revenue as the y-axis (value). This correctly displays
the revenue for each month, but I'd like to display a running total of
the revenue for the year.

I'm currently displaying:
Jan $2
Feb $1
Mar $4

I want to display:
Jan $2
Feb $3
Mar $7

I know this is possibly using the RunningTotal formula in Reporting
Services, but I'd like to do this in Report Builder so the users can
change and create their own graphs with running totals. Is there a way to do this in Report Builder?

Thanks.

I have been able to get the running total in a TABLE by right clicking on a field, selecting formula, and inserting the following string:

=Format(RunningValue(Fields!Count.Value,Sum,Nothing),"")

The format statement allows formatting "C" (at the end would produce currency (Dollar sign, etc.), not sure what "" (I didn't want currency).

I would also like to know how to do this in a chart....

Running Totals at group level

Hi,

I have a report that groups data by day - I have created a running value to

show cumulative sales for Monday, Monday+Tuesday, Monday+Tuesday+Wednesday etc.

I have a group below this level that expands out the customer. I

wish to create a cumulative sales value for that customer for that day of the

week. i.e. Customer A Monday value, Customer A Monday+Tuesday

value.

When you use RunningValue with scope Nothing, ie.

RunningValue(Fields!nett_value.Value, Sum, Nothing), the value returned on

Tuesday is Total Monday value plus each customer Tuesday value cumulative

adding. If you use scope at customer group level it cumulatively

adds that day’s customer totals.

I need to recreate:
Daily

Cumulative

Monday

100 100

Customer A 50

50

Customer B 25

25

Customer C 25

25

Tuesday

100 200

Customer A 50

100

Customer B 25

50

Customer C 25

50

Any ideas?

Thanks

While I am having fun trying to work this out...

Any help would be great!!!

Thanks!

Running total with "treshold value" detection..?

Hi,
I have to create a query which evalaute, for each year, the employees who
reached a particular number of absences and when this value is reached.
I have 1 000 000 records in my absence table.
my table is like this:
DateID, EmployeeID, ActivityID, AbsenceDurationInDays
I want a list of 1 date by year by employee and by activity when the runin
absenceduration total reached 5 days.
Also, I'll evaluate the same formula but by quarter instead-of year and the
target value is 3 days.
AbsenceDurationInDays is a floating value like:
0.5 = half a day (or 4hours of work)
any guide?
any sample query anywhere?
how to setup my indexes?
I'll store the result in a table using DTS, and I'll evaluate this query 1
time a week.
thanks for your help.
Jerome.
Not sure I understand exactly what you're after but if I guess correctly
you want a query like this (assuming dateID is a smalldatetime or a
datetime):
selectdatepart(year, DateID) as [Year],
EmployeeID,
ActivityID,
sum(AbsenceDurationInDays) as RunningTotal
from AbsencesTable
group by datepart(year, DateID), EmployeeID, ActivityID
having sum(AbsenceDurationInDays) >= 5
If you want to alter the query to deal with quarters instead of years
just change the first argument from "year" to "quarter". If you want to
change the threshold just change the "5" value in the HAVING clause to
whatever you want it to be.
Cheers
Mike
Jj wrote:
> Hi,
> I have to create a query which evalaute, for each year, the employees who
> reached a particular number of absences and when this value is reached.
> I have 1 000 000 records in my absence table.
> my table is like this:
> DateID, EmployeeID, ActivityID, AbsenceDurationInDays
> I want a list of 1 date by year by employee and by activity when the runin
> absenceduration total reached 5 days.
> Also, I'll evaluate the same formula but by quarter instead-of year and the
> target value is 3 days.
> AbsenceDurationInDays is a floating value like:
> 0.5 = half a day (or 4hours of work)
> any guide?
> any sample query anywhere?
> how to setup my indexes?
> I'll store the result in a table using DTS, and I'll evaluate this query 1
> time a week.
> thanks for your help.
> Jerome.
>
|||but this query doesn't return the date when the runningtotal reached the 5
value.
For example, if an employee has 10 days of absences on the same activity
from the 1st feb. to the 10st feb.
then the expected result is the 5th feb. when the employee has cumulated 5
days of absences.
"Mike Hodgson" <mwh_junk@.hotmail.com> wrote in message
news:eXfyF4JFFHA.3780@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Not sure I understand exactly what you're after but if I guess correctly
> you want a query like this (assuming dateID is a smalldatetime or a
> datetime):
>
> select datepart(year, DateID) as [Year],
> EmployeeID,
> ActivityID,
> sum(AbsenceDurationInDays) as RunningTotal
> from AbsencesTable
> group by datepart(year, DateID), EmployeeID, ActivityID
> having sum(AbsenceDurationInDays) >= 5
> If you want to alter the query to deal with quarters instead of years just
> change the first argument from "year" to "quarter". If you want to change
> the threshold just change the "5" value in the HAVING clause to whatever
> you want it to be.
> Cheers
> Mike
>
> Jj wrote: