Reporting Services

You are currently browsing articles tagged Reporting Services.

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: , ,

On the forums I regularly encounter questions like:

I have a report and want to show A, B, C but sometimes I want X, Y, Z and not A, B, C. How?

Or, in other words:

I have a parameter and based on the selection I want to return different fields in my dataset.  How can I do that?

My initial reaction would be “I don’t think you can do that”.  But then I thought it would be useful in certain situations and decided to try it out. And guess what?  You can do that!  Here’s how.

Just to make sure everyone is on track: this article is not about dynamicity in terms of rows returned, that would be filtered datasets and you’ll already find plenty of references on the internet.  This article is about a varying number of columns in the dataset, which is a little less straightforward.

The database used in the examples is AdventureWorks2008R2, available at Codeplex.  And the screenshots are taken from SQL Server 2008 R2 x64 SP1.

The Scenario

The report we’re going to build should show a list of products sold.  But the report is used by different user groups: those who just want to see the sales numbers and those who also care about stock levels!

So by default the report should show a list of items and number sold, but it should also be possible to render that same report whilst displaying the stock-related statistics.

And to make it even a bit more interesting, by default the data should be ordered according to product number but in “stock level mode” the ordering should put those with the lowest current stock first.

The Report

Let’s get started!  The first step in creating a report is often the writing of a SELECT statement.  In this scenario we’ll be needing two of them, both in the same dataset.

The Dataset

Our dataset is going to need a parameter to be able to decide what type of user is requesting the report. Let’s call that parameter WithStockData, and its type will be Boolean.

The layout of the dataset statement will be this:

if @WithStockData = 1
-- sales and stock data
else
-- regular sales data

A simple IF statement, taking the parameter into account.  The parameter is a boolean value so when it’s True, it equals to 1.

And here’s the full statement for the dataset:

if @WithStockData = 1
    -- sales and stock data
    select P.ProductNumber, P.Name
        , P.SafetyStockLevel, P.ReorderPoint
        , SUM(SOD.OrderQty) SoldQuantity, SUM(I.Quantity) InventoryQuantity
        , SUM(I.Quantity) - SUM(SOD.OrderQty) CurrentStock
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    inner join Production.ProductInventory I on I.ProductID = P.ProductID
    group by P.ProductNumber, P.Name, P.SafetyStockLevel, P.ReorderPoint
    order by CurrentStock asc
else
    -- regular sales data
    select P.ProductNumber, P.Name
        , SUM(SOD.OrderQty) SoldQuantity
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    group by P.ProductNumber, P.Name
    order by P.ProductNumber asc;

Some data as returned by the SELECT in the then part:

The sales and stock data: 7 fields in total

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

The regular sales data only consists of three columns

As you can clearly see, the first query returns seven fields while the second one contains only three.  You can also see that both results are ordered differently.

Now, let’s get the fun started!  Create a new report, set up a data source that points to the AdventureWorks2008R2 database and create a dataset with the query above:

Setting up the dataset

Power tip: to create the dataset, do not right-click on the Datasets node in the Report Data pane, but right-click on the Data Source and then select Add Dataset.  That saves you some work because the Data Source will be pre-populated.  All you need to do is paste the query in the Query field and give it a decent Name.

Click the OK button to close the Dataset Properties.

Now open the new dataset in the Report Data pane and count its fields:

Our dataset contains seven fields!

You should come to seven!  How nice, all of our fields are there.  This is not always the case, but I’ll handle that later.

The Parameters node in the Report Data pane should now contain a new parameter called @WithStockData:

The @WithStockData parameter got created automatically

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

The WithStockData parameter properties

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

Our Boolean parameter is set to false as default

With the dataset fully set up, let’s now move on to visualizing it.

Displaying Dynamic Columns

Put a Table on the report Design and set it up as follows:

The Table contains seven columns

All seven columns have been added.

One step remains: even though the values won’t always be present, the columns will not disappear automatically.  To take care of that, we’ll enter an expression on the Hidden property of each column.  Each column in a dataset has got the IsMissing property.  When its value is True, it means that the column is not present and should thus be hidden.

