Monthly Archives: March 2011

Part One – Provision and Deploy to SQL Azure Reporting Services

Posted on by
4

In many enterprise applications one often overlooked, in respect to their importance, requirement are reports. Reports are typically consumed by upper management of the organisation to determine the health of their organisation through the organisational data collected and crunched by their enterprise app. Fortunately Azure provides SQL Azure Reporting, currently in Limited CTP, to extend the already familiar development experience of SSRS to the cloud.

This is part one of a series on SQL Azure Reporting Limited CTP.  This post will focus on deployment of an SSRS report to the SQL Azure Reporting Limited CTP.  Following posts will detail how to programmatically consume these reports.

Sign Up for a Free account to Access SQL Azure
If you would like to follow along with this post there are currently two ways you can try SQL Azure FREE of charge:

  1. Sign up for this limited-time promotion, and you’ll get TWO 1GB Web Edition databases for one month. No credit card information is required. To get started, insert promo code SQLAZURE25
  2. Get a 1GB Web Edition database for no charge for 3 months. This account requires a credit card, as any additional usage above 1GB will be billed at standard rates. After the Free Trial period, you can switch to a paid account without losing your data

For details please on the FREE access pelase see www.sqlazure.com/getstarted and visit www.sqlazure.com/community for additional resources.

Once you have your account you will need to Sign Up for Access to the SQL Azure Reporting Services CTP

  1. In the Azure Management Portal click on the Reporting in the left nav bar and follow the instructions

    Sign Up for SQL Azure Reporting Services CTP

    Sign Up for SQL Azure Reporting Services CTP

  2. At some point later (in my case 1 to 2 weeks) you will receive an email with your invite and access code for the beta. Once you receive this you need to login the the portal as per above and then press the 2. Provision option and select an appropriate subscription

    Provision step 1

    Provision step 1

  3. Supply the token code provided on email

    token code

    token code

Create your Reporting Project
The development experience in VS2010 with regard to Reporting projects synonymous with Windows Mobile development i.e not supported, unless you want client reports *.rdlc. To me this raises similar questions to that of the huge leap forward seen in Windows Mobile to Windows Phone 7 – Will we soon see a similar leap forward in support for Reporting in the next release of Visual Studio? – I dont know the answer but if I had to take a pick project Cresent is looking like it will be a contender.

This being said that the way to build your reports is using SQL Server Business Intelligence Development Studio (BIDS) which is installed with SQL Server 2008 as follows:

  1. To Start BIDS goto Start >> All Programs >> Microsoft SQL Server 2008 R2 >> SQL Server Business Intelligence Development Studio
  2. Create Report project File >> New Project >> Business Intelligence Projects >> Report Server Project

    Create Report Project

    Create Report Project

  3. Create Shared Data Source  to retrieve content from your SQL Azure Database.  In the  Solution Explorer right click the Shared Data sources folder
       >> Press Add New Data Source
    • Note: At the time of writing this post please note that the SQL Azure Reporting CTP is currently only hosted in our South Central US datacenter – we strongly recommend that you host any servers and databases you might use for your reporting testing needs at this datacenter. You will be charged for bandwidth usage for data transfers to/from the South Central US datacenter should you host your data that you report against outside of this datacenter. Also, co-locating with the service will provide optimal performance.
    • Note: While Shared Data Sources are supported Shared Datasets are not currently supported in this Limited CTP
  4. Select Microsoft SQL Azure in the Type dropdown of the shared datasource properties and use the Edit button to configure and test your connection string to your Database.

    Shared Data Source Properties - General

    Shared Data Source Properties - General

  5. In Solution Explorer >> Right click on the Reports folder >> Select Add a New Report and define a report against your shared datasource.  For links to resources about learning to author reports, see SQL Azure Reporting Resources.  The image below illustrates the report design view against my shared data source.

    Report Definition

    Report Definition

  6. Note: Once you have finished defining your report you can press the preview tab (next to Design tab in the above image) to preview the report.

Deployment

  1. In a browser go to the Azure Management Portal >> select Reporting from the left Nav and then expand out your report subscription to reveal the Web Service URL and username configured through the initial provisioning process.  The image below highlights the two

    Reporting Service Details

    Reporting Service Details

  2. In Solution Explorer >> Right click your  reporting project >> select Properties
  3. Copy the Web Service URL text from the Portal as per image above in step 1 and format the url to be https://<url from management portal>/reportserver    – Note: the https  and /reportserver.  If you are getting an issue when deploying as follows When deploying the project or an item in the project, you get the error message: Could not connect to the report server …. Verify that the TargetServerURL is valid… the common cause is not formatting the url correctly.  Once your done it should be in a form similar to https://fghijk5678.database.windows.net/reportserver
  4. Press Ok on the properties dialog
  5. To Deploy to SQL Azure Reporting go to Solution Explorer >> Right click your reporting project >> Select Deploy
  6. A dialog will popup prompting for your reporting services login.  This will be the username as shown in the image in step 1 above and the password you supplied during the provisioning process.  Enter them and press Ok

    Deploy to SQL Azure Reporting. Report Portal Username and Password

    Deploy to SQL Azure Reporting. Report Portal Username and Password

  7. If deploy was unsuccessful please see the Troubleshooting section towards the bottome of this post.

