Reporting Services 2008

You are currently browsing articles tagged Reporting Services 2008.

Changing Parameter Order In An SSRS Report

April 29, 2011 in Reporting Services | 5 comments

This week I was asked twice, by two different (luckily) colleagues, if I knew how to change the order of the parameters in a report in SQL Server Reporting Services. I still remember that the first time when I needed this after my upgrade to 2008, I also spent some time looking for it. Apparently it’s still hard to find so I’ve now decided to write a quick post about it. I promise you, once you know it you’ll say “how silly”.

The following screenshot should say it all:

Use the arrows in the Report Data pane to move parameters up or down

That’s right, those two small arrow buttons in the Report Data pane can be used to move the parameters up or down. They may be difficult to spot but once you’ve found them you’ll never forget. Why are they difficult to spot?

For several reasons:

they are greyed out when no parameter is selected
they may not be visible when your Report Data pane is too small

How silly huh?

Have fun!

Valentino.

References

How to: Change the Order of a Report Parameter (Reporting Services)

Tags: Reporting Services 2008, Reporting Services 2008 R2, SSRS

Put Some Images On Those SSRS Reports

July 7, 2010 in Reporting Services, SQLServerPedia Syndication | 5 comments

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.

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 by 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

Tags: data, Reporting Services, Reporting Services 2008, SSRS, Tutorial

Your First OLAP Report

January 24, 2010 in Analysis Services, Reporting Services, SQLServerPedia Syndication | 3 comments

Introduction

This article is aimed at report developers who are used to develop reports using relational databases and have gotten a first-time assignment to develop reports on OLAP cubes.

It demonstrates how to build a report using SQL Server Reporting Services 2008 with data coming from an OLAP cube running on SQL Server Analysis Services 2008.

The OLAP database used in the article is called “Adventure Works DW 2008”, available for download at CodePlex.

If you’re fairly new to Reporting Services (aka SSRS) and you find that this article is going a bit too fast, I’d like to point you to my other article which explains how to build a report that’s retrieving data using regular stored procedures.

OLAP <> OLTP

When people are talking about databases, what they are usually referring to are “regular” relational OLTP databases. OLTP stands for Online Transaction Processing. As the name implies, these types of databases are built to handle many simultaneous transactions (consisting of actions such as inserts, updates, deletes) in real-time. I’m sure you’re familiar with these types of database so I won’t go further into them.

OLAP (Online Analytical Processing) on the other hand is a totally different story. OLAP cubes are built to answer multi-dimensional analytical queries as fast as possible. For that purpose, what you can find in such a database are measures (these are the numbers) stored in cubes, and dimensions which allow filtering the measures. This filtering is often referred to as slicing and dicing. Furthermore, OLAP cubes contain pre-aggregated data, again to be able to answer queries as fast as possible.

Let’s make this clear with an example. Imagine the following request:

“Give me the sum of all sales of product X for period Y in country Z.”

Three dimensions can be recognized in that request: “product X” is found in the Product dimension, “period Y” in the Date dimension and “country Z” in the Geography dimension. (I’ve used the actual dimension names as they are called in the Adventure Works OLAP database.)

Each dimension consists of attributes and attribute hierarchies and it’s those attributes that you’re actually referring to when building an MDX query. MDX stands for Multidimensional Expressions and that is the language used to query an OLAP database, just like you use SQL to query a relational database.

Looking at our example, what we need is for the Product attribute in the Product dimension to be equal to X. An attribute in a dimension can also be written as [Dimension].[Attribute], thus we also want [Date].[Date] to be equal to Y and [Geography].[Country] equal to Z.

As for the measure part, that’s what “the sum of all sales” is referring to. When looking at the measures available in the Adventure Works cube, one of the measures that would fulfill the request is the Reseller Sales Amount in the Reseller Sales measure group. The Analysis Services engine searches the cube and retrieves the aggregated number for [Measures].[Reseller Sales Amount] available at the intersection of [Product].[Product] X, [Date].[Date] Y and [Geography].[Country] Z.

