Tau Liang's Blog

SQL Server codename “Denali” CTP3, including Project “Crescent” is now publically available. This is awesome!

Project Crescent Demo

What’s New in Microsoft SQL Server Code-Named “Denali” for Reporting Services

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI211

BI Power Hour

http://channel9.msdn.com/Events/TechEd/NorthAmerica/2011/DBI201

It is pretty exciting to see it announced to the general public.  It greatly simplifies the ways user interact with data. A teaser video is available online for a sneak preview of its capabilities.

Candlestick is a pretty useful technical indicator for trading equities. Some people built business around it. And it is pretty straightforward to create candlestick charts in Report Builder 3.0 (RB3.0)  for reporting services. Today I’ll give a five-minute walkthrough of how to do it in RB3.0.

Data Preparation

1. Download data from Yahoo.
2. The download is usually a CSV file, save it on disk, use SQL Server Management Studio to import the data into a database table (e.g., table SPY under database AdventureStocks. Make sure that data types of the columns are updated appropriately (the default is string).

Create Report

1. Start RB3.0, pick “New Report”.
2. Create a new data source connecting to the database on localhost.
3. Create a dataset from querying the SPY table.
4. On RB3.0 canvas, insert chart, select “Candlestick” under “Range” type.
5. Drop “Date” to X axis and it will show up in “Category Groups”.
6. Drop “Open” to “Values”.
7. Select data series on chart and configure its properties as follows:
8. Adjust Minimum and Maximum axis values in Vertical Axis Properties to fit with the OLHC values of SPY.
9. Preview the report and it is all set.

RDL Specification

• 2000
• 2005
• 2008
• 2008 R2 (XPS)

2005 White Papers

• http://msdn.microsoft.com/en-us/library/aa964128%28SQL.90%29.aspx
• http://msdn.microsoft.com/en-us/library/aa964129%28SQL.90%29.aspx
• Integration (2005 SP2) with SharePoint

2008 White Papers

• http://www.microsoft.com/sqlserver/2008/en/us/wp-sql-2008-reporting-services.aspx
• http://msdn.microsoft.com/en-us/library/ff679930.aspx

SQL Server Training Kits

• SQL Server 2008 R2 Training Kit
• SQL Server 2008 Training Kit

Books

• Microsoft SQL Server 2008 Reporting Services
• Applied Microsoft SQL Server 2008 Reporting Services
• Microsoft SQL Server Reporting Services Recipes: for Designing Expert Reports

SQL Server Reporting Services 2008 R2 added support for SQL Azure data source. It is nice to have as this makes it easier to put one foot of your report into the cloud. Sometimes, report server throws this mysterious (and much dreaded) error:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 – Could not open a connection to SQL Server)

It usually happens like this: report works in Report Builder, report works in BIDS, SQL Server Management Studio has no trouble whatsoever connecting to the data source, but, once the report is deployed to the server and when user tries to run it, out of the blue, this error jumps out.  As the message suggests, it is a network issue. Usually, the SQL Azure data source has to be access via a proxy (e.g., Reporting Services get to it through ISA client installed on the report server).  You can configure the Report Server service to run under any of these account types:

• Least-privileged Windows user account
• NetworkService
• LocalSystem
• LocalService

And the service account is the one being used to get across the proxy and reach out to the SQL Azure server sitting in the cloud.  If your reporting server is running behind the firewall, more often than not, only a “Least-privileged Windows user account” has permission on the proxy server to sneak out. Thus, if Report Server is using any of the other three as the service account, chances are good that it won’t be able to get hold of a connection to SQL Azure server. I hope the workaround is obvious to you at this point now, yes, set the service account to a least-privileged Windows user account which happens to have permission on the proxy server.

1. http://technet.microsoft.com/en-us/library/ff519560%28SQL.105%29.aspx
2. http://msdn.microsoft.com/en-us/library/ms160340.aspx

As a fan of data, I track foreclosure numbers as a hobby. Last month, nation wide default notices off by 27 percent, but bank repossessions set new monthly record. I don’t have any more detailed and recent data to plot an interactivity map like the one on NPR, but I managed to find some data from Q3 2009 on RealtyTrac. Let’s see how easy it is to build a map using Report Builder 3.0.

First, we’ll download the data from the source. Start Microsoft Excel and go to “Data”—>”From Web” and enter the URL in the address bar: http://www.realtytrac.com/foreclosure/foreclosure-rates.html. Scroll down to the “US Foreclosure Market Data by State – Q3 2009” table. Click on the arrow to select the table and import data.

Second, save the file as an Excel 2000-2003 work book on disc, say, as Q32009ForeClosure.xls on D:\. Create a system data source named “Foreclosure” pointed to this file.

Now, we can launch Report Builder 3.0 to start the Map Wizard. Select “USA by State Inset”. Select defaults on next screen.

And pick Color Analytical Map on next screen.

Add a new analytical dataset based on data source “Foreclosure”

Run query “select * from [Sheet1$] to make sure that it works.

And specify the matching fields for spatial and analytical data. In this case, the field in spatial dataset is “STATENAME” and the field in analytical dataset is “State_Name”.

Pick the theme, as well as the field to visualize, and the color rule. Get rid of the legends etc and leave only the color scale on the map. Add title “Foreclosure Map”.

Click on the map, a property panel will show up on the RHS. Right click on “Polygon Layer” and select “Polygon Color Rule”. Make sure that the correct data field is picked, start and end colors are right. In the case we have, we’ll start with something red and end it with blue as the smaller the number, the more severe foreclosure condition is in the state. Change the “Distribution” to have 10 subranges, with them starting at 0 and ending at  3000.

Want to see some tooltips? Go to “Polygon Properties”—>”General” and add it there. Let’s add something like

   1: ="In "+ Fields!State_Name.Value + ",1 in every " + Fields!ID1_every_X_HH__rate_.Value.ToString() + " house is in some stage of foreclosure process."

Hit the “Run” button to get a preview. Hover the mouse on some of the states to check out the tooltips.

In less than five minutes, we build this colorful map visualizing the foreclosure conditions in different states across the US. The completed RDL file along with the Excel data file is available in archived format here.

Data Reference

1. http://www.microsoft.com/downloads/details.aspx?familyid=6ccd8427-1017-4f33-a062-d165078e32b1&displaylang=en

Microsoft SQL Server 2008 R2 Report Builder 3.0 RTM build is available here: http://www.microsoft.com/downloads/details.aspx?FamilyID=d3173a87-7c0d-40cc-a408-3d1a43ae4e33&displaylang=en

It can be also downloaded part of the SQL Server 2008 R2 feature pack.

http://www.microsoft.com/downloads/details.aspx?familyid=CEB4346F-657F-4D28-83F5-AAE0C5C83D52&displaylang=en

Tutorials:

http://technet.microsoft.com/en-us/library/ee706623.aspx

1. http://blogs.msdn.com/robertbruckner/archive/2010/03/21/reporting-services-recipes-book-released.aspx

In a rush? Laptop doesn’t have a solid network connection to corporate network for an important report for traveling executives? Make your report portable.

There are two not-so-often-mentioned easy ways to make your reports portable. Use embedded XML in report, or use an ODBC data connection to local data stores. More details could be found on MSDN.

1. http://msdn.microsoft.com/en-us/library/aa337458%28v=SQL.90%29.aspx
2. http://msdn.microsoft.com/en-us/library/ms156450.aspx