I've been working with Microsoft and a few partners lately for questions related to connection SAP Crystal Reports to Microsoft SQL Azure. If you are not aware, SQL Azure is part of the Microsoft Azure family of products which also offers the Windows OS in the cloud (i.e., Windows Azure) and the ability to securely connect an off premise application to data behind a firewall (i.e., Azure AppFabric). The total Azure framework allows application developers and organizations who are already using Microsoft technologies the opportunity to easily migrate parts of their deployments into the cloud.
During the aforementioned discussions, there are two immediate information needs identified: connecting to data in SQL Azure and hosting the SAP Crystal Reports runtime on Windows Azure. This post will focus on the first of these two.
Step One: Create your SQL Azure Instance
The first step is to create your instance of SQL Azure and configure your database. I won't walk through the details of how to login to Azure and configure your database. This is straightforward once you sign into Azure. I was able to complete the process in about 10 minutes.
As you can see from the above screenshot, you first create a subscription (e.g., SAP CR Testing). Then you create an instance of Azure (e.g., fuvcjhuha7). This appears to be a randomly assigned identifier. Once the instance is created, you will see that you have the common 'master' database, but you can also create a new database. For the purposes of this post, I will just use the 'master' database.
After creating your instance and deciding the database that you want to use for reporting, you will need to create a connection to the database from the desktop where the reports will be created. This can be done by either creating a pre-configured DSN or by creating a direct connection in SAP Crystal Reports. Both methods are demonstrated below.
Before moving on to creating the connections, you should make a note of the connection details for your SQL Azure database. You can click on "View" button under the Connection String properties while you have your database selected in the Azure Management Console.
Step Two (Option 1): Preconfigured ODBC Connection via User DSN
The second step is comprised of two options: create a User DSN for your ODBC connection or directly create your connection in SAP Crystal Reports. Either way, this connection allows you to create reports that includes data from SQL Azure. The process for creating a DSN is described below.
During the DSN creation process, you will need to define the location of the server to which you want to connect. As you can see from the screenshot above, you need define the 'tcp:' keyword in front of the servername to denote the protocol used to connect. SQL Azure only supports TCP-based connections.
Then, you will need to define the user details for the connection. SQL Azure requires that the server name be included as part of the login ID. If you do not include it, then your connection will not be successful.
As mentioned before, I'm connecting to the 'master' database for the purposes of this post; however, set your database as part of this screen. Skip the options on the next screen as they are not relevant for a basic connection. Be sure to test your connection before moving on to creating your reports.
Once you've defined your ODBC DSN, you can now move on to using the connection SAP Crystal Reports.
Once in SAP Crystal Reports you want to create a new connection via the Data Source Selection wizard. You will see that your User DSN is in the list of Data Source Names. Choose your DSN.
On the next screen, you will input all of your user credentials. Again, be sure to include the server name as part of the user ID. Click Finish and your connection has been created.
Step Two (Option 2): Define ODBC Connection via Connection String in SAP Crystal Reports
A more efficient alternative is to just use the connection strings provided in the SQL Azure Management Console and create a connection directly from SAP Crystal Reports without first creating the DSN.
Again, create your connection using the Data Source Selection wizard, but this time choose the 'Enter Connection String' option.
For the 'Connection String', I simply copied and pasted the ODBC connection string provided in the SQL Azure Management Console. Click 'Next'.
In the next screen, you need to define the user credentials for your connection. Remember to include the server name after the user ID as shown in the screenshot above.
Step Three: Create the Report
Now that you have your connection defined, you can create your report in SAP Crystal Reports just like normal.
When creating your reports or custom applications that take advantage of SQL Azure, ensure that you reference the following page that discusses General Guidelines and Limitations of the SQL Azure Database.
In closing, I'd like to hear about your experiences with creating reports using SQL Azure. If you run into any issues either with these steps or otherwise, feel free to email me (coy.yonce@sap.com), let me know on Twitter (http://www.twitter.com/coyyonce), or comment on this post.
In the coming weeks, I'll also post about how to get the SAP Crystal Reports runtime working on Windows Azure, whether its possible to connect to SQL Azure via the Common Semantic Layer, and details about connecting to other cloud databases.
The SAP Crystal Reports team is also planning a session for SAP TechEd 2011 discussing how to leverage SAP Crystal Reports and Microsoft Azure to deliver reporting as a service.
Thanks for reading, Coy.
2 Comments
