Using Maps to Visualize Data in SQL Server Reporting Services Reports
SQL Server Reporting Services 2008 R2 introduced many features in the area of graphical display of information. According to the Microsoft Developer Network, there were three new features introduced in SQL Server 2008 R2: maps; sparklines and data bars; and indicators. In this first article of a three-part series on data visualization, we will explore the maps feature as we build reports using Dynamics GP data.
Setting the Stage
The majority of readers probably have used a map. You certainly used one to plan a trip or find nearby restaurants or figure out the names of those states in the Midwest. But have you ever wanted to visualize your GP data on a map? For example, you may have customers all over the state, country or world—it would be nice to step back and see where they are so you can develop strategies for marketing to them or building sales in other locations.
We’re going to take advantage of the maps feature to walk you through building a report using sales order processing invoice history. We’ll define the data, build the report, deploy the report and view it.
Defining the Data
As any report writer knows, a very important phase in building a report is defining the data. Using SQL Server Reporting Services (SSRS) parlance, we would say we are going to define the data set, which defines the information used by the report.
For our sales map, we’re going to pull our data from an SQL view called “SalesTransactions.” This view contains all transactions entered in Sales Order Processing, posted and unposted. For our report, we will need to restrict the data to posted invoices. In addition, our report is going to provide users the ability to enter a date range.
To map the data, we’ll use the state information found in the data. The mapping feature will use that information to plot our sales data on a map. We’ll also use the “Subtotal” column, so we exclude items like sales taxes and freight.
Building the Report
For this report, we are going to use Report Builder 3.0, released with SQL Server 2008 R2. It gives report writers a lot of functionality that required the Business Intelligence Development Studio in prior versions.
Upon launch of Report Builder, we get the “Getting Started” wizard.
Look! There is a Map Wizard built into the tool. How cool is that? Click the “Map Wizard” icon.
You get a number of choices. Without going into the details of each, we are going to use the Map gallery. Notice there are a set of predefined maps from which to choose. For our report, we’ll select the USA by State Exploded option. You can click on each map type to see a preview.
Once you select the map type, you’ll be able to choose some additional options for your map, including adding Bing features. On another page of the wizard, you can select how the data should appear:
Basic Map – The name says it all. It provides a multihued data view and is good for reporting by territory or region.
Color Analytical Map – This map enables you to view data using colors. For example, you can use green for areas of growing sales and red for problem areas.
Bubble Map – This map displays data as bubbles on the map. The bubbles can vary in size based on the data. This is the map type we’ll use.
To define our data set, select the columns and filters as shown here.
The next screen is the key to mapping the data. Notice the three fields: STATEFP, STUSPS and STATENAME. Looking at the types of data for each, you’ll quickly see we want to map to the STUSPS column, because that’s the type of data we have.
You’ll get another window to pick a theme and some other properties. After selecting your options, the last screen shows the report in design mode. Click “Run” to view the report.
You can modify and remove the components on the report. For example, you can remove the legend and make the map bigger, and you can move items around.
Deploying the Report
Once the report is designed the way you want, save it to the “Charts and KPIs” folder on the Report Server. It’s important to note that you need to ensure the SalesTransaction view is assigned to the report roles for the users who should view the report.
Using the Report
Log in to Dynamics GP.
Customize the home page and add the “Sales by State Map” report to the selected metrics.
Click “OK” and refresh your home page. Voila! Just like that, we have a map of our sales data.
It looks like we’re doing very well in some areas.
We’ve walked through using the mapping data visualization feature of SQL Server 2008 R2 and 2012, using Report Builder 3.0 and its built-in mapping wizard to create the report, deploying the report to the Report Server website and viewing it on our GP home page.
If your company wants to view its data on a map, consider this feature for your toolbox. Next time, we’ll examine the sparklines and data bars functionality.