Problem
What count function variations and related functions are available in SQL Server Reporting Services (SSRS)?
Solution
I have been working with SQL Server Reporting Services for some time, and I recently came across a few neat functions that I have never used before nor even knew existed. These functions include the Previous function and the CountDistinct function. The Previous functions can be used to show various prior values within a tablix while the CountDistinct function is especially helpful for making comparison between values and rolled up totals.
Specifically, the Previous function returns the value that is found in the immediately previous and scope qualified instances of a cell or textbox. For instances, if you have a tablix with 10 detail rows, using the previous function would actually provide the value from the previous row. Thus the Previous value for row 6, would be the value in row 5, within the same or specified scope.
The CountDistinct function actually evaluates and counts the number of distinct values for the field specified in the function and within the scope noted.
For both functions, if no scope is specified than the current scope / level is used to evaluate the function.
We will again use the Adventure Works databases as the basis for our SSRS report design. The 2014 versions of the regular and data warehouse databases are available on Codeplex: AdventureWorks Sample Databases and Scripts for SQL Server 2016 CTP3. Once you download and install the SQL Server databases, we will subsequently use the SQL Server Data Tools for Business Intelligence (SSDT-BI) for Visual Studio 2013 to develop reports. You can download SSDT-BI from: Business Intelligence for Visual Studio 2013.
Using the Previous Function in SQL Server Reporting Services
We will start by going over the Previous function in SQL Server Reporting Services; the basic syntax of the function is: Previous (Expression, Scope). The scope argument is optional and if omitted, then the current scope will be utilized by the expression. The previous function takes into account all sorting and filtering for all applicable objects including filters and sorts at the current scope level (normally groups) and any filters at higher levels. A couple of caveats of course exists with the functions use and include:
- If the evaluated expression has no prior rows at the specified scope level, i.e. it is the first row on the report, then the previous function returns nothing.
- A detailed field can only be specified at the detail group level; otherwise an aggregate function must be used (SUM or FIRST for instance). Similarly said if multiple values exist in the previous row, an aggregate, such as FIRST, will need to be included.
- The scope designated in the function must be either the current scope or a scope contained within the current context of the defined scope (scope at a lower level than the current scope such as a child group).
- The Previous function cannot contain another or “sub” Previous function.
Let us get started with an example; we will use the high and low temperatures for two cities in the U.S.A: Westerville, OH and Kennesaw, GA. A snippet of the dataset is displayed below.
This dataset gives a good previous value to seek out. So we start SSDT-BI, and create a new report. Next we create a data source to our local database.
In Step 2, we create our data set to retrieve our weather data which was outlined above.
Finally, we create a simple matrix report with Date on the row, location on the column, and the following data points:
- Current Day Precip: =Sum(Fields!Precip.Value)
- Prior Day Precip: =PREVIOUS(SUM(Fields!Precip.Value))
- Change from Prior Day: =SUM(Fields!Precip.Value) – Previous(SUM(Fields!Precip.Value))
Previewing the report, we can quickly see that the Previous function shows the prior day’s Precip value for the appropriate column and row group detail level, since no scope was added.
So what happens if we do specify a scope at the group level? Since the “Date” group is actually the detail level, no change in the results will occur. However, as shown below, we can use the Location column group in the scope argument.
Now the Previous value is actually the “previous column value” in the report shown below.
As you can, see several uses exist for the Previous function including completing year over year or other period over period comparisons.
CountDistinct Function in SQL Server Reporting Services
The CountDistinct function gives report designers the ability to quickly count distinct values on a report at various scope levels. This type of function provides a different perspective from just summing up a field. We can see not only, for instance, the total number of products sold, but also the number of unique products sold. Using the weather example utilized so far within this tip, we can see the number of distinct days which have had precipitation. The basic syntax of the function is: CountDistinct (expression, scope, and recursive). The expression argument is required where as the scope and recursive arguments are optional. If not specified the scope parameter will use the current level and scope context. The default value for the recursive argument is Simple. Some caveats for using the CountDistinct function include:
- The scope argument must be a value and not an expression.
- The First, Last, Previous, and RunningValue functions cannot be used in the expression argument.
- CountDistinct (appropriately so), does not count Null or Nothing values.
So let us show an example of CountDistinct in our weather data. In the below screen print, we add a CountDistinct function in one cell below the upper left corner to tell us the number of unique Precip values. The distinct count will include 0.000 values, but any values that are Null will not be included in the count.
We can see in the below illustration that even though we had 31 days of Precip data across 2 locations, only 16 distinct precip values exist.
To check these results I ran the below query which also returns 16 rows.
Let us take the CountDistinct to the group level. As shown next, we add a CountDistinct for the Precip values using the Location group as the scope. We also add the same item, but just use the Count function.
As illustrated in the below screen print, we now have a report showing us that Kennesaw has 8 distinct Precip values while Westerville has 9 distinct values. You may be saying, but “how come the Days with Distinct Precip is only showing 16?” Great question! We have one value that persists across both locations, and that value is 0.000. So 0.000 is only counted once in our Days With Distinct Precip value of 16.
Conclusion
This tip covered two functions that I had not used before just a few weeks ago, but they can be of great value when attempting to analyze your datasets on an SSRS report. The Previous function provides a simple way to extract the previous value on a report and accepts an expression and scope argument. In a similar way, the CountDistinct function gives us the ability to not just count the number values in a set, but to count the unique values in a set, excluding Null or Nothing values.
Next Steps
