Extending Data Sets in SSRS with VB Functions


A common event that occurs rather often is when a report has to be altered to include some set of data that does not exist in the ‘corporate’ data warehouse. This type of situation is easy to imagine in sales environments where a campaign exists (one which was not planned at budget time) and someone* would like to see this data reported against actual sales (and possibly even a comparative to budget). I agree that it would be ideal to have this data ‘corporatized'; however, there are often competing forces and changes in priorities, which means that this data never makes it in. This post looks at how this data can be included into reports.

Consider the situation where and how someone has gone through and created a new budget for adventure works data (as below) and we want to include this in our report.

In order to include this will create a function which specifies what the new value should be given the inputs of year in category. Naturally, in a production environment, these combinations increase but the principle applied is exactly the same. You can read a little bit more about functions here but essentially what we’re trying to do is return some value given inputs. We can then append that to our data set as an additional field (i.e. another measure) and then use that field in the report.

Base Data

The base query for [reseller sales amount] by using categories would be;

select
[Measures].[Reseller Sales Amount] on 0,

[Date].[Calendar].[Calendar Year]
* [Product].[Category].[Category]
on 1

from [Channel Sales]

Creating the Function 20 oh

Having the data in the tabular format is makes it really easy to manipulate in Excel, in fact, we could add another column with a formula to specify what the return value should be given the year and category. In my workbook, the formula would be;

=”if (year_name=”””&A2&””” and category_name =”””&B2&”””) then return ” &D2

And it is apply to every row that we have data for (as in the screenshot below).

Now that we have these formulas, we can embed them in a property construct and add them to the report code. In order to do this you define the property name, the return value type, the input parameters (and their type).

Public Function UserValue(year_name As String, category_name As String) As Double

‘use if statements here
End Function

Note that we are defining a read-only property (UserValue) and specifying a ‘Get’ modifier for the property. All this means is that a function will be created (called UserValue) and require the input variables of year_name and category_name. The function will return a double type. A cut of the complete function looks like this;

Public Function UserValue(year_name As String, category_name As String) As Double
  if (year_name=”CY 2005″ and category_name =”Accessories”) then return 20000
  if (year_name=”CY 2005″ and category_name =”Bikes”) then return 7340000
  if (year_name=”CY 2005″ and category_name =”Clothing”) then return 34000
  …..

  if (year_name=”CY 2005″ and category_name =”Components”) then return 615000
  if (year_name=”CY 2006″ and category_name =”Accessories”) then return 92000
  if (year_name=”CY 2006″ and category_name =”Bikes”) then return 19000000
End Function

As a tip, it may be easier to create this in a text editor before adding it to report code.

Adding the Function to Report Code

There are a few ways to add code to a report, perhaps the easiest is to right click (outside the canvas) and select the report properties from the pop-up menu and then navigate to the code. You could then paste the code directly into the custom code box.

Calling the Function

Functions can be called by editing the expression (that we are interested in). Since we want to append to our original dataset, we edit the dataset and adding new column. Right click on the dataset and select edit. We then add a new calculated field (as below);

The field can be given a name (New_Value) and entered by clicking the expressions button. Note that I can use values from the current dataset as inputs from a function. Just as any other expression editor I simply need to double click on the items that I want to add.

My report now has a new measure (New_Value) which can be added to the report, just as any other measure.

Other Titbit’s and Gotchas

Although this function was added to the dataset, it could also be used as a cell in a matrix or table. The benefit of adding an additional column to the dataset is that the value can be aggregated in your report and used in totals. This would not occur if was added to the control directly.

Also note that the default MDX generated by the query designer in SSRS excludes non-empty data and this may lead to misleading results where no ‘sales’ have occurred, but there is a New_Value amount to be shown. In this situation it is advisable to remove the nonempty criteria query so that you can guarantee that data will be shown (regardless of the results returned through the underlying dataset).

A side benefit of using this technique is that the data then become (somewhat) available to other users.  Once the report is published, it can be consumed as an atom feed.

* The use of the word someone here is intentional because the person making the request for the change to a report does not often have control over the data that is entered into the data warehouse. While there is a business imperative to include this data, it is not seen as an IT imperative. There is simply not enough time to go through the appropriate channels.

About these ads

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