Products

Solutions

Resources

Partners

Community

About

New Community Website

Ordinarily, you'd be at the right spot, but we've recently launched a brand new community website... For the community, by the community.

Yay... Take Me to the Community!

Welcome to the DNN Community Forums, your preferred source of online community support for all things related to DNN.
In order to participate you must be a registered DNNizen

HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportstrouble with getting user data into useful formattrouble with getting user data into useful format
Previous
 
Next
New Post
1/5/2011 3:57 AM
 
We have a request to provide user data for a portal. This should include profile information from the Users and UserProfile tables, etc.

This data is all easily visible in the DB, and we can display it in the reports module without problem. BUT! The data is spread out over numerous rows, so the data for each user can be on ten rows, rather than just one.

This makes it extremely hard to read.

What we need to do is to get the data to be displayed in a grid, with column headings for each data type and one row per user.

Any ideas how this can be done?

Thanks for any input!
 
New Post
1/5/2011 8:20 AM
 
You never stated what information you're trying to retrieve. Here is a simple one that pulls username, First & Last name, email and roles they belong to:

SELECT
    UserID,
    UserName,
        (FirstName +' '+ LastName) As Name,
        Email,
    (
        SELECT
            RoleName + ', '
        FROM
            Roles R
            INNER JOIN UserRoles UR ON (UR.RoleID=R.RoleID AND UR.UserID=U.UserID)
        WHERE
            (UR.EffectiveDate IS NULL OR UR.EffectiveDate <= getdate()) AND (UR.ExpiryDate IS NULL OR getdate() < UR.ExpiryDate)
        FOR XML PATH('')
    ) AS Roles
FROM Users U

Hope this helps...


 
Previous
 
Next
HomeHomeDNN Open Source...DNN Open Source...Module ForumsModule ForumsReportsReportstrouble with getting user data into useful formattrouble with getting user data into useful format


These Forums are dedicated to discussion of DNN Platform and Evoq Solutions.

For the benefit of the community and to protect the integrity of the ecosystem, please observe the following posting guidelines:

  1. No Advertising. This includes promotion of commercial and non-commercial products or services which are not directly related to DNN.
  2. No vendor trolling / poaching. If someone posts about a vendor issue, allow the vendor or other customers to respond. Any post that looks like trolling / poaching will be removed.
  3. Discussion or promotion of DNN Platform product releases under a different brand name are strictly prohibited.
  4. No Flaming or Trolling.
  5. No Profanity, Racism, or Prejudice.
  6. Site Moderators have the final word on approving / removing a thread or post or comment.
  7. English language posting only, please.
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out
What is Liquid Content?
Find Out