OLAP cubes are usually, although not necessarily, build on top of a data warehouse. In SQL Server, a data warehouse is still a relational database, unlike an OLAP cube, but the table structure is different from an OLTP database. A data warehouse contains tables that represent dimensions and other tables that contain the facts. The facts are the numbers, so the measures that were mentioned earlier. This is called a dimensional model. Dimensional modeling was invented by Ralph Kimball, one of the pioneers in data warehousing. For completeness I’d like to mention that another data warehousing approach was described by Bill Inmon. I’ll leave it up to you to do some research on both approaches and decide for yourself which one you prefer, possibly even a mix of both.

As far as the “Adventure Works DW 2008” OLAP database is concerned, it’s built on top of the AdventureWorksDW2008 dimensional database.

Okay, I believe this theoretical explanation was sufficient for now, let’s start with the report!

Your First Report

Business Requirements

You’ve gotten the assignment to create a report that shows the reseller sales numbers by region. The highest level to be shown is Country, with drilldown through State/Province to City.

Creating The Shared Data Source

Just like when building reports on OLTP databases, we’re not going anywhere without a Data Source. I’m going to create a Shared Data Source called OLAP_AdventureWorks.rds:

Shared Data Source connecting to Adventure Works OLAP Database

The Type that we need is Microsoft SQL Server Analysis Services, which is the SQL Server service that’s running the OLAP databases. Furthermore I’ve selected the “Adventure Works DW 2008” database.

Connection Properties specifying the Adventure Works DW 2008 OLAP database

There’s no need to type the database name yourself. After you’ve provided sufficient credentials in the Credentials page, you can just select it from the dropdown in the Connection Properties screen. This screen is opened by clicking that Edit button on the Shared Data Source Properties window.

Your First OLAP Dataset

I’ve created a new report called FirstOLAPReport.rdl. In that report I’ve specified that I’ll be using the Shared Data Source created earlier. This source is known as srcAdventureWorksOLAP in my report.

Next step is to create the dataset. I’m calling it dsResellerSalesByRegion. As this is our first OLAP report, we’re not going to write the MDX ourselves but we will use the Query Designer which is opened by clicking the button that has the words Query Designer printed on them, how difficult can that be?!

How to open the MDX Query Designer

The BIDS knows that it should open the MDX Query Designer because our data source is connecting to an Analysis Services server. All we need to do now is to drag the measures and dimension attributes that we require into the area marked with “Drag levels or measures here to add to the query.”.

Let’s start by dragging our measures into that area. We need two measures, both located in the Reseller Sales measure group. They are called Reseller Order Quantity and Reseller Sales Amount. Following screenshot shows the situation after the first measure has been added. The second measure was being dragged into it as well. When dragging items into the area, a vertical blue line appears to indicate where the item can be added.

MDX Query Designer: dragging a measure into the query

Next I’m going to drag the Geography hierarchy, located in the Geography dimension, into the design area.

MDX Query Designer: dragging a hierarchy into the query

Now we’ve got all the data we need for our report.

As you have noticed, the Query Designer automatically executes the query each time it gets modified when you’re dragging an item into the design area. If you don’t want this behaviour, it can be switched off by clicking the Auto Execute button in the toolbar (indicated by a red 1 in the screenshot below).

Query Designer toolbar

Another interesting button is the Design Mode button (indicated by a green 2). This one allows you to toggle between the graphical designer and the text editor. By clicking it you can see the actual MDX query that the designer has prepared for you.

As you can see, the query is nicely formatted using capitals for the keywords and so on. Well, no, actually it’s the worst editor around! No syntax coloring, no multi-line formatting, nothing. So if you are going to take a close look at the query, I recommend you to use the Management Studio. Connect to your Analysis Services server, locate your database and right-click it in the Object Explorer. Then choose New Query > MDX and paste the query into that new window. You’ll still need to manually break it down into different lines but at least you get syntax coloring. Furthermore, if you’re going to make manual modifications to it, you’ve got some command completion and error indicators as well.

Please take into account that once you’ve made manual changes to your query, you cannot switch back to the graphical designer. Well, you can, but you will lose all manual modifications. Don’t worry about doing it accidentally though, a nice pop-up will warn you:

Warning message when switching back to design mode.

Something else that you’ll also notice is that the results displayed in the Query Designer and those displayed in the Management Studio are not exactly the same. That’s because both environments interpret the results differently. Remember, you’re not retrieving two-dimensional row/column data like with a SQL query. You’re retrieving multi-dimensional data!

If you take a closer look at the query that we’ve produced above, it’s similar to this:

SELECT something ON COLUMNS,
    something_else ON ROWS
FROM [Adventure Works]

That query is selecting data on two axes: COLUMNS and ROWS. But in fact, MDX supports up to 128 axes. However, the client tools that we are using here are not able to visualize that kind of cellset (as the result set of an MDX query is also called).

Okay, enough about our dataset. We’ve got the data, let’s put it on the report!

Displaying The Result Set

As a reference, these are the fields available in our dataset:

Fields available in OLAP dataset

Without going into too much detail – there’s no difference compared to reporting off a relational database – I’ve set up a table with three grouping levels on the rows. I’ve also added some makeup like background colors and font modifications.

As shown in following screenshot, the highest-level group is Country, followed by State_Province and City to conclude, just as specified in the requirements mentioned at the start of this chapter.

Rendering the report in preview gives us something like this:

What is still missing at this point is decent formatting for those numbers! And here’s where we can take advantage of the fact that we’re retrieving data from an OLAP cube. A cube developer has the possibility to define the format for the measures in the cube itself. Doing that ensures that the same formatting is applied no matter what OLAP client tool is used. Any client that supports this way of formatting will show the numbers using the same format.

As you’ve seen in that last screenshot, there’s no formatting applied at all. Does this mean that there was no format defined in the cube? Let’s find out!

A Little Walk Into The Analysis Project

We are going to open up the Analysis Services project that contains the cube definition. If you don’t have any experience with SSAS, don’t worry! We will just have a look at a couple of properties and that’s it, plus I’ll explain each step as needed. In case you’ve forgotten where the sources are located, this is the default location: C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks Analysis Services Project\. I’m opening the project located under the \enterprise subfolder by double-clicking the Adventure Works.sln file.

Once the project is loaded into the BIDS, locate and open the Adventure Works.cube in the Solution Explorer. You can find it in the Cubes folder of the Adventure Works DW project.

By default it will open the cube Design showing the first page called Cube Structure. At the top-left, we’ve got the Measures pane. The measures are shown in measure groups. Open the group called Reseller Sales. Now locate the measure called Reseller Sales Amount and select it.

Cube in Design with Reseller Sales Amount selected

Now that we’ve selected one of the measures that we are retrieving in our report, have a look at the Properties window. In case it’s not open yet you can right-click the measure and select Properties. The property that we’re interested in is called FormatString.

Properties of the Reseller Sales Amount measure showing Currency as format string

The cube developer has specified that this measure should be shown as being a Currency.

Now that you’re in the cube, have a look at the properties for our other measure, the Reseller Order Quantity. This one is being formatted as #,#.

The FormattedValue Field Property

So why are we not seeing those formats in our report? Because by default they are not applied in an SSRS report! When dragging fields from the Report Data window onto the design area, what the BIDS is retrieving is the Value property of the field. However, there’s also a property called FormattedValue.

(You may want to make a copy of your report before applying the following changes.)

Now, change the six table cells that are showing the numbers (so including the ones showing the totals) to retrieve the FormattedValue property instead of the Value property. The expression for the totals of the Reseller Sales Amount looks like this:

=Sum(Fields!Reseller_Sales_Amount.FormattedValue)

Once you’ve done that, have a look at the Preview:

Report Preview showing no numbers after retrieving the FormattedValue property

That doesn’t look right, does it? We’ve lost our numbers!

Now hit the Refresh button:

This time we’ve got some numbers:

Report Preview showing formatted numbers, and errors!

But we’ve also got some errors for free! Looking at the Output window we get some extra details on the reason for the error. Here’s one of them:

[rsAggregateOfNonNumericData] The Value expression for the textrun ‘Reseller_Order_Quantity1.Paragraphs[0].TextRuns[0]’ uses a numeric aggregate function on data that is not numeric. Numeric aggregate functions (Sum, Avg, StDev, Var, StDevP, and VarP) can only aggregate numeric data.

In short, what it says is that our data is not numeric. And this poses an issue when it tries to apply the SUM() aggregate function. Right, as our data now contains formatting, it became a string instead of a number, and strings can’t be added together using SUM().

So that’s not a good way to apply the formatting, not in this case anyway. Luckily there’s another method to do that.

But first, undo those last changes and replace the FormattedValue with the Value property.

(Or switch back to the original report if you took a copy earlier.)

The Cell Properties

What exactly is our MDX query doing? I’m taking a closer look at it by taking it from the Dataset Properties window and pasting it into a MDX query window in the Management Studio:

SELECT
NON EMPTY { [Measures].[Reseller Sales Amount], [Measures].[Reseller Order Quantity] }
ON COLUMNS,
NON EMPTY { ([Geography].[Geography].[Postal Code].ALLMEMBERS ) }
DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME
ON ROWS
FROM [Adventure Works]
CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE,
FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS

Besides retrieving the requested measures and dimension attributes, it’s retrieving several Cell Properties, including FORMATTED_VALUE and FORMAT_STRING. I believe that the first one rings a bell by now. What we’re going to do is to retrieve the second one and apply it as Format property for our numeric table cells.

In the report’s Design, select one of the table cells containing a number. In the Properties window, one of the properties is called Format. Click to select it, then in the dropdown choose Expression…. For each of the six numeric cells, create an expression similar to the following:

=Fields!Reseller_Order_Quantity("FORMAT_STRING")

The example above tells the BIDS to retrieve the FORMAT_STRING cell property from the Reseller_Order_Quantity field.

Tip: you don’t need to open up the Expression builder for each of the six cells. You can just copy/paste the string from the Format field. Just ensure that you’re retrieving the format from the same field as the one that the cell is displaying.

Now let’s have a look at the Preview again:

Format is working for the quantity amounts but not for Currency!

Hmm, the quantities are fine now, but the currencies are not! So, let’s try out yet another method for those cells.

For the three cells containing a currency measure, remove the Format property – it’s not working anyway!

Next, change the expression that’s retrieving the Value property to something similar as this one:

=Format(Sum(Fields!Reseller_Sales_Amount.Value),
    Fields!Reseller_Sales_Amount("FORMAT_STRING"))

This expression applies the value of the FORMAT_STRING property using the Format() function. In this particular case it’s the expression used to produce the Reseller Sales Amount total.

Having modified all three currency cells, here’s another Preview look:

That certainly looks better doesn’t it?!

Okay, to conclude, let’s activate drilldown by setting the subgroup levels to a collapsed state by default.

I will not go into full detail on this. To start, make sure that the cells that are going to contain the +/- toggle have gotten a decent name, such as txtCountry for the cell that shows the Country name. Then edit the properties of the subgroups by setting Visibility to Hide. Also, activate the Display can be toggled by this report item checkbox and select the textbox showing the label one level higher. Shown below is how to configure the group on State_Province.

Let’s have another look at the report Preview:

By default all nodes were collapsed. I’ve expanded a couple of them just to show that it’s all working.

The InitialToggleState Property

Okay, I will not let you go just yet. To really conclude I’ll let you in on a little feature related to the drilldown. Open up the group properties for the State_Province group and set the initial visibility to Show (leave the “Display can be toggled by this report item” checked!). Then checkout Preview:

Wow, that’s weird, the country level is expanded and yet there’s a plus icon in front of the country’s name. Clicking it will collapse the states and change the icon to minus. If that isn’t mixed up then I don’t know what is!

Well, the solution to this problem is simple. Select the textbox showing the country name and locate the InitialToggleState property. By default this is set to False, which means collapsed or in other words, False shows the plus icon. Change it to True and now your initial state icon will be a minus!

Conclusion

