SQL Server Reporting Services report with SQL Azure
Today we are going to look at how to create a report with SQL Server Reporting Services (SSRS) and Microsoft SQL Azure. In this manner we are using the database that is housed on the cloud server whereas we will be running SQL Reporting Services in the local environment. For today’s topic we are using Visual Studio 2008 with Microsoft SQL Azure RTM.
Go ahead and launch Microsoft Visual studio. Select File – New – Project. Next under Business Intelligence Projects, go ahead and select Report Server Project Wizard, given it a name and then click OK. This is shown in the following screen capture.
This will invoke the report wizard of as shown below. Using the report does serve we can select a data source, build a SQL query, design and create a SQL report.
Next the report wizard will take you to the Data Source dialog box. This is where you will select New data source, choose Microsoft SQL Azure as data source type, give it a name and then proceeds to build the connection string. We have shown this in the following screenshot.
After you click Edit, you can configure your SQL Azure cloud server connection information. This includes the Server name, SQL Server Authentication information, specific database that you’re going to connect to. Next you click OK. The following image displays our connection information.
You may get another dialogue box, where you may have to verify the login information. Here’s a screen shot of this right below
Moving onto the next step which is going to be the Query Builder. Go ahead and click Query Builder. This will take you to the Query Designer screen. This is where you can select the tables and the fields that will become part of the SSRS report. You also have the ability to add filters which we’re not going to use for now.
The following two screen captures display what we are talking about.
After you have selected the fields and clicked OK, you can see the actual SQL Azure Select statement in the next screen. Go ahead and click Next.
Next up in the Report Wizard is to actually select the report type. You can either choose Tabular or Matrix format. We are going to go ahead and select Tabular and click next.
This is shown below.
After this you can actually a group of data by different report sections if you like. You can group either by Page, Group or by the Details section. We’re not going to select anything for now and simply click Next. This is what it looks like on our computer display.
Now we will choose a table style from different custom choices. For this SQL Azure demo we are going to use Ocean and then click Next.
The report is almost complete. We would like to go ahead and deploy this SQL Azure report to a report server. For our case we are using a local report server. In your scenario it may be different so please choose accordingly. Go ahead and click Next.
We are almost done with the SSRS wizard. It is asking us to give the report a name which we do. You will notice that it is also including a little bit of summary regarding the SQL Azure server location and some of the choices that we had made to earlier.
So here is what it looks like so far.
We finally have a design view of our report pulling data from SQL Azure. The report is shown in the middle of the section. As you can see we have other screens like the Report data (all the selected fields), on the left we have the Solution explorer on the right side. The following images displays all these sections
When you select the Preview tab, the report tries to make a connection to the SQL Azure server one more time. It is asking for the login information which we provided and then click View report.
As you can see, the report is complete. We have the report name, field information and all the underlying customer data. Remember that all this information has been pulled from the Northwind database in the Azure cloud server. Right above the report, you will see options like record navigation, printing capabilities and export functionality to other formats.