Setting the DNN Data Source

You can set the Reports module to use the DNN Data Source, which retrieves data by executing a SQL query against the database configured for this DNN application. For example, the following query retrieves a table of all the pages in the site:

SELECT * FROM {oQ}Tabs

Aside from the standard SQL syntax, this data source accepts two special 'tokens' which will be replaced when the query is run. The "{oQ}" token is replaced with the object qualifier configured for the database. The "{dO}" token is replaced with the database owner configured for the database. Users can also use the longer "{objectQualifier}" and "{databaseOwner}" tokens. These tokens allow the queries to be moved between database configurations without changing the query.

Here's how to set the DNN Data Source:

1. Go to a Reports module.
2. Select Manage > Settings from the module actions menu.
3. Go to the Data Source Settings section of the Report Settings tab.
4. In the Title text box, enter a title for the report. Note: "Title" and "Description" are both displayed on the module when "Show Info Pane" is selected. The Title and Description are also included in export files when Export Content is selected.
5. Optional. In the Description text box, enter a description of the report.
6. In the Allowed Url Parameters text box, enter the URL QueryString parameters to be used in this query.
7. At Active Data Source, select DotNetNuke Data Source.
8. To set the SQL query, select the SQL query by clicking the Upload SQL File and select the file, which will then be displayed in the Query text box, from your computer - 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 view the Xml of the data source. If there is no errors, then the Xml Source used in XSLT transformations of the data is displayed in the Xml Source text box. A message will be displayed detailing any specific errors.
11. Optional. See "Setting Converters".
12. Click the Update button.

Parameters: The DNN Data Source supports parameterized queries. E.g. To retrieve the record in the Users table corresponding to the current user, use the query:

SELECT * FROM {oQ}Users WHERE UserID = @UserID 

See "Sample Report: Counting Users In Each Role".