With this article I believe to have shown you how to get started with reporting off an OLAP cube while throwing in a couple of tips in the process.

Have a look at another article that I wrote earlier, it explains an issue which you may run into when taking OLAP reporting a step further: SSRS and MDX: Detecting Missing Fields

Happy Reporting!

Valentino.

References

BOL 2008: The Basic MDX Query

BOL 2008: Using Cell Properties (MDX)

MDX: Retrieving Cell Properties by Greg Galloway

Tags: Analysis Services, data, MDX, Reporting Services 2008, SSAS, SSRS, Tutorial

SSMS Showing Incorrect Version Numbers In Object Explorer

January 10, 2010 in Management Studio | 1 comment

Ever since I upgraded to SQL Server 2008 Service Pack 1 I noticed that the Management Studio was reporting incorrect version numbers when connected to Integration or Reporting Services. This incorrect version number is located to the right of the server instance in the Object Explorer.

As usual, a picture says so much more than … :

Object Explorer showing wrong version numbers

As I have posted earlier, 10.0.2531 is the version number for SP1, while 10.0.1600 is the original RTM version number.

I never really spent time looking for an answer to this. It was obviously a bug but I could live with it and someone else would probably already have filed it as being a bug. So recently I came across a post by Phil Brammer that mentioned this issue. This post got a comment from Matt Masson, a developer on the SSIS team. Have a look at the comment but in short: the version numbers that are being shown in the Object Explorer are actually the version numbers of the service’s .exe file! And SSMS is now showing the wrong number because these files didn’t get an update in SP1.

After a little search I found the bug report on Microsoft Connect, reported on March 11, 2009, by Dan English. Its status is Fixed but it seems that it isn’t. At least, looking at the comments, CU5 (Cumulative Update) for SQL Server 2008 SP1 is still showing the problem. So I guess you could go over to the Connect page and click on that Yes button if you’re interested in seeing this fixed. After all, it could be quite misleading to novice DB guys and gals…

On this same subject, there’s another interesting post by Adam W. Saxton, a member of the Microsoft SQL Server Escalation Services Team. In this post he takes a closer look at the SQL Server 2008 Reporting Services version number after having installed CU2.

Conclusion: if you need to find out what version your server is running, do not rely on the version numbers that you see in the Object Explorer. As Adam explained, one way is to look at the version numbers of the files that were included in the upgrade. But that may a bit of an overkill. My favorite way, assuming that all components of the SQL Server installation have been upgraded to the same version, is to use the following query:

SELECT @@VERSION;

On my machine that comes back with the following result:

Microsoft SQL Server 2008 (SP1) – 10.0.2531.0 (Intel X86) Mar 29 2009 10:27:29 Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 3)

And remember, have fun!

Tags: bug, data, Integration Services 2008, Management Studio, Reporting Services 2008, SQL Server 2008, SSIS, SSMS, SSRS

Reporting On Data From Stored Procedures (part 2)

November 21, 2009 in Reporting Services, SQLServerPedia Syndication | 3 comments

Introduction

In a previous article I announced that I would write a sequel covering how to pass multiple-value parameters from a SQL Server Reporting Services report to a stored procedure. So that’s what I will be writing about in this article.

As usual, I will be using the AdventureWorks2008 sample database (running on SQL Server 2008 SP1), downloadable from CodePlex.

The examples in this article, Part 2, are building further on the result achieved when following the steps described in Part 1, so please refer to the previous article if needed.

Passing Multi-Value Parameter To Stored Procedure

As we’ve already seen in Part 1, parameters can be passed from a Reporting Services report to a stored procedure. The parameter that was used was just a simple, single-valued parameter. However, a report parameter can be defined as being multi-value. Let’s set one up!

Our report currently shows a list of employees who were hired after the selected hire date. One of the columns being shown is the department in which they’re active. We will modify the report so that it’s possible to filter the data on department – only the selected departments are to be retrieved from the database.

Creating A Multi-Value Report Parameter

The first step is to create a new report parameter, so right-click the Parameters node in the Report Data pane and select Add Parameter….

