In the most recent version (4.4.2), I removed the filtering that was performed on the query to stop the use of 'EXEC', 'CREATE', etc. because only SuperUsers can create queries anyway. This may make it easier to do, because you could use dynamic SQL and temporary tables.

---

Andrew Nurse
DotNetNuke Core Team Member and Reports Module Project Lead
Microsoft Certified Professional Developer

It's great to have a custom modul, but for those who have like me only a small needed amout of informations I post a basic SQL... that can help if you don't want to spend to much money just for one querry.

As I'm happy to work only in a single domain / single portal, I don't care about the modulID's, you should keep this in mind.

You can easy update the SQL statement with your ID's or extto had it with more fields. For every field that you want to add from the properties you need this part of SQL Code....
Replace the Country with your favorite name and replace the 29 with your Property ID of your field
Country= ISNULL
(
(
SELECT     UserProfile.PropertyValue
FROM         ProfilePropertyDefinition INNER JOIN
UserProfile ON ProfilePropertyDefinition.PropertyDefinitionID = UserProfile.PropertyDefinitionID
WHERE     (UserProfile.PropertyDefinitionID = 29) AND (ProfilePropertyDefinition.Deleted = 0) AND (UserProfile.UserID = Q.UserID) 
)
,
'not set'
)

 Full example:

Select
Q.DisplayName AS [Name]
, Country= ISNULL
(
(
SELECT     UserProfile.PropertyValue
FROM         ProfilePropertyDefinition INNER JOIN
UserProfile ON ProfilePropertyDefinition.PropertyDefinitionID = UserProfile.PropertyDefinitionID
WHERE     (UserProfile.PropertyDefinitionID = 29) AND (ProfilePropertyDefinition.Deleted = 0) AND (UserProfile.UserID = Q.UserID) 
)
,
'not set'
)
, Nationality= ISNULL
(
(
SELECT     UserProfile.PropertyValue
FROM         ProfilePropertyDefinition INNER JOIN
UserProfile ON ProfilePropertyDefinition.PropertyDefinitionID = UserProfile.PropertyDefinitionID
WHERE     (UserProfile.PropertyDefinitionID = 40) AND (ProfilePropertyDefinition.Deleted = 0) AND (UserProfile.UserID = Q.UserID) 
)
,
'not set'
)
, [Continental Federation]= ISNULL
(
(
SELECT     UserProfile.PropertyValue
FROM         ProfilePropertyDefinition INNER JOIN
UserProfile ON ProfilePropertyDefinition.PropertyDefinitionID = UserProfile.PropertyDefinitionID
WHERE     (UserProfile.PropertyDefinitionID = 41) AND (ProfilePropertyDefinition.Deleted = 0) AND (UserProfile.UserID = Q.UserID) 
)
,
'not set'
)
FROM
Users Q
INNER JOIN
UserProfile ON Q.UserID = UserProfile.UserID
INNER JOIN
UserRoles ON Q.UserID = UserRoles.UserID
GROUP BY
Q.UserID
, Q.DisplayName
, UserRoles.RoleID
HAVING     
UserRoles.RoleID  = 3