SQLServerPedia Syndication

Posts in this category are syndicated at sqlserverpedia.com.

Once again I’ve been wasting some time because of a silly bug.  This time it was due to the OLE DB Source component and the way it works with parameters.  If you are in a situation where you know your query is working fine and yet no records are going down the data flow, here’s a possible solution!

Disclaimer: this issue exists up until SQL Server 2008 R2.  Read on for details!

Update: after being advised to do so by several people, including Jamie Thomson, I’ve filed a bug at MS Connect: SSIS OLE DB Source incorrectly returns zero records in combination with parameter and comment

The Situation

I had a Data Flow with an OLE DB Source that uses one parameter, for instance:

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

I knew the query was working fine because when executed through SSMS and with the question mark replaced with ‘blue’, it would return 28 rows:

28 records in Management Studio

But when executed in BIDS, through either Execute Package or Execute Task, it would return zero records:

Zero records, zilch, nada, niente, none at all!

So I thought something must be going wrong with the package variable that gets passed into the source parameter, somehow.  I’m not going into details on what I tried out in my attempt to get this working, but I can tell you that I started to get really irritated.  My colleague Koen Verbeeck (b|t) can confirm this because I called him over to my desk to help me think! (thanks btw!) Smile

After some further tinkering with the data flow, we had our smart moment of the day and decided to launch SQL Server Profiler to see what BIDS was sending to the server!  I’m not sure if you’re aware of this but BIDS is doing some metadata-related stuff when preparing queries.  As far as I can tell, it also tries to determine the parameter type by running the following query:

 set fmtonly on select Color from  dbo.DimProduct
--some really smart comment goes here where 1=2 set fmtonly off

When creating this statement, it seems to use the whole FROM clause of the original query, including any trailing comments.  It combines that with a SELECT statement that contains the field that gets filtered and it appends " where 1=2 set fmtonly off".

But alas, apparently it’s not aware that lines can be commented out by using a double dash.  So part of its generated statement is commented out.  What it should have done is used some CRLFs, especially in front of the WHERE clause.  But it didn’t.

So, as a result of that, FMTONLY remains on while the SELECT statement gets executed, resulting in zero records!

For those unfamiliar with the FMTONLY setting:

Returns only metadata to the client. Can be used to test the format of the response without actually running the query.

And I can actually confirm what I’m stating here by changing the query to the following:

set fmtonly off;
select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = ?

28 records down the pipe!

We've got data!

But this hack is a little too dirty to put in production.  So what else can we do?  Well, use block-style comments instead and we won’t face the issue!

select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
/* some even smarter comment goes here */
where Color = ?

So, as I mentioned at the start of the post, this behavior can be reproduced using SSIS versions prior to 2012.  What about 2012 then?  Here’s the result of the Data Flow using the first query mentioned above:

SSIS 2012: we've got data, even with the "faulty" query!

Alright, that works better!  Now let’s use Profiler to check what’s going on here.  This is the first statement that gets executed:

exec [sys].sp_describe_undeclared_parameters N'select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = @P1'

Further down, I also see this one:

exec [sys].sp_describe_first_result_set N'select ProductAlternateKey, EnglishProductName
from dbo.DimProduct
--some really smart comment goes here
where Color = @P1',N'@P1 nvarchar(15)',1

It is using an entirely different approach, no longer using the FMTONLY setting!  Hang on, this rings a bell!  Look what the BOL page for SET FMTONLY (2012 version) specifies:

Do not use this feature. This feature has been replaced by sp_describe_first_result_set (Transact-SQL), sp_describe_undeclared_parameters (Transact-SQL), sys.dm_exec_describe_first_result_set (Transact-SQL), and sys.dm_exec_describe_first_result_set_for_object (Transact-SQL).

Cool stuff!

Conclusion

If you’re not on SQL Server 2012 yet, be careful with comments in OLE DB Sources in the SSIS Data Flow!  Ow, and get the SQL Server Profiler off its dusty shelf now and then!

Have fun!

Valentino.

Share

Tags: , , , ,

