Creating Spatial Reports from Sources other than SQL Server

With the release of SQL Server 2008 R2, Microsoft provided the capability of generating Reporting Services solutions that provided more than just tablix data. It provides the option of showing static maps to the report viewer. The control provides a wizard that makes it very easy to consume data from sources such as ESRI Shapes or spatial queries against SQL Server. What seems to be missing is the ability to access spatial type data from other sources such as Excel, text or other sources. I was challenged to make this happen. After several attempts and a number of roadblocks, I have discovered a way to make this work.

Through the rest of this post, I will show you how to create a report that maps the locations of employees out of the Human Resources (HR) schema within an Oracle database. This would be easy to extend to other data sources as well. Let me clarify that the data I will be accessing is not specific spatial data types unique to different sources. It is the data that is typically stored in a number of databases and spreadsheets, the Latitude and Longitude of a location.

Create a Reporting Services Project

Begin by opening Business Intelligence Developer Studio 2008/Visual Studio 2008. Create a new Business Intelligence project using the “Report Server Project” template. This will leave you with an empty Solution Explorer shown in Figure 1.

image

Figure 1

Right click on the Reports Folder and add a new report by choosing ‘Add -> New Item -> Report’ and give the report a name.

Define a Data Source and Data Set

Next add a Data Source in the Report Data window, by right clicking on Data Sources then ‘Add Data Source’. Within the Data Source Properties window, name the data source, choose the provider and give it an appropriate connection string. Since I’m going against an Oracle database and want to ensure my connection, I’m providing the connection string from the tnsnames.ora file. See Figure 2 for my setup.

image

Figure 2

Choose the ‘Edit’ button to provide credentials. Since I’ve unlocked the HR schema and I want the Employee database, I provide user name and password of ‘hr’ and ‘hr’. I’ll also have it save my password as seen in Figure 3. Then I finish by testing my connection.

image

Figure 3

Now to create the base dataset, right click in the ‘Report Data’ window on Datasets and choose to ‘Add Dataset’. Within the window for Dataset Properties, name the dataset, let it be embedded in the report and choose the Data Source we created earlier ‘OracleSource’. Finally provide the query. In this query, select the first name, last name, email, latitude, longitude from the Employees table. (Note: I had to enhance the schema of Employee to include two new fields of Number type with precision and scale set for 10 and 8 respectively. Rather than update all 107 records, I only added location information to the first ten records for Employee_ID 100 -110.) Figure 4 shows the window.

image

Figure 4

Here are the results of the query in Figure 5.

image

Figure 5

This will generate a Report Data window like Figure 6:

Figure 6

So far, this is nothing special from generating a report geared toward a table. However, we have to do something different in order to get to generating a SQL Server Spatial field type. In order to create this field, we have to add external library references to the report. Right click the work area of the report and choose ‘Report Properties’. Under the References tab, add two .Net assemblies, System.Data and Microsoft.SqlServer.Types. Do this by clicking Add and then choose the ellipse on the right for each one. Here are the results of your work in Figure 7.

image

Figure 7

After creating the references, right click on the EmployeeInformation dataset and create a new Calculated Field.

Within the Dataset Properties window, under the Fields tab, add an extra field with the name ‘GeoLocation’, click the expression button and type: =Microsoft.SqlServer.Types.SqlGeography.Point(Fields!LATITUDE.Value,Fields!LONGITUDE.Value, 4326)

The expression provides an elliptical projection with an expectation of points populated by the datasets Latitude and Longitude fields. The ‘4326’ is a projection reference for maps. I’ll leave it to you to research if this is right for you. Now, you have added a calculated field to your dataset.

Generate Visual Elements of the Report

Let’s begin by adding a table to the report by right clicking the design surface and adding a table to the top left of the report. Next drag out the FIRST_NAME, LAST_NAME, and EMAIL fields to the table. Next insert a Map control and place it on the top right of the report. A wizard will then begin. Ignore this wizard by canceling it. This works great if you are adding SQL Server Spatial data, but that is not what we are attempting. The design should look like Figure 8.

image

Figure 8

A map typically consists of a number of layers. These layers can be tiled images, data points, lines, and/or polygons. We are going to add two: Bing Maps tile and a series of data points from our dataset. Double click any area of the map to present the Map Layers window. Choose the second icon in the toolbar to add a Tile layer. This will add a Bing map of the world to your control. Add another layer to your map, this time a Point Layer as shown in Figure 9.

image

Figure 9

In the properties window change the DataSetName to EmployeeInformation like in Figure 10.

image

Figure 10

Go back to the Map Layers window and choose the selector (down error) for the Point Layer and select Data Layer. From the list of options in the Map Point Layer Properties, choose the ‘Spatial field in an analytical data region’ and close this window. Over in the properties window, expand the Spatial Data property to expose the VectorData field. From the dropdown choose the expression option to input the code: =Fields!GeoLocation.Value.