Report Data pane - Add Parameter

I’m calling my parameter Department, and I want it to be of type Integer. In case you’re wondering why Integer, it will become clear very soon. I have also checked the Allow multiple values checkbox:

Report Parameter Properties - setting up multi-value param

We want to make the parameter user-friendly so that the user sees a list of departments and can just select those that he needs. That means the parameter needs to be populated with that list of departments. To be able to do that, we first need to create a new dataset that retrieves the list of departments.

So for now, close the Report Parameter Properties screen and use the following query to create a dataset called dsDepartmentList:

select D.DepartmentID, D.Name as DepartmentName
from HumanResources.Department D
order by D.Name asc

This is what our query returns:

Result of DepartmentList query - a list of departments

Once the dataset is created, open up the properties of the Department Report Parameter created just before and select the Available Values page.

On that page, select the Get values from a query radio button, choose dsDepartmentList in the Dataset dropdown, select DepartmentID as Value field and DepartmentName as Label field. The Label field is what the user sees while the Value field is what Reporting Services will use as value. After all, we want to pass the IDs of the selected departments to our stored procedure, not the department names. And we want the user to see the department names, not their ID.

Report Parameter Properties - Available Values

By default, no values are selected. To make it a bit more user-friendly, let’s select all departments by default when the report first loads. This is done on the Default Values page.

Select the Get values from a query radio button, dsDepartmentList as Dataset and DepartmentID as Value field.

Report Parameter Properties - Default Values

That’s it, the multi-value report parameter is created! Of course, at this moment it doesn’t have any effect on the report’s content yet (switch to Preview if you don’t believe me and have a look). We’ll get to that next.

Discover What Is Being Passed To The Stored Procedure

To be able to handle the values passed into our stored procedure, let’s first find out what exactly our report is passing into it. We’ll do this by temporarily creating a new stored proc that will just accept the parameter values and return them.

This is what our test SP looks like:

CREATE PROCEDURE MultiValueParam
    @MyParam varchar(1000)
AS
BEGIN
    SELECT @MyParam as TheParameterReturned;
END

It accepts one parameter and returns it in a field called TheParameterReturned.

Set up a new dataset that calls this SP, called dsMultiValueParamTest. I’m sure you know how to do this by now

When creating the dataset, on the Parameters page, select the new parameter [@Department] that we created earlier:

Dataset Properties - Parameters

To see what the field contains, drag it from the Report Data pane onto the report canvas, above the table that was created in Part 1. Enlarge the textbox a bit and activate the Preview tab. Select a hire date (doesn’t matter which one) and click the View Report button. As we’ve set up the report to select all departments by default, we don’t need to select them manually. But of course if you want you can have a look in the Departments dropdown to check if they are actually selected. This is the result after clicking the View Report button:

Content Of Multi-Value Parameter

If you compare that list of numbers with the result that our dsDepartmentList query returns, you’ll see that these are the values from the DepartmentID field in exactly the same order as in the query’s result. And separated by commas. So in other words: it’s a comma-separated string of selected values.

Wrong Way To Implement The Parameter

Now that we know what exactly the multi-value parameter passes to a stored procedure, let’s modify our main procedure by adding the extra parameter to it.

Here’s the modified procedure:

ALTER PROCEDURE GetEmployeeData
    @HireDate date,
    @DepartmentList varchar(1000)
AS
BEGIN
    SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender,
        E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours,
        D.GroupName as DepartmentGroupName, D.Name as DepartmentName,
        P.FirstName, P.MiddleName, P.LastName
    FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory EDH
        ON EDH.BusinessEntityID = E.BusinessEntityID
        AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = EDH.DepartmentID
    INNER JOIN Person.Person P
        ON P.BusinessEntityID = E.BusinessEntityID
    WHERE E.HireDate > @HireDate
        AND D.DepartmentID IN (@DepartmentList);
END

Since our parameter is a comma-separated list of our values, I’ve used the IN operator to filter on only the selected values.

