Reporting Services 2008

You are currently browsing articles tagged Reporting Services 2008.

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/Database/Miscellaneous/A_1536-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:

Showing selected=

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=

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

Share

Tags: , , ,

Introduction

The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The dataset can use a SELECT query, which is the most common way of retrieving data and one that you’re probably already familiar with.  But it can also use a Stored Procedure (aka stored proc or SP).

The purpose of this article is to demonstrate how data can be retrieved from a SQL Server database through Stored Procedures defined in that same database, and then displayed in a SQL Server 2008 Reporting Services report.

I’ll be using the AdventureWorks2008 sample database available for download at CodePlex.

What Are Stored Procedures?

There are actually different types of stored procedure in the context of SQL Server.  The type that I am using in this article is called a “Transact-SQL Stored Procedure”.  According to the Books Online, this type of stored procedure is:

“A saved collection of Transact-SQL statements that can take and return user-supplied parameters.”

If you have experience using a regular programming language such as Visual Basic or C#, I’m sure this sounds familiar.  You can think of a stored procedure as a method that takes any number of parameters, depending on its definition, and that possibly returns a result, again depending on its definition.  Instead of being stored in a compiled .exe or .dll, it is stored in the database.

In this article I will be using some stored procedures that return a dataset as result.  How stored procedures are written is not the purpose of this article.  For that I’d like to refer you to this Books Online page: Implementing Stored Procedures.

Reasons For Using A Stored Procedure

There are several reasons why it is more interesting to use datasets based on stored procedures as opposed to SELECT statements.

Performance: stored procedures perform faster than SELECT statements.  The reason for this is because they are compiled when they’re created and their execution plan gets stored by SQL Server so that it can be reused for each procedure call.

Maintenance: when a database needs to undergo some changes to its schema, the changes can be handled in the stored procedures.  This makes it transparent for the reports that are using these stored procedures.  The reports will keep functioning as expected without any modifications to them.

Reuse: imagine a situation where several reports are reporting on the same set of data.  If you wouldn’t use stored procedures, you may have to repeat a possibly complex query in each report.  With stored procedures you just need to define the query in the stored procedure and then call it in each report’s dataset.

Security: in environments where DBAs are responsible for the SQL Server databases, the report developers will possibly not get sufficient rights to retrieve data from the tables directly.  One of the ways to prevent this is to give them access to a bunch of stored procedures and views instead.

Simple Procedure Call

Okay, enough theory, time to show you how it’s done!

Setting Up The Stored Procedure

Our first procedure queries the AdventureWorks database to return a list of employees with their corresponding department.  Here’s the code for the SP:

CREATE PROCEDURE GetEmployeeData
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;
END

Creating The Report

The next step is to create a new Report in a Report Server project using Business Intelligence Development Studio 2008 (aka BIDS).  To add a report my preferred way is to right-click the Reports folder in the Solution Explorer (this is assuming that you’ve already created a Report Server project) and then select Add > New Item… :
 
Creating a new Reporting Services report
 
Make sure that the Report template is selected.  I’m calling my report StoredProcDataset.
 
Add New Item - Report
 

Creating A Shared Data Source

Now that we’ve got an empty report we still need to get some data.  We’ll set up a Shared Data Source first.  Shared Data Sources are convenient when you’re planning to create several reports on the same database.  It removes the connection string from the report itself and puts it in a separate Shared Data Source object, making it easy to switch between databases.  (Imagine putting your reports into the production environment – instead of needing to modify each report to connect to the production database, you just need to modify the Shared Data Source).
 
In the Solution Explorer, right-click the Shared Data Sources folder and select Add New Data Source.
 
Solution Explorer Shared Data Sources - Add New Data Source
 
 
Shared Data Source Properties
 
I’m calling the Shared Data Source AdventureWorks2008.  A Data Source can connect to several different sources, such as Oracle or an Analysis Services cube.  In fact, it can connect to any source through OLE DB, as long as there’s an OLE DB provider that has all the expected functionality implemented.  For a good list of drivers, have a look at the Books Online: Data Sources Supported by Reporting Services.
 
