Formatting questions can be quite broad.  Here are some examples to give you an idea:

How do I convert a datetime to a specific string format?

How do I display an amount with a currency symbol and thousands separator?

How can I display a percentage with 4 positions following the decimal separator?

The internet does have some information available on formatting.  However, this info is spread over several different pages and to be able to find it you need to be aware of a couple of things.  Which is why I decided to write an article that’s dedicated 100% to the topic of formatting stuff in SSRS reports so I can refer to it when applicable.

Introduction

Basically you’ve got two options when formatting.  The first one is using the Format property, which is one of the properties of the Textbox.

Or you can decide to not use the Format property at all and write a custom expression that uses some formatting functions to create the value string.  I’ll explain both in combination with all applicable data types.

You should also be aware that the formatting functionality is not specific for SSRS only.  If you’ve got some development experience you’ll probably recognize some syntax.  SSRS is actually just a layer on top of the .NET framework, as far as formatting is concerned.  So in many cases you’ll find interesting info online by searching in the .NET documentation.  I’ll be referring to several .NET-related pages in the text below.

The Query

As with any good report, before we can display anything we first need to write a SQL query.  My data source is the AdventureWorksDW2012 sample database, available at CodePlex.

And here’s the query:

SELECT dd.FullDateAlternateKey as OrderDate
      , f.SalesOrderNumber
      , f.SalesOrderLineNumber
      , f.OrderQuantity * 42000 as OrderQuantity
      , f.UnitPriceDiscountPct
      , f.SalesAmount
      , dc.CurrencyAlternateKey as CurrencyISO
FROM dbo.FactResellerSales f
inner join dbo.DimCurrency dc on dc.CurrencyKey = f.CurrencyKey
inner join dbo.DimDate dd on dd.DateKey = f.OrderDateKey
where UnitPriceDiscountPct > 0

Little sidenote: I’m multiplying OrderQuantity with 42000 to get larger numbers to be able to demonstrate certain things, such as the thousands separator.  Don’t do that in the production version!

Formatting Numbers

Regular Numbers

Let’s start with the easiest of them all: the regular numbers.  Without any formatting specified, numbers are rendered without any "make up", similar to how the Management Studio shows them in the query Results window.  For a report that’s usually not what we prefer.

I mentioned the Format property earlier.  Now the question of the day is: what can you put in there to format a number differently?  There are some standard format strings that can be used.  Here’s one: type an N in the Format property of a Textbox that displays the OrderQuantity.  When rendering the report you’ll get a number with a thousands separator and two decimal positions:

What if we don’t want any decimals?  Or what if we actually want to display four instead of two?  No problem, precision can be specified by just adding a trailing zero or four to the N specifier: N0 or N4.

The standard format strings are useful but not very flexible.  If you need more flexibility then there are some custom format strings to be used.  In that case the equivalent of N0 would be #,###.  Here are some more:

The double comma that you see in the #,#,,.# example is called the number scaling specifier.  As you can see, for each instance of that specifier the number gets divided by 1000.  This is a very useful method when you want to use minimal space such as in charts.

With that knowledge we can even take it one step further!  (Get a coffee if you haven’t already.)

Let’s first create a calculated field called NumberFormat using the following expression:

=Switch(
    Fields!SalesAmount.Value < 1000, "0.#",
    Fields!SalesAmount.Value < 1000000, "#,.#K",
    true, "#,,M")

Now that we’ve got that field we can use an expression like this:

=Format(Fields!SalesAmount.Value, Fields!NumberFormat.Value)

Note: the Format function in use here is explained a bit further down.

Here’s what that would look like in a table:

If you want to specify a different format for negative numbers, don’t worry, can be done as well.  Just enter two custom format strings separated with a semi-colon: #,###;(#,###).  And the same for zero values, add another semi-colon and format string at the end: #,###;(#,###);’-’:

Can you achieve the same without using the Format property?  Sure, as I mentioned in the intro you can also make use of some functions when displaying the value.  One of the functions that can be used is called FormatNumber:

Function FormatNumber(

ByVal Expression As Object,

Optional ByVal NumDigitsAfterDecimal As Integer = -1,

Optional ByVal IncludeLeadingDigit As TriState = TriState.UseDefault,

Optional ByVal UseParensForNegativeNumbers As TriState = TriState.UseDefault,

Optional ByVal GroupDigits As TriState = TriState.UseDefault

) As String

NumDigitsAfterDecimal is used to specify precision.

IncludeLeadingDigit has an effect on numbers smaller than one. When set to True 0.6 will render as 0.6 while that will become .6 when set to False.

UseParensForNegativeNumbers will render parentheses around negative values when set to True, otherwise you’ll get a leading dash: (5) or -5.

GroupDigits indicates if the thousands separator should be rendered.

To give that a try, replace the =Fields!OrderQuantity.Value with this expression:

=FormatNumber(Fields!OrderQuantity.Value, 3, True, True, True)

Here’s what that looks like when rendered:

There’s something you do need to take into consideration though.  Take a good look at the above screenshot and compare it with some of the other ones shown earlier.  Do you see the horizontal alignment difference? The FormatNumber function actually returns a string while the formatting options through the Format property keep the value numeric and thus aligned right.  That has an effect on the export to Excel, so you may want to experiment a little before delving into using custom expressions.

Another really useful function is called Format. Here’s what it looks like:

Public Shared Function Format( _

ByVal Expression As Object, _

Optional ByVal Style As String = "" _

) As String

It works very similar to the Format property: any valid format string in the Format property will also be a valid string to be passed into the Style parameter of the Format function.

Sidenote: if you’re looking for the MSDN page on a certain function but can’t find it, try this.  First locate the function in the Expression builder:

Then enter the function name followed by the description (or part of it) surrounded with double quotes into the search box of your favorite engine:

Reference page found!

Leading Zeroes

What if you want your number to display leading zeroes?  That can be achieved by using another standard numeric string formatter: D.  Try using D10 on the SalesOrderLineNumber:

There you go, leading zeroes!

Currency Amounts

With regular numbers covered, let’s switch to currency values.  The standard format string for currency amounts is C.  When specified, the currency amount will be rendered with a leading currency symbol, a space, thousands separator and two decimal positions.

The symbol that’s used depends on the report’s Language property unless that property is not set.  I’m not going into localization issues now so have a look at following page for more info if needed: Set the Locale for a Report or Text Box (Reporting Services)

Can you use a custom format string for currency amounts?  Sure, here’s what it looks like: ‘€ ‘#,#.  You actually hard-code the currency symbol into the format string.

BTW: those single quotes are optional, you’d get the same result if you leave them out.

Formatting currency values through expressions is possible as well.  The function specifically created for this is called FormatCurrency:

Function FormatCurrency(

ByVal Expression As Object,

Optional ByVal NumDigitsAfterDecimal As Integer = -1,

Optional ByVal IncludeLeadingDigit As TriState = TriState.UseDefault,

Optional ByVal UseParensForNegativeNumbers As TriState = TriState.UseDefault,

Optional ByVal GroupDigits As TriState = TriState.UseDefault

) As String

As you can see it is very similar to the FormatNumber function.  The only difference is that it will generate a number with a leading currency symbol.

So what if your data consists of mixed currencies?  In that case the FormatCurrency function is not very useful.  But it can be done!  Let’s demonstrate this using the SalesAmount and the CurrencyISO fields.  In our sample data the CurrencyISO field contains the three-letter ISO code of the currency.

The reference data, a list of currencies accompanied by their symbol, should be available in a database somewhere.  For demonstration purposes I’ll create a new dataset called Currencies using the query below.  So just imagine it comes from a table.

select 'USD' CurrencyISOCode, '$' Symbol
union all select 'EUR', '€'
union all select 'CAD', '$'
union all select 'GBP', '£'

We can now use the Lookup function to retrieve the symbol that belongs to the currency ISO code.  Here’s what that would look like:

=Lookup(Fields!CurrencyISO.Value, Fields!CurrencyISOCode.Value, Fields!Symbol.Value, "Currencies")
    & " " & Format(Fields!SalesAmount.Value, "#,0.00")

The expression above would give us following output:

Percentages

Let’s conclude this article by displaying some percentages.  We’ll create a new dataset for that:

select 0.33 Pct
union all select 1.5
union all select 0.0045
union all select 42

Once more we’ve got a standard format string that can be used.  Here’s what it looks like if we put this Pct field in a table.  The left column is not formatted while the right has got the standard P formatter in the Format property:

Without any precision specified the P formatter uses two positions following the decimal separator, just like we’ve seen earlier.  And we get a space followed by the percentage symbol added to the end of the number.

But what’s really important to note and differs from regular numbers is that the P formatter will actually multiply the value by 100!  That means percentages from 0 to 100 need to be stored in the 0 to 1 range.  Don’t forget to take that into account or your reports won’t make much sense.

Another option to get this percentage formatted is through using FormatPercent function:

public static string FormatPercent(

Object Expression,

int NumDigitsAfterDecimal,

TriState IncludeLeadingDigit,

TriState UseParensForNegativeNumbers,

TriState GroupDigits

)

As it’s very similar to other functions mentioned above I won’t go into much detail here.  Try the following expression:

=FormatPercent(Fields!Pct.Value, 1)

And here’s its result:

If you need more flexibility have a look at the % symbol in following format string: #,0.00%

Result:

If you need per mille then then you’ll need to use the per mille sign: #,0.00‰

Notice how the per mille symbol multiplies the value by 1000, don’t forget to take it into account!

Tip: if you don’t know how to type that symbol, simply copy it from my expression above.

Conclusion

In this article I’ve explained practically all different options to get your numbers formatted the way you’d like to.

Keep your eyes open for the next chapter in which I’ll handle even more formatting, such as for dates and times.

In the meantime: happy formatting!

Valentino.

The participating user groups this year are VISUG, AZUG, Belgian C++ user group, BIWUG, CLUG, Pro-Exchange, SCUG, SQLUG, ACCB.net/VBIB, WinTalks, DotNetHub, MADN, TechNine, IAMCT and PHP Benelux.

