Host Michael Coles (blog|twitter) has selected LOB data as the topic for this month’s T-SQL Tuesday, so I’ll take this opportunity to post an overview of reporting with spatial data types. As part of my work with SQL Server 2008 R2 Reporting Services, I’ve been exploring the use of spatial data types in the new map data region. You can create a map using any of the following data sources:

• Map Gallery – a set of Shapefiles for the United States only that ships with Reporting Services
• ESRI Shapefile – a .shp file conforming to the Environmental Systems Research Institute, Inc. (ESRI) shapefile spatial data format
• SQL Server spatial data – a query that includes SQLGeography or SQLGeometry data types

Rob Farley (blog|twitter) points out today in his T-SQL Tuesday post that using the SQL geography field is a preferable alternative to ESRI shapefiles for storing spatial data in SQL Server. So how do you get spatial data? If you don’t already have a GIS application in-house, you can find a variety of sources. Here are a few to get you started:

• US Census Bureau Website, http://www.census.gov/geo/www/tiger/
• Global Administrative Areas Spatial Database, http://biogeo.berkeley.edu/gadm/
• Digital Chart of the World Data Server, http://www.maproom.psu.edu/dcw/

In a recent post by Pinal Dave (blog|twitter), you can find a link to free shapefiles for download and a tutorial for using Shape2SQL, a free tool to convert shapefiles into SQL Server data.

In my post today, I’ll show you how to use combine spatial data that describes boundaries with spatial data in AdventureWorks2008R2 that identifies stores locations to embed a map in a report.

Preparing the spatial data

First, I downloaded Shapefile data for the administrative boundaries in France and unzipped the data to a local folder. Then I used Shape2SQL to upload the data into a SQL Server database called Spatial. I’m not sure of the reason why, but I had to uncheck the option to create a spatial index to upload the data. Otherwise, the upload appeared to run successfully, but no table appeared in my database.

The zip file that I downloaded contained three files, but I didn’t know what was in them until I used Shape2SQL to upload the data into tables. Then I found that FRA_adm0 contains spatial data for the country of France, FRA_adm1 contains spatial data for each region, and FRA_adm2 contains spatial data for each department (a subdivision of region).

Next I prepared my SQL query containing sales data for fictional stores selling Adventure Works products in France. The Person.Address table in the AdventureWorks2008R2 database (which you can download from Codeplex) contains a SpatialLocation column which I joined – along with several other tables – to the Sales.Customer and Sales.Store tables. I’ll be able to superimpose this data on a map to see where these stores are located. I included the SQL script for this query (as well as the spatial data for France) in the downloadable project that I created for this post.

Step 1: Using the Map Wizard to Create a Map of France

You can build a map without using the wizard, but I find it’s rather useful in this case. Whether you use Business Intelligence Development Studio (BIDS) or Report Builder 3.0, the map wizard is the same. I used BIDS so that I could create a project that includes all the files related to this post. To get started, I added an empty report template to the project and named it France Stores. Then I opened the Toolbox window and dragged the Map item to the report body which starts the wizard. Here are the steps to perform to create a map of France:

1. On the Choose a source of spatial data page of the wizard, select SQL Server spatial query, and click Next.
2. On the Choose a dataset with SQL Server spatial data page, select Add a new dataset with SQL Server spatial data.
3. On the Choose a connection to a SQL Server spatial data source page, select New.
4. In the Data Source Properties dialog box, on the General page, add a connecton string like this (changing your server name if necessary):
   Data Source=(local);Initial Catalog=Spatial
5. Click OK and then click Next.
6. On the Design a query page, add a query for the country shape, like this:
   select * from fra_adm1
7. Click Next. The map wizard reads the spatial data and renders it for you on the Choose spatial data and map view options page, as shown below.
   
   You have the option to add a Bing Maps layer which shows surrounding countries. Depending on the type of Bing Maps layer that you choose to add (from Road, Aerial, or Hybrid) and the zoom percentage you select, you can view city names and roads and various boundaries. To keep from cluttering my map, I’m going to omit the Bing Maps layer in this example, but I do recommend that you experiment with this feature. It’s a nice integration feature.
