aUserCollection statement

The aUserCollection statement returns information about the specified users or about the collection of users in the specified groups, for one or more sites.

    <aUserCollection tableName="">
       <from type=""></from>
       <fields></fields>
       <where></where>
    </aUserCollection>

aUserCollection elements

from

Specify one or more users/groups from which to retrieve information. The type attribute will indicate how users/groups are specified.

Type	Description
Url	Specify one or more users/groups separated by a comma. A user/group is composed of a relative site url, a user/group name and a Boolean indicating if it’s a user. Each separated by a semi-colon. A report’s parameter may be specified instead of a literal value.
ResultSet	Specify the tableName value of another statement. The resulting data from the statement must contain a column named “relativeSiteUrl”, a column called “Name”, and an optional column named “MemberIsUser”.

The following statement will return all the users properties for each user in the “Demonstration Site Owners” group, and for the “John Smith” user.

Sample

    <aUserCollection tableName="UserCollection">
       <from type="Url">
    /sites/ersdedemo/;Demonstration Site Owners;false,
    /sites/ersdedemo/;ENESYS\John.Smith;true
       </from>
       <fields>*</fields>
       <where></where>
    </aUserCollection>

When using type = ” ResultSet ”, the result set must contain:

• A column called “RelativeSiteUrl” that contains relative urls that point to SharePoint sites.
• A column called “Name” that contains the name of a user, or the name of a group.
• An optional column called “MemberIsUser” of type Boolean. If true, the “Name” is a user, otherwise, the “Name” is a group. If the column is not present in the result set, it will be considered as a group.

You can use several statements as the source of the aUserCollection statement:

• An aListPermissions statement, which returns the three columns.
• An aSitePermissions statement which returns the three columns.
• An aSiteCollectionGroups statement which returns the two necessary columns.
• A list statement pointing to a SharePoint list that contains the specified columns.

In the following example, the query will return the user properties for each users and group members that are returned by the aListPermissions statement.

Sample

       <root xmlns="http://enesyssoftware.com/schemas">
        <aListPermissions tableName="aListPermissions1">
          <from type="Url">
            /sites/ersdedemo/;Products
          </from>
          <fields>Name,MemberIsUser</fields>
          <where></where>
        </aListPermissions>
        <aUserCollection tableName="results">
          <from type="ResultSet">aListPermissions1</from>
          <fields></fields>
          <where></where>
        </aUserCollection>
        <resultSet>results</resultSet>
       </root>

In the following sample, the query will return the user properties for each users and group members that are returned by the aSitePermissions statement.

Sample

    <root xmlns="http://enesyssoftware.com/schemas">
     <aSitePermissions tableName="aSitePermissions1">
       <from type="Url">
         /sites/ersdedemo/,
         /sites/ersdedemo/demosubsite01/
       </from>
       <fields>Name,MemberIsUser</fields>
       <where></where>
     </aSitePermissions>
     <aUserCollection tableName="results">
       <from type="ResultSet">aSitePermissions1</from>
       <fields></fields>
       <where></where>
     </aUserCollection>
     <resultSet>results</resultSet>
    </root>

In the following sample, the query will return the user properties for each user of the groups that are returned by the aSiteCollectionGroups statement.

Sample

    <root xmlns="http://enesyssoftware.com/schemas">
     <aSiteCollectionGroups tableName="aSiteCollectionGroups1">
       <from type="Url">
         /sites/ersdedemo/,
         /sites/ersdedemo/demosubsite01/
       </from>
       <fields>Name</fields>
       <where></where>
     </aSiteCollectionGroups>
     <aUserCollection tableName="results">
       <from type="ResultSet">aSiteCollectionGroups1</from>
       <fields></fields>
       <where></where>
     </aUserCollection>
     <resultSet>results</resultSet>
    </root>

fields

Specify the fields to be returned by the statement.

Field	Description
ID	The member ID for the user.
Sid	The unique security ID for the network account of the user.
Name	The display name of the user.
LoginName	The user name of the user.
Email	The e-mail address of the user.
Notes	Notes for the user.
IsSiteAdmin	A Boolean value that specifies whether the user is a site collection administrator.
IsDomainGroup	A Boolean value that indicates whether the user is a domain group.

where

SQL Where clause used to filter the resulting data.

The following query returns information about all the users in the “Demonstration Site Owners” that are site administrator:

Sample

    <aUserCollection tableName="aUserCollection1">
       <from type="Url">/sites/demo/;Demonstration Site Owners;false</from>
       <fields></fields>
       <where>IsSiteAdmin</where>
    </aUserCollection>

resultSet statement

The resultSet statement lets you specify which result set should be returned to Reporting Services.

 <resultSet>...</resultSet>

This statement is optional and the resulting data from the latest query statement will be returned when it is omitted.

Your query may be composed of various intermediate result sets. Most of the time, you would likely want to return the result of your last operation to Reporting Services. However, for testing purposes, it may be useful to return temporarily data from an intermediate result set.

Most query statements have a tableName attribute for naming to the resulting data.