And guess what: I’m a speaker! My colleague Koen Verbeeck and myself have once more decided to share a presenting slot that day.  And we’ll be sharing some Data Visualization Tips & Tricks with you!

Koen will be talking about how to best visualize data: should I use a pie chart or would a bar chart provide a more interesting result, is it really a good idea to use red and green, that kind of stuff.

When he’s finished I’ll be taking over to show you some cool things you can do with SQL Server Reporting Services.  Yes, that’s lots of demos and practically no slides!  Cool?

The expected level is 300.  Don’t worry though, we know that the Community Day crowd consists mainly of developers so we’ll be taking that into account.

Ow, and I’m accepting topic suggestions! If you’ve been wondering about how to get something visualized but never got it to work, don’t hesitate to contact me by either posting a comment here or through email (found on my About page).  Provide a clear description of what you’d like to achieve, with a mock-up example if needed.  I’ll be selecting the most interesting proposals to fill some minutes with. Looking forward to some useful suggestions!

More details about Community Day:

• Utopolis Mechelen, Spuibeekstraat 5, 2800 Mechelen
• June 20th 2013, 0830 – 1900
• Twitter: @ComDayBe
• Five tracks!

What are you waiting for? Register now before all seats are taken!

See you there and in the meantime: have fun!

Valentino.

This month’s party is hosted by Wendy Pastrick and the topic is about change in the work life, possibly related to technology.  Well yeah, in my career of approximately fifteen years now I can write a couple of words on that subject.  Here’s the story of my career.

Introduction

For those who don’t know me yet I have something to confess: I used to be a software developer!  And I’m not ashamed to admit this.  Perhaps not that surprising, fifteen years ago you didn’t finish your studies to conclude: "And now I’m going to be a Business Intelligence consultant!".  Oh no, not at all.

During my studies, out of all possibilities, I already knew what I liked most: to develop software.  And I also quickly learned that my favorite language would not be COBOL, too outdated.  It wouldn’t be Visual Basic either, feels like you’re writing a book.  I prefer the shorter C/C++ syntax style.  I also knew that I didn’t want a consultancy position.  I considered a consultant to be an expert, and how could I be an expert after just having finished my studies??

Change 1.1

The first three years of my career I was an in-house C++ programmer.  Then I got involved with a software package that was written in VB6.  They were lacking manpower to implement new functionality and I was asked to help out.  So I did some programming in VB6 because there was no other option.

Then the first version of .NET arrived and guess what I did?  Part of the package’s functionality was the import of data out of flat files.  My job was to automate this process.  SSIS would have been perfect for the job but that didn’t exist yet.  I did use DTS for a couple of things, but not here.  The existing code consisted of about 15,000 lines of VB6 code which means redesigning was not an option anyway.  So I wrote a Windows service in C# that called the VB6 code!  Process automated!

Change 1.2

I’d also been using C# to write a couple of tools to help me in my day-to-day job.  Then the opportunity arose to be part of a new project which would be written completely in C#.  I was happy to be part of this!  Compared with VB6 and VC++6, the new Visual Studio for .NET was really a joy to work with!

Change 1.3

After a good year or so, upper management decided to go on the free tour: instead of .NET we were required to use Java for any new development projects.  Sure, why not give that a try then?!  One of the subprojects on which I worked was an activity monitoring tool for our server application.  I ended up developing some stored procedures to extract statistics out of logging tables.  At that time I wasn’t familiar with the ETL acronym yet, but that’s what I was doing.  Nowadays I would use SSIS for such a task.  There was also a reporting part to the project so I coded a website, in Java, to display those numbers, with drill through functionality and all that fancy stuff.  Nowadays I would definitely use SSRS!

Several months and lots of code later I realized that I didn’t enjoy Java (and all involved tools, libraries, …) as much as I enjoyed C#.  Sure, I could get stuff done.  But it wasn’t always as straightforward as I would have hoped, and online info was not as good – in my opinion.

After ten years of being an in-house developer at three different companies, I decided the time had come for a bigger change!  What I haven’t mentioned so far is that practically all projects in which I was involved used SQL Server as database engine.  And I always enjoyed playing around with that.

Change 2.0

In my new job I would no longer be an in-house employee and my main activity wouldn’t be writing code: I became a SQL Server Business Intelligence Consultant!

My employer, Ordina, gave me some time to cope with the change.  I was allowed to spend some weeks studying books and even going for a week of training (SSAS).

I was also encouraged to start blogging.  Initially it was a challenge to find topics to blog about.  But only initially, nowadays I’ve got too many topics and not enough time.  I discovered it was actually interesting to write about things I’d encountered for real.

Here’s such an example.  In one of my shorter project interventions, I ended up calling a web service through SQL CLR.  This was not an easy task.  Without my developer background I would probably never have succeeded here!  When I turned this into an article on my blog it became my first real hit!  The article, Calling a Web Service From SQL Server 2005, was posted Nov 11, 2008 and has gotten over 26,000 page views to date.  Even today it is still one of the more popular pages I’m hosting.