Next we need to add the extra parameter to the dsEmployeeData dataset. Double-click it in the Report Data pane to get its properties and click the Refresh Fields button to have it add the new parameter to the list. Then switch to the Parameters page and select the [@DepartmentList] parameter as Parameter Value for the newly-added @DepartmentList parameter.

Close the properties popup and run the report by activating the Preview tab.

Oh no, an error! More precisely this one (I will only mention the last line):

Conversion failed when converting the varchar value ‘12,1,16,14,10,9,11,4,7,8,5,13,6,3,15,2’ to data type smallint.

This error comes from our stored procedure. It complains that it cannot convert the list of values from a string to a smallint. Weird isn’t it? Well, maybe not. Let’s have a look at what’s going on.

The following query would work perfectly:

SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender,
    E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours,
    D.GroupName as DepartmentGroupName, D.Name as DepartmentName,
    P.FirstName, P.MiddleName, P.LastName
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory EDH
    ON EDH.BusinessEntityID = E.BusinessEntityID
    AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in
INNER JOIN HumanResources.Department D
    ON D.DepartmentID = EDH.DepartmentID
INNER JOIN Person.Person P
    ON P.BusinessEntityID = E.BusinessEntityID
WHERE D.DepartmentID IN (12,1,16,14,10,9,11,4,7,8,5,13,6,3,15,2);

But that is not what is being executed by our SP! In the query above, we are passing a list of numbers to the IN operator. But our SP accepts a varchar, a string. Sure, the report parameter passes a list of numbers, but they are stored in a string! An equivalent query for what our SP actually executes is the following:

SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender,
    E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours,
    D.GroupName as DepartmentGroupName, D.Name as DepartmentName,
    P.FirstName, P.MiddleName, P.LastName
FROM HumanResources.Employee E
INNER JOIN HumanResources.EmployeeDepartmentHistory EDH
    ON EDH.BusinessEntityID = E.BusinessEntityID
    AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in
INNER JOIN HumanResources.Department D
    ON D.DepartmentID = EDH.DepartmentID
INNER JOIN Person.Person P
    ON P.BusinessEntityID = E.BusinessEntityID
WHERE D.DepartmentID IN ('12,1,16,14,10,9,11,4,7,8,5,13,6,3,15,2');

When executing that in the Management Studio, it will throw this error:

Msg 245, Level 16, State 1, Line 1

Conversion failed when converting the varchar value ’12,1,16,14,10,9,11,4,7,8,5,13,6,3,15,2′ to data type smallint.

Doesn’t that look familiar?!

The reason for this error is the following. The DepartmentID field is of type smallint. Therefore SQL Server tries to convert the list of values to smallint. In the first SELECT statement, each value gets converted to smallint and all works fine. In the second SELECT, SQL Server sees just one value, a varchar(1000), and tries to convert that to a smallint. It fails because the value that the string contains is not convertible to smallint. If the string would contain only one value, it would actually work.

You can try it out by replacing the last line with this:

WHERE D.DepartmentID IN ('12');

Right Way To Implement The Parameter

We’ve seen that our first implementation of using the IN operator is not a good idea. So we need to find another way to get this stored procedure working.

Note: it would actually be possible to use the previous method in combination with dynamic SQL but I’m not going to apply that technique here. In case you are interested in that method, just construct a long string that contains the whole query as it is in the working SELECT statement above. For more info on dynamic SQL I’d like to point you to this excellent article by SQL Server MVP Erland Sommarskog: The Curse and Blessings of Dynamic SQL.

The IN operator can take a subquery. So now we need to find a way to “select” the values out of our comma-separated string of values. I am not going to re-invent the wheel and use a function that’s mentioned in another great article by Erland Sommarskog. The article is called Arrays and Lists in SQL Server 2005 but is also applicable to 2008 and mentions a function called iter$simple_intlist_to_tbl.

For this article’s readability purposes I’ve renamed the function to list_to_tbl. Here’s the code to create it:

-- from http://www.sommarskog.se/arrays-in-sql-2005.html
-- original name: iter$simple_intlist_to_tbl
CREATE FUNCTION list_to_tbl (@list nvarchar(MAX))
   RETURNS @tbl TABLE (number int NOT NULL) AS
