DNN websites, modules, skins and support

Menu: Blog

Presenting a list of events with the DotNetNuke reports module

Presenting a list of events with the DotNetNuke reports module

The reports module is an excellent way to present data from your DNN installation in an ad hoc fashion. One can use it for any table in and outside the DNN installation.

In this example we will present a list of events from the events module. We start with a simple select query to process the list of events:

SELECT  EventName ,
        CONVERT(VARCHAR, EventDateBegin, 103) AS BeginDate ,
        CONVERT(VARCHAR, EventDateEnd, 103) AS EndDate ,
        CONVERT(VARCHAR, EventTimeBegin, 108) AS StartTime ,
        Duration / 60.0 AS Duration2 ,
FROM    dbo.Events
ORDER BY EventDateBegin

With a simple template like this:

<div>[EventName]<br/>From: [BeginDate] until [EndDate], at: [Tijdstip], 
Duration: [Duration2] hours<br/>[EventDesc]</div>

We get the following result:

this is a test event
From: 30/10/2010 until 30-10-10, at: 13:00:00, Duration: 4.000000 hours
This is a test event with some formatting. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Mauris dapibus lacus ut nisl mollis tristique?

test event 2
From: 02/11/2010 until 02-11-10, at: 09:00:00, Duration: 33.000000 hours
two day event...

nice day today
From: 09/11/2010 until 09-11-10, at: 09:00:00, Duration: 3.500000 hours
A simple event, with a small description

One quickly sees that the results are a bit disappointing, partly this was caused by my lack of understanding about the way the fields in the events table are filled (as it turns out, we only need the fields: ‘EventTimeBegin’ and ‘Duration’), but more importantly, we have three cases that need different formatting. The situations we have:

  1. An event, on a single day, with start time and end time, in the database we use: start day, start time and duration, end day only clutters the visual presentation, unless, the end time lays on the next day, in this case we do want to present the end date like a multi-day event.
  2. Multi-day event, with a specific start time. In the database we use start date, start time, and duration (e.g. a conference). Given the logic of the events module, we have to calculate the end date and end time ourselves.
  3. A multi-day event with no start time (e.g. a national holiday), the flag ‘alldayevent’ is set.
Furthermore, we want to filter the result set and show only those items within a certain timeframe.
Getting to a nicely formatted list of events requires some SQL processing, see attached script (stored procedure) and a simple  html template file, but can be realized quickly and is extremely flexible.
We hope you will consider the reports module in your future DNN work, since it can be a very handy tool.