Besides teaching others through my blog I also discovered that forums can be a really interesting way to improve skills.  So in August 2008 I created a free account at Experts Exchange.  I also found out that helping out on forums combined with blogging is a real win-win situation: it helps me to find topics to write about and I can help people out by referring to an already-written article!

In February 2012 I was delighted to read that EE had found my forum activity worth an extra credit so I was given the MVE – Most Valuable Expert – award! Since then I have managed to reach the number one position in the SSRS zone with over one million points in total and it looks like I’ll be staying there for some time.

Lately I have started presenting.  So far I have found that to be a stressing yet rewarding experience.  My next presentation will be at the Community Day where both I and my colleague Koen Verbeeck will each talk for about half an hour about SSRS visualizations.  The target audience are mainly developers, that’s going to be interesting!  And it will be my first time on a stage in a cinema room, fearing the spotlights already!

Ow, and registration is open so see you there?  Come over to say hello if you are planning to attend the conference.  We are scheduled in the first slot so once that’s over I’ll be relieved of my stress!

Conclusion

Did a change in technology influence my career path?  Ow yeah, I do believe it did!  SQL Server was not the only tech that influenced it but I do consider it the most significant one.  And I’m glad it did too, keeps things challenging! 

Now I’m off to install Oracle.  So long, and thanks for all the fish!

PS: one of my statements in this post is a lie.

Valentino.

Well, not entirely true, read on for details.

Because it took me some time to realize what was going on I decided to write a quick post on how to make the BOL 2012 available locally. Basically you’ve got two options.  Let’s start with the first one.

Method 1: Help Library Manager

The first – newly-introduced in SQL Server 2012 – method uses something called Help Library Manager.

This is a generic Microsoft product documentation management system, not exclusively for SQL Server.

Here are the steps to follow to get the SQL Server 2012 docs installed locally.  This method assumes that you’ve already got SQL Server 2012 installed and that your PC is connected to the internet.

To start, click the Manage Help Settings link located in the Documentation & Community subgroup of your SQL Server 2012 start menu shortcut group.

This will open up the Help Library Manager mentioned earlier:

Click the Install content from online link.  The Help Library Manager starts by fetching the list of available books.  Once finished, locate the SQL Server 2012 content:

Click the Add link next to any book you’d like to install locally.  The link will change to Cancel as shown in following screenshot:

When you’ve selected all books you’re interested in, click the Update button.  The Library Manager will now start downloading all the content you’ve requested.

Now go and get a coffee.  When you’re back, you should be able to click the Finish button and the Books Online will be available locally:

Method 2: Download from Download Center

It turns out Microsoft took into account that not all computers are able to access the internet, which is a requirement for method 1.  Microsoft does still have something available for download through the Download Center.  However, when I came across the link I hadn’t realized that this was actually the BOL download.  The reason that I didn’t realize this was because the stand-alone BOL download is now called Product Documentation for Microsoft SQL Server 2012 for firewall and proxy restricted environments. I thought this was documentation regarding firewall and other network-related issues but this is actually the full Books Online download!

So if you’re interested in the old-skool BOL download, check this out: [Download Center] Product Documentation for Microsoft SQL Server 2012 for firewall and proxy restricted environments

Have fun!

Valentino.

References
Add or Remove Product Documentation for SQL Server

Collections, huh?

If you’ve got some programming experience then I’m sure this concept is familiar to you.  On the other hand, and in that case you’re new to all this, think of a collection as a, well… a gathering of objects.  Similar to a fruit basket which is a collection of fruit.

Let’s first have a look at the syntax, how can these collections be used?

I’m using SSRS 2012 SP1 but this concept is not new.  I believe all expression will even work in SSRS 2005.

Collection Syntax

Each item in a collection can be referenced through two different methods, both giving the same result.  Let’s continue the fruit basket example.  If SSRS had a collection called FruitCollection, you’d be able to reference the different fruits in that basket through following syntax:

FruitCollection!FruitName

For instance FruitCollection!Lemon will give you the Lemon object, assuming such an object exists in the collection.  That’s the first method.  The advantage of this method is that the expression builder will display a nice popup list of all the items in the collection once you’ve typed the exclamation mark.

The second method of referencing an item in the collection is by using the following Visual Basic syntax:

FruitCollection("Lemon")

This syntax requires you to pass the object name as a string.  As a result of that, you don’t get any syntax checks on the object name, so if you’d typed Limon you would only notice the problem when you actually run the report and get an error.  That’s why I prefer using the syntax with the exclamation mark so that’s the syntax I will be using for the examples further down in this article.

Each object in a collection also has a number of properties that can be consulted.  To continue the fruit basket example, the Lemon object could have a Color property.  To access that property you would use this expression:

FruitCollection!Lemon.Color

Alright, time to have a closer look at the different collections in SSRS!

The Parameters Collection

If you’ve got any experience implementing report parameters, I’m sure you already know this one.  The Parameters collection allows you to access your report parameters.