I’ve been using the SQL Server Management Studio (aka SSMS) since it was first released with SQL Server 2005.  And yet, it hasn’t stopped to surprise me.  Earlier this week I’ve discovered a feature which I will use frequently as of now!

While doing my day-time job at the customer, I have a habit of connecting to several servers each time when I open SSMS.  I always connect to them in the same order so that I can quickly locate them in the Object Explorer.

The servers I want open all the time are DEV DB server, DEV SSIS server, UAT DB server and UAT SSIS server.  The SSIS servers are needed to get quick access to the Job Agent, while the DB servers are what I use all the time to actually do my job.  To avoid confusion: on those SSIS servers, I’m connecting to the Database Engine, not the SSIS service.

So, earlier this week I was trying to find a method to easily connect to these servers with as few clicks as possible.  And guess what: I found one! (Well, otherwise I wouldn’t be writing this of course.)

In the following paragraphs I’ll describe a method which you can use to connect to several servers at once, with just two clicks!  I can tell you, it sure beats the Connect To Server popup window!

Screenshots taken using SQL Server 2012, but this should work as of 2005.

First we’ll create a group of all the servers that we’d like to connect to with just two clicks.

Open the Management Studio and switch to the Registered Servers view.  If you can’t find it, use the menu to select View > Registered Servers or hit CTRL+ALT+G on your keyboard.

Open the Registered Servers view

Right-click the Local Server Groups node located under Database Engine and select New Server Group… to create a new group.

Creating a new registered servers group

Give it a good name and click OK.

New Server Group Properties

Right-click the new group and select New Server Registration….

Enter the server credentials as appropriate.

New Server Registration

What’s important here, besides the obvious such as entering correct credentials, is the Registered server name.  The servers will be ordered alphabetically using that name, and that’s also the order in which they’ll be opened!  So if you’re like me and you want your servers to be opened in a specific order, you’ll need some naming creativity or use numbers.  In my case I’m glad that DEV orders alphabetically before UAT, which is what I want. Smile

Ow, in case you’re wondering about that server name shown in the screenshot above, the dot refers to localhost and sql2012 is the instance name.

To demonstrate that I’m not joking, I’ll now register a second server and name it “Another server”.  After clicking the Save button, here’s what the Registered Servers window displays:

"Another server" shown above "My DB Server"

As I told you, “Another server” is shown above “My DB Server” even though it was created later.

Now, what you’ve all been waiting for, how do I tell SSMS to connect to these servers?

Click number one is a right-click on the group name, My Favorite Servers in this case:

Use Object Explorer on the group name to open all servers in the group!

And click number two is the one on Object Explorer!

SSMS will now switch to the Object Explorer window and connect to the servers, in alphabetical order!

Object Explorer is connected to multiple=

How’s that for a time-saver huh?  I hope you’ll enjoy this as much as I will!

As a little extra, let’s quickly discuss one more feature of the Registered Servers window.  When you right-click a group, you also get a New Query menu item.  Clicking that will open a query window that’s connected to all the servers in the group.  You’ll be able to tell because the database dropdown in the menu reads <multiple>:

Query window connected to multiple=

And the status bar mentions the name of your server group and also <multiple>:

Query window connected to multiple=

Having a query window connected to several servers at once let’s you do interesting things, such as quickly checking what version and edition you’re running on all those servers:

Running the SELECT @@VERSION command on more than one server at once

Of course, I’m sure I don’t need to remind you but I will anyway, this means it’s actually a dangerous window as well.  In the database dropdown, you’ll get a list of all databases that are found on each server.  There’s no advanced logic used in the process of building that list: if the database name is found on all servers in the group then the name of the DB is added to the list.

You can select one of those databases and execute queries against them, just like this:

Running a query against more than one database over several servers

The status bar will now mention the actual database it’s connected to, which is actually its regular behavior.  And the messages pane will mention how many servers you’ve run the statement against.

In the case above, I created a table in two tempdbs on two servers.  Imagine that I ran a DROP TABLE instead, and I forgot that one of the servers in the group was the production server on which I didn’t want to drop that table.  So, be careful with those windows.  To avoid errors, close them as soon as you’ve finished the job for which you needed them open!