Now your report could be presented right now, but let’s make it look a little sharper. Click on the selector again for the Point Layer and choose ‘Point Properties’. Select the EMAIL field for the Tooltip and change the Marker type to a triangle. See Figure 11 for the settings.

image

Figure 11

From the selector for the Point Layer, choose the Point Color Rules option. To make the locations easy to see, I set the option to visualize data by using color ranges and I set the color options all to red (play with this layer on your own for wild colors).

I’ve made all of the changes I want and I’m ready to preview the report. You may get some different results but it should be similar to Figure 12.

image

Figure 12

Conclusion

That’s it! You now have a static report ready to be printed or exported to another format.

Let me know if you have any questions or perhaps you have some other ideas how to use the map control against non-SQL Server resources.

Thanks to Keith Tran who brought this request to me.

Best Wishes!

Keith Nicholson

A New Day….

Hi everyone!


I just completed my first day at Microsoft as an Account Developer Consultant.  This is exciting.  I’ll be covering clients in Oklahoma, helping developers to do their jobs.


I’ve spent the day getting to know, Brian, Bill, Ben, and Sean (I thought about changing Sean’s name to Bean in order to keep the B theme going).  I got a walk around the facilities today in Irving, TX thanks to Brian and Sean is to be my Mentor right now.  He is a great guy and is helping me learn the ropes.


I got a new PC with Windows 7 on it.  First impression…very cool.  Hopefully tomorrow I’ll get my certificates  in order to actually sign into the network.

More 10-4

Episode 2 of “10-4” (Dec 24th – Jason Olson)


Key feature shown – The customized start page.  I like the carry over from office of pining down a document.  In this case the pining of a project.  I know this sounds small, but how many times have you been working on your core project, to interupt it with some proof of concept projects?  I find that I then have to either expand the number of projects in my file menu or navigate to the project files again.  This will allow me to keep my official work always at the top.


Jason then demonstrated the extensibility of the Start Page.  There are some options here like the folder lists and changing the background, etc.  However, in my training, I have found very few people who took advantage of any Start Page customization in the last two versions of VS.


I tried out the customizations myself through the walkthroughs that Brian Keller talked about in Episode 1 (see prior post).  While Jason showed us how to make changes by using notepad, I loaded up the csproj file into Visual Studio and used the WPF designer.  The first load of the xaml looked very odd.  After closing the xaml and reopening, it looked great.


Rather than deleting or commenting the code for the button referenced in the walkthrough, the Walkthrough button’s visibility was set to “Hidden” in the properties window.


In order to add a new item to the TrayGroup, I clicked on the group and expanded the Items property.


image image


I was surprised to see that I could not “Add” a new TrayGroupItem through the dialog box.


image


Therefore I used the XAML tab under the designer to add the new item manually.


image


Moving to the next part of adding content, be sure in step 5 to read the note.  I love in training when an attendee asks a question about a bug in their lab work.  I ask them to read the next sentence which asks them to observe the bug which was just created. The standard response is “Ooohh.”  Additionally, I created a new TabGroup to host any RSS Feeds I might want to track.  I inserted the following code  at the end after the “Visual Studio” TrayItem.


        <!– RSS Feeds –>
        <vs:TrayItem Header=”RSS Feeds” x:Uid=”RSS_Header”>
            <vs:TrayGroup>

                <vs:TrayGroupItem Content=”Soma’s blog”
                                  ImageSource=”c:\Users\Public\Documents\CTPWalkthroughs\Visual Studio\Samples\Start Page\soma.png”
                                  InnerContentUri=”SomaBlog;Component/UserControl1.xaml” />
            </vs:TrayGroup>
        </vs:TrayItem>

    </vs:Tray>
</Grid>


 


Here were my results:


image image


 


It looks pretty good except for one part.  Visibility with WPF does not mean to ignore the element and have other elements slide into its place.  By changing the property to “Collapsed” the image disappears and there is no need for the scroll bar to be used this time.  Feedback looks better when it is not cut off.


image


Overall, this was a pretty easy approach at adding items, using code to extend the IDE, and now for me to add several other blogs to my environment.


 


Episode 3 of “10-4” (Dec 31st – Jonathan Carter)


Two items out of this episode I liked hearing.  Jonathan firmly states that WebForms is still a major player in VS.  This version adds a lot of strength and new features to the model we have been using for the last six years.  Why would this be questioned?  Well VMC seems to be a fan favorite right now and there is a very vocal camp that assumes this is the only way to create content now.  I’m not an extremist myself, so I like to know how a proven tech is improved.


