March 2012

You are currently browsing the monthly archive for 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:

The sales and stock data: 7 fields in total

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

The regular sales data only consists of three columns

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:

Setting up the dataset

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:

Our dataset contains seven 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:

The @WithStockData parameter got created automatically

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

The WithStockData parameter properties

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

Our Boolean parameter is set to false as default

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:

The Table contains seven columns

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:

Using the IsMissing property to hide a column dynamically

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:

Report with all columns displayed

Once more, with the parameter set to False:

The report showing sales data only

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:

The dataset only has three columns, oh my!

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.

Manually adding additional fields to the dataset

So, add the four missing fields:

The four additional fields added manually

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.

Share

Tags: , ,

Here’s a quick one about yet one more annoying default setting.  When I go mountain biking I’m using a Garmin Edge 705 to record my statistics.  After every trip I upload my track to Garmin Connect.  One of my favorite statistics there was Average Moving Speed.  I typed “was” on purpose.  It’s no longer there!

Apparently they removed it because the community was confused about it, hmm.  Now how can I get that back?

This device is a GPS, so technically it’s able to detect if you’re moving and how fast.  It can also stop/start recording automatically.  And here it comes: by default it doesn’t do that!

Now, to activate the auto pause, it would seem logical that you’d need to change a setting in the Settings menu, right?  Well, wrong!  The setting is located in the Training menu!  Inside that menu, you need Auto Pause/Lap and there you’ve got the Auto Timer Pause setting which you can change to Off to When Stopped or Custom Speed.

Tomorrow I’ll try my first track with Custom Speed set to 4 km/h!  This way I hope I avoid that it records while stopped for refueling (read: sports drink and cookies, fruit, not gas or electricity).

Need more info?  Try this video then:

Edge 705 Auto Pause–Auto Lap

Have fun!

Valentino.

Share

Tags:

Earlier this month I’ve had the pleasure of installing a development PC from scratch.

As always, the first question that then comes to mind is: in what order should I install my software?  SQL Server, Visual Studio, any service packs, add-ons, …

Obviously, the basic requirement is SQL Server 2008 R2.  I also need Visual Studio 2008 for custom development, and Visual Studio 2010 for the Database Projects.  Furthermore we’re using Team Foundation Server 2010 for source control purposes.

The list below represents the order with which I’ve come up and which I’ve applied with success.  This order is based on own experience, plus some web research to make sure I didn’t miss anything crucial.

Tip: when given the chance, always go for Custom Install.

  • Visual Studio 2008 (ensure to not install SQL 2005 Express Edition)
  • VS2008 Team Explorer (link)
  • SP1 for VS2008 (link)
  • VS2008 SP1 Forward Compatibility Update for TFS2010 (KB974558)
  • SQL Server 2008 R2
  • SP1 for SQL2008R2 (link)
  • Visual Studio 2010 (ensure to not install SQL 2008 Express Edition)
  • SP1 for VS2010 (link)

In case you’re wondering why this matters so much, well, let’s just say that getting the order wrong can really cost you some time.  In the past I’ve had the pleasure of witnessing an Intellisense (in SSMS) that’s completely broken and an error in VS2010 that prevented me from using the Database Schema Comparison feature to script out the changes.

Hopefully this saves you some time, and I encourage you to have fun while you’re watching that installer’s progress bar!

Valentino.

Share

Tags: ,

Do you like the Custom Code functionality in SSRS?

And what would you think if SSIS offered the same possibility?  Imagine, being able to write a custom function in .NET and then use it in any expression in your package, how powerful that would be!

There’s already one function I would have written today: GetFilename(string path).

If you believe such functionality to be useful, please vote on the following Connect request: Add user defined function support to the SSIS expression language

Have fun!

Valentino.

Share

Tags: , ,

© 2008-2017 BI: Beer Intelligence? All Rights Reserved