The Reports module provides a way to display the results of a database (SQL) query inside a module on your page.  In order to make good use of this module, you’ll need to be familiar with writing SQL Queries against a SQL Server database.  The results of your query are displayed by using one of the built-in report “visualizers.” At this time, the available visualizers are:

Grid Visualizer

Displays results in a Grid/Table format

HTML Template Visualizer

Allows simple customization of each row of output by using “Tokens” for each column name in the query.  For example: [FirstName] would be replaced by the actual value of the FirstName column in the query.

XSL Transformation Visualizer

Provides more complex control over the output by performing an XSL transformation on the results.

Once you add the Reports module to a page, you’ll see a message indicating that there is no data source configured.  Let’s remedy that!  Click on the module settings icon/link and scroll down to the “Reports Settings” area to configure this module.

Let’s create a report that simply displays a list of users on our site in a grid format, and allows the user to sort the grid by clicking on the column header.

The important fields are:

Active Data Source

Where is our data coming from?  The default “DotNetNuke Data Source” will use the same SQL Server database that your DNN site is using, which will allow us to retrieve data such as users, pages, statistics, etc.

Query

This is where you enter your desired SQL Query.  Once you have entered a query you can click on the “Test Data Source” link to execute your query and see a count of the results.

Show Info Pane

The “info pane” will display the “Title” and “Description” above the report results.

This query will select FirstName, LastName, DisplayName, and Email from the Users table in the database where the email address contains @webascender.com.  Using the Grid Visualizer the data will be returned in a tabular format.


Other Reports Module features

Passing parameters to your SQL query

It is possible to filter the data displayed by passing parameters in the query string.  For example if you wanted to just show information about a user with the id of 5 you could build a query string like this http://yourwebsite.com/Reports.aspx?userid=5  where Reports.aspx is a page on your site with the reports module on it.  You would then add userid into the field named Allowed URL Parameters.  You can then use the @url_userid in your query.  Example: select * from users where userid=@url_userid.  

DotNetNuke will prefix any variables in the allowed URL parameters field with URL_ and you use it in your query by preceding it with the @ notation.

HTML Decode & Encode

Use this if you have encoded HTML in one of your fields.  Simply type in the column name of the field and the reports module will decode that HTML before rendering it to the screen. Vice versa for encode.

Paging & Sorting

Adding paging and allowing the user to sort using the header columns is as easy as a click of a couple buttons.

 


Newsletter Sign Up

Was this article useful?

Sign up for the Web Ascender Newsletter and get tips for marketing your website, innovative new project launches, and a desktop wallpaper hand-crafted for you, all to your inbox each month.

Share This Article:

comments powered by Disqus
TEST
Request A Quote

Have a project in mind?

TELL US ABOUT IT »