The expression looks like this:

=Fields!InventoryQuantity.IsMissing

Or visually:

Using the IsMissing property to hide a column dynamically

To set up the expression, click the grey area above the column title to select it and then locate the Hidden property in the Properties pane.  Do this for each dynamic column (don’t forget to change the column name in the expression). If you need to get white space removed, switch the Row/Column Groups to Advanced Mode and locate the  Hidden property of the appropriate (Static) item in the Column Groups.

And here’s what the rendered report looks like:

Report with all columns displayed

Once more, with the parameter set to False:

The report showing sales data only

The four stock-related columns are nicely hidden!  And the ordering is working as well because we’ve taken care of that in the dataset’s queries.

Easy, huh?  Well, yeah, but I’ve made sure that the process went as smoothly as possible.  It takes some knowledge on how SSRS actually works.  Let’s make this clear by adapting the example just a little.

Understanding The Dataset

Delete the current dataset and create a new one, using the following statement (ensure that you give it the same name as the original one):

if @WithStockData = 0
    -- regular sales data
    select P.ProductNumber, P.Name
        , SUM(SOD.OrderQty) SoldQuantity
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    group by P.ProductNumber, P.Name
    order by P.ProductNumber asc
else
    -- sales and stock data
    select P.ProductNumber, P.Name
        , P.SafetyStockLevel, P.ReorderPoint
        , SUM(SOD.OrderQty) SoldQuantity, SUM(I.Quantity) InventoryQuantity
        , SUM(I.Quantity) - SUM(SOD.OrderQty) CurrentStock
    from Production.Product P
    inner join Sales.SalesOrderDetail SOD on SOD.ProductID = P.ProductID
    inner join Production.ProductInventory I on I.ProductID = P.ProductID
    group by P.ProductNumber, P.Name, P.SafetyStockLevel, P.ReorderPoint
    order by CurrentStock asc;

The only difference with the previous version is that the IF condition is reversed and thus the two queries are swapped.

Now render the report.  What do you see?

An error occurred during local report processing.

The definition of the report ‘/DynamicDataset’ is invalid.

The Hidden expression for the text box ‘Textbox7’ refers to the field ‘InventoryQuantity’. Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope.

Letters in the names of fields must use the correct case.

Oh my, it’s broken!

Now take a good look at the available dataset fields:

The dataset only has three columns, oh my!

That’s right, only three!  Four of them have gone missing!  The reason for that is because SSRS uses the first SELECT query it encounters in the whole statement to determine the available fields.  It’s not able to automatically detect the different situations and create all the fields that can possibly be returned.

One way to ensure all fields are created is to put the query that returns all possible fields as first query, which is what I initially did.  But of course that’s not always an option.

Manually Adding Fields To A Dataset

Luckily it’s possible to manually add fields to the dataset.  You can do this by clicking the Add button in the Fields page of the Dataset Properties and then selecting Query Field.

Manually adding additional fields to the dataset

So, add the four missing fields:

The four additional fields added manually

If you now render the report, it should behave exactly the same as in the initial version!

What About Those Warnings?

If you’re someone who pays attention to the Error List pane, you may have noticed some warnings.  Two for each dynamic field to be exact.

Here’s an example of the rsMissingFieldInDataSet warning:

Warning    1    [rsMissingFieldInDataSet] The dataset ‘dsProductSales’ contains a definition for the Field ‘SafetyStockLevel’. This field is missing from the returned result set from the data source.    C:\test\SSRS\SSRS2008\DynamicDataset.rdl    0    0

And here’s the rsErrorReadingDataSetField warning:

Warning    2    [rsErrorReadingDataSetField] The dataset ‘dsProductSales’ contains a definition for the Field ‘SafetyStockLevel’. The data extension returned an error during reading the field. There is no data for the field at position 4.    C:\test\SSRS\SSRS2008\DynamicDataset.rdl    0    0

I’ve got a developer background, so I always try to remove all warnings.  So if you really want to get rid of those warnings too, even that’s possible.  But it will require some Custom Code writing.  I already covered that topic some years ago, when I wrote an article about Detecting Missing Fields.