The one that we’re interested in now is called Microsoft SQL Server, which is by default selected in the Type dropdown.
 

Creating The Data Source

Next we’ll add a Data Source to the report based on the Shared Data Source.
 
Report Data pane - New Data Source
 
In the Report Data pane, select New > Data Source….
 
If you don’t see the Report Data pane, you can open it by going to the View menu item and selecting Report Data right down at the bottom of the menu window.  Or hit CTRL + ALT + D.  That’s an interesting shortcut to memorize because the Report Data pane has a tendency to disappear, especially if you’ve closed and re-opened the BIDS.
 
Data Source Properties
 
I’ve called my Data Source srcAdventureWorks2008 and I’ve told it to use the existing Shared Data Source.
 

Creating The Dataset

So, now we’re ready to query our stored procedure.  In the Report Data pane, right-click the newly created Data Source and select Add Dataset….
 
Right-click Data Source to Add Dataset

 

Dataset Properties

I’m calling the Dataset dsEmployeeData.  The Data Source edit box is already prefilled with the right data source because we’ve right-clicked the data source to which we want to add a dataset, how easy can it be?

To query a stored procedure from a dataset is really very straightforward.  All you need to do now is select the Stored Procedure radio button.  Doing that replaces the bottom part of the window.  Instead of an edit box that expects a query, we now get a simple dropdown where we need to select our SP.

Once you’ve done that, select the Fields page on the left.

Dataset Properties - Fields

As you can see, the list of fields has been pre-filled.  The BIDS queries the stored procedure’s metadata so we do not need to manually specify the fields returned by our SP, saving us quite some time!

Once you click OK to close the Dataset Properties window, you’ll see that the Report Data pane gets populated with our list of fields.

Report Data pane - list of fields in dataset

Show Me The Data

To prove that everything is working as expected, let’s set up a quick report using a Table.  From the Toolbox pane, drag a Table object onto the report canvas:

Report with a Table

Next, from the Report Data pane, drag some fields into the cells in the Data part of the Table.  As we’re dealing with employee data, it would be interesting to see their names.  I’ve also added their job and department.

Resize the columns a bit so that everything will fit nicely, and color the Header cells different from the Data cells to easily distinguish them.

Table with some fields added and basic layout

Now we’re ready to run the report, so click the Report’s Preview tab.

Table showing some employee data

So, that was our first procedure.  Wasn’t too complicated, was it?  So, ready for another one?

Parameterized Procedure Call

This time we’ll make it a little more complicated.  As you know, stored procedures can take parameters.  And SSRS knows how to pass them into a stored proc.  So let’s do that!

Setting Up Stored Proc Version 2.0

The stored procedure shown below is based on our previous one.  Except now it takes one parameter: @HireDate.  The SP will only return employees that have been hired after given HireDate.

ALTER PROCEDURE GetEmployeeData
    @HireDate date
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;
END

 

As the HireDate field in the HumanResources.Employee table is of type date, I chose to make the parameter that same data type.

Please note that the above script uses ALTER PROCEDURE instead of CREATE PROCEDURE.  This will only work if you’ve already created the previous procedure.  If not, just replace the word ALTER with CREATE.

Modify Report To Pass Parameter To Stored Procedure

The next step is to modify our existing Dataset so that it passes a date into our stored procedure.

Open up the Dataset’s properties by double-clicking it in the Report Data pane.  Then select the Parameters page:

Dataset Properties - Parameters empty

As you can see, it is still empty.

Now switch to the Query page and click the Refresh Fields… button:

Dataset Properties - Query page - Refresh Fields button

Then switch back to the Parameters page:

Dataset Properties - Parameters page populated

The parameter has been automatically added when we clicked the Refresh Fields… button!

The BIDS again used the stored proc’s metadata to do this.  No need for manual intervention.  For now, leave the Parameter Value box as it is, empty.  Click OK to close the properties window.

Clicking OK caused another action in our report!  If you open up the Parameters folder in the Report Data pane, you’ll see that it has gotten an item as well:

Report Data pane - Parameters

You can double-click the @HireDate parameter to get its properties:

Report Parameter Properties