The second item proves my closing sentence in the prior paragraph: ClientID!  Working with dynamically created items through code has been a hassle for me since the framework 1.1.  I spent a lot of time in 2003-2004 writing code with Regular Expressions to help me navigate through shopping carts and such.  Microsoft has provided some really great approaches to this.  I’m very hopeful for this feature in making your lives easier.  Be sure to watch this one.

That’s a Big 10-4 Little Buddy!

Can you believe we are already talking Visual Studio 2010 and the .NET Framework 4.0?  It seems like some of us just started learning 2008.   In fact, according to a Developer Guidance Council meeting today, hosted by our regional DE in Dallas, Texas, many are still learning 2005. 


Go ahead and say it.  I know you want to!  “There are still people learning VS 6.0!”


However, if you like moving forward, MS has created a Tech Preview of the next environment.  This is not Beta.  It is pre-Beta.  I learned about this through a casual search that landed me on Channel9.  There was a reference to a series of videos under the label of “10-4”. The title comes by playing off the release year (2010) and framework numbers (4.0), while mixing in a little Bandit language off the airwaves (CB Radio).


The link I found was for number #14 in the series.  The topic is on F# which I have not been paying enough attention to yet. I is a nice short 17 minute video introducing you and me to the syntax.  It was all text driven but the idea interested me enough to look into episode #1.  In the first episode, Microsoft introduces us to a process of downloading a VirtualPC image with all of the build pre-installed on top of Vista.


It is a pretty hefty download and I’ll probably have to wait overnight while expanding the images before I can move on to Episode #2 and get my hands dirty.  Thanks Brian Keller for setting this all up for us back several months ago.  I look forward to watching the other episodes this week.


Congratulations to My Buddy Brian Moore

I just learned that a friend from the past is moving up in Microsoft and providing his great skills to a larger group of Microsoft Developer/Platform Evangelists.

Brian Moore, previously the Dallas Developer Evangelist, moved to St. Louis, MO a few years ago to work that region and was then promoted to manage others in the north.  The current plan now is for him to manage the entire central region of Developer Evangelists.

Brian is a very knowledgeable, efficient, personal, guy.  I wish him the best!

SQL Server 2008 RC0 Candidate is Released!!

FROM Microsoft’s Connect Site (http://connect.micorsoft.com)

 

SQL Server 2008 CTP
Do you have passion for technology? Do you want to make a difference? Then try out SQL Server 2008 Release Candidate 0 (RC0) and provide your feedback. The SQL Server development team uses your comments to help refine and enhance product features.



Info Download Release Candidate 0 from the Download Center! New!
The SQL Server team presents the future of SQL Server!  SQL Server 2008 represents a significant leap in features, functionality, and improvements.  Get your hands on RC0 at the Download Center and then come back here to learn about all the features.
Info What’s New in SQL Server 2008? New!
RC0 is now available.  Discover all the extra features and the latest improvements.
Info SQL Server Product Site
Visit the SQL Server 2008 Product site for product information, marketing whitepapers, webcasts, and videos.
Info Download the RC0 Feature Pack from the Download Center! New!
Chat SQL Server Connect Feedback
We want to hear from you! Tell us about any bugs you find or difficulties you encounter, and what you want to see in SQL Server 2008 for this and future releases. We appreciate your feedback!
Chat MSDN SQL Server Forums
Have questions? Run into an issue and you could use some feedback?  Discuss SQL Server 2008 with our developers, MVPs, and the entire SQL Server community.
Dev Icon CodePlex Community Samples
Want to see different samples and uses of SQL Server code?  Visit CodePlex and look at real world examples.

 


I started the download process on this and the BOL.

I always like to see the Feature Pack included.  This time it has the normal components that can be seperated from the general install to be used with developers and clients.  But its the other items that are the greatest value.

  • Microsoft SQL Server 2008 Analysis Services 10.0 OLE DB Provider
  • Microsoft SQL Server 2008 Command Line Utilities
  • Microsoft SQL Server Backward Compatibility Components
  • SQL Server Compact 3.5 SP1
  • Microsoft SQL Server 2008 Data Mining Add-ins for Microsoft Office 2007 (Wait till you see these!!!)
  • Microsoft Core XML Services (MSXML) 6.0
  • Microsoft SQL Server 2008 Management Objects
  • Microsoft OLEDB Provider for DB2
  • Microsoft SQL Server 2008 Native Client
  • Microsoft SQL Server 2008 Policies (Best practice examples of taking advantage of the new Policy Based Management)
  • Microsoft SQL Server 2008 Report Builder 2.0
  • Microsoft SQL Server 2008 Report Services Add-in for Microsoft SharePoint Technologies
  • Microsoft SQL Server System CLR Types (Allows Client Applications to use these new types (geometry, geography, and hierarchy) outside of the server.

And Finally available for download for the first time (previously only on installation media)….

  • Microsoft SQL Server  2008 Upgrade Advisor

 

P.S. Do not forget the SQL Server 2008 Upgrade Assistant from SE in work with Microsoft.

BOL for SQL Server 2008 RC0

So you can now download Books On Line for SQL Server 2008 by going to http://www.microsoft.com/downloads/details.aspx?FamilyID=19db0b42-a5b2-456f-9c5c-f295cdd58d7a&DisplayLang=en.

 

I started the process and I got an error on installation requiring a new version of Microsoft Installer. I have Vista SPI installed on this system. Yet I still do not have the latest installer. Curious. So further research shows that it needs version 4.5 which was just released on June 5th. You can find this at http://support.microsoft.com/kb/942288

It was only 2.8 MB and installed in less than 5 minutes. It supplied a hotfix KB942288,  which of course required a reboot.

 

Get started on this now.  I’ll post info about the RC0 later.

UPDATE: Maximum Capacity Specification for SQL Server in BOL from CTP 6

There are some items to validate on the Maximum Capacity Specification for SQL Server in BOL from CTP 6.

1) Database size: 1,048,516 terabytes?

If you can only have 32,767 files and each data file can only be 16 TB, then wouldn’t this equate to 524,272 TB for database size? Or has the number of files changed? Or has the size of a data file changed. Current BOL for SQL Server 2005 on http://msdn.microsoft.com show 524,272 TB

2) Instances per computer: —–32-bit: 50 instances on a stand-alone server for all SQL Server editions except for Workgroup Edition. Workgroup Edition supports a maximum of 16 instances per computer. —–64-bit: 50 instances on a stand-alone server.

