How to create a SQL query statement that can be embedded into a Crystal report

The following procedure describes how to create a SQL query statement that can be embedded into a Crystal report, running from your client desktop.

Step	Action	Result
1.	Install SQL View as described in the Installation section and configure the Web Part to make sure it can communicate with the SharePoint content database as described in the Configuration section.
2.	Go to the page where SQL View is installed and generate the SQL query statement by completing as shown here:	In the Select Site drop-down list, pick the site where the Task list was created. Select “Tasks” in the Select List drop-down list. Enter a name in the View Name text box. To create a view in the SharePoint Content database, this view name must be unique. If a view with a similar name already exists, then the Web Part identifies it and view is not created. Note that the existing views are not deleted or dropped. Select the Validate Lookup Fields check box so the query will perform the joined query to get the name of the assigned user. Select the fields you want returned in the query. Check the Generate View in Database check box and click the Generate button.
3.	After clicking the Generate button, check the database to make sure a view was created, using SQL Enterprise Manager as in this example:
4.	Create a data source on your desktop that provides the connection to the SharePoint content database where the task list exists. You can do this on a Windows XP desktop by selecting Start > All Programs > Administrative Tools > Data Sources (ODBC).
5.	Add a new data source, using the SQL Server driver.
6.	Enter the name of the data source and select the appropriate SharePoint server name.
7.	Fill in the account logon with the appropriate permission to read the content database.
8.	Select the correct table name for the SharePoint content database.
9.	Test the data source you just created to make sure that access permission to the table is working correctly. You should now have a new DSN (WSS-RD1 in this example) listed in the ODBC data sources list.
10.	Startup Crystal Reports, create a new report and select a new ODBC driver.
11.	Expand the ODBC tree, select the DSN that you just created in step 3, supply the correct user information, and then select the SharePoint content database.
12.	Once the connection to the database is completed, you are presented with the tables, views and stored procedures in the SharePoint content database. Expand the Views node in the tree, and select the SQL View name that was created in step 1 above (TaskView in this example).
13.	You will now be able to see the fields that were selected in SQL View above, which is ready to be dragged and dropped on to the report Design View.