Have fun!

Valentino.

Share

Tags: , , ,

First Timer at PASS Summit 2012Even though I’m not new in the SQL Server world, last week I had the pleasure of being a First Timer at the PASS Summit in Seattle.  Many thanks to my employer Ordina for making this possible!

And not only was I a First Timer at the conference, at the same time it was my first trip to the US!

I believe I had prepared myself quite well so that I was ready to take maximum profit from all that was to be experienced in Seattle.  I attended Denny’s interesting First Timers webcast and I read many of the First Timers blog posts available through the PASS Summit site.

And yet, still some things took a different direction once I was actually experiencing all the great stuff at the conference.

Here’s a write-up of some of my encounters over there.  Not being an American I believe my post will contain some info which hasn’t been written down yet in the posts mentioned above.  On the other hand, I’d like to think it also contains good info for all first timers in the years to come, American or not.

Experiencing The PASS Summit

The Jetlag Issue

Never having been to America, I thought the jetlag would be okayish.  Well, it wasn’t!  In my case I arrived Monday evening and I was attending a preconference session the day after.  THIS IS NOT A GOOD IDEA!

Being at the conference means long days, really long days.  Breakfast starts at 7AM, the sessions start at 8AM and continue until 6PM.  And then the evening activities begin, you surely don’t want to miss out on those.

So, if you need to cope with a significant time difference and a long flight – in my case the trip took about 15 hours and there’s a 9-hour time difference with Belgium – leave at least one day earlier than I did so that you’ve got two nights in between arrival time and conference start.

Doing that also gives you a good chance to get to know the city a bit before it gets dark, and do some shopping if that’s what you’re after.  Don’t forget the gifts for the kids and wife/husband who got left alone in your home country.

In my case I chose to skip the two keynotes to get at least some jetlag worked away.  My reasoning was it’s better to skip the shows so that I could at least concentrate a bit more in the learning part of the conference.  Okay, some new stuff was announced but you can, in fact you WILL, always find out about that later.

And I was successful in getting over my jetlag, by the time it was Friday… And on Friday, I kissed Seattle goodbye to get started on yet another, even worse, jetlag! :/

Getting Money

If you don’t have any American dollars yet, don’t worry about exchanging money in your home country.  You can do that using Maestro.  However, give your bank a ring before getting on the plane because Maestro needs to get activated specifically for the US.  By default it is not activated because apparently 75% of fraud transactions is sourcing from the US.

At the same time, check with your bank if your VISA or MasterCard is activated for the US.  You may also want to increase it’s limit, depending on what that limit is currently set to and what you’re expecting to pay for with it.

Once you’ve arrived in the airport, use your Maestro card to get some dollars out of one of the available ATMs.  Also, insert your credit card and enter your pin code.  You don’t need to do anything with it, but entering it into the machine makes sure it’s registered as “being located in the US”.  At least, that’s what I was told to do and also what I eventually did.  I’m not sure if this step is absolutely necessary, but better be safe than don’t have any access to your credit card, right?

Using Money

I’m sure we all know how to use money, right?  So this chapter is mainly about the tipping, which is really different in the United States compared to Belgium (and several other European countries).

Here’s what I was told.  When going out to eat or drink something, you should always tip.  How much depends on the service.  The regular tipping amount is 18% and if you really liked it you should tip 20%.  If it wasn’t any good, you should still tip 15%.  That may sound weird to Europeans but apparently that’s how it works.  Well, unless they’ve been rude without cause, I guess in that case it’s okay to walk away without tipping.  Luckily I’ve only seen situations were I should tip at least 18%.

The last day I left my luggage with the bell boys.  A general tipping rule here is one dollar per bag, though some people give 5 dollars in total.

I’m not sure if the above applies to the whole of the US.  Next year the conference will be on the East Coast, Charlotte more precisely.  So if someone could let me know if tipping in Charlotte uses the same percentages as in Seattle, that would be great!

Meeting People

Thanks to my activity in the SQL community, which has been going on for several years now, I knew some people who were going to be present at the conference as well.  The Who’s attending list surely helped a lot here!

