Monday, January 2, 2017

SSRS - RunningValue Function


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:
  1. We cannot use RunningValue for the filter or sort expression.
  2. 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.
  3. Scope parameter cannot be an expression.
  4. Expression parameter can contain calls to nested aggregate functions with the following exceptions and conditions:
  5. 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.
  6. Scope for nested aggregates cannot be the name of a dataset.
  7. Expression must not contain First, Last, Previous, or RunningValue functions.
  8. 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
EmpId
EmpName
Salary
101
Ryan Arjun
25000
102
Kimmy Wang
15000
103
Black Smith
22000
104
Rosy Gray
12000
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 - 

No comments:

Post a Comment

Popular Posts