Conclusion

With this article I believe to have demonstrated that datasets can be quite flexible, even though it doesn’t always seem so.

Have fun!

Valentino.

Share

Tags: , ,

When thinking about Reporting Services in combination with the word cascading, the first that jumps to mind is cascading parameters.  We all know that one parameter can have its list of values filtered by what’s selected in another parameter.

But what about calculated fields?  Are those cascading as well?  Can we refer to a calculated field in the definition of another calculated field?  Let’s find out!

For the example I’ll be using the ContosoDW sample data warehouse running on SQL Server 2008 R2, more precisely:

Microsoft SQL Server 2008 R2 (RTM) – 10.50.1600.1 (X64)   Apr  2 2010 15:48:46   Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit) on Windows NT 6.1 <X64> (Build 7600: )

The result can be downloaded from my Skydrive through this link.

The Example

Scenario

We’ve been asked to build a report that produces a product catalogue.  The report only has one requirement: as our company is well-known for its branding, each product category has got its own color and this color should be used as background color in the report.

Report

Let’s first get some data.  Here’s a fairly simple query that retrieves all products with their related category and subcategory from the ContosoDW database:

select DPC.ProductCategoryName, DPS.ProductSubcategoryName, DP.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;

After building a dataset in a new report I end up with this:

The Report Data pane before adding any calculated fields

Calculated Field Number One

Because this post is about calculated fields, we’re now going to apply a little dirty trick of hard-coding the category names and their corresponding color into a calculated field.  I do not recommend this for professional reports where the colors should be coming from the database so that your reports are not impacted when extra categories are added or when the marketing department decides to change their vision.

But for this example it’s perfect so let’s create a calculated field in the dataset.  That can be done by right-clicking the dataset and then selecting Add Calculated Field…

Right-click the dataset to add a calculated field

Give the field a clear name, such as ProductCategoryColor, and click the fx button to enter the following expression:

=Switch(
    Fields!ProductCategoryName.Value = "Audio", "#FFD800",
    Fields!ProductCategoryName.Value = "Cameras and camcorders ", "#FF0000",
    Fields!ProductCategoryName.Value = "Cell phones", "#00FF00",
    Fields!ProductCategoryName.Value = "Computers", "#0000FF",
    Fields!ProductCategoryName.Value = "Games and Toys", "#FF00FF",
    Fields!ProductCategoryName.Value = "Home Appliances", "#FFFF00",
    Fields!ProductCategoryName.Value = "Music, Movies and Audio Books", "#00FFFF",
    Fields!ProductCategoryName.Value = "TV and Video", "#ABCD12"
)

Funny side note: do you notice that trailing space in the “Cameras and Camcorders” category?  It’s intentional!  Apparently that record has got a trailing space stored in the ProductCategoryName field in DimProductCategory.

With the first calculated field created, add a table to your report to display the products.  Set the BackgroundColor property of the whole Details row to the newly-created calculated field.

So far so good, here’s what the rendered report currently looks like:

Rendered report with background color

Calculated Field Number Two

According to the business requirements we’re done creating the report.  However, it’s Friday early afternoon and we feel like having some fun.  And this post is about “cascading” calculated fields, so we need at least two of them.  Let’s create an Easter egg!

The “fun” requirement is the following: if the product’s name starts with an A then the text color for that record should be the same as the background color, but with the Blue component set to FF.  For example, if the background color is #00FF00 (green) then the text color should become #00FFFF (cyan).

Let’s create another calculated field in our dataset, called EasterEgg (don’t make it too hard for your colleagues to fix the weirdly behaving report).  Give it the following expression:

=IIF(Left(Fields!ProductName.Value, 1) = "A",
    Left(Fields!ProductCategoryColor.Value, 5) + "FF",
    "Black")

As you can see, we’re referring to the ProductCategoryColor field, the calculated field created earlier.

Now set the Color property of the Details row to this new calculated field and Preview the report.

Guess what?

Rendered report with cascading calculated field