In case you’re dealing with a multiple-valued parameter, there are a couple of interesting properties you should be aware of, such as the Count and IsMultiValue property.  Careful though: the Count property returns the number of selected values, not the number of available values!

To reference just one of the selected values you can use the following syntax:

Parameters!YourParameter.Value(0)

This expression will return the Value property of the first item in the selected items list.  Note that it’s a zero-based index.

Another interesting function you should be aware of is the Join.  It allows you to join the different selected values together into one string.

The screenshot below shows you some expressions and their result.  The Category parameter has got three available values with two of them selected.

The Variables Collection

The Variables collection gives you access to the report variables.  To demonstrate this, I’ve set up the following variable:

And here’s the expression to access its value:

Variables!MyVariable.Value

The Globals and User Collections

The Globals and User collections contain some useful properties and are provided by default in every report.  These are the items available through the Built-in Fields category in the expression builder:

Through those collections you’ve got access to some really interesting properties, such as the RenderFormat.Name property which indicates what output format is being rendered.  I’ve actually mentioned this one before: Hide/Show Items Dependant On Export Format (SSRS)

Okay, time to get started with those hidden collections.  But first, why do I call them hidden?  Simple, because they are not shown in the expression builder Category list, as you can see from the previous screenshot.

The (Hidden) ReportItems Collection

A very commonly used collection is the one called ReportItems.  It allows you to reference the different textboxes on your report.  If you know that each “cell” in a Tablix is actually a Textbox, well, I’m sure you realize that this can be quite useful in certain situations.

Let’s have a look at a basic example.  Say I want to retrieve the list of selected Category values that’s shown in the screenshot in the Parameters Collection subchapter above.

Tip: always give your textbox a clear name when you’re going to reference it in an expression.  Never use the default Textbox666 name.  You can rename a textbox by changing its Name property.

In the following expression you can see that I’ve given the textbox another name, one that indicates what it contains:

ReportItems!txtFilterValues.Value

And here’s the result of that expression:

Tip: the above is only possible if the textboxes are not in a group in the tablix.  So the tablix on the left does not contain any grouping, it’s a simple table with five basic rows and two columns.

The (Hidden) DataSources and DataSets Collections

Finally we’ve come to the collections which made me write this article.  Did you know it’s possible to display your dataset query in your report?  That’s right, through the DataSets collection you can access your actual query string!  And through the DataSources collection you have access to some other properties, such as the source type and the shared data source being referenced.

My report has got the following data sources and datasets defined:

As indicated by the arrow in the data source icon, AdventureWorksDW2012 is referencing a shared data source while tempdb_embedded is an embedded source in the report.

Here are some examples of possible expressions and their result:

Through the DataSources collection you have access to a Type property on the source.  This property is only given a value in case of an embedded data source.

You also have access to a property called DataSourceReference.  It contains the name of the shared data source being referenced so only contains a value in case of a shared data source.

And through the DataSets collection you can access the CommandText property, which returns your actual query statement.  This can be particularly useful when you’re building your query dynamically through an expression, very interesting for troubleshooting!

There’s also a property called RewrittenCommandText.  When I initially saw that property I thought it would return the statement with any parameters replaced with their actual values.  Well, I was wrong, as the screenshot above indicates.  Apparently this property is used for model-based queries, as mentioned by Robert Bruckner.  I try to avoid report models…

Conclusion

This article introduced the concept of collections in Reporting Services and then went into explaining the use of each of them, including a couple of hidden collections.

Have fun!

Valentino.

References

Built-in Collections in Expressions (Report Builder and SSRS)

What DataSet Query Text Is Executed?

Here’s an example:

Then Jamie Thomson, aka SSIS Junkie, made me aware of a blog post he had posted earlier this week.

And in that post, he introduced an interesting alternative to what I (and a lot of other SQL Server bloggers) was doing!  Instead of using that [Blog] prefix it’s better to use a hashtag!  Why is that better?  Well, because searches and filters on hashtags work better than searches on just plain text. In fact, he proposes several hashtags.  Have a look at his post for the details: Bootstrapping SQL Server bloggers and blog readers with Twitter!

And here’s my earlier tweet reworked as per Jamie’s proposed standards:

However, the #blogged hashtag is not the one I will be using.  Several other bloggers commented on his post that they’re more in favor of the concatenation of #blogged and #sqlserver into #sqlblog.  I like that proposal because it’s closer to my earlier prefix and it uses less characters.  Given the nature of Twitter, if we can deliver the same message using less characters then that would be the preferred method, right? So that’s the one I will be using from now on!

I’ll even demonstrate it by updating this post once I’ve tweeted about it.  Yeah, a recursive blog post!

Have fun, and spread the word!

Valentino.

To get started, I’ll first imagine I’m being interviewed.  (Yeah, I like that.)

Q: You mentioned “without making any changes to the dataset query”.  So you’ll be filtering on the SSRS side then?

A: (Darn, I think she’s onto me.)  Well, yes.

Q: Would you recommend this method?

A: (Yep, she is.) Well, erm, no.  For performance reasons it’s much better to filter on the database server side.  You don’t waste any network bandwidth and database servers are specialized in filtering data.