Verifying your deployed Report

  1. Take the URL configured in step 3 above i.e it should be in the form https://<url from management portal>/reportserver   and append /login.aspx e.g the final form will be https://fghijk5678.database.windows.net/reportserver/login.aspx 
  2. Browse to your report server using your browser and when prompted use the same username and credential supplied when deploying your reporting project in step 6 above.

    Login

    Login

  3. Once logged in Browse to your reports folder and select your report to render
    Rendered Report - SQL Azure Reporting

    Rendered Report - SQL Azure Reporting

Troubleshooting Deployments

  1. Check out the SQL Azure Reporting Limited CTP release notes for solutions to common problems

Documentation and Feeback

  1. Documentation for getting started and using the SQL Azure Reporting CTP can be found in the SQL Azure library on MSDN here
  2. You can provide us feedback through the Connect site (here) and filing a Bug or Suggestion (Select Category = “SQL Azure Reporting) or by visiting the SQL Azure forum. 
  3. To vote on feature requests and make suggestions for V1 features, please visit http://www.mygreatsqlazurereportingidea.com/

Summary

This post detailed how to provision, deploy and view reports to/on SQL Azure Reporting Limited CTP.  The next post in this series will detail how to programmatically consume these reports.

WP7 Nights at the Round Table – March 31

Posted on by
Reply

Hi there Windows Phone 7 Developers in Sydney Australia,

Time to get out from infront of the computer and into the bar to talk about Windows Phone 7 and Azure Dev :)

Event:
WP7 Nights at the Round Table – March 31st Sydney

Purpose:
Let’s get together for some drinks to trade WP7 Dev stories, demos or seek free advice from other devs to help get your app off the ground and into Marketplace. This event will be informal, around bar tables, so bring along your device or laptop if you wish to show people what you have been up to.

Let us know your coming:

If your on LinkedIn please indicate your attendance here – Windows Phone 7 Developer Dev Drinks March 31st or in the comments section below.

Date, Time, Location:

6-8pm
Thurs 31st March 2010
City Hotel,
Corner of King and Kent St, Sydney CBD.

Hope to see you there,

Nick Harris :)

Geography STDistance and Radial Search

Posted on by
3

I am currently whittling away, unfortunately rather slowly, on a new blog post series that makes use of the spatial data capabilities provided by both SQL Azure + SQL Server 2008 R2 that then exposes the content to be consumed by a WP7 client.

In the meantime while I chip away on that I was sitting wondering why my recent flight to the US was faster from San Fran to Sydney then from Sydney to LA. Taking away the variables such as head/tail winds, airspeed and flight plan, yes all important factors, we can quite easily get a raw view of the direct distance from San Fran to Sydney and LA to Sydney using spatial data and the OGC methods on the geography type – cool :)

But first we need some data i.e the latitude and longitude of the three airports. You can quite easily retrieve these manually from bing maps by saving the points in your My places with a Right Click >> Add Push Pin then once you have all three clicking Actions >> Export you can retrieve the lat + long of each place. If you wanted to automate the process you could using the Bing Maps API to geocode the address to your lat/long

The result of the manual export from bing maps is as follows, we are only interested in the lat and long for this post:

<?xml version="1.0" encoding="utf-8"?>
<gpx xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" version="1.1" creator="Bing Maps" xmlns="http://www.topografix.com/GPX/1/1">
<metadata>
<name>Unsaved places</name>
<desc />
</metadata>
<wpt lat="37.6235624402761" lon="-122.383551299572">
<name>San Francisco Int'l Airport</name>
<desc>San Francisco, CA 94128</desc>
</wpt>
<wpt lat="33.946468" lon="-118.384506">
<name>Los Angeles Airport Marriott</name>
<desc>5855 W Century Blvd, Los Angeles, CA</desc>
</wpt>
<wpt lat="-33.936897" lon="151.168334">
<name>Sydney Airport Medical Centre</name>
<desc>International Terminal, Mascot, NSW 2020</desc>
</wpt>
</gpx>

Calculating the distance:

-- using the geography datatype for round earth calculations rather then geometry(flat earth)
DECLARE @SanFran geography
DECLARE @LA geography
DECLARE @Syd geography

-- define the points
SET @SanFran = geography::Point(37.6235624402761, -122.383551299572, 4326)
SET @LA = geography::Point(33.946468, -118.384506, 4326)
SET @Syd = geography::Point(-33.936897, 151.168334, 4326)

-- calculate the distance between points in km
SELECT @Syd.STDistance(@SanFran)/1000 as SydToSanFran
SELECT @Syd.STDistance(@LA)/1000 as SydToLA

Results:
Sydney to San Fran: 11936.3 km
Sydney to LA: 12053 km

From this simple calc we can see that San Fran is in fact closer to Sydney then LA.

While this is a simple example you can apply what you have learnt here to perform radial searches. e.g if you have a table of Airports with their co-ordinates located in a Location column and wanted to see what airports were within a 13000 km radius you could do:

DECLARE @Origin geography
SET @Origin = geography::Point(<YourLat>,<YourLong> , 4326)

SELECT *
FROM dbo.Airports
WHERE Location.STDistance(@Origin) <= 13000000

Enjoy,
Nick

WP7 Nights at the Round Table Slide Deck Feb

Posted on by
Reply

Hi there,

For those of you that were asking for the links here is the slide deck, that was presented on my phone :)

All in all a great night and we seen several cool new WP7  apps some of which were supported by Azure services.

The app of the night was What I Ate and Drank – a WP7 app with services and site both up on Azure. Check it out here on Zune its free.

Note: we are in need of a Logo if you got the design skills please feel free to create our group a free logo – creative inspiration could include WP7, Knights, Azure, Cloud Power, Sydney

Kind Regards,
Nick Harris