March 2012

On the forums I regularly encounter questions like:

I have a report and want to show A, B, C but sometimes I want X, Y, Z and not A, B, C. How?

Or, in other words:

I have a parameter and based on the selection I want to return different fields in my dataset.  How can I do that?

My initial reaction would be “I don’t think you can do that”.  But then I thought it would be useful in certain situations and decided to try it out. And guess what?  You can do that!  Here’s how.

Just to make sure everyone is on track: this article is not about dynamicity in terms of rows returned, that would be filtered datasets and you’ll already find plenty of references on the internet.  This article is about a varying number of columns in the dataset, which is a little less straightforward.

The database used in the examples is AdventureWorks2008R2, available at Codeplex.  And the screenshots are taken from SQL Server 2008 R2 x64 SP1.

The Scenario

The report we’re going to build should show a list of products sold.  But the report is used by different user groups: those who just want to see the sales numbers and those who also care about stock levels!

So by default the report should show a list of items and number sold, but it should also be possible to render that same report whilst displaying the stock-related statistics.

And to make it even a bit more interesting, by default the data should be ordered according to product number but in “stock level mode” the ordering should put those with the lowest current stock first.

The Report

Let’s get started!  The first step in creating a report is often the writing of a SELECT statement.  In this scenario we’ll be needing two of them, both in the same dataset.

The Dataset

Our dataset is going to need a parameter to be able to decide what type of user is requesting the report. Let’s call that parameter WithStockData, and its type will be Boolean.

The layout of the dataset statement will be this:

if @WithStockData = 1
-- sales and stock data
else
-- regular sales data

A simple IF statement, taking the parameter into account.  The parameter is a boolean value so when it’s True, it equals to 1.

And here’s the full statement for the dataset:

if @WithStockData = 1
    -- sales and stock data
    select P.ProductNumber, P.Name
        , P.SafetyStockLevel, P.ReorderPoint
        , SUM(SOD.OrderQty) SoldQuantity, SUM(I.Quantity) InventoryQuantity
        , SUM(I.Quantity) - SUM(SOD.OrderQty) CurrentStock
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    inner join Production.ProductInventory I on I.ProductID = P.ProductID
    group by P.ProductNumber, P.Name, P.SafetyStockLevel, P.ReorderPoint
    order by CurrentStock asc
else
    -- regular sales data
    select P.ProductNumber, P.Name
        , SUM(SOD.OrderQty) SoldQuantity
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    group by P.ProductNumber, P.Name
    order by P.ProductNumber asc;

Some data as returned by the SELECT in the then part:

And some data as returned by the query in the else part:

As you can clearly see, the first query returns seven fields while the second one contains only three.  You can also see that both results are ordered differently.

Now, let’s get the fun started!  Create a new report, set up a data source that points to the AdventureWorks2008R2 database and create a dataset with the query above:

Power tip: to create the dataset, do not right-click on the Datasets node in the Report Data pane, but right-click on the Data Source and then select Add Dataset.  That saves you some work because the Data Source will be pre-populated.  All you need to do is paste the query in the Query field and give it a decent Name.

Click the OK button to close the Dataset Properties.

Now open the new dataset in the Report Data pane and count its fields:

You should come to seven!  How nice, all of our fields are there.  This is not always the case, but I’ll handle that later.

The Parameters node in the Report Data pane should now contain a new parameter called @WithStockData:

Double-click it to get to its properties and change the Data type to Boolean (by default it’s Text).

If you want, you can also specify a default value.  The value should be either “true” or “false”:

With the dataset fully set up, let’s now move on to visualizing it.

Displaying Dynamic Columns

Put a Table on the report Design and set it up as follows:

All seven columns have been added.

One step remains: even though the values won’t always be present, the columns will not disappear automatically.  To take care of that, we’ll enter an expression on the Hidden property of each column.  Each column in a dataset has got the IsMissing property.  When its value is True, it means that the column is not present and should thus be hidden.