Q: Then why are you writing an article about filtering on the report side?

A: Well, because sometimes you don’t have another option.  Not all data sources are as flexible as SQL Server, and SSRS supports many different providers.  In some occasions, the DBA that supports exotic data source X gives you a command, similar to a stored procedure call, that returns the data for your report.  And you don’t get any say on how that gets implemented, you’re just told to use that command.  Unfortunately that command returns items A-Z while the report has an item filter.  In those scenarios, it’s interesting if you’d be able to filter on the report side.

Q: Let’s say I would like to filter on the database server side, how would I do that?

A: Just use the WHERE clause in your query.

Alright, enough intro, time to start the article!

To build the report I’ll be using SQL Server 2012, more precisely:

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I’ve made the RDL available for download from my SkyDrive.

The Scenario

You’ve been asked to develop a report that shows a list of products, grouped by category.  The report needs to take a filter on product category into account.  The values on which you need to filter are stored in a configuration table.  You’ve been given a query which for some mystical reason can’t be modified.

Weird enough?  Okay, let’s get that implemented then!

The Report

Let’s first set up a simple report with a dataset and a table.  The table groups the products on category:

In Preview it would look like this:

The dataset used in the report above is called ProductList and uses the following query:

select DPC.EnglishProductCategoryName ProductCategoryName
    , DPS.EnglishProductSubcategoryName ProductSubcategoryName
    , DP.EnglishProductName ProductName
from dbo.DimProduct DP
inner join dbo.DimProductSubcategory DPS
    on DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey
inner join dbo.DimProductCategory DPC
    on DPC.ProductCategoryKey = DPS.ProductCategoryKey
order by DPC.EnglishProductCategoryName, DPS.EnglishProductSubcategoryName
    , DP.EnglishProductName;

Retrieving The Filter Configuration

So now we need to filter this report based on configuration stored in a table.  That means we’ll need an additional dataset, let’s use the following query and call it CategoryFilter:

select 'Category' FilterName, 'Components' FilterValue
union all select 'Category', 'Clothing'

(Sure, I’ve hardcoded the configuration data but for scenario’s sake, imagine it’s coming from a table.)

Here’s what the result looks like:

The dataset contains two fields: the name of the filter and a value on which to filter.  Each value on which we need to filter has its own record.  Having this FilterName field in the table means that it can be re-used for several different filters, if needed.  Your dataset would then use a WHERE FilterName = ‘YourFilter’ clause.

Now, one crucial question remains: how can we use this dataset to filter the data from the other dataset?

Depending on your requirements, there are several possibilities. If you’re sure none of your data regions require the data that will get filtered out, you can use the Filter options on the Dataset.  Alternatively you can use the Filter options on your data region.  In any case, those filters all use the same interface, so implementation-wise it’s all the same.

I’ll implement the filter on the tablix, this gives me the opportunity to show how to get to the properties (dataset is easier).

Okay, so how do we implement a filter on a tablix?

First select the Tablix and right-click the grey square in the top left corner:

Select Tablix Properties and switch to the Filters page.  Click the Add button to add a filter.  As you can see, filters support expressions.  That’s a good thing!

In the Expression combobox, select the field on which you want to filter the data.

The Operator that fits with our requirements should be one that supports multiple values.  In case you’re wondering why, take a good look at our CategoryFilter dataset mentioned earlier.  We want to filter on both Components and Clothing.  So the default equals sign is not really what we’re after, the IN operator will serve much better.

The only remaining empty textbox is Value.  How can we get the values from our filter dataset into this textbox?  In any case, we’ll need to use an expression so click the little fx button next to the Value textbox.

As you’re probably aware, a data region can only be linked to one dataset.  Which means we can’t say:

=Fields!FilterValue.Value

because FilterValue doesn’t exist in the dataset that’s linked to the tablix.

So how can we implement a filter based on data from another dataset?  Once again we’ve got several options.  Let’s look into them!

Option One: Report Parameter

If we’d set up a Report Parameter, we could reference that, right?  Let’s close the properties popup for now (click OK) and create a new report parameter:

Our parameter is called CategoryFilter, its type is Text and it should allow multiple values.  We’ll also set visibility to Internal because it doesn’t need to get exposed in any way.

The parameter’s default values are the values retrieved by the CategoryFilter dataset, so switch to the Default Values page and specify the dataset as shown in the screenshot:

With the parameter created, switch back to the Filters page on the Tablix Properties and enter the following expression:

=Parameters!CategoryFilter.Value

If you’ve created the expression by double-clicking the parameter in the Values box, don’t forget to remove the (0) or your data will get filtered on the first item only.

Click OK and render the report:

It’s working!  Accessories is no longer the first category that gets shown because it’s been filtered out.  The data is now filtered on the configuration as specified through the second dataset.

But… do we really need to set up a report parameter for this?  Well, no!  Let’s investigate option number two.

Option Two: The LookupSet Function