BEGIN
   DECLARE @pos        int,
           @nextpos    int,
           @valuelen   int 

   SELECT @pos = 0, @nextpos = 1 

   WHILE @nextpos > 0
   BEGIN
      SELECT @nextpos = charindex(',', @list, @pos + 1)
      SELECT @valuelen = CASE WHEN @nextpos > 0
                              THEN @nextpos
                              ELSE len(@list) + 1
                         END - @pos - 1
      INSERT @tbl (number)
         VALUES (convert(int, substring(@list, @pos + 1, @valuelen)))
      SELECT @pos = @nextpos
   END
  RETURN
END

It takes a list of comma-delimited integers and returns a resultset containing integers, just what we need!

When we implement this in our main procedure, this is what it looks like:

ALTER PROCEDURE GetEmployeeData
    @HireDate date,
    @DepartmentList varchar(1000)
AS
BEGIN
    SELECT E.NationalIDNumber, E.JobTitle, E.BirthDate, E.MaritalStatus, E.Gender,
        E.HireDate, E.SalariedFlag, E.VacationHours, E.SickLeaveHours,
        D.GroupName as DepartmentGroupName, D.Name as DepartmentName,
        P.FirstName, P.MiddleName, P.LastName
    FROM HumanResources.Employee E
    INNER JOIN HumanResources.EmployeeDepartmentHistory EDH
        ON EDH.BusinessEntityID = E.BusinessEntityID
        AND EDH.EndDate IS NULL -- current active department does not have EndDate filled in
    INNER JOIN HumanResources.Department D
        ON D.DepartmentID = EDH.DepartmentID
    INNER JOIN Person.Person P
        ON P.BusinessEntityID = E.BusinessEntityID
    WHERE E.HireDate > @HireDate
        AND D.DepartmentID IN (select * from list_to_tbl(@DepartmentList));
END

And indeed, if we now run our report again, it works perfectly!

Report using multivalue parameter

Note: for another great reference on how to deal with a delimited list as Stored Proc parameter, I’d like to point you to the following article by colleague Expert and SQL Server MVP angelIII: http://www.experts-exchange.com/articles/Database/Miscellaneous/delimited-list-as-parameter-what-are-the-options.html

Displaying The Filter On The Report

Another best practice as far as report readability goes is that it should be clear on your report what data has been filtered. As the multi-value parameter is on focus here, I’ll demonstrate how you can show the selected values on your report.

In fact, it’s not really the parameter’s values that we are interested in now (those are DepartmentIDs, remember?). No, it’s the labels. And here’s how to get to them. Add a new textbox above the main report table. Make it the same width as the table and right-click it to add an Expression. Enter the following expression:

=Join(Parameters!DepartmentList.Label, ", ")

It uses the Join function to join all members of the Label collection together into one string, using comma and space as the value separator. This is what it looks like on the report:

In case you would like to see the selected departments under each other instead of in a long string, that’s also quite easy to achieve. The expression is based on Visual Basic, and in Visual Basic there’s a constant called vbCrLf – Visual Basic carriage-return line-feed. Adapt the expression to the following and the values will be shown in a list instead of a long string:

=Join(Parameters!DepartmentList.Label, vbCrLf)

Let’s have another look at the effect:

Showing selected values under each other

Conclusion

With this article I believe I’ve demonstrated that it is possible to pass multi-value parameters from a SQL Server Reporting Services report to a stored procedure, while applying some best practices such as giving the users a nice list of values to select from.

Happy reporting, thank you for reading my article, and should you feel like it: post a comment!

Valentino.

References

BOL 2008: the IN operator

The Curse and Blessings of Dynamic SQL by Erland Sommarskog, SQL Server MVP

Arrays and Lists in SQL Server 2005 by Erland Sommarskog, SQL Server MVP

BOL 2008: Expression Examples (Reporting Services)

delimited list as parameter, what are the options? by angelIII, SQL Server MVP

Tags: data, Reporting Services 2008, SSRS, Tutorial

« Older entries