Current BOL for SQL Server 2005 on msdn.microsoft.com show the same. However, I was under the impression that 50 is only available for Enterprise Edition whether 32-bit or 64-bit. What is correct?

Go to http://connect.microsoft.com and add your comments to this feedback. Otherwise, I’ll let you know the results later.

If you have not signed up for Connect, this would be a great time.  I really like how they are enhancing the site.

Update:

Last week I received status back from Microsoft that my observations are correct and the next version of BOL with RC0 will include the changes.

Update 2 Update:

Before I even finish this, I find out there has been an announcement around a new release of BOL for RC0.  see an upcoming Post.

A NULL Question with "+=" Syntax in SQL 2008

During an internal presentation today, a co-worker asked a question about working with nulls using the new T-SQL syntax of “+=

I tried the following code and it seems to work as expected.  Feel free to give it a run.

 

–Requires a SQL 2000 for validation.  Code Works of course.

 

PRINT ‘Setting CONCAT_NULL_YIELDS_NULL ON’;

GO

– SET CONCAT_NULL_YIELDS_NULL ON and testing.

SET CONCAT_NULL_YIELDS_NULL ON;

GO

DECLARE @NullValue varchar(10)

 

DECLARE @Alpha varchar(10)

SET @Alpha = ‘abc’

 

SET @Alpha = @Alpha + @NullValue ;

SELECT @Alpha;

GO

 

– SET CONCAT_NULL_YIELDS_NULL OFF and testing.

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

DECLARE @NullValue varchar(10)

 

DECLARE @Alpha varchar(10)

SET @Alpha = ‘abc’

 

SET @Alpha = @Alpha + @NullValue ;

SELECT @Alpha;

GO

 

 

— Requires SQL 2008 – Will not work on 2000.

 

PRINT ‘Setting CONCAT_NULL_YIELDS_NULL ON’;

GO

– SET CONCAT_NULL_YIELDS_NULL ON and testing.

SET CONCAT_NULL_YIELDS_NULL ON;

GO

DECLARE @NullValue varchar(10);

 

DECLARE @Alpha varchar(10) = ‘abc’

 

SET @Alpha += @NullValue ;

SELECT @Alpha;

GO

 

– SET CONCAT_NULL_YIELDS_NULL OFF and testing.

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

DECLARE @NullValue varchar(10)

 

DECLARE @Alpha varchar(10)= ‘abc’

 

SET @Alpha += @NullValue ;

SELECT @Alpha;

 

 

–Flip It

 

PRINT ‘Setting CONCAT_NULL_YIELDS_NULL ON’;

GO

– SET CONCAT_NULL_YIELDS_NULL ON and testing.

SET CONCAT_NULL_YIELDS_NULL ON;

GO

DECLARE @NullValue varchar(10);

 

DECLARE @Alpha varchar(10) = ‘abc’

 

SET  @NullValue += @Alpha ;

SELECT @NullValue;

GO

 

– SET CONCAT_NULL_YIELDS_NULL OFF and testing.

SET CONCAT_NULL_YIELDS_NULL OFF;

GO

DECLARE @NullValue varchar(10)

 

DECLARE @Alpha varchar(10)= ‘abc’

 

SET  @NullValue += @Alpha ;

SELECT @NullValue;