The BIDS has created a report parameter and linked it to the parameter in our Dataset.  It automatically chooses the correct data type for the report parameter, Date/Time in this case.  (Unlike in T-SQL, there are no separate Date and Time data types in SSRS 2008.)

To confirm that the report parameter is linked to the parameter in the Dataset, close the Report Parameter Properties window and open up the Dataset Properties again, selecting the Parameters page:

Dataset Properties - Parameters page - Parameter Value filled in

The Parameter Value for our parameter has now gotten a value, more precisely it’s referring to the Report Parameter called @HireDate.  If you don’t believe that it is actually the report parameter (I admit, it looks similar to the parameter in the Dataset, except for the square brackets surrounding the report parameter’s name.), click the fx button to see the formula:

=Parameters!HireDate.Value
 

Indeed, it is referring to the parameter on the report.

Test The Changes

Now that the parameter has been set up, it’s time to test our report again.

As the report is now filtering on the HireDate, it would be interesting to actually show this field in the table.  Add an extra column and drag this field into the data cell.

Our newly added field’s type is datetime, but we’re only interested in the date part.  So we’ll set up a format code.  Select the HireDate data cell and locate the Format property in the Properties pane.  Enter a small d into it:

Formatting a datetime to only show the date

This instructs the BIDS to show the field using the “short date pattern”.  A list of possible codes is available on MSDN: Standard Date and Time Format Strings.  As you notice, SSRS is using the regular formatting strings as they are known in .NET.

Note also that the format used to display the data depends on the report’s Language property.  I’ll leave it at its default: en-US.

Right, time to have a look at the report.  Select the Preview tab.  You can now see the Hire Date parameter which is expecting a value.  You can either type a value or select it from the calendar which is shown when you click the button on the right of the textbox:

Calendar control for a datetime report parameter

To be sure that you’re using the correct format for the date, I suggest to select a date using the control.  Then you can always modify it to whatever day you’d like to select.

For our test I know that some employees started before 2000 and others later, so I’ll select a date somewhere in the year 2000.

Report with datetime filter

And indeed, our report shows only employees that started after our selected HireDate.

Conclusion

With this article I hope to have shown you that it’s fairly straightforward to report on data coming from stored procedures.

In case the above hasn’t fulfilled your appetite on this subject yet, watch out for part 2.  In that sequel I will build further on the example used in this article to show you how you can use multi-value parameters to filter your report’s data.

In the meantime: happy reporting, and thank you for reading my article!

References

BOL 2008 – How to: Create an Embedded or Shared Data Source

BOL 2008 – How to: Create a Dataset (Reporting Services)

BOL 2008 – How to: Refresh Fields for a Dataset

BOL 2008 – Implementing Stored Procedures

BOL 2008 – Data Sources Supported by Reporting Services

Share

Tags: , , ,

A while ago I wrote an article called Chart Optimization Tips.  This article explained how to optimize a Column Chart.  Today I have returned to show you some Pie Chart implementation techniques.

As usual, I will be using the AdventureWorks2008 database, available at CodePlex.  The chart itself will be implemented using SQL Server 2008 Reporting Services.

Retrieving The Data

The dataset in our report uses the following query:

select SWD.*, SWA.City, SWA.StateProvinceName,
    SWA.PostalCode, SWA.CountryRegionName, SWA.AddressType
from Sales.vStoreWithDemographics SWD
inner join Sales.vStoreWithAddresses SWA
    on SWA.BusinessEntityID = SWD.BusinessEntityID

This query illustrates a bad coding practice: never use “SELECT *”.  Ideally you should only retrieve the columns that you need for the report.  That will optimize performance when generating the report.  But that is not the goal of this article so I’ll leave the query as it is.

A Basic Pie Chart

To get started with our Pie Chart I have selected the third icon in the list of Shape charts.  This adds a regular 3D pie chart to the report.

Select Chart Type window

To set up the chart I dragged AnnualSales from the Report Data pane into the “Drop data fields here” area and StateProvinceName into the “Drop category fields here”.

In case you don’t see the Report Data view (it has a tendency to disappear now and then), you can open it through the main menu: View > Report Data.

Report Data Pane

This is what our report looks like in Preview:

Basic Pie Chart

Wow, we’ve still got some work to do, this looks like a kids color book!  You wouldn’t say that this chart is showing the annual sales, would you?  There aren’t even any numbers on it!  Let’s get started on improving this.

Sorting The Numbers

A good implementation practice is to sort the slices from large to small.  If the slices are not sorted, it’s difficult to tell which state is performing better than another.  Just take a look at the previous image and compare the following two slices:

  • the pie shown in grey at 12:00
  • the pie shown in yellow at 03:00

Which one is the larger of the two?  Indeed, “I don’t know” is the right answer.

To implement the sorting you need to think about what you want to achieve.  What is it that we want to sort?  The states.  And these are shown as categories on the chart, so we should take a look at its properties.  As shown in the following screenshot, right-clicking on the [StateProvinceName] button gives a pop-up menu.  Select Category Group Properties.

Context menu for Category Group

Then we need to think about how we want the states to get sorted.  For that we should look at what is being shown as data of the chart.  In our case that is the sum of the AnnualSales field.

In the Category Group Properties, select the page called Sorting.  Clicking the Add button will add a line in the sorting options list.  Use the following expression for the “Sort by” field:

=Sum(Fields!AnnualSales.Value)

As we want to sort the largest values first, select “Z to A” for the Order dropdown.

Category Group Properties

Right, time to have another look at our report in Preview.

Sorted Pie Chart

So, we went from a colorful mess to an ordered colorful mess.  Chaos has been reduced a bit, but this is still one difficult-to-read report.

On to the next improvement!

Limiting The Pies

As you have noticed, a pie chart is not suitable to show that many categories.  We need to find a way to reduce the slices.  One way to do that is by adding a filter.  Another way is to add the smallest slices together into one slice.  This can be interesting in cases where we want to use all the data but we’re only interested in the larger slices.  Luckily, this can be done using standard pie chart properties.

Click on the pie itself, this will select the Chart Series.  One way to tell if you’ve selected the correct part of the chart is by looking at the Properties pane.  Its selection should show something like “AnnualSales Chart Series”, where AnnualSales is the name of the chart series.  Another way to tell is by the small white selector bulbs: they should be surrounding the pie.

Now, among the properties of the Chart Series you will find a property group called CustomAttributes.  Open this one by clicking the plus icon in front of it.  Change the CollectedStyle property to SingleSlice.  This tells the chart that we want to group the smallest slices into one slice.

Other interesting properties here are CollectedThreshold and CollectedThresholdUsePercent.  I’ve put CollectedThreshold to 2 and CollectedThresholdUsePercent to True (which is its default).  This means that any slice smaller than 2 percent of the pie will be added into the “collected slice”.

More useful properties are CollectedLabel, that’s the text that is shown on the slice itself, and CollectedLegendText, the text shown in the legend.

Chart Series - collected slice properties

The CollectedStyle property has another option besides the one I’ve shown, called CollectedPie.  Choosing that will generate a second pie next to the main one to represent all the small slices.  See the following screenshot for what it looks like.  In some cases this may be an interesting option but not in our example here.

Pie Chart showing a CollectedPie

The collected pie can also show labels by setting the CollectedChartShowLabels property to True, and the categories shown on the collected pie can be shown in the pie’s legend by setting CollectedChartShowLegend to True.

If you’d like the collected slice to jump out, there’s a property called CollectedSliceExploded.  Setting it to True will produce something like the following:

Exploded Collected Slice

As you have noticed, the previous screenshots have started to show text on the slices.  This can be easily activated by right-clicking the pie and selecting the Show Data Labels menu item.

Context menu on pie chart - Show Data Labels

And the next screenshot shows what our chart currently looks like.

Pie chart with collected slice

The small slices have been replaced by a really large one, and the text on the large slice is our customized version.  The other slices are showing some rather large numbers, so we still have some work to do.

Displaying Percentages

Let’s customize the label shown on the slices.  As the numbers are really large, I recommend to divide them by 1,000.  As long as it’s clearly mentioned on the report, it will make everything more readable.

