Using the Dallas Project in Excel and an SSRS Report

December 7, 2009

I read a while ago in Chris Webb’s blog about project Dallas which really got me interested. Quoting from the site itself: “Microsoft® Codename “Dallas is a new service allowing developers and information workers to easily discover, purchase and manage premium data subscriptions in the Windows Azure platform.
So what does it mean? Basically that you have a public data source in the cloud that you can connect to and use for your own applications.
And why am I so psyched about this? I have to admit that I am constantly looking for data I can use for demos. Yes, we all have Adventure Works to use, but sometimes you find you need something more. Dallas offers a great variety of information for you to use, from various sources like: infoUSA, Associated Press, NASA and many more.

 

We could go into what this means strategically for Microsoft with its cloud services, but for the moment, I’d like to talk about it on a more practical stand point – as in what you and I may gain from this. Dallas was planned to be used against PowerPivot, if you’d like to perform any analytics against it. Now I have to admit I have yet to install Office 2010 Beta (including SharePoint 2010 and Excel 2010 with PowerPivot added to it), but I really hoped that didn’t mean I couldn’t use the data on Dallas for an SSRS 2005 report.
So I went into my Dallas account, and created a query using the values given for example on the various fields connecting to the infoUSA database.

I decided to preview the data in its raw form in the form of a table. Dallas also enables you to consume the data in Atom 1.0 form.

 

Now, you can connect to XML as a data source in SSRS, but that means you also have to build a query against it in your dataset. Looking at XQuery language, made me think I had better use something friendlier… so I decided to use Excel 2007 instead.
I invoked the query as Atom 1.0, got my result and saved it on my PC. 

Your Excel 2007 (also in 2003) enables you to connect to an XML source and open it.    

               


I grant you, except for the data columns, you also get the data type columns.


 


So what I did was simply copy, paste and format the columns I needed from the main sheet to a new one.


 

Specifically, I needed geographic coordinates so I could check a third party surface chart report component for SSRS 2005.
Defining an excel file as a data source is also quite simple going from your Start > Control Panel > Administrative Tools > Data Sources (ODBC) and then continue with connecting to excel as a datasource for your SSRS report. And Voila! You have Dallas data to use in your SSRS 2005 report.

If I managed to get you curious about Dallas, then I really encourage to sign – up for it, as it’s free now, while it’s CTP1.

Add comment
facebook linkedin twitter email

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>

*