To make sure I didn’t forget anyone, I had made a list of names.  However, if you’re thinking that you’ll meet them by just running into them, forget about it!

The convention center is huge and there are more than 4000 people through which you need to search.  And as we all know, searching without an index can take a long time.  If you’re lucky then you run into them.  If you’re not, well, you don’t.  So if you really want to ensure that you’ll be able to meet someone, get their phone number!  This way you can text them to arrange a meeting location and time.  I say “text” instead of “call” here on purpose: calling abroad using my Belgian provider is really not an option unless I want to go bankrupt quickly.

If you didn’t get a phone number, get at least their email address.  Get linked with them on LinkedIn and start following them on Twitter, prior to the conference.  Also, let them know in advance that you’ll be there as well and that you’re looking forward to meeting them!  Ow, and find out if you’re staying in the same hotel.  That can surely help to meet them during the evening hours.

Having done all the above will increase your chances of meeting your friends significantly.

Was I successful in this?  Well, partly.  I met some, I didn’t meet others.  Better luck next time!  I believe the more you’ve been there, the easier it will become.  The reason for that is that your friends have connections as well.  So the more connections you have, the higher the chances become that your connections know the ones you haven’t met yet.  In that case, they can easily introduce you!

So far we’ve only mentioned peers whom we’ve already been in contact with somehow before.  How about the new ones?

To make sure you remember who you’ve met, you should ensure that you have a stack of business cards with you.  That’s the fastest way to give someone your contact details.  Don’t try memorizing names, unless you’re really trained for that.  You’ll meet so many people in such a short amount of time that it will become really challenging.

If you run out of cards, note down their name on your smartphone.  Or add them on Twitter and LinkedIn.

Be Smart, Get A Smartphone!

No, I don’t work for a hardware reseller.  But really, if you don’t have a smartphone yet, now’s your time to get one!  If the previous paragraph didn’t make that clear yet, I’ll explain why you need one right now.

A lot of SQL Server professionals are active on Twitter.  And there will be free wireless internet at the convention center.  This results in a lot of Twitter activity during the whole conference period.  You’ll be able to gain a lot of additional info which you wouldn’t have without a smartphone and access to Twitter.

Important hash tags this year were #sqlpass and #summit12.  Another one is #sqlfamily and you may also want to look into #sqlhelp, as explained in this nice article by Sarah Strate.

In fact, Twitter is used so widely that now and then you’ll even see your friends asking where you are, through Twitter!  Don’t forget to install the Twitter app too, much easier than using a browser!

To give you an idea, my Twitter Followers count has increased with at least 25 in one week time!

Finding A Room

Don’t worry too much about this.  A convention center is used to having to deal with lots of people unfamiliar with its layout.  There are signs everywhere.  And if you’re in a rush, every entrance door is guarded by people who know their way around there so just ask them in what direction you should walk.

Also, if you followed my previous tip, you should have a smartphone.  This year, PASS used an app called Guidebook.  Get it installed and you have the maps (and much more) in your pocket, real easy to reach!

Getting Into A Room

Do worry about this!

I missed out on two good sessions (well, three actually but one of them is not a regular session) because the room was stacked with people sitting on the floor even before it started!  So if there’s a particular session that you really don’t want to miss, go there early!

There’s only 15 minutes in between the sessions and if you need to switch floors, take into account that 15 minutes is really, really short.  Don’t start a conversation with people you meet on your way, tell them to meet later (lunch, evening) because you don’t want to miss the session.  Don’t worry too much about hurting their feelings, they’ll probably have the same on their mind.  To avoid any misunderstanding, just tell them what you’re up to.  Who knows, if you’re lucky they’re on their way to the same room!

If really needed to ensure you can get into the room, leave the previous session a little early.  But only if needed and the speaker is going over the end time.

How do you know to which sessions you should go super early?  That’s a bit difficult to predict, but certainly don’t look at room size.  The two I missed were located in the larger rooms.  Or maybe do look at room size: if the speaker is in a larger room, that may indicate it’s a popular one!  If you know that the speaker has a popular blog, or has written popular blog: another good indicator!  And if the speaker is a regular I’m sure that’s a good indicator as well!