Furthermore I’ll show you how to use built-in chart keywords (only available to ToolTips, custom legend text, and data point label properties), such as #PERCENT.

Right-click on one of the data labels and select Series Label Properties.

Context menu of data labels - Series Label Properties

Click the expression (fx) button on the General page and enter the following expression:

=FormatCurrency(Sum(Fields!AnnualSales.Value) / 1000, 0) & " (#PERCENT{P1})"

The first part divides the sum of AnnualSales by 1,000 and then applies the FormatCurrency function to the result.  The second parameter for FormatCurrency tells the function that we don’t want any decimals.  The result of this function call is concatenated with the second part using Visual Basic string concatenation (&).

The second part looks like a regular string but it contains a built-in keyword: #PERCENT.  This will show the percentage that the slice represents.  Furthermore, there’s a custom string formatter appended: P1.  By default the percentage would show 2 decimals.  This way it will only use one digit for the decimal fraction.

See here for a list of all built-in keywords and this page for more information on the available formatting options.

And following screenshot shows what our chart now looks like.

Pie chart showing percentages on slices

I’ve also given it a clear title, decreased the Data Label font size to 8 and moved the legend down.

To move the legend: right-click it, select Legend Properties and play with the radio buttons for the Legend Position.

Legend Properties

So, we’ve now got a fairly readable chart.  It’s not perfect, some labels are overlapping, but it’s doable.  However, we won’t rest here.  On to the next tip.

Rotating The Pie

Some people may ask you, “Why on earth does the first slice (the blue one representing 10.6% in our example) start at this weird angle at 4 o’clock?  Why can’t it start at 12:00?”.

Again we’re lucky because this can be controlled using a standard property.  Among the Chart Series CustomAttributes property group there are still some properties which haven’t been mentioned earlier.  One of them is called PieStartAngle.  By default it is set to zero.  Funny enough, zero stands for 30°.  Try it out and enter 30 for the property value.  Did you see the effect?  Indeed, nothing happens!  Now enter 90.  Did you see the chart rotate, even in Design mode?  Switch to Preview to get a better view of what the impact is.  As you can see, setting it to 90 will cause the first slice to start at 06:00.  To make it start at 12:00, we thus need to set the property to 270 degrees.

Pie Chart with customized rotation angle

Labels Outside Pie Chart

Other people may tell you, “But I don’t want all these labels on the pie itself, I want them next to it.”.

We’re still lucky because again this can be achieved using standard properties.  Still in the Chart Series CustomAttributes, there’s a property named PieLabelStyle.  Its default value is Inside.  Switching it to Outside will render the labels outside the pie, with lines attaching them to their respective slice.

Other interesting properties for the outside labeling are 3DLabelLineSize and MinimumRelativePieSize.

3DLabelLineSize defines the amount of space used for drawing the line between the label and its corresponding slice and is a percentage of its default size.  Values range from 30 to 200.  I’ve put it to 30 to get as much space as possible for the pie itself and the labels.

MinimumRelativePieSize represents a percentage of the chart area size and defines the minimum acceptable pie size.  Values range from 10 to 70.  I’ve put this one to 70 to maximize the size of the pie.

Chart Series properties with properties for outside labeling highlighted

With these modifications we’ve actually gotten some extra space for the labels.  Let’s take advantage of that and add extra information in the labels.  Change the Data Label expression to the following:

="#LEGENDTEXT" & vbcrlf &
FormatCurrency(Sum(Fields!AnnualSales.Value) / 1000, 0) & " (#PERCENT{P1})"

Our expression uses another built-in keyword: #LEGENDTEXT.  This will add the legend text to the label itself, which means the legend becomes obsolete.  So I’ve removed it.

And this is what our chart now looks like:

Pie chart showing labels on the outside

With Halloween coming up I thought it would be nice to create a spidery chart :-)

One More Custom Attribute

I’ve already mentioned several CustomAttribute properties of the Chart Series and I’d like to mention one more.  This property is called PieDrawingStyle and it will only appear in the list of properties when 3D is not enabled.  After disabling 3D I could set it to either SoftEdge or Concave.  I also noticed that labels outside of a pie chart will only have lines attached to them when rendered in 3D, so I’ve switched back to Inside for the PieLabelStyle property.