A couple of years ago I wrote an article to explain how you can retrieve data from another dataset through the new lookup functions.  Well, “new” at the time meant SQL Server 2008 R2.  We’re now on 2012 so the functions still exist!

The function that can help us out today is LookupSet.  Open up the Filter properties once again and replace the Value expression with the following:

=LookupSet("Category", Fields!FilterName.Value, Fields!FilterValue.Value, "CategoryFilter")

Close the popup window and preview the report.  If it runs fine, delete the report parameter and run the preview again.

Yes, it still works, nice!  So we don’t need to use a report parameter to filter our data, we can use a fairly simple expression using the LookupSet function!

Option Three: The Split Function

To finalize let’s investigate a variation on the requirement.  In this variation, the filter configuration doesn’t need to come from a table.  It can just be hardcoded in the report.  Can we do that, without using an additional dataset?  Yes we can!

In the Filter properties, change the Value expression to:

="Components,Clothing"

Now render the report:

Hmm, that didn’t work well.  Ow right, the IN operator expects an array of string values and not a comma-separated value string.  Let’s see, how can we get an array out of a character-separated value string?  Actually, I’ve covered this in one of my #tsql2sday posts.  We can use the Split function:

=Split("Components,Clothing", ",")

With that expression specified, let’s render the report a last time:

Woohoo, it works once again!

Conclusion

In this article I’ve demonstrated three different possibilities to implement filtering on multiple values on the report side.  It is not a recommended method but in some cases it can be very useful, especially when you don’t have another choice!

Have fun!

Valentino.

During that presentation, I demonstrated that you can use the Reporting Services web service in a custom application to get a list of your deployed objects from the server.  Using that functionality you’d be able to generate the batch script containing the RS commands which in their turn use the RSS scripts which I’ve demonstrated.

For those who missed the presentation but ended up here nevertheless, you can download the slides and demo code through my other follow-up post.

However, that’s not the only option!

In this post I’ll be using a technique which is one of my favorite methods of saving work.  Instead of typing a lot of code yourself, type just a bit of code to generate the code that you actually want.  Or, in other words and more concrete: today I’ll be using T-SQL to generate the RS commands for the batch file!

I will be querying some SSRS system tables, so take into account that the code may need changes for future versions of SQL Server, but I can confirm that it should work with both SQL Server 2008 and 2012.  SQL Server 2005 probably too but I don’t have that running anymore so can’t test it out.  Don’t hesitate to leave a comment if you can confirm that this will run on 2005.

The database on which you need to run these queries is called ReportServer, or ReportServer$YourInstance if you didn’t install it with the default instance name.

Figuring It All Out

One of the tougher parts of writing these queries was figuring out how the system tables can be linked with each other.  I studied some of the stored procedures and combined that knowledge with what I actually saw as content on my test system.  I’ll first share a couple of generic queries which may prove useful in case you just want to query the tables.  Then I’ll share the queries that will produce the RS statements.

Usually you’re only interested in the objects that belong to your project.  So each of the following queries has a filter to only show the content of my project folder, called Awesome Reports.

List Of Objects Dependent On Data Source

The following query shows a list of all objects that have a data source dependency.

--list of objects dependent on data sources
select
    case C.[Type]
        when 1 then 'Folder'
        when 2 then 'Report'
        when 3 then 'Resource'
        when 4 then 'Linked Report'
        when 5 then 'Data Source'
        when 6 then 'Report Model'
        when 7 then '???' --post comment if you know what 7 means
        when 8 then 'Shared Dataset'
        when 9 then 'Report Part'
    end ObjectType
    , C.Name
    , DS.Name DataSourceName
    , DSET_SRC.Name DatasetExternalDataSourceName
    , PARENT.Path
from dbo.Catalog C
inner join dbo.Catalog PARENT on PARENT.ItemID = C.ParentID
inner join dbo.DataSource DS on C.ItemID = DS.ItemID
left outer join dbo.DataSets DSET on DSET.LinkID = C.ItemID
left outer join dbo.DataSource DSET_SRC on DSET_SRC.ItemID = DSET.ItemID
where C.Type <> 5 --no data sources
    and C.Path like '/Awesome Reports/%'
order by c.Type, C.Name

So what does this return on my test system?

In the case of a shared dataset, the DataSetDataSource name is what the data source is called inside the shared dataset.  To get to the real name of the data source, we need to join with the dbo.DataSource table through the dbo.DataSets table while using the right key fields.

List Of Datasets With Linked Data Source

The query below can be interesting to generate a list of your datasets with their data source dependency, including their location.

--list of datasets with linked data source
select DSET.Name DatasetName
    , DSET_PARENT.Path DatasetLocation
    , DS.Name DataSourceName
    , DS_PARENT.Path DataSourceLocation
from dbo.DataSets DSET
inner join dbo.Catalog DSET_C on DSET_C.ItemID = DSET.LinkID
inner join dbo.Catalog DSET_PARENT on DSET_PARENT.ItemID = DSET_C.ParentID
inner join dbo.DataSource DS on DS.ItemID = DSET.ItemID
inner join dbo.Catalog DS_C on DS_C.ItemID = DS.Link
inner join dbo.Catalog DS_PARENT on DS_PARENT.ItemID = DS_C.ParentID
where DSET_C.Path like '/Awesome Reports/%'

