SQL Reports are quite possibly the most versatile and powerful assets in WebGUI. When most people think SQL Reports they think of financial tables and charts. Though WebGUI's SQL Reports engine could be used for that, they are just as useful as user interface components.

1. Select the SQL Report asset from the New Content menu of the Admin Bar.

2. The “Add SQL Report” screen will open.
   
   

 

3. Enter a title or heading for the SQL Report in the “Title” field.

4. The “Menu Title” is the title as it appears in the site navigation. If left blank, WebGUI will insert the Title here.

5. “URL” can also be left blank, and WebGUI will generate a URL for you.

6. In the “Description” field, enter a description of the asset, perhaps an explanation of the data being displayed. Anything entered in this field will appear between the title of the SQL Report and the SQL query results.

7. If “Debug?” is toggled to Yes, WebGUI will automatically debug the SQL query. So, if there is something wrong with the query, a screen will display helpful information.
   
   

 

8. If “Preprocess macros on query?” is toggled to Yes, any you’ve used will be processed before the query; in other words, you can generate your query based on information from the macro.

9. The “Placeholder Parameters” field is where you can enter placeholders and variables so that you can generate dynamic queries. There are four types of placeholders: integer, form, query results, and string.

10. In the “Prequery Statement” field enter the SQL statements that you want run before the main query statement. This allows you to set variables to be used in the main query. In this example the following query was entered: select * from userLoginLog where userID=3 Prequery statements are separated by returns and can not use placeholders; however, you can use macros. Prequery statements are only visible in the query they belong to, and you can only use statements that are allowed by the database link.

11. Enter the SQL query in the “Query” field using standard SQL query format. The allowed keywords for the WebGUI database are select, describe and show.

12. At the bottom of the “Add SQL Report” screen you can select the “Database Link” from the dropdown menu.

13. Selecting to “Add another query” will allow you to nest queries. If this is done, the query entered after this will be performed on each line returned from this query. You can have up to 5 total queries.

14. Set the display options in the “Display” tab.
    
    

 

1. Hide from navigation?: if toggled to Yes, the asset’s title will be hidden in the site navigation.

2. Open in new window?: if toggled to Yes, the asset will open in a new browser window when viewed directly.

3. Display the title?: If toggled to No, the asset’s title will be hidden on the web page.

4. Style template: select a style in which to view this asset, if the asset is viewed directly. If the asset is part of a page layout asset, the page layout style will be used.

5. Printable Style: if the page is made printable a pared-down printer friendly version of the asset will be made available.

6. SQL Report Template: this is the default SQL report template. To edit this template, select the Edit button. This template will arrange the data that’s returned by your query. Ensuring that you have a good template will make the data easier to read.

7. Cache Timeout: determines how much time will pass before a user’s cache is refreshed. You may want to set this close to zero to ensure your report returns fresh data.

8. Paginate After: this determines how many rows of data will be displayed before results are paginated.

15. Set security settings in the “Security” tab.
    
    

 

1. Owner: the owner has full viewing and editing rights of this asset. Usually, the owner is the person who created the asset.

2. Who can view?: this determines the group of users allowed to view the report and the results of the query.

3. Who can edit?: this determines who is allowed to edit asset content.

4. Download User Group: determines the group of users who can download the report.

16. The Metadata tab is the standard Metadata screen common amongst all assets. If you need to handle metadata for the purposes of content profiling you can do so here.

17. Click save to create the SQL Report.

 

 

Examples 

The following are several examples of how you can use WebGUI to get more use out of the data already in your WebGUI database.

User Interfaces

Example 1: Technical Alerts

Another way to use the events calendar data is for displaying a quick summary of a particular calendar. For instance, you may build a calendar of technical alerts featuring things like scheduled outages or known problems. In that case you may want to display a summary of calendar data for just a few events happening immediately.

Query

select

Event.startDate,

assetData.title,

Event.description

from

Event

left join assetData on (Event.assetId=assetData.assetId)

left join asset on (Event.assetId=asset.assetId)

where

Event.startDate >= CURDATE()

and Event.revisionDate = (SELECT MAX(revisionDate) FROM Event WHERE assetId=assetData.assetId) and asset.parentId="X"

group by

Event.assetId

order by

Event.startDate

limit 5

NOTE: Replace the bold X with the asset ID of the calendar to summarize.

Template

<tmpl_loop rows_loop>

<span style="font-size: 8pt;"><tmpl_var row.field.startDate.value> </span><br><b><tmpl_var row.field.title.value></b> - <tmpl_var row.field.description.value>

<p>

</tmpl_loop>

 

Example 2: Who’s Online

If you're running an online community, your users often want to see who else is logged in besides them.

Query

select

users.userId,

users.username,

round((unix_timestamp()-userSession.lastPageView)/60) as inactiveTime

from

users,

userSession

where

users.userId=userSession.userId

having

inactiveTime < 600

order by

users.username

Template

<table width="100%">

<tr class="tableHeader">

<td>User</td>

<td>Inactive For</td>

</tr>

<tmpl_loop rows_loop>

<tr class="tableData">

<td><a href="/sql_reports/user_interfaces2/whos_online?op=viewProfile&uid=<tmpl_var row.field.userId.value>"><tmpl_var row.field.username.value></a></td>

<td><tmpl_var row.field.inactiveTime.value> minutes</td>

</tr>

</tmpl_loop>

</table>

Example 3: What’s New

Perhaps you'd like to show the most recent assets added to the site to keep your users informed of what is new.

Query

select

url

title

lastUpdated

from

asset

order by

lastUpdated desc

limit 5

Template

<table>