This is what SoftEdge looks like.  I think it’s rather nice.

Pie chart using SoftEdge drawing style

Custom Coloring

To conclude, there may be some people who tell you, “I don’t like those colours, and I don’t like any of the predefined sets.  I want to specify custom colours.”.

So again we’re lucky because even that is supported by default.

To get started with our color customization, select the chart object.  To know if you’ve made the correct selection, the Property pane should show “Chart” as non-bold part of the dropdown.  Alternatively you can just use that dropdown to select the Chart.  As Chart is a main object on the report, it is shown in the list (whereas parts of a Chart, such as Chart Series and Chart Area, are not shown in that list).

With the Chart selected, locate the Palette property.  By default it is set to BrightPastel.  In case you’re happy with one of the predefined palettes you can just select it here.  But we go for Custom, located at the bottom.

Next, locate the CustomPaletteColors property.  Selecting the property will show a button with an ellipsis as button text.  Click this button to get to the ReportColorExpression Collection Editor (what a name for a popup window!).  This window allows you to specify a list of colors.  I’ve specified the following 10 colors:

ReportColorExpression Collection Editor

And finally this is what our report looks like.  To stay in the theme, I’ve specified some colors which are suitable for Halloween-time charting.

Pie chart with custom colors - Halloween-style!

Coloring Consistency Using Dynamic Colors

And now to really conclude this article I’d like to mention one additional tip related to chart coloring.  In some occasions it may be interesting to have coloring consistency between different charting periods.  With that I mean that California would always show in grey, no matter whether it came first or not, Washington as brown, and so on.  This is currently not the case.  With the current implementation it’s the first pie that gets the grey color, the second pie is brown, and so on.

The best way to achieve that is to store the colors in the database and then fetch them in the same dataset that is used to retrieve the chart data.  The AdventureWorks database hasn’t got any color codes stored so I’ll just illustrate what I mean using a little cheat.

In order to get our dynamic coloring working, we will override the colors from the palette.  This is how it’s done.  Right-click on the pie and select Series Properties.  Select the Fill page and click the Expression (fx) button to define the color.  In the case where you’re selecting the color code as one of the database fields, your expression would look similar to this (assuming that colors are stored using their 6-digit hexadecimal representation with 000000 being black and FFFFFF being white):

="#" & Fields!ColourCode.Value

To imitate dynamic coloring I’ve used the following expression:

=Switch
(
    Fields!StateProvinceName.Value = "California", "Blue",
    Fields!StateProvinceName.Value = "Washington", "Red",
    Fields!StateProvinceName.Value = "Florida", "Green",
    True , "#888888"
)

The expression gives three states their own color and all the others will be colored a kind of grey.

This is what it looks like:

Pie Chart using dynamic colors

So, I hope you’ve enjoyed reading this article.  Feel free to post any comments should you wish to do so, and… happy charting!

References

SQL Server 2008 Books Online: Pie Charts

How to: Collect Small Slices on a Pie Chart

Formatting Data Points on a Chart

How to: Define Colors on a Chart Using a Palette

Share

Tags: , , , ,

In this short article I will be talking about two functions in the SQL Server Reporting Services (SSRS) function stack.  Those functions are IIF() and Switch().  And I’ll be showing you how easy it is to add an Else part to the Switch function.

Two commonly-used functions in Reporting Services are the IIF() and the Switch().  These are two functions of the Program Flow type, or Decision Functions as they are called on this MSDN page.

In case you’re wondering why it’s so difficult to find a function reference for the built-in functions of SSRS, it’s because these are actually Visual Basic functions and Microsoft refers to those for any detailed explanation.  Click this link for the IIF() function in the Visual Basic Language Reference, and this one for the Switch().

Anyone who’s done some programming most likely already knows the if <expression> then <some_code> else <other_code> statement.  If <expression> evaluates to true then <some_code> gets executed, else <other_code>  gets executed.

The IIF() works in the same way.  According to its description it

Returns one of two objects, depending on the evaluation of an expression.

This is its definition:

Public Function IIf( _
ByVal Expression As Boolean, _
ByVal TruePart As Object, _
ByVal FalsePart As Object _
) As Object

Here’s a simple example:

=IIf(Fields!YearlyIncome.Value >= 60000,"High","Low")

Using this expression, the "High" string is returned when the value of the YearlyIncome field is equal to or above 600, while the string "Low" is returned when the value is below 600.

Now have a look at the following example.  It has been nicely structured with indentation and line breaks to make reading easier.

=IIF
(
    Sum(Fields!LineTotal.Value) >= 100,
    "Violet",
    IIF()
    (
        Sum(Fields!LineTotal.Value) < 25,
        "Transparent",
        "Cornsilk"
    )
)

As you see, it shows a nested IIF inside another one.  Imagine that there were several more nestings and that line breaks were not used by the coder.  Would be a nightmare to read, right?

That’s why the Switch() was invented.  The description for the Switch function reads:

Evaluates a list of expressions and returns an Object value corresponding to the first expression in the list that is True.

And this is the function definition:

Public Function Switch( _
    ByVal ParamArray VarExpr() As Object _
) As Object

In Reporting Services, the VarExpr parameter is simply an even list of expressions and/or object references separated by commas.  Which comes down to something like this: Switch(<expr1>, val1, <expr2>, val2).

Here’s a simple example:

=Switch
(
    Fields!State.Value = "OR", "Oregon",
    Fields!State.Value = "WA", "Washington"
)

This expression says that if the value for the State field is "OR" then the Switch function will return "Oregon", and so on…

Now, to get to the point of this article, the Switch function does not contain an ELSE part like the IIF does.

But I wouldn’t be writing this if there wasn’t a workaround, would I?  If you read the Switch’s description closely, it says that it will return the first expression in the list that is true.  So each expression is evaluated in the order that they are passed to the function.  To get ELSE-like behavior we would need an expression that evaluates to True but only when all other expressions are False.  So, why not use True as expression?  It’s the simplest expression that I can think of and it does the works!

Have a look at the following, it’s a rewrite of the last IIF example mentioned earlier.

=Switch
(
    Sum(Fields!LineTotal.Value) >= 100, "Violet",
    Sum(Fields!LineTotal.Value) < 25, "Transparent",
    True, "Cornsilk"
)

So, which one do you think is the most readable?  The IIF, or the Switch?  These are only simple examples that I’ve been using, imagine situations with ten or more possibilities.  Well, I think you’ve got my point by now.

Quick tip for users of Report Builder 2.0: to be able to format your expression with line breaks and tabs, you need to use CTRL + ENTER or CTRL + TAB in the Expression Builder.  Just hitting ENTER will close the popup window.  It’s quite annoying if you’re used to the BIDS interface, but it works :-)

Happy reporting,

Valentino.

Share

Tags: , , ,

I came across an issue when playing around with Report Builder 2.0.  I had created a report using an embedded data source.  Once I’d published the report to the report server, I couldn’t get it to run anymore.  Instead it gave me the following error:

This report cannot be run in report builder because it contains one or more embedded data sources with credential options that are not supported.  Instead of embedded data sources use shared data sources or save and view the report on the server.

Okay, no problem I thought, let’s just create a shared data source and switch to that one then.  So I opened up the Data Source Properties in Report Builder and selected the Use a shared connection or report model radio button.

Unfortunately, when running the report it threw me that same error?!  And when I open the Data Source properties again, my change was undone!  It was still using the embedded data source.

As far as I’m concerned that should be a bug.

The only way that I could switch my data source to a shared connection was by creating a new data source, which means you also need to move all datasets connected to the original data source.

Quick tip: if you first rename the original data source and datasets to something like srcMyDataset_OLD, you can give the correct name to the new one straightaway.

So I guess that’s another workaround on my list :-)

This issue was encountered while using Report Builder 2.0 (10.0.2531.0).  I tried to reproduce it using Report Builder 3.0 (10.50.1092.20 – that’s the version of the SQL Server 2008 R2 August CTP) and I couldn’t.  Which means it has been fixed.  Good on you Microsoft!

Share

Tags: , , , , , ,

« Older entries § Newer entries »

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