Then there are the special sessions, such as the Lightning Talks and the BI Power Hour.  I didn’t have a problem with the Lightning Session that I attended, but the Power Hour was a totally different story.  Even though it was the last session on Thursday evening, the room was packed half an hour before it even started!

Travel Bags

Ensure you’ve got some space left when you close up your bags at home.  You’ll be getting your hands on some swag and I’m sure you don’t want to leave part of that at the hotel because it won’t fit in your bags!

In my case I was travelling with a Swissgear backpack as hand luggage and a compact trolley for regular luggage.  At the conference entrance we were given a nice backpack and I didn’t want to walk around at the airport with two backpacks.  The new backpack was too large to fit in my other one so only one option remained: it had to fit in my trolley!

I was also lucky enough to get my hands on a free, signed copy of the Professional Microsoft SQL Server 2012 Integration Services book by Brian Knight and co, which didn’t help either. Smile

Long story short: I went home with a trolley zipper that wasn’t far from bursting and a heavy backpack because of the book!

Conclusion

T-SQL TuesdayThe conference is not only about improving your SQL-fu.  It is about much more!  Meeting people you know but have never seen before, meeting new faces and minds, and of course also seeing your friends again!  Be well prepared and you’ll certainly have your hands full at the conference.

Coincidentally, my post seems to fit right in with this weeks T-SQL Tuesday topic, so here’s my participation!  See all of them through the #tsql2sday hashtag.

Looking forward to seeing my SQLFamily again!

And as always: remember to have fun!

Valentino.

Share

Tags: , , ,

Now and then I get inspired to write about a certain topic as a result of what I encounter on the forums.  Here’s one of those moments.

The examples in this article are created using SQL Server 2012 RTM – 11.0.2100.60 (X64).  I’m also using the free AdventureWorksDW2012 sample database available at CodePlex.  But that doesn’t imply that you actually need 2012 to get this to work.  The principle explained in this article should also work on SQL Server 2008 and even 2005.

You can download the resulting RDL from my SkyDrive.

Introduction

Have you ever needed to create a report that shows a list of something spread over multiple columns?  Then you may have come across a feature called newsletter-style reports.  Excited to have found out about that feature, you started creating your report.  But alas, upon rendering the preview you discovered that it didn’t work.  Further investigation made you conclude that the feature is only supported in two specific renderers (PDF and Image).  Ouch!

In this article I’ll be showing you a method to create multiple-column reports that you can actually use with all renderers!

Setting The Scene

We’ve been asked to create a report that shows a list of product codes with each category displayed on a separate page.  The codes should be displayed in four columns.

Implementing The Report

To get these requirements implemented, we’ll be using some T-SQL skills in combination with nested tables.  So, first step is the dataset query.

The Dataset

The query retrieves only the fields needed in the report plus one additional, calculated field called DisplayColumn.  This additional field indicates the column in which the code should get displayed.  Here’s what the results look like:

List of product codes with column number by category

As you can see, the column numbering starts at 1, goes up to 4 and then starts again at 1.  It also starts at 1 again at the start of a new category.  This is needed because we’ll be grouping the data on category in the report.

Now, let me show you that query and explain a bit how the DisplayColumn is getting calculated:

declare @numberOfColumns int = 4;

select dpc.EnglishProductCategoryName, dp.ProductAlternateKey
    , (ROW_NUMBER() OVER (
        PARTITION BY dpc.EnglishProductCategoryName
        ORDER BY dp.ProductAlternateKey) + @numberOfColumns - 1) % @numberOfColumns + 1
    as DisplayColumn
from dbo.DimProduct dp
inner join dbo.DimProductSubcategory dps on dps.ProductSubcategoryKey = dp.ProductSubcategoryKey
inner join dbo.DimProductCategory dpc on dpc.ProductCategoryKey = dps.ProductCategoryKey;

The main ingredient here is the ROW_NUMBER function.  According to its definition, it “returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition”.

That brings us to the OVER clause, and its PARTITION BY statement.  In our example we partition by category because we need the numbering to start over for each category.