<tmpl_loop rows_loop>

<tr class="content">

<td>ˆD("%c %D",<tmpl_var row.field.lastUpdated.value>);</td><td>::</td><td><a href="ˆ/;<tmpl_var row.field.url.value>"><tmpl_var row.field.title.value></a></td>

</tr>

</tmpl_loop>

</table>

Example 4: Upcoming Events

If you have a lot of events calendar's on your site (maybe one per department on an intranet), you may want to show a quick overview on the front page of the site.

Query

select

assetData.url AS "url",

assetData.title AS "title",

assetData.menuTitle AS "menuTitle",

Event.startDate AS "startDate",

Event.description AS "description"

from

Event

left join assetData on (Event.assetId=assetData.assetId)

where

Event.startDate>=CURDATE()

and Event.revisionDate = (select MAX(revisionDate) FROM assetData where assetId=Event.assetId)

group by

Event.assetId

order by

Event.startDate, Event.startTime

limit 50

Template

<table border=0 cellspacing=10 class="content">

<tmpl_loop rows_loop>

<tr><td valign="top">

<a href="//<tmpl_var row.field.url.value>"><tmpl_var row.field.menuTitle.value></a><br>

<span style="font-size: 8pt;">Wednesday December 31</span>

</td><td valign="top">

<b><tmpl_var row.field.title.value></b> - <tmpl_var row.field.description.value>

</td></tr>

</tmpl_loop>

</table>

Dynamic Reports

Dynamic reports can be useful on many levels. Perhaps you want the user to specify a date range for a given report, or perhaps you'd like to have a drill-down report. Or perhaps you'd like to display information based on the page that a person is on, or who they are logged in as. Whatever the case, WebGUI provides some functionality to let you generate dynamic reports.

Example 1: Date Range Report

Let's say you want the user to input two date values (which form a range) and then generate a report based upon that input. Create an SQL Report and check the box that reads "Preprocess macros on query?"

Description

Please enter a date range to search for:

<form>

<input type="text" name="startDate" value="2007-01-29">

<input type="text" name="endDate" value="2007-01-29">

<input type="submit">

</form>

Query

select

users.userId,

users.username,

userLoginLog.ipAddress,

userLoginLog.timeStamp

from

users,

userLoginLog

where

users.userId=userLoginLog.userId

and userLoginLog.timeStamp > unix_timestamp('^FormParam(startDate);')

and userLoginLog.timeStamp < unix_timestamp('^FormParam(endDate);')

order by

userLoginLog.timestamp

Template

<table border="1" width="100%">

<tr>

<th>Username</th>

<th>IP Address</th>

<th>Date of Login</th>

</tr>

<tmpl_loop rows_loop>

<tr>

<td><tmpl_var row.field.username.value></td>

<td><tmpl_var row.field.ipaddress.value></td>

<td>^D("%z %Z",<tmpl_var row.field.timeStamp.value>);</td>

</tr>

</tmpl_loop>

</table>

Example 2: Drill Down Reports

Continuing with the Date Range Report, what if you wanted to link to another report which showed a summary of all the logins of a particular user? The first thing you need to do is change the Report Template (in the Date Range Report) to give you the link.

Template

<table border="1" width="100%">

<tr>

<th>Username</th>

<th>IP Address</th>

<th>Date of Login</th>

</tr>

<tmpl_loop rows_loop>

<tr>

<td><a href="/page_2?userId=<tmpl_var row.field.userId.value>"><tmpl_var row.field.username.value></a></td>

<td><tmpl_var row.field.ipAddress.value></td>

<td><tmpl_var row.field.timeStamp.value></td>

</tr>

</tmpl_loop>

</table>

Now that you've changed that report, add a new report to a new page that called "Page 2". Again, remember to turn on macro preprocessing.

Query

select

ipAddress,

timeStamp

from

userLoginLog

where

userId='^FormParam(userId);'

order by

timeStamp

Template

<table border="1" width="100%">

<tr>

<th>IP Address</th>

<th>Date Of Login</th>

</tr>

<tmpl_loop rows_loop>

<tr>

<td><tmpl_var row.field.ipAddress.value></td>

<td>12/31/1969 6:00 pm</td>

</tr>

</tmpl_loop>

</table>

As you can see, a two-level drill down report has been created. You could very easily take this even further and allow your users to select an IP address to see if any other users had ever used that IP. You could also provide a link to the user's profile. The possibilities for drill down reports are only limited by the diversity of your data.

Advanced Queries

You can perform as advanced queries as your database can handle using the SQL Report. For instance, if you wanted to export some of your user information for a mailing list, you could write a query like this:

select

users.userId,

users.username,

a.fieldData,

b.fieldData,

c.fieldData,

d.fieldData,

e.fieldData,

f.fieldData,

g.fieldData

from

users

left join

userProfileData as a

on

users.userId=a.userId and

a.fieldName='email'

left join

userProfileData as b

on

users.userId=b.userId and

b.fieldName='firstName'

left join

userProfileData as c

on

users.userId=c.userId and

c.fieldName='lastName'

left join

userProfileData as d

on

users.userId=d.userId and

d.fieldName='workAddress'

left join

userProfileData as e

on

users.userId=e.userId and

e.fieldName='workCity'

left join

userProfileData as f

on

users.userId=f.userId and

f.fieldName='workState'

left join

userProfileData as g

on

users.userId=g.userId and

g.fieldName='workZip'

where

users.userId > 25

order by

c.fieldData

This query pulls out the user id, username, email address, and work mailing address of every user in the system sorted by last name. Note that it will likely be a pretty empty query unless you require your users to fill in their name and address information.