May 2010

You are currently browsing the monthly archive for May 2010.

Earlier I wrote an article about the new lookup functions that ship with SQL Server 2008 R2.  Today I’m going to show you another new feature of SSRS 2008 R2, this time in the visualization department.  This feature is the Data Bar.  With this new component it’s fairly easy to make your tabular data a lot more visual, and thus easier to interpret.  And here’s how to do it.

I’ll be starting from the report that I created in Your First OLAP Report.  That allows me to focus on the visualization part, without first needing to build a table report.  (Okay, I admit, it’s not 100% the same report – I’ve modified the colors a bit because I felt the green was too dark.) But obviously this method will work with any report that’s showing data in a table.

Furthermore I’m running SQL Server 2008 R2 Nov CTP, 64-bit, and I’m using the BIDS to develop the report.

The final result can be downloaded from Skydrive here.

Implementing The Data Bar

The report that we’re using is showing some sales figures grouped in three levels: Country, State/Province and City.  We’re going to add an extra column on the right of the table to contain the data bar.  Let’s first explore the Toolbox pane to discover the new visualization report items.

The new Reporting Services 2008 R2 report items: Map, Data Bar, Sparkline and Indicator

The new items have been highlighted in yellow.  As you can see, besides Data Bar there’s also Sparkline, Map and Indicator.  But those are not on topic now.

To add a Data Bar, simply drag it from the Toolbox into a textbox on the report.  Doing that will show the following popup window:

Select Data Bar Type window

From left to right, there’s Bar, Stacked Bar and 100% Stacked Bar.  And those are also available in vertical direction, Column.  I’m going to use the regular Bar as highlighted in the screenshot.

The Data Bar has now been added to the report, but it doesn’t do anything yet.  We first need to tell it what data to visualize.  Clicking it once will select it, clicking it once more will show us the following Chart Data popup:

Data Bar: Chart Data

Click the plus icon to get a drop-down of fields in the dataset.  Select the numeric field that you want to visualize, in my case that’s the Reseller_Sales_Amount.

Data Bar: Chart Data with Reseller_Sales_Amount selected 

