Saturday, 26 November 2011

Getting Started with SQL Azure Reporting Service–Part 2

This is a continuation of previous post (Getting Started with SQL Azure Reporting Service–Part 1).

Configuring Reporting Server URL

Before publishing the report to SQL Azure Reporting Server, you required to configure the Web Service URL which got from Azure Management Portal (Get the Web Service URL section).

Step 1: Right click the project and select Properties. Change the TargetServerURL value to Web Service URL got from Management Portal.

Step 2: Click OK.

Publishing the Project/Report

We can publish a single report or the whole project to Reporting Server. When publishing project, it publish all the reports to the Server.

Step 1: Before publishing any report to Reporting Server, test the report whether it works fine. This can be done by right click the report and select Run. It opens a Report Preview window with the report output.

Step 2: Open the report and verify the Data Source whether it is pointing to SQL Azure database. Follow Step 3 to 5 below.

Step 3: On the Report Data window (after opening the report), right click the data source under Data Source node and select Data Source Properties. (Note: here we are using embedded data source)

Step 4: In the General tab verify the data source which points to the report. Select Microsoft SQL Azure from the Type drop down and provide Connection String for connecting SQL Azure. (Note: This connection string will be used for connecting the SQL Azure database when running the report from the cloud. So if the system is not able to connect SQL Azure no issue - when TCP 1433 outbound not open. Providing connection string correctly is fine)

Step 5: Select the Credentials tab and select the Use this user name and password. Enter the User Name and Password used in the SQL Azure server connecting and click OK.

Step 6: Build the report and check if any errors. If no error, then publish.

For publishing the project, right click the project and select Deploy. The Visual Studio will deploy required Data Source and the reports from the project.

For publishing a single report, right click the report and select Deploy. The Visual Studio will deploy the report with the Data Source to Reporting Server.

Step 7: The Visual Studio will pop up a window Reporting Services Login for accessing the SQL Azure reporting server. Enter the login credential which was given in Step 4 of Creating a New SQL Azure Reporting Server section.

Visual Studio will build the project/report and upload the reports to the SQL Azure reporting server. The output can find in Visual Studio Output window.

Note: If the data source is not properly connected with SQL Azure, it will provide below error. Verify the connection string and credentials (as per Step 5) and deploy again.

Step 8: If the status of the deployment is successful (Deploy: 1 succeeded, 0 failed, 0 skipped), then we are ready to verify the reports in Azure portal.

Verifying the Reports in SQL Azure Reporting Service

Step 1: Open the Azure Management Portal and select the Reporting Server of Reporting section.

Step 2: Click the Link button, it will open SQL Azure Reporting Preview in browser.

Step 3: Provide the credential given in Step 4 of Creating New Reporting Server section.

Step 4: The browser will redirect to reporting project folder. Select the folder.

Step 5: It will redirect list of reports. Select the report.

Step 6: The browser will show the report on the screen.

If any error such as "Cannnot create a connection to data source..." as defined before, verify the data source and deploy again.

Note: If any changes in the report, better to delete the report from the Management portal and deploy the report again. When I deploy the report without deletion, the latest report is not shown.

download the working project implemented in this example here.

0 Responses to “Getting Started with SQL Azure Reporting Service–Part 2”

Post a Comment