It works!

Conclusion

If you’re in a situation where you’d like to add calculated fields to an existing dataset and one of those fields should use the value of another calculated field, you can do it!  Cascading calculated fields are working fine in Reporting Services.

Have fun!

Valentino.

Share

Tags: , ,

On the forums I now and then encounter questions regarding images on SSRS reports.  Instead of re-inventing the wheel each time, I decided to write an article about the subject.  So in this article I’ll be discussing and demonstrating several different ways of how images can be put on a report.

I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods explained here will work on any SSRS 2008.  Furthermore I’m using the AdventureWorks2008R2 database, available at CodePlex.

The resulting report, including image files, can be downloaded from my Skydrive.

The Scenario

The marketing department has requested a product catalogue.  This catalogue should contain all products produced by our two daughter companies: The Canyon Peak and Great Falls Soft.  The catalogue should be grouped on company, with the next company’s products starting on a new page.

Further requirements are:

  1. Each page needs an image in its header, with even pages displaying a different image than odd pages.
  2. Each company has a logo.  The logo should be displayed in the company’s header.
  3. Each product has a logo.  The logo should be displayed as part of the product details.

A design document containing the expected layout, including all image material, has been provided.

The Data

The following query provides us with all the data needed to produce the report:

SELECT 'The Canyon Peak' as Company, 'TheCanyonPeak_logo.png' CompanyLogo,
    'The Canyon Peak company specializes in all kinds of bikes, such as touring and road bikes.' CompanyDescription,
    P.Name as Product, PS.Name as Subcategory, PC.Name as Category,
    PP.LargePhoto, P.ListPrice, P.Weight, P.Size,
    P.SizeUnitMeasureCode, P.WeightUnitMeasureCode
FROM Production.Product AS P
    INNER JOIN Production.ProductSubcategory AS PS
        ON PS.ProductSubcategoryID = P.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS PC
        ON PC.ProductCategoryID = PS.ProductCategoryID
    LEFT OUTER JOIN Production.ProductProductPhoto PPP
        ON PPP.ProductID = P.ProductID
    LEFT OUTER JOIN Production.ProductPhoto PP
        ON PPP.ProductPhotoID = PP.ProductPhotoID
WHERE PC.Name = 'Bikes' --The Canyon Peak sells bikes
    and PP.ProductPhotoID > 1 --I don't want NO IMAGE AVAILABLE
UNION ALL
SELECT 'Great Falls Soft' as Company, 'GreatFallsSoft_logo.png' CompanyLogo,
    'Great Falls Soft uses only the softest tissues available for those sporting clothes.  And on top of that, they''re waterproof.' CompanyDescription,
    P.Name as Product, PS.Name as Subcategory, PC.Name as Category,
    PP.LargePhoto, P.ListPrice, P.Weight, P.Size,
    P.SizeUnitMeasureCode, P.WeightUnitMeasureCode
FROM Production.Product AS P
    INNER JOIN Production.ProductSubcategory AS PS
        ON PS.ProductSubcategoryID = P.ProductSubcategoryID
    INNER JOIN Production.ProductCategory AS PC
        ON PC.ProductCategoryID = PS.ProductCategoryID
    LEFT OUTER JOIN Production.ProductProductPhoto PPP
        ON PPP.ProductID = P.ProductID
    LEFT OUTER JOIN Production.ProductPhoto PP
        ON PPP.ProductPhotoID = PP.ProductPhotoID
WHERE PC.Name = 'Clothing' --Great Falls Soft sells clothes, waterstopping soft clothes
    and PP.ProductPhotoID > 1 --I don't want NO IMAGE AVAILABLE
ORDER BY Category asc, Subcategory asc, Product asc;

I’m not going into the details of this query.  Let’s just say that I’m manipulating data from the database in combination with some hardcoded data to get usable data for our example.  I’ve added some comments to make it clear what the query is doing.  If you have a look at its output, you’ll see that it produces a list of products with some additional fields.

Results of the query

Different Ways Of Adding Images