The expression looks like this:

=Fields!InventoryQuantity.IsMissing

Or visually:

To set up the expression, click the grey area above the column title to select it and then locate the Hidden property in the Properties pane.  Do this for each dynamic column (don’t forget to change the column name in the expression). If you need to get white space removed, switch the Row/Column Groups to Advanced Mode and locate the  Hidden property of the appropriate (Static) item in the Column Groups.

And here’s what the rendered report looks like:

Once more, with the parameter set to False:

The four stock-related columns are nicely hidden!  And the ordering is working as well because we’ve taken care of that in the dataset’s queries.

Easy, huh?  Well, yeah, but I’ve made sure that the process went as smoothly as possible.  It takes some knowledge on how SSRS actually works.  Let’s make this clear by adapting the example just a little.

Understanding The Dataset

Delete the current dataset and create a new one, using the following statement (ensure that you give it the same name as the original one):

if @WithStockData = 0
    -- regular sales data
    select P.ProductNumber, P.Name
        , SUM(SOD.OrderQty) SoldQuantity
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    group by P.ProductNumber, P.Name
    order by P.ProductNumber asc
else
    -- sales and stock data
    select P.ProductNumber, P.Name
        , P.SafetyStockLevel, P.ReorderPoint
        , SUM(SOD.OrderQty) SoldQuantity, SUM(I.Quantity) InventoryQuantity
        , SUM(I.Quantity) - SUM(SOD.OrderQty) CurrentStock
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    inner join Production.ProductInventory I on I.ProductID = P.ProductID
    group by P.ProductNumber, P.Name, P.SafetyStockLevel, P.ReorderPoint
    order by CurrentStock asc;

The only difference with the previous version is that the IF condition is reversed and thus the two queries are swapped.

Now render the report.  What do you see?

An error occurred during local report processing.

The definition of the report ‘/DynamicDataset’ is invalid.

The Hidden expression for the text box ‘Textbox7’ refers to the field ‘InventoryQuantity’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.

Letters in the names of fields must use the correct case.

Oh my, it’s broken!

Now take a good look at the available dataset fields:

That’s right, only three!  Four of them have gone missing!  The reason for that is because SSRS uses the first SELECT query it encounters in the whole statement to determine the available fields.  It’s not able to automatically detect the different situations and create all the fields that can possibly be returned.

One way to ensure all fields are created is to put the query that returns all possible fields as first query, which is what I initially did.  But of course that’s not always an option.

Manually Adding Fields To A Dataset

Luckily it’s possible to manually add fields to the dataset.  You can do this by clicking the Add button in the Fields page of the Dataset Properties and then selecting Query Field.

So, add the four missing fields:

If you now render the report, it should behave exactly the same as in the initial version!

What About Those Warnings?

If you’re someone who pays attention to the Error List pane, you may have noticed some warnings.  Two for each dynamic field to be exact.

Here’s an example of the rsMissingFieldInDataSet warning:

Warning    1    [rsMissingFieldInDataSet] The dataset ‘dsProductSales’ contains a definition for the Field ‘SafetyStockLevel’. This field is missing from the returned result set from the data source.    C:\test\SSRS\SSRS2008\DynamicDataset.rdl    0    0

And here’s the rsErrorReadingDataSetField warning:

Warning    2    [rsErrorReadingDataSetField] The dataset ‘dsProductSales’ contains a definition for the Field ‘SafetyStockLevel’. The data extension returned an error during reading the field. There is no data for the field at position 4.    C:\test\SSRS\SSRS2008\DynamicDataset.rdl    0    0

I’ve got a developer background, so I always try to remove all warnings.  So if you really want to get rid of those warnings too, even that’s possible.  But it will require some Custom Code writing.  I already covered that topic some years ago, when I wrote an article about Detecting Missing Fields.

Conclusion

With this article I believe to have demonstrated that datasets can be quite flexible, even though it doesn’t always seem so.

Have fun!

Valentino.