8. Use the + or – button to rexize the map as needed. (I used the + button to increase the size of the map until its edges were just inside the boundaries of the visible map area (which is called the viewport). You can eliminate the color scale and distance scale boxes that appear in the map area later.
9. Select the Embed map data in this report for faster rendering. The spatial data won’t be changing, so there’s no need to leave it in the database. However, it does increase the size of the RDL. Click Next.
10. On the Choose map visualization page, select Basic Map. We’ll add data for visualization later. For now, we have just the outline of France to serve as the foundation layer for our map. Click Next, and then click Finish.
11. Now click the color scale box in the lower left corner of the map, and press the Delete key to remove it. Then repeat to remove the distance scale box in the lower right corner of the map.

Step 2: Add a Map Layer to an Existing Map

The map data region allows you to add multiple layers. Each layer is associated with a different data set. Thus far, we have the spatial data that defines the regional boundaries in the first map layer. Now I’ll add in another layer for the store locations by following these steps:

1. If the Map Layers windows is not visible, click the report body, and then click twice anywhere on the map data region to display it.
2. Click on the New Layer Wizard button in the Map layers window.
   
   And then we start over again with the process by choosing a spatial data source.
3. Select SQL Server spatial query, and click Next.
4. Select Add a new dataset with SQL Server spatial data, and click Next.
5. Click New, add a connection string to the AdventureWorks2008R2 database, and click Next.
6. Add a query with spatial data (like the one I included in the downloadable project), and click Next.
7. The location data now appears as another layer on top of the regional map created earlier. Use the + button to resize the map again to fill as much of the viewport as possible without cutting off edges of the map. You might need to drag the map within the viewport to center it properly.
8. Select Embed map data in this report, and click Next.
9. On the Choose map visualization page, select Basic Marker Map, and click Next.
10. On the Choose color theme and data visualization page, in the Marker drop-down list, change the marker to diamond. There’s no particular reason for a diamond; I think it stands out a little better than a circle on this map. Clear the Single color map checkbox as another way to distinguish the markers from the map.
    You can of course create an analytical map instead, which would change the size and/or color of the markers according to criteria that you specify, such as sales volume of each store, but I’ll save that exploration for another post on another day.
11. Click Finish and then click Preview to see the rendered report. Et voilà…c’est fini.
    
    Yes, it’s a very simple map at this point, but there are many other things you can do to enhance the map. I’ll create a series of posts to explore the possibilities.

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

If you have any experience at all with Reporting Services, you have probably developed a report using the matrix data region. It’s handy when you want to generate columns dynamically based on data. If users view a matrix report online, they can scroll horizontally to view all columns and all is well. But if they want to print the report, the experience is completely different and you’ll have to decide how you want to handle dynamic columns. By default, when a user prints a matrix report for which the number of columns exceeds the width of the page, Reporting Services determines how many columns can fit on the page and renders one or more separate pages for the additional columns. In this post, I’ll explain two techniques for managing dynamic columns. First, I’ll show how to use the RepeatRowHeaders property to make it easier to read a report when columns span multiple pages, and then I’ll show you how to “wrap” columns so that you can avoid the horizontal page break. Included with this post are the sample RDLs for download.

First, let’s look at the default behavior of a matrix. A matrix that has too many columns for one printed page (or output to page-based renderer like PDF or Word) will be rendered such that the first page with the row group headers and the inital set of columns, as shown in Figure 1.

The second page continues by rendering the next set of columns that can fit on the page, as shown in Figure 2.This pattern continues until all columns are rendered.

The problem with the default behavior is that you’ve lost the context of employee and sales order – the row headers – on the second page. That makes it hard for users to read this report because the layout requires them to flip back and forth between the current page and the first page of the report. You can fix this behavior by finding the RepeatRowHeaders of the tablix report item and changing its value to True. The second (and subsequent pages) of the matrix now look like the image shown in Figure 3.

The problem with this approach is that the number of printed pages to flip through is unpredictable when you have a large number of potential columns. What if you want to include all columns on the same page? You can take advantage of the repeating behavior of a tablix and get repeating columns by embedding one tablix inside of another.

For this example, I’m using SQL Server 2008 R2 Reporting Services. You can get similar results with SQL Server 2008. (In fact, you could probably do something similar in SQL Server 2005, but I haven’t tested it. The steps would be slightly different because you would be working with the old-style matrix as compared to the new-style tablix discussed in this post.) I created a dataset that queries AdventureWorksDW2008 tables:

SELECT TOP (100)
e.LastName + ', ' + e.FirstName AS EmployeeName, d.FullDateAlternateKey, f.SalesOrderNumber, p.EnglishProductName, sum(SalesAmount) as SalesAmount
FROM FactResellerSales AS f INNER JOIN
DimProduct AS p ON p.ProductKey = f.ProductKey INNER JOIN
DimDate AS d ON d.DateKey = f.OrderDateKey INNER JOIN
DimEmployee AS e ON e.EmployeeKey = f.EmployeeKey
GROUP BY p.EnglishProductName, d.FullDateAlternateKey, e.LastName + ', ' + e.FirstName, f.SalesOrderNumber
ORDER BY EmployeeName, f.SalesOrderNumber, p.EnglishProductName

To start the report:

• Add a matrix to the report body and drag Employee Name to the row header, which also creates a group.
• Next drag SalesOrderNumber below Employee Name in the Row Groups panel, which creates a second group and a second column in the row header section of the matrix, as shown in Figure 4.

Now for some trickiness.

• Add another column to the row headers. This new column will be associated with the existing EmployeeName group rather than causing BIDS to create a new group. To do this, right-click on the EmployeeName textbox in the bottom row, point to Insert Column, and then click Inside Group-Right.
• Then add the SalesOrderNumber field to this new column. By doing this, you’re creating a report that repeats a set of columns for each EmployeeName/SalesOrderNumber combination that appears in the data.
• Next, modify the first row group’s expression to group on both EmployeeName and SalesOrderNumber. In the Row Groups section, right-click EmployeeName, click Group Properties, click the Add button, and select [SalesOrderNumber].
• Now you need to configure the columns to repeat. Rather than use the Columns group of the matrix like you might expect, you’re going to use the textbox that belongs to the second group of the tablix as a location for embedding other report items. First, clear out the text that’s currently in the third column – SalesOrderNumber – because it’s already added as a separate textbox in this report design. Then drag and drop a matrix into that textbox, as shown in Figure 5.

Again, you need to do some tricks here to get the appearance and behavior right. We don’t really want repeating rows in the embedded matrix, so follow these steps:

• Click on the Rows label which then displays RowGroup in the Row Groups pane below the report body.
• Right-click on RowGroup,click Delete Group, and select the option to delete associated rows and columns.

As a result, you get a modified matrix which has only a ColumnGroup in it, with a row above a double-dashed line for the column group and a row below the line for the aggregated data. Let’s continue:

• Drag EnglishProductName to the data textbox (below the line).
• Add a second data row by right-clicking EnglishProductName, pointing to Insert Row, and clicking Below. Add the SalesAmount field to the new data textbox.
• Now eliminate the column group row without eliminating the group. To do this, right-click the row above the double-dashed line, click Delete Rows, and then select Delete Rows Only in the message box.

Now you’re ready for the fit and finish phase:

• Resize the column containing the embedded matrix so that it fits completely.
• Also, the final column in the matrix is for the column group. You can’t delete this column, but you can make it as small as possible. Just click on the matrix to display the row and column handles, and then drag the right edge of the rightmost column to the left to make the column virtually disappear.
• Next, configure the groups so that the columns of the embedded matrix will wrap. In the Column Groups pane, right-click ColumnGroup1 and click on the expression button (labeled fx) to the right of Group On [EnglishProductName].
• Replace the expression with the following: =RowNumber(“SalesOrderNumber” ). We use SalesOrderNumber here because that is the name of the group that “contains” the embedded matrix.
• The next step is to configure the number of columns to display before wrapping. Click any cell in the matrix that is not inside the embedded matrix, and then double-click the second group in the Row Groups pane – SalesOrderNumber.
• Change the group expression to the following expression: =Ceiling(RowNumber(“EmployeeName”)/3)
• The last step is to apply formatting. In my example, I set the SalesAmount textbox’s Format property to C2 and also right-aligned the text in both the EnglishProductName and the SalesAmount textboxes. And voila – Figure 6 shows a matrix report with wrapping columns.

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

On April 14, 2010, Microsoft Press (blog | twitter) released my latest book, co-authored with Ross Mistry (twitter), as a free ebook download - Introducing Microsoft SQL Server 2008 R2. As the title implies, this ebook is an introduction to the latest SQL Server release. Although you’ll find a comprehensive review of the product’s features in this book, you will not find the step-by-step details that are typical in my other books. For those readers who are interested in a more interactive learning experience, I have created two samples file for download:

• IntroSQLServer2008R2Samples project
• Sales Analysis workbook

Here’s a recap of the business intelligence chapters and the samples I used to generate the screen shots by chapter:

Chapter 6: Scalable Data Warehousing covers a new edition of SQL Server, Parallel Data Warehouse. Understandably, Microsoft did not ship me the software and hardware to set up my own Parallel Data Warehouse environment for testing purposes and consequently you won’t see any screenshots in this chapter. I received a lot of information and a lot of help from the product team during the development of this chapter to ensure its technical accuracy.

Chapter 7: Master Data Services is a new component in SQL Server. After you install Master Data Services (MDS), which is a separate installation from SQL Server although it’s found on the same media, you can install sample models to explore (which is what I did to create screenshots for the book). To do this, you deploying packages found at \Program Files\Microsoft SQL Server\Master Data Services\Samples\Packages. You will first need to use the Configuration Manager (in the Microsoft SQL Server 2008 R2\Master Data Services program group) to create a database and a Web application for MDS. Then when you launch the application, you’ll see a Getting Started page which has a Deploy Sample Data link that you can use to deploy any of the sample packages.

Chapter 8: Complex Event Processing is an introduction to another new component, StreamInsight. This topic was way too large to cover in-depth in a single chapter, so I focused on information such as architecture, development models, and an overview of the key sections of code you’ll need to develop for your own applications. StreamInsight is an engine that operates on data in-flight and as such has no user interface that I could include in the book as screenshots. The November CTP version of SQL Server 2008 R2 included code samples as part of the installation, but these are not the official samples that will eventually be available in Codeplex. At the time of this writing, the samples are not yet published.

Chapter 9: Reporting Services Enhancements provides an overview of all the changes to Reporting Services in SQL Server 2008 R2, and there are many! In previous posts, I shared more details than you’ll find in the book about new functions (Lookup, MultiLookup, and LookupSet), properties for page numbering, and the new global variable RenderFormat. I will confess that I didn’t use actual data in the book for my discussion on the Lookup functions, but I did create real reports for the blog posts and will upload those separately. For the other screenshots and examples in the book, I have created the IntroSQLServer2008R2Samples project for you to download. To preview these reports in Business Intelligence Development Studio, you must have the AdventureWorksDW2008R2 database installed, and you must download and install SQL Server 2008 R2. For the map report, you must execute the PopulationData.sql script that I included in the samples file to add a table to the AdventureWorksDW2008R2 database. The IntroSQLServer2008R2Samples project includes the following files:

• 01_AggregateOfAggregates.rdl to illustrate the use of embedded aggregate functions
• 02_RenderFormatAndPaging.rdl to illustrate the use of page break properties (Disabled, ResetPageNumber), the PageName property, and the RenderFormat global variable
• 03_DataSynchronization.rdl to illustrate the use of the DomainScope property
• 04_TextboxOrientation.rdl to illustrate the use of the WritingMode property
• 05_DataBar.rdl
• 06_Sparklines.rdl
• 07_Indicators.rdl
• 08_Map.rdl to illustrate a simple analytical map that uses color to show population counts by state
• PopulationData.sql to provide the data necessary for the map report

Chapter 10: Self-Service Analysis with PowerPivot introduces two new components to the Microsoft BI stack, PowerPivot for Excel  and PowerPivot for SharePoint, which you can learn more about at the PowerPivot site. To produce the screenshots for this chapter, I created the Sales Analysis workbook which you can download (although you must have Excel 2010 and the PowerPivot for Excel add-in installed to explore it fully). It’s a rather simple workbook because space in the book did not permit a complete exploration of all the wonderful things you can do with PowerPivot. I used a tutorial that was available with the CTP version as a basis for the report so it might look familiar if you’ve already started learning about PowerPivot.

In future posts, I’ll continue exploring the new features in greater detail. If there’s any special requests, please let me know!

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

As you might have guessed if you’ve been reading my blog lately, I’ve been spending a lot of time working with the business intelligence features in SQL Server 2008 R2. Not just Reporting Services, but the whole BI stack. However, it just so happens that today is T-SQL Tuesday and the topic this month is Reporting (hosted by Aaron Nelson – blog | twitter) , so I thought I’d use the opportunity to contribute another observation about new properties and globals that you can find in the upcoming release of Reporting Services. Today, I’ll explain how you can use the PageName and ResetPageNumber properties to dynamically change the values of Globals!PageName (a new global) and Globals!PageNumber (a long-time global) respectively during report execution. And I’ll introduce Globals!OverallPageNumber and Globals!OverallTotalPages.

One of the problems that I currently have with earlier versions of Reporting Services is the inability to control page numbering when I use subreports. I have a situation where I have many individual reports that I want to combine into a single PDF document for distribution. Page numbering is available only in the page header or page footer of a report, so I can’t define the page numbering strategy in the report that I define as a subreport. And even if I could, prior to SQL Server 2008 R2, there was no simple way to renumber pages dynamically within the report. That is, the page numbering applies to the entire report. I cannot number the pages in a distinct section of the report separately from other sections.

With the new version of Reporting Services, I can now solve this problem. Not only can I have separate page numbering by “section”, but I can also assign names to each “section”. For this example, I’ve taken a very simple approach to prove out what’s possible. That means it’s  not pretty or elegant, or meant to illustrate best practices, but it’s functional and to the point. Let’s get started.

The master report contains a Tablix data region which has a PageName property that I can use for naming the first section of my report, as shown in Figure 1. You can use static text, as I have done, or you can use an expression. The PageName property is a property of a data region or a group. I haven’t tested it extensively, but it appears to remain constant from page to page until the report execution renders another data region or group where the PageName property is set to a new value.

Figure 1

In the report that I’ll use as a subreport, I set the PageName property to Sales Summary, on the outermost tablix. You’ll see later that this property “pulls through” to the rendered report. I can also set page breaks (see Figure 2) on the groups in that report which appear in the rendered report, which I have on the group (which breaks on calendar year) of the outermost tablix. The only thing that I cannot define in this report is page numbering because pagination properties are ignored when a report is used as a subreport. Note that the PageName is left blank here. It will continue to use Sales Summary which is defined on the Tablix2 object.

Figure 2

Now the problem that I have with subreports is that there is no way for me to tell it to 1) insert a page break before rendering the subreport, and 2) reset the page numbering. However, I can use a rectangle to handle the pagination for me. So I add a rectangle to MasterReport, insert a subreport inside the rectangle, and configure the subreport to point to the ResellerSales report. Next, I configure the pagination properties on the rectangle, as shown in Figure 3. I force a page break with BreakLocation = Start, and set ResetPageNumber to True.

