Part One – Provision and Deploy to SQL Azure Reporting Services

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.

  • Badal Kotecha

    I get this error while publishing the report (post i had given the URL and credentials of my SQL Azure reporting services)

    Following is the error

    An attempt has been made to use a data extension ‘SQL’ that is either not registered for this report server or is not supported in this edition of Reporting Services

    I am using SQL Server 2008 (Not R2) BIDS and in my report one of the parameter is using a dataset that has inline query and the report data is returned by stored procedure.

    Am I missing something? Did you face this error before?

    Regards
    Badal

  • nick.harris

    Hi Badal, I have not encountered this one. Lets see if your msdn forum post yields any results.

    nick

  • http://www.eastcoastaircharter.com jack

    Thanks this was the only walk through I coudl find. It was a great help

  • nick.harris

    Hi Jack,

    thanks for the feedback. The Windows Azure Platform Training Kit – April 2011 Update has just been released which now contains a new SQL Azure Reporting Services Lab. It also contains a whole array of useful Labs for getting to know Azure. You can download it here – http://www.microsoft.com/downloads/en/details.aspx?FamilyID=413e88f8-5966-4a83-b309-53b7b77edf78&displaylang=en

    Kind Regards,
    Nick Harris