A common question that comes up in the Reports Module forums is "How can I display data relevant to the current User in the Reports Module?". So, I figured I should write a blog post about a feature that has been in the Reports Module for a few versions. When writing your SQL Scripts for the Reports Module, you automatically have access to four parameters: @PortalID, @TabID, @ModuleID and @UserID. When the SQL Script is executed, these are replaced by the ID of the current Portal, Tab, Module and User (respectively).
So, if you wanted to display a list of Roles for the currently logged in user you could use this script:
SELECT R.*
FROM {oQ}UserRoles UR
INNER JOIN dnn_Roles R
ON UR.RoleID = R.RoleID
WHERE UR.UserID = @UserID
When you run this script, the current user's ID will be substituted in the place of the @UserID parameter. (Note: This is done by SQL Server itself, which avoids SQL Injection attacks). Also, the {oQ} token is replaced with the Object Qualifier configured for your database (if you configured one in your web.config file)
Here's another example. It's a script that retrieves the Website profile property for each user in the current portal.
SELECT U.Username,
(SELECT PropertyValue
FROM {oQ}UserProfile AS UP
INNER JOIN {oQ}ProfilePropertyDefinition AS PPD
ON PPD.PropertyDefinitionID = UP.PropertyDefinitionID
WHERE UP.UserId = U.UserId
AND PropertyName = 'Website'
AND PropertyCategory = 'Contact Info') AS 'Website'
FROM {oQ}UserPortals AS UP
INNER JOIN {oQ}Users AS U
ON UP.UserId = U.UserId
WHERE PortalID = @PortalID
The italicized section is the part that actually retrieves the property value, you can replace the PropertyName and PropertyCategory values as you like and repeat this section multiple times to bring back multiple profile properties.
One of the planned future features for the module is to support user-specified parameters. This means you could place a text box, drop down, calendar or other control on the view page for the module and the value of this control would be used as a parameter to the Report. At the moment you're limited to these four parameters, but there's still a lot you can do with them!