Figure 3

The page number reset applies only to the current page counter. You display the value of the current page counter in the page header or footer by using the Globals!PageNumber, which has always been a part of Reporting Services. However, in SSRS 2008 R2, you can now reset the current page counter to zero anytime you want to begin a new page numbering sequence in your report. Meanwhile, you can display the running count of ALL pages in your report by using the new globals, Globals!OverallPageNumber and Globals!OverallTotalPages. To illustrate the two page counters, I have two textboxes in the page footer of my report (which is the only place you can use the globals related to pagination).

The first expression applies the current section of my report which is affected by the page number reset and displays the value of the PageName property:

=Globals!PageName + " "  + CStr(Globals!PageNumber) + " of " + CStr(Globals!TotalPages)

The second expression displays the overall page count, which is unaffected by the page number reset:

=CStr(Globals!OverallPageNumber) + " of " + CStr(Globals!OverallTotalPages) + " Overall"

Now when I preview the report, I see the footer shown in Figure 4 on the first page of the report. Here you can see the first “section” of the report is a total of 5 pages while the overall report is 9 pages.

Figure 4

And then paging forward to the first page of the subreport, I find the footer shown in Figure 5. The PageName property of the tablix in the subreport now replaces the prior page name. (Remember that you can use an expression instead of static text.)