To get started, open up a SSRS solution, add a new report, add a data source connecting to your AdventureWorks 2008 R2 DB, and add a dataset using the above query.

Embedding Images In Your Report

The first way of adding images to a report that we’ll take a look at is by embedding them inside the report.  Looking at the scenario requirements described earlier, this is requirement 1.

Let’s add a header to the report.  In the BIDS menu, select Report > Add Page Header.

Adding a header to a report

If you don’t see the Report menu item, you probably have not selected your report.  Click your report in the Design view to select it.

From the Toolbox, drag the Image report item onto the header portion of the report.  Doing that will show a pop-up window, the Image Properties.  By default, the Select the image source combobox is set to Embedded.  Good, that’s what we need at this point.  What we now need to do is import an image into the report, using the Import button.

Clicking the Import button shows a common file Open dialog.  Our marketing department has given me two images for use in the header: Cloudy_banner.png and AnotherCloudy_banner.png.  Let’s select the first one.

Adding an image to a report by using the Import button on the Image Properties window

If you don’t see any images, have a look at that filter dropdown as highlighted in the screenshot above.  By default this is set to JPEG files.

Here’s the result in the Image Properties:

Image Properties with an image selected

On the Size page, select Clip instead of Fit proportional.  This is a setting that you’ll need to look at case per case.  For our header images, Clip is the most suitable option.

Image Properties: set Display to Clip

Close the Image Properties window and enlarge the image placeholder so that it occupies the whole header area:

Image added to report header

As you can see, we now have an image in the header.  But we haven’t fully implemented the requirement yet.  The even pages should display a different image than the uneven ones.

To be able to do that, we’ll first add the second banner image to the report.  In the Report Data pane, locate the Images node and open it up.  You’ll notice that the image that we inserted earlier can be found here.

The Images node in the Report Data pane shows all embedded images

Right-click the Images node and select Add Image.

Right-click Images node to add an embedded image to the report

That opens up the familiar file Open dialog which was used to add the first image.  So I’m now selecting the file called AnotherCloudy_banner.png, after changing the default filter to PNG.  After clicking OK, the image gets added under the Images node.

Second banner image added to the report

With the second image added, all that remains to be done is tell the header that it should pick different images depending on the page number.

Right-click the image in the header and select Image Properties.  On the General page, when you click the dropdown of the setting called Use this image, you’ll notice that there are two values now.  These are the same values as displayed in the Report Data pane.  And these are the values to be used in the expression that we’ll create to rotate the images depending on page number.

Click the fx button next to the dropdown and enter the following expression:

=IIF(Globals!PageNumber Mod 2 = 0, "Cloudy_banner", "AnotherCloudy_banner")

This is a fairly simple expression, using the Mod operator and the IIF() function.  When page number can be divided by two, which means it’s an even page number, Cloudy_banner is displayed.  Otherwise the other banner is displayed.

That’s it, the report header is finished.  When you have a look at the report in Preview, it should now show the second banner on the first page – this is an uneven page.

To conclude this chapter I’d like to mention that this method is usually not the preferred one.  A disadvantage here is that the images are stored inside the report RDL and thus cannot be modified without altering the report itself.

Here’s the evidence:

 <EmbeddedImages>
    <EmbeddedImage Name="Cloudy_banner">
      <MIMEType>image/png</MIMEType>
      <ImageData>iVBORw0KGgoAAAANSUhEUgAABVsAAABaCAIAAA...

To have a look at the RDL yourself, just right-click the report in the Solution Explorer and select View Code.

On to requirement number two!

Displaying Images Through A URL

At the moment, the report body is still empty, so drag a Table onto it.  Put the Table in the upper-left corner, remove one of the columns so that two remain, remove the Header row and make it a bit wider.

Now set the DataSetName property of the Tablix to the name of your dataset, in my case that’s dsProducts.

The report should display the data grouped on company, so right-click on the line that says Details in the Row Groups window part at the bottom of the Design View.  Select Add Group > Parent Group.

Right-click the Details line in Row Groups to add a new parent group

Group by Company and add a group header:

Tablix grouping

