SQL Server 2008 R2

You are currently browsing articles tagged SQL Server 2008 R2.

I came across an interesting little enhancement which I’d like to share with you.  I’m sure you’re all familiar with the following error message:

Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object ‘dbo.#t’ with unique index ‘PK_Unique’.

That’s right, the “hey, you’re inserting junk, stop that right now!” message.

However, earlier this week I encountered a variation of that error in our ETL logs:

Msg 2601, Level 14, State 1, Line 3
Cannot insert duplicate key row in object ‘dbo.#t’ with unique index ‘PK_Unique’. The duplicate key value is (1, 2).

I’m sure I don’t need to explain how useful that extra sentence can be, right?  So, then I started digging because obviously I want that error to always include the offending values.

Recently our database back-end servers have been replaced with new machines running SQL Server 2008 R2 SP1.  And indeed, as of R2 SP1, this error message has gotten an upgrade!

If you want to check for yourself, the following code snippet can be used to generate the error:

create table #t (PK1 int, PK2 int);
create unique index PK_Unique on #t(PK1, PK2);
insert into #t values (1, 2),(1, 2);

Now, message 2601 is not the only one complaining about duplicate keys.  Another example can be generated using the following query:

create table #t (PK1 int unique, PK2 int unique);
insert into #t values (1, 2),(1, 2);

Executing that snippet on SQL Server 2008 R2 SP1 results in this error:

Msg 2627, Level 14, State 1, Line 1

Violation of UNIQUE KEY constraint ‘UQ__#t________C5776555123EB7A3′. Cannot insert duplicate key in object ‘dbo.#t’. The duplicate key value is (2).

Do you notice the difference?  The message did indeed get an upgrade, but is not able to support a simultaneous violation on multiple columns, while msg 2601 can.  Hopefully they can get that fixed by SQL Server 2012 RTM!

Out of curiosity I decided to get a closer look at other possible variations on this error and dug into the sys.messages table using this query:

select * from sys.messages
where text like ('%duplicate key%');

Results of that query:

All error messages containing "duplicate key"

So, there’s one more to investigate: 1505.  To get that error generated is not that complicated.  We actually already have all the statements, just need to use them in the right order, like so:

create table #t (PK1 int, PK2 int);
insert into #t values (1, 2),(1, 2);
create unique index PK_Unique on #t(PK1, PK2);

And what do we get?

Msg 1505, Level 16, State 1, Line 1

The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name ‘dbo.#t__________________________________________________________________________________________________________________00000000001D’ and the index name ‘PK_Unique’. The duplicate key value is (1, 2).

Nice, message 1505 also supports multiple columns, that makes two out of three!

Ow, don’t forget to clean up (yes, I even do that for temporary tables).

drop table #t;

Additional Info

Some further digging around on the internet brought me to the following suggestion on Microsoft Connect: Duplicate Key Values by Anton Plotnikov.  Looks like our enhanced statements are the result of that request.

There’s another suggestion as well: Tweak To Duplicate Key Message by Louis Davidson.  Louis suggests to have all offending values added to the message as well, but in a different situation than when dealing with multiple unique columns.  You can create one insert statement that inserts more than one record with offending values, and that’s the situation he’s referring to.  If you feel that’s important, get over to Connect and cast vote!  I do think that the number of values would need to get limited in that case, we might end up with really long error messages otherwise.

One last link, also by Louis Davidson.  He also wrote a blog post about the error message when he found out about it in Denali.

Have fun!

Valentino.

Share

Tags: ,

Now and then I encounter forum questions in the style of the following:

I have a report with a title.  When rendered through the Report Manager and when exported to PDF, I want it to render as normal.  However, when exported to Excel I do not want to get the title.  How can I hide it?

Because I don’t like re-inventing the wheel each time I decided to write a blog post about it.

As of SQL Server 2008 R2, we’ve got a built-in global field that can help us out.  This field is called Globals!RenderFormat.  It has two properties: Name and IsInteractive.  Name represents the unique name that indicates the chosen renderer, and IsInteractive indicates whether or not the chosen report format is, well, interactive.

Depending on the renderer, the values of the properties differ.  To be able to use the variable in an expression, we need to know its values for each rendering format.  Here’s the list of different possibilities:

Renderer RenderFormat.Name RenderFormat.IsInteractive
Preview in BIDS or rendered through Report Manager RPL True
XML file with report data XML False
CSV (comma delimited) CSV False
TIFF file or Print button IMAGE False
PDF PDF False
MHTML (web archive) MHTML True
Excel EXCEL False
Word WORD False

If these names for RenderFormat look familiar to you, you’re probably right.  Have a look at the rsreportserver.config file in the C:\Program Files\Microsoft SQL Server\MSRS10_50.SQL2008R2\Reporting Services\ReportServer folder.  Note that you may need to adapt the folder to your specific settings.  In my case my instance is called “SQL2008R2”.  Near the bottom of that configuration file you can find the <Render> node, located under <Extensions>.  The names that you see there are those used by the RenderFormat.Name property.

Now that we know what values to test on, let’s get started.

If we get back to the example of hiding a title, or textbox, when exporting to Excel, here’s what needs to happen.  Locate the Hidden property of the textbox that you want to hide, and give it the following expression:

=IIF(Globals!RenderFormat.Name = "EXCEL", True, False)

What we’re saying here is: if the RenderFormat is EXCEL, then the Hidden property should be set to True.  Which results in a hidden textbox whenever the report is exported to Excel!

As Erik pointed out in the comments, in this particular case you don’t need the IIF() statement.  The result of the expression results in True when the expected value should be True, and False when False is expected.

As a quick note: when building your expression through the expression builder, you’ll notice that the Intellisense doesn’t know the new RenderFormat field yet.  Do not worry about that, just continue typing and ignore any errors being indicated.  If you use the syntax as I highlighted above, it will work!  Well, unless you’re running an earlier version than SQL Server 2008 R2 of course.  In that case it won’t work.

Intellisense doesn't know RenderFormat yet

In contradiction to the Intellisense, the bottom part of the expression builder screen has been updated to show the new properties.  So if you don’t remember the syntax, you can just locate the field in the Built-in Fields category and give it a good double-click.

RenderFormat is located in the Built-in Fields category

Of course, the Excel example in this post is just one of many possibilities that this new field offers.  Is your company environment-friendly and does it want to prevent wasting paper?  Now it’s possible, just hide that 50-pages long table when the report is being rendered for print!

Have fun!

Valentino.

References

Globals!RenderFormat aka Renderer Dependent Report Layout by Robert Bruckner

Share

Tags: , , ,

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)
    / 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)
    / 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!

Valentino.

References

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

Share

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:

=Lookup(
    Fields!ProductAlternateKey.Value,
    Fields!ProductNumber.Value,
    Fields!Description.Value,
    "dsProductInfo"
)

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,
    P.Color
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:

=Join(
    LookupSet(
        Left(Fields!ProductAlternateKey.Value, 6),
        Fields!ProductCodeWithoutColorAndSize.Value,
        Fields!Color.Value,
        "dsProductColors"
    ),
    ", "
)

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 "http://schemas.microsoft.com/sqlserver/2004/07/adventure-works/StoreSurvey";
        (/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:

=Join(
    MultiLookup(
        Parameters!Regions.Value,
        Fields!TerritoryID.Value,
        Fields!ShopString.Value,
        "dsShopsOpenedInFirstYear"
    ),
    "<br>"
)

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!

Valentino.

References

BOL2008R2: Lookup Function

BOL2008R2: LookupSet Function

BOL2008R2: MultiLookup Function

Share

Tags: , , , ,

As I have recently become a core-member of the Belgian SQLUG, you’re probably going to see a bit more spread-the-word posts about interesting events or other advantages, such as this one.

image

The Belgian SQL Server User Group offers a significant 35% discount for its members (even more than the early-bird discount) for any PASS European Conference 2010 registration.

Use discount code BEC15Y and enjoy your savings on the registration.

For more information check out the SQLUG website.  PASS European Conference 2010 is Europe’s premier conference for SQL Server technical education and business networking.  Meet top SQL Server experts from Europe and around the world.  Learn about best practices, effective troubleshooting, how to prevent issues, save money, and build a better SQL Server environment for your company or clients.

A while ago I already blogged about the sessions that I’m planning to see (which reminds that I should have another look at the agenda and update my list there :-) )

Have fun!

Valentino.

Share

Tags: , ,

« Older entries

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