Next up is the ORDER BY part.  We order by ProductAlternateKey, which is the product code that our report should display.  We want the codes to be ordered alphabetically so the column numbering should take that order into account.

As you can tell, I’m using some numeric trickery on the result of the ROW_NUMBER function.  The reason for that is because I’d like my numbers to start at 1 and end at 4.  If we would just do “row_number % 4” (% is the modulo operator btw), then our last column would be zero instead of 4.

Okay, that’s part one of the method explained.  Over to the visualization part.

Setting The Tables

We’re going to create a table that shows the product category in a header with the product codes spread over four columns below the header.

So, first drag a table into the design canvas and give it an additional column.

To quickly link the table with the dataset, drag the product code field into the detail cell of the first column.

Now we’re going to set up the grouping.  We’ll also be making use of nested tables and because tables cannot be nested on the detail level of a tablix, we need to set up grouping even on that detail level.  So first, open up the Group Properties of the Details level by right-clicking it in the Row Groups pane:

Opening up the Detail-level Group Properties

Click the Add button to add a group and group on the product category:

Group the Detail level on product category

We want to display a header on the category level, so we’ll need to add another group on top of this one.  Right-click the Details row group once more and add a Parent Group:

Adding a Parent Group to the Details level

Group by product category once more and activate the Add group header checkbox:

Adding parent group on product category

Here’s what our table now looks like:

Some cleaning-up to be done

We don’t need that first column and we don’t need that top row, so delete both.  Now select the four textboxes in the upper row, right-click and select Merge Cells:

Merging the header cells to prevent text getting cut off or wrapped

This way the header line gets to use all available space.  Now put the product category field into the remaining merged cell and render the report:

Grouping set up as expected

If all went well you should be seeing the four categories.  That means the grouping part is implemented.

Of course, the detail level is not yet as needed because only the first code of each category is being shown and the three other columns are still empty.

So let’s address that now!

Nesting The Tables

Currently our four detail-level cells are just plain textboxes.  But textboxes don’t allow filtering and we need filtering because the first column should show only the product codes with DisplayColumn equal to 1, second column 2, and so on.

So what we’re going to do is nest another tablix inside each of those four cells.  This nested tablix should be one-celled, so just one column and one detail-level row.  That can be done through several ways.  I’ll show you two.

To set up the first detail-level cell, drag another Table inside that first textbox (the one that currently contains the product code).  That gives us this:

Nesting a table in a table

Now select the nested table by clicking one of its cells:

Select the nested table

It may be a bit tricky so if needed, temporarily increase the height of the detail-level row to enlarge the nested table.

Now remove two columns and remove the header row of the nested table.  Then right-click the grey square in the upper-left corner and open up the Tablix Properties:

image

These are the properties of the nested table.

Switch to the Filters page, click the Add button and set the filter to DisplayColumn = 1.

Filter on DisplayColumn

Click OK to close the properties.

Now put the product code field again in that first detail-level cell and render the report:

One column done, three remaining!

One column done, three remaining!

Let’s set up the second column.  This time, drag a List into the second detail-level cell:

image

A List is in fact a one-celled tablix with a Rectangle inside.  But we don’t need that rectangle here, we need a Textbox.  So select the rectangle by clicking once inside the cell.  You can tell that the rectangle is selected by looking at the Properties window, as shown above.

Now hit the Delete button on the keyboard.  In the Properties window, you should see Rectangle changing into Text Box:

Removing the Rectangle from the List leaves us with a Text Box

Once more we’ve got a one-celled tablix.  Set up filtering on DisplayColumn = 2, using the method as explained when setting up the first column (hint: upper-left grey square).

Put the product code field inside the second detail-level cell and render the report:

Second column set up

If all went well, your second column should now display some codes as well.

Two more to go!

Setting up the remaining two columns is actually really easy.  Select the nested tablix inside the second cell by clicking the small grey square in the upper-left corner.  Then hit CTRL+C, select the third detail-level cell and hit CTRL+V.  Also paste into the fourth detail-level cell.

Now change the filtering to 3 and 4 for column three and four and render the report:

All four columns display product codes