Figure 5

If you want to take a closer look, you can download my project and try it out yourself. To execute the report, you must have the AdventureWorksDW2008R2 database installed, and you must download and install SQL Server 2008 R2.

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.

Today’s post is the 3rd post in a series about the new lookup functions in SQL Server 2008 R2 Reporting Services. I covered the Lookup function in Part I of this series and the MultiLookup function in Part II.  The third and final lookup function is LookupSet.

First, a quick review. The Lookup function evaluates one value per row in the source to find one value for that row in the destination and the MultiLookup function evaluates one set of values per row, finds each matching value in the destination, and returns the matches in a set. LookupSet evaluates each row in the source and looks for all matches in the destination, returning the resulting matches as a set.

To illustrate how to use LookupSet, I want to produce a list of the sales by state and include a count of customers so that I can compare the average sales per customer by state. In Dataset1, I have a list of the states’ two-letter codes and total sales. In Dataset2, I have a list of customer IDs and the state associated with the customer. You can see a sample of data in each of these datasets below in Figure 1.

Figure 1

If you look at the report layout and a portion of the report shown in Figure 2, you can infer that I have a combination of lookups in play. The report is a simple table that includes StateProvinceCode in the first column. In the State column, I  use the Lookup function in an expression to get the state name from a third dataset. In the Customer Count column, I have an expression that includes the LookupSet function to retrieve the set of customers for the state on the current row and then I use a Count function on that set. Of course, if I wanted a list of those customers, I could use the Join function to convert the array that returns from the LookupSet function into a string. Continuing on with this example, though, I have SalesAmount from Dataset1, and then another expression for “Avg Sale Per Customer” that divides SalesAmount by Customer Count.

