Created in 2006, Twitter is the latest in social networking achieved via micro-blogs (140 character limit) that
the users post thru' their twitter web sites or other means. These blogs (tweets) can be displayed by the
subscribers, also known as the followers. Twitter can be programmatically accessed through some of the
APIs's that exposes its functionality. This article uses the Twitter API to author a report using Microsoft
SQL Server Reporting Services 2008.
The procedure used in this tutorial is described in great detail in my recently published book, "Learn SQL
Server Reporting Services 2008'. This comprehensive book illustrated with screen shots was published by
Packt publishing inc (March 31, 2009). The Reporting Services is covered in over 50 hands-on exercises.
Working with this tutorial
In order to work with this tutorial you should get yourself registered with Twitter (join the flock!!) at
http://twitter.com/. You will be creating a username and password that will be used in this tutorial. My
username is
subbagiri and my password is
XXXXXXX (password crossed out here).
There are several API's which are really URL's that you can access with basic authentication. You may
read more about the ApI's here[
http://apiwiki.twitter.com/REST+API+Documentation].The URL access
is the simplest way to interact with Twitter. In this tutorial we will look at the updates made by some
of my followers. The reporting services uses the returned data after some massaging to produce a
report which can be later filtered or processed by the powerful SQL Server Reporting Services.
Preparing the datasource for the report
All that is needed is to access the URL, http://twitter.com/statuses/friends_timeline.xml which provides
the updates made by the followers in xml. When this page is browsed you need to provide the authetication
information you provided when you registered with Twitter. Make sure you use the IE 8.0 browser for the method
proposed here. If you use other browsers the information may be formatted differently and you may have to
adopt other methods of processing the file to generate reports.
The next figure shows a screen shot of the XML that is displayed when you finish providing the
authentication information. The nodes are shown in the collapsed state.
Each of the status node has children in its node as shown in the next figure. This just shows the details
in one status node.
This is a valid XML file. It also has the processing instruction in the first line of the previous figure.
Now save this file with a name of your choice with the xml extension. Herein it is saved as TwitterJay.xml saved
to the root folder of my web server, the localhost. With this, I can access this XML file simply as
http://localhost/TwitterJay.xml.
Creating the report of follower's updates using Reporting Services
We will be using the saved XML file
TwitterJay.xml in authoring a report.
Preparing to author a report
In order to use the Report Builder 2.0 to create a report you must start :
* the SQL Server Data Engine
* the SQL Server Reporting Services from the windows services, or from the Reporting Services configuration
Manager
* Bring up the Office 2007 styled Report Builder 2.0 using its shortcut
Report Datasource
The XML file on the local host is the data source for the report. This may be configured as shown in the
following figure.
The authentication is provided in the credentials page of the Data Source dialogue as shown in the enxt
figure.
Since the report data source as used here requires a windows authentication you must add the username as a
windows user. Otherwise you will be unable access data source.
Report Dataset
The report data set is obtained by querying the data on the localhost. In this case querying the XML data
source on the localhost described earlier. The querying must satisfy the data extensions used by the
Reporting Services.
The XML data that was derived from the Twitter API needs to be massaged before it can be used for reporting
services. In the last section some of the observed problems are described as well as some of the artifacts
introduced during massaging. Here the query used in the Query Designer in the Report Builder tool is
shown in the next figure. The screen shot of this was taken after massaging and after running the query. The
results are shown in the bottom pane.
The next figure shows the fields in the Twitter API that will be going into the report. This is accessed
by the list on the left in the Dataset Properties page.
Report design
Get rid of the default New Table/Matrix icons in the report builder designer. Drag and drop a table. Do not use the
report wizard. From the Dataset1 shown on the left ,drag and drop fields into the table as shown. You need
not use all the fields. For demonstration some of the fields are used.
Run the report from the main menu. The report gets displayed as shown in the following figure.
Some minor formatting (changing the font weight and font color of the column headers) has been
made.
Massaging the XML file
The XML file as saved on the loaclhost used as is may not be acceptabe by the Query Designer. You do get
error messages as t whre you might have a problem. Although they are suefu, sometimes the error message
may not make sense. Use a text editor to clean up the XML file, what I call massaging.
The following procedure must be followed:
* The XML file obtained by URL accesing the Twitter API file is a valid xml file. However for it to be
processed by the reporting services you need to remove the XML processing instruction line from the xml.
* The remaining XML fragment must be enclosed between <Query></<Query> tags before feeding it to the Query
Designer in the Report Buidler tool. <query></query> will not do.
* If you find the hyperlink fields in the XML file such as [HYPERLINK \l ""-]you must remove them. This is important
since you will be copying the browser display and pasting it into a text editor.
* Make sure that the text with the hyperlink is on a single line in your text editor.
For example:
<profile_background_image_url>http://static.twitter.com/images/themes/theme9/bg.gif</profile_background_imag
e_url>
will not be accepted by the query designer.
* Replace the character "&" in the query by any suitable character herein "and" was used. SSRS does not like it.
* Further massaging may be needed depending on the xml returned rows.
Summary
The Twitter API makes it very easy to generate stunning (although not quite so here), interactive reports using the SQL
Server 2008 Reporting Services. This can be uploaded to the Report Server and viewed using the Report Manager. For
successfully creating a report, the XML returned by the Twitter API may need further processing. The steps involved in
creating the report is fully explained. The book y the author cited in the introduction describes the process in greater detail.