Remove the extra first column that just got generated:

Remove unwanted column

We’ve now got an empty tablix with two columns, a Details row and a Company header row.  In our dataset, one of the fields is called CompanyDescription.  Hover the mouse pointer above the textbox in the top-right, click the small icon that appears and choose the field from the dropdown that appears when you click the icon.

Click the small icon to get a list of fields

To add the company’s logo, drag an Image from the Toolbox pane into the textbox on the left of the company description.  Doing this opens up the by now familiar Image Properties dialog.

Give it a good name, such as CompanyLogo, and select External as image source.

Click the fx button next to the Use this image box and enter an expression such as this one:

="file:C:\vavr\test\" + Fields!CompanyLogo.Value

When using External as image source, the image expression should result in a valid URL, any valid URL.  In my example the files are located in a local folder called c:\vavr\test.  Keep in mind that, when you deploy the report to a server, the images should be located in that same folder, this time located on the server.

The Image Properties configured to display an External image

By default the image gets displayed using the Fit Proportional setting.  You can verify this in the Size page of the Image Properties.  We want the image to get fully displayed while maintaining the aspect ratio, so leave the setting as it is.  Close the image properties dialog.

Vertically enlarge the first row in our tablix to an acceptable size.  In my case the marketing department specified to use a height of 1.5 inches for the company logo.  With the image selected, locate the Size > Height property and set it to “1,5in”.  Note that the decimal separator used here depends on your local settings.

Now have a look at the report in Preview:

The report with company logos added

Note that I’ve removed the borders of all textboxes by setting their BorderStyle property to None.

With the logo images implemented we have fulfilled requirement two.  On to number three.

Retrieving Images From The Database

In this last requirement we’ll have a look at displaying images that are retrieved from the database, also known as data-bound images.

The retrieving part is actually already implemented.  In our dataset there’s a field called LargePhoto, that one contains a picture of the product.

Let’s add some product details and a picture in that remaining blank row.  To get full control over layout I want to make the detail part of the tablix a freestyle part.  First merge the two cells together by selecting both of them, then right-click and choose Merge Cells.

Merging two cells together in a tablix

Now select a Rectangle in the Toolbox pane and drop it into the merged area.  To add fields such as Subcategory and Product you can just select them from the Report Data pane and drop them inside the rectangle.  I’m also adding some additional labels and fields, as shown in the next screenshot.

The product details in Design view

As you can see I’ve modified the fonts a bit.  The rendered version:

The rendered product details

This is the expression used for displaying the weight:

=IIF(
    IsNothing(Fields!Weight.Value),
    "unknown",
    Fields!Weight.Value & " " & Fields!WeightUnitMeasureCode.Value
)

And here’s the expression for the size field:

=Fields!Size.Value & " " & Fields!SizeUnitMeasureCode.Value

For the layout of the price field I’ve just entered C in the Format property of the textbox.

With the textual product details completed, all that remains to be done is adding the product image.

From the Toolbox pane, drag an Image into the remaining whitespace in the rectangle, next to the product details.  (You did keep some space available, right?)

Again we get the familiar Image Properties popup.  Give it a good name, like ProductImage, and select the image source that we haven’t used yet, Database.  In the Use this field dropdown, select LargePhoto, and select image/gif as MIME type.

Note: the images are stored as GIF.  You can verify this by running a select on the Production.ProductPhoto table.  Looking at the LargePhotoFileName field we see that the extension is .gif.

There one textbox on the General page that’s still blank.  That one is called Tooltip.  Click the fx button next to it and enter following formula:

=Fields!Product.Value

Click sufficient OK buttons until the properties dialog is gone, then resize the image placeholder so that it occupies the remaining whitespace.

Here’s what the result looks like in preview:

The final report, with a tooltip on the product image

When hovering the mouse pointer above the product image, you’ll get a nice tooltip.

Conclusion

In this article I have illustrated the three possible methods of adding an image to your Reporting Services report.

Have fun!

Valentino.

References

BOL: Adding Images to a Report

Share

Tags: , , , ,

« Older entries § Newer entries »

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