March 2011

You are currently browsing the monthly archive for March 2011.

Did you know that as of SQL Server Reporting Services 2008 R2 you can give the worksheets a customized name when exporting your report to Excel?  If you didn’t, or you did but never took the time to find out how you’d implement that, I’ll show you here and now!

For this example I’ll be starting off from the report created in my earlier post on Cascading Calculated Fields.

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

The Scenario

As you may recall, our report shows a list of all our company’s products.  When the report gets exported to Excel, each product category should get its own sheet.  So all products from the Audio category should be located in a sheet called “Audio”, all Games and Toys in a sheet called “Games and Toys”, and so on.

The Report

Starting Position

Let’s first have a quick look what the export to Excel currently looks like, without any modifications to the report.

Default export to Excel - all data in one sheet

All records are being exported to just one sheet.  And, by default, the name of the sheet is the name of the report.  (I made a copy of my existing report and called it NamingExcelSheets.rdl.)

In case you want to change the default name of the sheet, it’s possible.  On the report itself, there’s a property called InitialPageName.

Use the InitialPageName property on the report to change the default name of the Excel sheet

Fill in a value and here’s the result in Excel:

The Excel sheet with its default name changed to a very unique name

Adding The Category Group

To be able to get the different categories into different sheets, we need to add a group on Category to the tablix in the report.

With the tablix selected, right-click the Details line in the Row Groups pane and select Add Group > Parent Group.  Select ProductCategoryName as field to group by and activate the Add group header checkbox.

Add group on Product Category

Remove the group column that gets added automatically and move the header cells from the main header to the group header.  Delete the main header row so that you end up with something like this:

Tablix with group on Product Category added

Open up the Group Properties by double-clicking the new ProductCategoryName item in the Row Groups pane.  Select the Page Breaks page and activate the Between each instance of a group checkbox.  Doing this ensures that each group gets its own page in the report, and its own sheet in Excel.

Adding page breaks between the instances of a group

Let’s render the report and export to Excel to have a look at the effect of adding these page breaks.

Report exported to Excel with each group in a separate sheet

Indeed, every group has gotten its own worksheet.  However, they’ve also gotten the very original names such as Sheet1, Sheet2 and so on.

Customizing The Names Of The Sheets

On to the final part of the requirements: giving our own customized name to the generated Excel sheets.  This is actually really easy once you know how to do this.

First select the ProductCategoryName group in the Row Groups pane so that its properties are displayed in the Properties pane.  In the Properties pane, locate the Group > PageName property and specify the following expression:

=Fields!ProductCategoryName.Value

That’s it, that’s all you need to do!  Don’t believe me?  Here’s what the export to Excel now looks like:

Data exported to Excel, with customized sheetnames

Conclusion

As we’ve seen in this article, it really doesn’t take too much effort to implement a custom name for the worksheets when exporting a report to Excel.  Neat feature!

Have fun!

Valentino.

References

Understanding Pagination in Reporting Services (Report Builder 3.0 and SSRS)

Share

Tags: , ,

It’s March, Spring is trying to make a start here in rainy Belgium, the first flowers are in bloom and the birds are tweeting (yes, they have an account).

And the SQL Server User Group is ready to present you the second session of the year!

If you were present at last year’s SQL Server Day, then you may already have seen part of this session by Marc Mertens.  If you felt that the session was too short, here’s the answer!

Session Subject

SQL Server Locking: Take back control of SQL Server

Since the beginning of RDBMS systems locking control has been taken away from the programmer.  SQL Server has however many ways to let you control the locks placed and their duration.

This session discusses how SQL Server does locking and how you can control locking.  If you’ve always wanted to know all the details but never had time to look it up for yourself, join us for this session and let the info stream into your brain.  Can it be even easier??

Presenter

Marc Mertens is a SQL Server and .NET trainer/consultant for Global Knowledge. He has been working with SQL Server from version 4.x up to the current version.  His Microsoft Certifications are MSCA, MSCE, MCDBA, MCAD, MCSD and MCT.  He has been a trainer for every aspect of SQL Server, from building .NET applications using ADO.NET and LINQ to BI solutions.

Date and Agenda

Thursday, March 31, 2011.

1800: Registration

1830: Session Start

2100: Session End (unless Marc will try to break his record, established during his session last year on indexes)

Location

Global Knowledge Belgium

Industriepark Mechelen Noord II

Zandvoortstraat 1

2800 Mechelen

Registration

Click here to register for this free event

See you there!

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

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