You can set the Microsoft SQL Server Data view in the Source option on the Active Date Source field for the Reports module. Note: This Data Source does not support the "{oQ}" and "{dO}" tokens since it is assumed the queries are for databases that are not related to DNN.

1. Go to a Reports module.

2. Select Manage > Settings from the module actions menu.

3. Go to the Data Source Setting section of the Report Settings tab.

4. In the Title text box, enter a title for the report.

5. Optional. In the Description text box, enter a description of the report.

6. At Active Data Source, select Microsoft SQL Server Data Source. This displays the following settings:

7. At Configuration Mode select one of the following two options:

   • Select Generate a Connection String to generate a connection string based on options entered.

     1. In the Server Name text box, enter the name of the server to connect to.

     2. In the Database text box, enter the name of the database to connect to.

     3. Choose from the following authentication options:

        • At Use Integrated Security, mark the check box to use the user account on the web server that is running DNN (usually the NT AUTHORITY\NETWORK SERVICE user account). The system will determine the exact name of the account that will be used to authenticate with the SQL Server.

        • Enter the User Name and Password of the user that will be used to authenticate with the SQL Server.

   • Select Use the Connection String I Enter and manually enter a complete ADO.Net Connection String for connecting to a Microsoft SQL Server instance into the Connection String text box.

8. To set the SQL query, click Upload SQL File and select the file containing the SQL query, from your computer, which will then be displayed in the Query text box - OR - Enter the SQL query into the Query text box.

9. Optional. Click the Test Data Source link to test the data source. If the data runs successfully, the number of records returned will be listed. A message will be displayed detailing any specific errors.

10. Optional. Click the Show Xml Source link to view the Xml of the data source. If there are no errors, then the Xml Source used in XSLT transformations of the data is displayed in the Xml Source text box. If there is an error, then details of the specific issue will be displayed as an error message.

11. Optional. See "Setting Converters".

12. Click the Update button.

Tip: Both the Title and the Description are displayed on the module when Show Info Pane is checked. The title and description are exported with the report when Export Content is selected from the module actions menu.

Parameters: The Microsoft SQL Server Data Source supports four default parameters and a set of custom user defined parameters. See "Overview of Data Sources".