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:
- 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
- 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
Once you have your account you will need to Sign Up for Access to the SQL Azure Reporting Services CTP
- In the Azure Management Portal click on the Reporting in the left nav bar and follow the instructions
- 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
- Supply the token code provided on email
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:
- To Start BIDS goto Start >> All Programs >> Microsoft SQL Server 2008 R2 >> SQL Server Business Intelligence Development Studio
- Create Report project File >> New Project >> Business Intelligence Projects >> Report Server Project
- 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
- 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.
- 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.
- 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.
- 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
- In Solution Explorer >> Right click your reporting project >> select Properties
- 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
- Press Ok on the properties dialog
- To Deploy to SQL Azure Reporting go to Solution Explorer >> Right click your reporting project >> Select Deploy
- 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
- If deploy was unsuccessful please see the Troubleshooting section towards the bottome of this post.
Verifying your deployed Report
- 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
- 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.
- Once logged in Browse to your reports folder and select your report to render
- Check out the SQL Azure Reporting Limited CTP release notes for solutions to common problems
Documentation and Feeback
- Documentation for getting started and using the SQL Azure Reporting CTP can be found in the SQL Azure library on MSDN here
- 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.
- To vote on feature requests and make suggestions for V1 features, please visit http://www.mygreatsqlazurereportingidea.com/
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.