Hello,

I want to use the Report module in combination with the Events module. I hardly know anything about SQL, but I need to display a custom created profile field using the Reports module. I've added a profile field called "Bedrijfsnaam". But I can't get it to display in the report. I've tried the following:

SELECT

Users.Bedrijfsnaam AS [Bedrijfsnaam], Users.FirstName AS [First Name], Users.LastName AS [Last Name], Users.Email AS [E-Mail]

FROM

EventsSignups INNER JOIN
Events

ON EventsSignups.EventID = Events.EventID INNER JOIN
Users

ON EventsSignups.UserID = Users.UserID
ORDER

BY LastName DESC

That gave me an error. It works fine if I remove "Users.Bedrijfsnaam AS [Bedrijfsnaam], " Can anyone tell me how I can achieve this?
Thanks in advance.

I actually found how to do it. For anyone else who's interested, see below:

SELECT

(Select UP.PropertyValue From UserProfile UP Where Users.UserID = UP.UserID And UP.PropertyDefinitionID = 61) As [Bedrijfsnaam]

etc.

You can find out which PropertyDefinitionID you need with this query (change PortalID if necessary):

SELECT DISTINCT PortalID, PropertyDefinitionID, PropertyName
FROM ProfilePropertyDefinition
WHERE (PortalID = 0)
ORDER BY PropertyDefinitionID

I built a query that includes a couple of custom profile fields, using ideas I gleaned from several different posts.  This is the result; hope it helps someone.

SELECT
  u.LastName AS [Last Name],
  u.FirstName AS [First],
  r.Rolename AS Type,
  CONVERT(VARCHAR, ur.EffectiveDate,1) AS Joined,
  CONVERT(VARCHAR,ur.ExpiryDate,1) AS Expires,
  upd.DOB,
  upd.Phone,
  u.Email
FROM dbo.Users as u INNER JOIN
  dbo.UserRoles AS ur ON u.UserID = ur.UserID  INNER JOIN
  dbo.Roles AS r ON ur.RoleID = r.RoleID
    LEFT OUTER JOIN
  (SELECT      
    up.UserID,  
    MAX(CASE WHEN ppd.PropertyName = 'DateOfBirth' THEN up.PropertyValue ELSE '' END) AS DOB,
    MAX(CASE WHEN ppd.PropertyName = 'Telephone' THEN up.PropertyValue ELSE '' END) AS Phone
   FROM           
    dbo.UserProfile AS up INNER JOIN 
    dbo.ProfilePropertyDefinition AS ppd ON up.PropertyDefinitionID = ppd.PropertyDefinitionID AND ppd.PortalID = 0 
   GROUP BY up.UserID) AS upd ON u.UserID = upd.UserID 
ORDER BY CAST(u.Lastname as varchar(3)) ASC

William