On my system I get the following result:

Generating The RS Commands

Using the knowledge we got by examining above queries, we can now build some queries that generate the RS commands.  And here they are:

--generate DeployDataset commands
select '%RS% -i "%SCRIPTLOCATION%\DeployDataset.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v dataset="'
     + DSET_C.Name + '" -v datasetLocation="' + DSET_PARENT.Path
     + '" -v dataSourceName="' + DS.Name
     + '" -v dataSourceLocation="' + DS_PARENT.Path + '"'
from dbo.DataSets DSET
inner join dbo.Catalog DSET_C on DSET_C.ItemID = DSET.LinkID
inner join dbo.Catalog DSET_PARENT on DSET_PARENT.ItemID = DSET_C.ParentID
inner join dbo.DataSource DS on DS.ItemID = DSET.ItemID
inner join dbo.Catalog DS_C on DS_C.ItemID = DS.Link
inner join dbo.Catalog DS_PARENT on DS_PARENT.ItemID = DS_C.ParentID
where DSET_C.Path like '/Awesome Reports/%'

--generate DeployReport commands
select '%RS% -i "%SCRIPTLOCATION%\DeployReport.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v name="'
    + C.Name + '" -v folder="' + PARENT.Path + '"'
FROM dbo.Catalog AS C
inner join dbo.Catalog PARENT on PARENT.ItemID = C.ParentID
where C.Type = 2 --report
    and C.Path like '/Awesome Reports/%'

--generate LinkReportToDataSource commands
select '%RS% -i "%SCRIPTLOCATION%\LinkReportToDataSource.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v reportName="'
    + REP.Name + '" -v parent="' + REP_PARENT.Path + '" -v dataSource="'
    + DS.Name + '" -v dataSourceLocation="' + DS_PARENT.Path + '"'
from dbo.Catalog AS REP
inner join dbo.Catalog REP_PARENT on REP_PARENT.ItemID = REP.ParentID
inner join dbo.DataSource DS on REP.ItemID = DS.ItemID
inner join dbo.Catalog DS_C on DS_C.ItemID = DS.Link
inner join dbo.Catalog DS_PARENT on DS_PARENT.ItemID = DS_C.ParentID
where REP.Type = 2 --report
    and REP.Path like '/Awesome Reports/%'
order by REP.Name asc, DS.Name asc

--generate LinkReportToDataset commands
SELECT '%RS% -i "%SCRIPTLOCATION%\LinkReportToDataset.rss" -s %REPORTSERVER% -l %TIMEOUT% -e %ENDPOINT% -v reportName="'
    + REP.Name + '" -v reportLocation="' + REP_PARENT.Path + '" -v datasetName="'
    + DSET.Name + '" -v datasetLocation="' + DSET_PARENT.Path + '"'
from dbo.Catalog AS REP
inner join dbo.Catalog REP_PARENT on REP_PARENT.ItemID = REP.ParentID
inner join dbo.DataSets AS DSET ON REP.ItemID = DSET.ItemID
inner join dbo.Catalog DSET_C on DSET_C.ItemID = DSET.LinkID
inner join dbo.Catalog DSET_PARENT on DSET_PARENT.ItemID = DSET_C.ParentID
where REP.Path like '/Awesome Reports/%'

And here’s the output:

Conclusion

It may not be a recommended practice to use the Reporting Services system tables, but as long as you’re only reading them and you keep in mind that your code may break in a next version of SQL Server, they can surely be useful!

Have fun!

Valentino.

The screenshots in this post are created using Microsoft Word 2010.

So I used the Shapes dropdown from the Insert menu to add two Rounded Rectangles to my document.

From that same menu, I used the arrow (second item in the Lines items) and tried to connect the two rectangles, ending up with this:

Okay, I dragged the rectangles around a bit for demonstration purposes, but I’m sure you can all see that the arrow is not connected to the boxes.  I couldn’t find any way to get these items to connect with each other!

I almost gave up but then I thought:

Hang on, it can’t be that they’ve implemented this functionality to make it as useless as this, right??

So I decided to have a quick internet search and was glad to have found a solution.  It turned out that I skipped a step.  Apparently, to be able to connect shapes with each other, you first need to add a new Drawing Canvas to the document!  That can be done using that same Shapes menu:

After adding the Canvas, I repeated the process of adding rectangles and connecting them using an arrow, this time with more success!

Many thanks to Nicholas Hebb for writing this How to Create a Flowchart in Word 2007 and Word 2010.  Check out his article for more tips on flowcharting in Word!

Have fun!

Valentino.

Small disclaimer: you’re free to use the methods presented for your own projects, but I can’t allow that you use the material to deliver the presentation yourself.  Hope you understand.

Keep your eyes on this blog.  I’ll soon be describing a method which you can use to generate the batch files in order to fully automate the deploy process without using a custom .NET application!

Have fun!

Valentino.