Figure 2

The expression in the Customer Count column uses the LookupSet function and looks like this:

=LookupSet(Fields!StateProvinceCode.Value,Fields!StateProvinceCode.Value,
Fields!CustomerID.Value,"Dataset2").Length

LookupSet takes four arguments, much like the Lookup function except that Lookup returns a single value per row whereas LookupSet returns an array:

1. The first argument, Fields!StateProvinceCode.Value,  is a field in the dataset that is bound to the data region (Dataset1 in this example).
2. The second argument is the StateProvinceCode field in Dataset2 that you use as the lookup field. The LookupSet function matches each value in the Dataset1 field with a value in the Dataset2 field. The values to match must have the same data type.
3. The third argument, Fields!CustomerID.Value, is the field in Dataset2 that you want to return as the function result. The difference here between the Lookup and LookupSet functions is that the Lookup function returns the first value it finds, but LookupSet returns an array of all matching values that it finds.
4. The fourth and final argument is the name of the second dataset which must be enclosed in quotes and is case-sensitive.

LookupSet returns an array, so you can’t use the results directly in the table but must use some other function to operate on the array to get a single value.

I like pictures to help me understand how things work, so I created Figure 3 below as another way to visualize what the LookupSet function does.

And that concludes this series on the new Lookup functions in SQL Server 2008 R2 Reporting Services. I hope you find these explanations useful. I admit that I haven’t had a compelling need for them, but that’s because I do the necessary integration in the back-end first – either by querying a data mart/warehouse or by joining the data in the dataset. However, I can imagine scenarios where those two options aren’t available in which case having the Lookup functions as an alternative is helpful. What do you think?

This content is published under the Attribution-Noncommercial-No Derivative Works 3.0 Unported license.