By default, the aggregation used on the data is Sum.  But there are other options as well, have a look at the dropdown next to the [Sum(Reseller_Sales…  In the example here I’m going to keep the Sum.

Other aggregation functions of the Data Bar

The report Preview looks like this:

Report preview with the data bar

In this report we can easily see what states have got a higher sales amount: those with the longer bars.

Let’s add bars for the Country level as well.  There are two ways to achieve that: you can either drag a new Data Bar onto the report or you can just copy/paste the textbox containing our first Data Bar.

To make the report easier to read I will change the color of the Data Bar to the color of the group’s background.  Setting up the color of the Data Bar is done as follows: select the Data Bar so that you get the Chart Data popup.  In the Properties pane you should see that the Chart Series is selected.  Then right-click on the bar and select Series Properties.

The right-click menu on the Data Bar.

In the Series Properties window, select the Fill page and select your favorite color.  If you want you can also use a gradient fill or pattern.  You can even use a Switch statement and color them differently depending on their value, similar to the method that I used in my SSRS and MDX: Detecting Missing Fields article.  Well, in short, any expression that you can think of and results in a color will work fine!

Series Properies: setting up the Fill color

With the second data bar and custom coloring set up, here’s the rendered report:

Report with data bar on two grouping levels

Hang on, is that correct? As you can see, the data bar for California is longer than the one for Canada while the sales amount for Canada is definitely the higher one.  An even nicer example is United Kingdom with only one state England.  Both amounts are equal yet their data bars are certainly not.

Well, this is because the data bar by default uses the same scope as the group where it’s put.  Canada and United Kingdom are in the Country group and all Country data bars compare nicely to each other.  California and England are in the State/Province group and also compare nicely to each other!

Depending on the report’s requirements this may or may not be the desired effect.  But I wouldn’t be mentioning this if there weren’t any other options, would I?

Setting The Maximum Value Of The Horizontal Axis

A correct column label for our current column would be “% of group”.  Let’s add a second column which will show the percentage of the row compared to the total of the dataset, “% of overall total”.  After adding the extra column, copy/paste the data bars from the first column over into the new textboxes in the second column.

To get what we want, we need to tell the data bar that the maximum for the horizontal axis is the total of the dataset and not the total of the grouping level of the table.  So, right-click one of the data bars in the new column and select Horizontal Axis Properties.

Right-click menu on tablix textbox with a data bar

As you can see, the default for the Maximum value is set to “Auto”.  Note that the name of my tablix is “Tablix1”, as shown in the Align axes in dropdown.

Horizontal Axis Properties

Let’s replace the Auto as Maximum with the following expression:

=Sum(Fields!Reseller_Sales_Amount.Value, "Tablix1")

This expression says that we need the sum of the Reseller_Sales_Amount, scoped over the whole tablix.

Having changed the Maximum value on both data bars gives us the following preview:

Report rendered with status bar scoped over whole dataset

As you can see, this time around the data bars for England and United Kingdom have gotten the same size.  Bars from different grouping levels can now be compared with each other.

Showing Labels On The Data Bars

In this report it would be interesting to add a label that displays the percentage to the status bars, so let’s do that.

For the percentage calculation of the first column of data bars we need to get the total of the current group and divide that by the total of the group one level higher.  For the State/Province level that gives us the following expression:

    / Sum(Fields!Reseller_Sales_Amount.Value, "grpCountry")

The grpCountry refers to the name of the grouping one level higher, as shown in following screenshot:

The groupings as defined on my example tablix

And for the Country level we need this expression:

    / Sum(Fields!Reseller_Sales_Amount.Value, "Tablix1")

The only difference with the previous expression is the scope.

“Ok, so where do I type those expressions?” I hear you think.  Right-click the Data Bar (after first selecting the textbox that contains it) and choose Show Data Labels.

Show Data Labels in right-click popup on Chart Series

That adds a label to the chart but we still need to configure it to show the percentage.  By default it uses the value as label.  This can be verified in the Chart Series properties:

Chart Series properties: UseValueAsLabel

The UseValueAsLabel is set to True, confirming what I just stated.  Furthermore, activating the Show Data Labels option also set the Visible property to True.  So another way of adding the label is by just setting this property to True.

Let’s now configure that label.  Right-click it and select Series Label Properties.

Right-click menu on label

The General page of the Series Label Properties window allows you to define the Label data.  That’s where you need to enter the expression that I mentioned earlier.

After you’ve entered the expression, the BIDS will ask you if you want to set the UseValueAsLabel to False, so click Yes.

Do you want to set the UseValueAsLabel to False?

With the label selected as shown in the next screenshot you can use the formatting toolbar buttons to give it a decent font and all.

Data Bar with Label selected

For our second column of data bars the expressions need to take into account that we need the percentage as compared to all the data, unrelated to the level.  This actually makes it easier because we can use the same expression on both the State/Province and Country level.  Furthermore, it’s exactly the same expression as the one used on Country level in the first data bar column (i. e. the second expression above) because Country is the highest level.

Let’s have a look at the result in preview:

Preview of report with labels on data bars

Woah, looks like we forgot something doesn’t it?  We forgot to format the label as being a percentage!  There are two ways to get that done: through the Number page on the Series Label Properties window:

Series Label Properties: formatting number as percentage

Or by using the properties with the label selected (the Properties pane should show Chart Series Labels in the dropdown on top):

Chart Series Labels properties

I’ve opted for a percentage without any decimals.

Let’s have another look at that report:

The final report with two data bar columns

That sure looks better doesn’t it?  The status bars on the right compare over the grouping levels while those on the left compare with each other within the same group.  And they all have a clear label indicating what they represent.

And then colleague-expert Mark Wills jumps in with the following remark (rephrased a little):

Hang on, are you really sure those bars on the left are what they should be?  How’s it possible then that 71% of NSW (Australia) appears to be only about 20% and the 100% of England (United Kingdom) is only halfway filled?

Interesting observation and indeed, Mark is right: those data bars on the left are still not right!  Let’s get this fixed.

On the Country level the fix is really easy.  What we want the bar to reflect here is the same as in the bar on the right: the percentage of the country compared to the whole dataset.  That’s achieved using this expression as Maximum in the Horizontal Axis Properties:

=Sum(Fields!Reseller_Sales_Amount.Value, "Tablix1")

Here’s what that looks like:

Data bar on Country level now reflects the percentage of the group

And finally, here’s the expression to be used to fix the data bar on State/Province level:

=Sum(Fields!Reseller_Sales_Amount.Value, "grpCountry")

Indeed, the data bar on this level should show the percentage compared to all the items in the group.

Final result:

All data bars fully operational as advertised!

Conclusion: it’s not a good idea to leave the Maximum setting in the Horizontal Axis Properties set to “Auto”.

Have fun putting those data bars on your reports!



BOL 2008R2: What’s New (Reporting Services)

BOL 2008R2: Sparklines and Data Bars

An Introduction to Data Bars in SQL Server Reporting Services 2008 R2


Tags: , , , ,

Almost a year ago I wrote a small blog post to ask everyone to vote on a certain Connect item related to the linking of two datasets in a Reporting Services report.  At this moment there have been 87 positive votes on the request.

Now that SQL Server 2008 R2 has been RTM’ed, it’s time to demonstrate new functionality in SSRS that allows the retrieval of data from another dataset, a dataset not linked to your data region.  It’s not completely the same as actually joining two datasets, but it’s better than nothing – and it works!

In this article I’ll show you how exactly this works by using the new Lookup, LookupSet and MultiLookup SSRS functions.

I’m using the AdventureWorks 2008R2 relational database and the AdventureWorksDW 2008R2 data warehouse, available from CodePlex.  The main data is coming from the data warehouse while all the lookups are done on the relational database.

Setting Up A Basic Table Report

In my report I’ve created a dataset called dsInternetSales.  This dataset is retrieving data from the AdventureWorksDW2008R2 data warehouse using the following query:

select PROD.EnglishProductName, PROD.ProductAlternateKey, PROD.ListPrice,
    PSC.EnglishProductSubcategoryName, PC.EnglishProductCategoryName,
    S.OrderQuantity, S.SalesAmount
from FactInternetSales S
inner join DimProduct PROD on S.ProductKey = PROD.ProductKey
inner join DimProductSubcategory PSC
    on PROD.ProductSubcategoryKey = PSC.ProductSubcategoryKey
inner join DimProductCategory PC on PSC.ProductCategoryKey = PC.ProductCategoryKey

Using that dataset, I’ve set up a Table as shown in following screenshot.

Basic Table Report

The Details group has been set up to group on ProductAlternateKey.  On top of the Details group, I’ve grouped on EnglishProductSubcategoryName and the top-level group is grouping on EnglishProductCategoryName.  The Order Quantity column is displaying the sum of the OrderQuantity values for each ProductAlternateKey.

The result is a report that shows all (internet) sales per product, without any filtering.  Very useful report if you want to know how many items your company has sold since it’s existence.  Okay, management would probably like to see some filtering on here, but that’s not the purpose of this article.

Here’s what it looks like in Preview:

Basic Table Report Rendered


Adding Data From Another Database

Imagine now that you need to add an extra line under each product, containing the product description.  But this description is not available in the data warehouse.  In fact it could even be stored on another server.

In the example here we will retrieve the description from the AdventureWorks2008R2 relational database.

Setting Up The Second Dataset

I’ve created an additional dataset called dsProductInfo, using the following query:

select P.ProductNumber, PD.Description
from Production.Product P
inner join Production.ProductModel PM on P.ProductModelID = PM.ProductModelID
inner join Production.ProductModelProductDescriptionCulture PMPDC
    on PMPDC.ProductModelID = PM.ProductModelID
    and PMPDC.CultureID = 'en'
inner join Production.ProductDescription PD
    on PMPDC.ProductDescriptionID = PD.ProductDescriptionID

Not only does it retrieve the product’s description, we’re also fetching the ProductNumber.  Here’s what part of the result looks like:

Result of product description query

The reason that we’re retrieving ProductNumber as well is because it matches with the ProductAlternateKey which we’ve retrieved earlier in our first dataset.  And this is very important because that’s the key on which we’re going to link the datasets.

Using The Lookup Function

I’ve added an additional row inside the Details group and inserted a Placeholder to retrieve the product’s description, using the new Lookup function.

Additional row inside Details group

So, what does the Placeholder’s expression look like?  Here it is:


As you can see, the Lookup function requires four parameters.

The first parameter is the key value in your current dataset, the dataset used by the table data region.  In our case that’s the ProductAlternateKey field in the dsInternetSales dataset.

The second parameter is the name of the key field in the second dataset, the one on which the lookup will happen.  In our case that’s the ProductNumber in the dsProductInfo dataset.

The third parameter is the field from the second dataset that you’re wanting to retrieve using the lookup, in our case the Description field from dsProductInfo.

And finally, the last parameter is the name of the dataset on which you want to do the lookup.

Please note that parameter number four is a string parameter, so the value needs to be enclosed by double quotes.  If you forget about that, you’ll get a couple of nice error messages like these:

[rsInvalidLookupScope]  The Value expression for the textrun ‘Textbox29.Paragraphs[0].TextRuns[0]’ has a scope parameter that is not valid for a lookup function. The scope parameter must be set to a string constant that is the name of a dataset.

So, don’t forget the quotes.

With the Lookup call set up as explained, here’s the updated report Preview:

Rendered report with product descriptions added through Lookup

How cool is that huh?  Each product has gotten a description, retrieved from another database, and still in the same table data region.  Before R2 of SQL Server 2008, this wasn’t possible to achieve (well, not easily anyway) and now it’s actually fairly simple!

Is that all?  Ha, I was kinda hoping that you were going to ask that.  No, it’s not all, there are two more new lookup functions: LookupSet and MultiLookup.

More Lookups: The LookupSet Function

In case you’re wondering, in the Expression Builder the new lookup functions are located under the Miscellaneous node:

Expression Builder: the new lookup functions are under Miscellaneous

Let’s say that you want to add another detail row, this time it needs to show all colors in which the product is manufactured.  Again this additional info is coming from the AdventureWorks relational database.

I’ve created a dataset called dsProductColors using the following query:

select distinct LEFT(P.ProductNumber, 6) as ProductCodeWithoutColorAndSize,
from Production.Product P
where P.Color is not null

When looking at the product codes, I noticed that for the products which are available in several colors and sizes, the last four characters represent the color and size.  Which means the first six characters define the product itself, without color or size.  That’s why the query is using the Left function to create a product code of only the first six characters of the ProductNumber.  Using the distinct keyword, we remove any duplicate records.

(Please note that I’m not 100% sure if this logic applies to all products but for this demo it’s fine.)

Here’s what the query retrieves:

List of product colors per product

As you can see, for some products there’s more than one record.  And that’s exactly what the LookupSet function was made for: it retrieves a set of data based on the key given to it.  This is different from the Lookup, where for each key value it would fetch only one value.

Again I’ve added an additional row inside the Details group and used a placeholder with the following expression:

        Left(Fields!ProductAlternateKey.Value, 6),
    ", "

The LookupSet call itself looks very similar to the Lookup, with the same four parameters.  I’ve used the Left function on the first parameter to apply the same logic to the ProductAlternateKey as we did with the ProductNumber.

However, there’s one important difference: the call of the Join function.  This is needed because the LookupSet is returning a set, or better, a VariantArray, not just a single value.  And an array cannot be visualized without first concatenating the values somehow.  With the Join, we can concatenate the different values, using a comma as separator.

And here’s the resulting report:

Our report displaying the list of colors for each product

With the first two lookup functions covered there’s one more to go.

Just One More Lookup: The MultiLookup Function

Guess what crazy request the business people have come up with this time?!  The report should have a multi-value filter on region, and for each region selected, the top of the table should list the number of shops opened in the first year in those regions.  For example, if the first shop in France was opened in 1970 and in that same year there were two other shops opened in France, the report should state “France: 3 shop(s) opened in 1970”.

Ow, and that list should be located right under the main header so deciding to use a textbox outside of the table is not a good idea :-)

Sounds like we can use the MultiLookup function for this request.  But let’s first set up the filter.

I’ve created a dataset called dsRegions, using the following query on the data warehouse:

select DST.SalesTerritoryAlternateKey,
    DST.SalesTerritoryCountry + ' - ' + DST.SalesTerritoryRegion as CountryRegion
from DimSalesTerritory DST
where DST.SalesTerritoryAlternateKey > 0

Then I’ve added a multi-value parameter called Regions with the Available Values coming from the dsRegions dataset.

Regions parameter: the Available Values

This parameter can now be used in our main dataset.  Here’s the updated query:

select PROD.EnglishProductName, PROD.ProductAlternateKey, PROD.ListPrice,
    PSC.EnglishProductSubcategoryName, PC.EnglishProductCategoryName,
    S.OrderQuantity, S.SalesAmount
from FactInternetSales S
inner join DimProduct PROD on S.ProductKey = PROD.ProductKey
inner join DimProductSubcategory PSC
    on PROD.ProductSubcategoryKey = PSC.ProductSubcategoryKey
inner join DimProductCategory PC on PSC.ProductCategoryKey = PC.ProductCategoryKey
inner join DimSalesTerritory DST on S.SalesTerritoryKey = DST.SalesTerritoryKey
where DST.SalesTerritoryAlternateKey in (@Regions)

The only difference with the previous query are the two last lines: we add DimSalesTerritory to the joins and filter it on SalesTerritoryAlternateKey.

Don’t forget to set up the parameter.

Setting up the parameter on dsInternetSales

With the filter implemented, let’s get started on that extra lookup. 

First we need to add the dataset containing the data that we need.  I’ve created a dataset called dsShopsOpenedInFirstYear, using the following query on the relational database:

with ShopOpened as
    select T.TerritoryID, T.Name Territory, S.Name ShopName,
        S.[Demographics].value('declare default element namespace "";
        (/StoreSurvey/YearOpened)[1]', 'integer') AS [YearOpened]
    from Sales.Store S
    inner join Sales.SalesPerson SP on S.SalesPersonID = SP.BusinessEntityID
    inner join Sales.SalesTerritory T on SP.TerritoryID = T.TerritoryID
FirstShopOpened as
    select MIN(YearOpened) YearOpened, TerritoryID
    from ShopOpened
    group by TerritoryID
select SO.TerritoryID,
    SO.Territory + ': ' + CAST(COUNT(*) as varchar(100)) +
    ' shop(s) opened in ' + CAST(SO.YearOpened as char(4)) as ShopString
from FirstShopOpened FSO
inner join ShopOpened SO on SO.TerritoryID = FSO.TerritoryID
    and SO.YearOpened = FSO.YearOpened
group by SO.TerritoryID, SO.Territory, SO.YearOpened

This query uses a couple of Common Table Expressions to get to the result as we need it.  The first CTE, ShopOpened, creates a list of all shops with their territory and the opening year.  The second CTE, FirstShopOpened uses the ShopOpened CTE to retrieve the first opening year for each territory.

And finally the main query uses both CTEs to create the following result:

Number of shops opened in first year per territory

For each territory we’ve constructed a string that shows how many shops were opened in the first year of that region, and in what year it happened.  Coincidentally all regions had shops opened in 1970.

The TerritoryID corresponds with the SalesTerritoryAlternateKey, which is the value of our Regions parameter.

I’ve added an extra row under the top row in the table data region, and I’m using the following expression in that row:


The MultiLookup takes four parameters, just like the two previous lookup functions.  They are all the same, except for the first one.  It may not be very obvious in the example here, but the Parameters!Regions.Value is in fact not just a single value.  It’s an array because we’ve set up the parameter as being multi-valued.

And that’s exactly what the MultiLookup function requires.  Here’s the description for that first parameter, as stated in the Books Online:

(VariantArray) An expression that is evaluated in the current scope and that specifies the set of names or keys to look up. For example, for a multivalue parameter, =Parameters!IDs.value.

Just like the LookupSet function, MultiLookup returns a VariantArray, so we use the Join function to concatenate the values.

Interesting to note here is that I’m adding the break HTML tag as separator.  I want the result of the expression to be treated as HTML, so that each value retrieved ends up at a new line in the textbox.  To get this to work as expected, you need to tell the Placeholder that the resulting value should be treated as HTML:

Using HTML in a Placeholder

Everything is now set up to have another report Preview.  The following screenshot shows the report with the data filtered on Canada, France and Australia:

The final report performing three different lookups

Seems to be working fine, doesn’t it?

Okay, that’s it for now, have fun looking up that data!



BOL2008R2: Lookup Function

BOL2008R2: LookupSet Function

BOL2008R2: MultiLookup Function


Tags: , , , ,

Consider the following scenario. You’ve been developing some SSIS packages, nicely making sure that all errors and warnings are gone from the Error List window. So the next step is to deploy the packages to the server and schedule them. After some days you decide to have a look at the sysssislog table and discover that it’s filled with OnWarning records!

Well, this is an extreme example of course. Careful developers would notice the warnings in either the Output window or the Progress tab while testing the package in the BIDS.

All these warnings are indicating that some columns in your data flows have become obsolete.  Here’s an example:

The output column “BKCustomerID” (13529) on output “OLE DB Source Output” (12088) and component “OLE_SRC MySource” (12077) is not subsequently used in the Data Flow task. Removing this unused output column can increase Data Flow task performance.

The problem with these warnings is not only that they slow down the transformation process but they obfuscate the SSIS log with thousands of records, making it difficult for you when you need to investigate an issue. And this is something that you really don’t want, especially when the business people are calling you to ask why the data is not up-to-date on the production servers.

If you ever wished that you’d see these warnings whilst developing the package instead of at runtime only, now’s your chance to make a change. Or better, to ask Microsoft to implement the change.

Follow this link to cast your vote on Connect!

Thank you Jamie for pointing this out.

Have fun!



Tags: , ,

Now that the calendar has been filled and the registration opened, it’s time for another free event announcement: Community Day 2010!

A total of 14 different Belgian User Groups focused on Microsoft technology will be delivering sessions all day long, a total of 24.  The SQLUG will deliver two sessions.

Community Day 2010

So what are you waiting for?  Sign up for a full day of free training!

And if you’re a LinkedIn user, don’t forget to add the event to your list: Community Day 2010 on LinkedIn.  Ow, and post a comment here if you’re going.  I’d like to find out if any Belgians are actually reading this (most of my visitors are from the US).

As for the food department: there won’t be any French (French??) fries this year.  I wonder what they’ve got for us this time.  Me personally I wouldn’t mind if the “fritkot” was replaced with a Burger King “fetchpoint”.  Okay, it’s probably not as healthy as some other choices, but who cares at that stage?  All we want is food and the requirements are: good taste, and plenty of it!  After all, we’re still in a movie theatre, which is not really the place to go to when you’re on a health trip.  We’ll eat a piece of fruit when we get back home.  But I wouldn’t say no to a good glass of freshly squeezed OJ in the morning, followed by a croissant and a strong coffee :-)

Practical event details
Utopolis Mechelen (Map)
Address: Spuibeekstraat 5 2800 Mechelen
Event date: Thursday June 24th
Start & end time: 8.30 – 18.30

Community Day on Twitter: #comday2010

Need more info?  Click here.

Happy learning, and see you there!



Tags: ,

No excuses for not attending if you’re a SQL Server DBA, system administrator or just interested in the subject.  It’s a free training event, it’s presented by Ricardo Noulez and it’s taking place in the Microsoft offices in Zaventem.


Thursday, April 20th, 2010


Microsoft België
Corporate Village
Leonardo Da Vincilaan 3
1935 Zaventem

More info

Managing SQL Server requires insight in the state and performance of the underlying infrastructure, OS and individual SQL components.

System Center Operations Manager 2007 R2 cannot only give you that insight, but it can be reused in a broader application/service monitoring view.
This session focuses on the out-of-the-box capabilities of System Center Operations Manager 2007 R2 but also shows you how a distributed application can easily reuse these capabilities to provide an extra step in monitoring.

About Ricardo Noulez

Ricardo Noulez is a Senior Consultant at Microsoft Consulting Services in the Benelux. Been with Microsoft since January 2002 as a subject matter expert in Microsoft Operations Manager (now System Center Operations Manager) and Systems Management Server (System Center Configuration Manager). Ricardo has a great focus around the System Center product family and he is currently expanding his horizons towards the System Center Service Manager product. He is always interested in finding ways to improve the way that customers manage their IT environment and has certifications and great affinity with ITIL good practices and the MOF framework to help them achieve this. In the first third of his 18 year career, Ricardo was a developer but gradually converted himself to Systems Management and IT operations. This gives him a broad understanding in the different needs and view-points of all technical players that make IT ideas come true.

Click here for more info or here to register!

See you there,



Tags: , ,

« Older entries

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