All four columns display product codes!

What now remains is some visual cleanup and adding page breaks on the category group.  I’m only going into details on the page break.

In the Row Groups of the main table, right-click the upper group and select Group Properties.  Then switch to the Page Breaks page and check the Between each instance of a group checkbox:

Adding a page break on the category group

Render the report to see the final result:

Report implemented: product codes in four columns grouped per category!

The first page displays the product codes of the first category, spread over four columns!  The other pages contain the other categories.

Requirements implemented!

Conclusion

Even though it takes a bit of work and some tricks, it is possible to create multi-column (aka newsletter-style) reports using basic SSRS components whilst still supporting all renderers.

I hope you enjoyed this article, have fun!

Valentino.

References

ROW_NUMBER() function

Share

Tags: , , , ,

On the forums I now and then encounter questions asking for the possibility to display a checkbox control on a Reporting Services report.  And the methods usually presented are either through images or by using a certain font one way or another.  However, as of SQL Server 2008 R2 there is actually a third and interesting alternative which comes really close to actually having a control!

In this article I’ll be presenting those three methods.

Screenshots are made using SSRS 2012, and so is the Checkboxes.rdl available for download on my Skydrive.

Checkbox Control, huh?

You may be wondering why people would want to put a checkbox on a report.  After all, reports are not capable of accepting input – except through parameters but that’s a different story – and isn’t that what a checkbox is all about?

Not entirely.  Reporting Services is not only used for data exploration.  Sometimes people use it to produce data-driven printouts, such as letters or even checklists.  In that perspective, having checkbox control functionality would indeed be useful.

A Silly Scenario

My yummy pasta sauceMy imagination is failing me a little today so I came up with this silly example: a recipe checklist.  And today we’ll be cooking some pasta sauce!

This is the query that produces the list of ingredients:

select 'Yummy Pasta Sauce' as Recipe, 'zucchini' as Ingredient, 1 as Quantity, 'piece' as Unit, 1 as Needed
union select 'Yummy Pasta Sauce', 'mushrooms', 500, 'g', 1
union select 'Yummy Pasta Sauce', 'minced meat', 1, 'kg', 1
union select 'Yummy Pasta Sauce', 'Brussels sprout', 1, 'kg', 0
union select 'Yummy Pasta Sauce', 'onion', 2, 'piece', 1
union select 'Yummy Pasta Sauce', 'tomato sauce', 1, 'L', 1
union select 'Yummy Pasta Sauce', 'potato', 1, 'piece', 0
union select 'Yummy Pasta Sauce', 'Brussels sprout', 1, 'kg', 0

I’m cheating a bit here, all the data is hardcoded in the query.  Normally you’d of course have a database that contains your recipes.

And I’ve also introduced some ingredients which I wouldn’t really want in my sauce, such as Brussels sprouts. Silly, but it gives me a good excuse to use the red checkbox.

Time to explore the possibilities.

Method 1: Images

I will not be going into full detail to explain the usage of images in SSRS reports.  If you need additional information on that, please first read my previous article that covers all possibilities of putting images on reports.

As I don’t have access to an inventory of stock images, I opened my favorite drawing program, Paint.NET, and created two images myself.  Then I embedded both images in the report:

Two images embedded in the report

I created a dataset using the query above.  Then I set up a Table with a group on the Recipe field whilst adding a header row to display the name of the recipe.

The row groups: Recipe > Details

The first column will show the ingredient details, using the following expression:

=Fields!Quantity.Value & " "
    & Fields!Unit.Value
    & " of " & Fields!Ingredient.Value

In the second column in the detail level cell, I did a drag & drop of one of my images.  That gives the following pop-up:

Dragging an image into the report brings up the Image Properties

Clicking the fx button brings up the brings up the Expression editor, in which I created the following expression:

=IIF(Fields!Needed.Value = 1, "checkbox_true", "checkbox_false")

Rendering the report shows us:

Using images to display checkboxes on a report

While easy to implement, a disadvantage to this method is that you need to get hold of the images.  For my example I created them myself to avoid any copyright issues, but I wouldn’t call them fit for professional purposes.  For a recipe list they serve just fine though! Smile

