SSRS Tips – RunningValue

Running value is a very generic and useful function.  All the 3 parameters can be manipulated for different purpose.

·         Expression
the target on which the aggregation is applied. Most of the time it’s the column name in a dataset, or a combination of a few columns, e.g. “Group1 & Group2”.

·         Function
Aggregate functions, and the most often used functions include

Avg

Returns the average of all non-null numeric values specified by the expression

Count

Returns a count of non-null values specified by the expression

CountDistinct

Returns a count of all distinct non-null values specified by the expression

Max

Returns the maximum value of all non-null numeric values specified by the expression. You can use this for specifying a chart axis maximum value to control the scale.

Min

Returns the minimum value of all non-null numeric values specified by the expression. You can use this for specifying a chart axis minimum value to control the scale.

Sum

Returns the sum of all the non-null numeric values specified by the expression

 

·         Scope
The scope can be dataset, table or table groups.

 

The sample report below shows some applications of RunningValue. The dataset uses this query

SELECT Company, Product, InvoiceNumber, InvoiceAmount from InvoiceTable

The table (SSRS table control) is configured to have 2 groups, “gCompany” and “gProduct”.

Here is a report featured with Running Value tips

Alternate color for Product Group

Normally the “green-bar” (green background color for alternate rows) is implemented by setting the back ground color of the detail row to

  =IIF(RowNumber(Nothing) Mod 2,"Green","White")

To set color for alternate groups, use the expression below for background color

=IIF(RunningValue(Fields!Product.Value,CountDistinct,Nothing) Mod 2, "LightBlue", "White")

The RunningValue function works like a counter/index for the product group.

 

Running Sum value

Cumulative sum is calculated in this expression:

=RunningValue(Fields!Amount.Value, SUM, Nothing)

“Scope = Nothing” means the outermost data region. And for Running Sum by Company or Product, the scope if set to the corresponding group name.

=RunningValue(Fields!Amount.Value, SUM, “gCompany”) or

=RunningValue(Fields!Amount.Value, SUM, “gProduct”)

 

 

Group Running Number

The summary table uses the same dataset as the details table. Normally the row number uses the function

= RowNumber(“table1”)

For this case, the row number is using the CountDistinct function. The expression is

=RunningValue ( Fields!Company.Value + Fields!Product.Value,

CountDistinct, Nothing)

 

Notice that the 2 groups are both used, to prevent mixing products from different company. 

About these ads
This entry was posted in SSRS. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s