RunningValue function is very useful feature in
SSRS report which is used to pull a running aggregate of all non-null numeric
values specified by the expression, evaluated for the given scope. This
function takes three input parameters as given below:
Syntax
RunningValue(expression,
function, scope)
This function based on three parameters such
as
Expression
- The
expression on which to perform the aggregation, for example, [Quantity].
Function - (Enum) The name of
the aggregate function to apply to the expression, for example, Sum. This
function cannot be RunningValue, RowNumber, or Aggregate.
Scope - (String) A string
constant that is the name of a dataset, data region, or group, or null
(Nothing in Visual Basic), that specifies the context in which to evaluate
the aggregation. Nothing specifies the outermost context, usually the report
dataset.
Return
Type -
Determined by the aggregate function that is specified in the function
parameter.
|
The most interesting thing is that the value for
RunningValue resets to 0 for each new instance of the scope.
If a group is
specified, the running value is reset when the group expression changes.
If a data region is specified, the running value
is reset for each new instance of the data region.
If a dataset is specified,
the running value is not reset throughout the entire dataset.
Limitation
of RunningValue function
There is few limitation of this function which is
given below:
- We cannot use RunningValue for the filter or sort expression.
- The set of data for which the running value is calculated must have the same data type. To convert data that has multiple numeric data types to the same data type, use conversion functions like CInt, CDbl or CDec.
- Scope parameter cannot be an expression.
- Expression parameter can contain calls to nested aggregate functions with the following exceptions and conditions:
- Scope for nested aggregates must be the same as, or contained by, the scope of the outer aggregate. For all distinct scopes in the expression, one scope must be in a child relationship to all other scopes.
- Scope for nested aggregates cannot be the name of a dataset.
- Expression must not contain First, Last, Previous, or RunningValue functions.
- Expression must not contain nested aggregates that specify recursive.
How does
RunningValue Function Work?
To understand this function, we are taking an
example of Employee Salary where we should need to calculate the cumulative
salary or running salary across the report. First of all connect your report
with your data source and create a dataset as given below:
Select 101 as EmpId, 'Ryan
Arjun' as EmpName, 25000 as Salary
UNION
Select 102 as EmpId, 'Kimmy
Wang' as EmpName, 15000 as Salary
UNION
Select 103 as EmpId, 'Black
Smith' as EmpName, 22000 as Salary
UNION
Select 104 as EmpId, 'Rosy
Gray' as EmpName, 12000 as Salary
|
In the report, we have the report dataset called
dsEmployee as given below:
Now
just set the DataSetName as dsEmployee to your report as given below:
After
adding the dataset your report and setting the column names to your report,
just preview your report which will be look like as given below:
Now,
we need to add the running salary to the report. For this, just add a new
column to your report and named as Running Salary as given below:
After adding new column your report, just right
click in the detail column and choose expression as given below:
After choosing the Textbox Expression, it will launch
the Expression window. In the Expression window, we must add the RunningValue
function with the requested parameters such as Expression, function type and
scope of the function as given below:
After doing the needful settings, click OK
button and preview the report as given below:
We can add the average running salary in the
report as given below:
= RunningValue(Fields!Salary.Value, Avg, Nothing)
|
Conclusion
This function is very useful to calculate on the
fly calculation such running sales with the month, within the year or within
the product specific. It gives us to facilitate to use the aggregate functions
to calculate the different type running aggregation values. It has some
limitation such as sorting and filtering data. For live demo - please visit us at -
I really appreciate this wonderful post that you have provided for us. I assure this would be beneficial for most of the people. GISI.com
ReplyDelete