Mission one accomplished: the report shows some image-based textboxes!  On to mission two then!

Method 2: The Wingdings Font

I won’t be going into too much detail on this method either.  It’s already been explained by Krzysztof Kryczka in his article here.

In short, you just use the Wingdings font with the appropriate character to show an image.

In my table, I added an additional column to the right of the last one and entered the following expression:

=IIF(Fields!Needed.Value = 1, Chr(252), Chr(251))

As you can see in the character table on Krzysztof’s site, character 252 is a check mark while 251 is a cross.  Using the Chr() function, I can put the character on the report without typing the letter. (I actually doubt that they even match a letter so typing it would be difficult anyway.)

After setting some font-related properties, here’s what the rendered report looks like:

Using the Wingdings font to show a check mark

A small disadvantage to this method is that the Wingdings font needs to be installed on the SSRS server.

Mission two accomplished!  On to number three!

Method 3: The Indicator Control

Have you already used the Indicator control?  It’s new since SQL Server 2008 R2!

The list of Report Items in the Toolbox contains an Indicator

Let’s create a third column to the right of the last one and drag an Indicator into it.  That brings up following pop-up:

The SSRS Indicator comes in four different types with several different shapes!

In our context here the “3 Symbols (Uncircled)” seem like a valid option, so I’m selecting that.

The next step is to inform the indicator which field it should use.  In our case, it’s the Needed field.  It can be selected by using the dropdown next to (Unspecified).

Telling the Indicator which field is should use

Let’s have a look at the rendered report now:

Using the Indicator control to display check marks!

We’ve used the built-in Indicator control to show check marks, mission three accomplished!

(remark from audience:)

Erm, mister SSRS dude sir, how does the indicator know what image it should show?

(me thinking:)

Darn, they noticed I cheated a little.

You’re right, I have something to confess.  I made use of some handy defaults of the Indicator to skip some steps.  Ready to have some fun?  Add the following line to the query in the dataset:

union select  'Yummy Pasta Sauce', 'Let''s break it!', 1, 'attempt', 100

Without any other changes, render the report:

Oh no, I've broken the Indicator!

Oh my, look at that! The image and font-based check marks are still okay but the Indicator is broken!

Right, next test, in the additional query line added earlier, change the 100 to 2 and render the report:

Now the Indicator is totally broken!

That surely didn’t better the situation, it made it worse!  Don’t worry, the explanation is fairly easy.  Let’s have a look at the actual settings of the indicator.  Open the Indicator properties by right-clicking the indicator and selecting Indicator Properties.  Then select the Value and States page:

The default settings of the indicator

As you can see, the units are measured using percentages with “red” starting at zero while ending at 33.  That means that, based on all available values in the dataset, all values that fall in the first 33% will become red.

And that is exactly what the two tests above demonstrate.  If we take the last one, the value ranges from zero to two.  So zero is 0%, 1 is 50% and 2 is 100%.  That’s why all ones end up as the yellow exclamation mark!

If all you need is a green/red check mark like in our example, you need to ensure that you’ve only got two possible values and the “red” value needs to be lower than the “green” value.  That way you can make use of the default values of the indicator, making implementation a piece of cake.

Alternatively, if you’d like to customize the behavior of the indicator, that’s an option as well.  Have a look at what the Icon dropdown produces:

The Indicator ships with several different icons to be used

Any of those built-in icons can be selected.  Customizing the color is easy too, just use the Color dropdown.  And of course the numeric ranges can be changed as well.

If the percentage-based measurement doesn’t work out well in your situation, you can switch to Numeric:

The indicator can use either Percentage or Numeric measurement

If that hasn’t convinced you yet, then I don’t know what will…

Alright alright, one more:

Putting smileys on your SSRS report

Conclusion

In this article I’ve demonstrated three different methods of implementing checkboxes in SSRS reports.  Contrary to what is currently still believed, it is actually possible to add checkboxes to a report making use of just built-in functionality!

Remember: have fun!

Valentino.

References

MSDN: Indicators

Share

Tags: , ,

« Older entries

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