<?xml version="1.0" encoding="utf-16"?>
<xListSettings xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <recordsPerPage>25</recordsPerPage>
  <enableAlphaFilter>true</enableAlphaFilter>
  <enablePageSelection>true</enablePageSelection>
  <enableRecordsPerPage>true</enableRecordsPerPage>
  <enableCustomPaging>false</enableCustomPaging>
  <enableQueryDebug>false</enableQueryDebug>
  <enableMultipleColumnSorting>true</enableMultipleColumnSorting>
  <showAll>false</showAll>
  <searchItems>
    <anyType xsi:type="SearchOptionItem">
      <Index>0</Index>
      <SearchOption>Last Name</SearchOption>
      <SearchField>LastName</SearchField>
    </anyType>
    <anyType xsi:type="SearchOptionItem">
      <Index>1</Index>
      <SearchOption>First Name</SearchOption>
      <SearchField>FirstName</SearchField>
    </anyType>
    <anyType xsi:type="SearchOptionItem">
      <Index>2</Index>
      <SearchOption>City</SearchOption>
      <SearchField>dbo.fn_GetProfileElement('City',PropertyNames,PropertyValuesString)</SearchField>
    </anyType>
    <anyType xsi:type="SearchOptionItem">
      <Index>3</Index>
      <SearchOption>Country</SearchOption>
      <SearchField>dbo.fn_GetProfileElement('Country',PropertyNames,PropertyValuesString)</SearchField>
    </anyType>
    <anyType xsi:type="SearchOptionItem">
      <Index>4</Index>
      <SearchOption>Region</SearchOption>
      <SearchField>dbo.fn_GetProfileElement('Region',PropertyNames,PropertyValuesString)</SearchField>
    </anyType>
  </searchItems>
  <queryItems />
  <listItems>
    <anyType xsi:type="ListFormatItem">
      <Index>0</Index>
      <GroupStatement />
      <ListHeader>&lt;style&gt;
        .ListAsc { background-color: #C8C8CC; background: url(/images/asc.gif) top right no-repeat; width: 100%; }
        .ListDesc { background-color: #C8C8CC; background: url(/images/desc.gif) top right no-repeat; width: 100%;}
        .FirstSortItem { border: 1px solid #EFEFFF; background: #DDDDDD; }
        .SortItem { background: #DDDDDD; border-left: 1px solid #EFEFFF; border-right: 1px solid #EFEFFF; border-bottom: 1px solid #EFEFFF; }
        .LastSortItem { background: #DDDDDD; border-left: 1px solid #EFEFFF; border-right: 1px solid #EFEFFF; border-bottom: 1px solid #EFEFFF;}
        .lstContact {border-bottom: 1px solid #cccccc;}
        .lstAContact {border-bottom: 1px solid #cccccc; background: #eeeeee;}
.lstHeader { background: #C8C8CC; font-family: arial; font-size: 16px; font-weight: bold; }

&lt;/style&gt;
&lt;table width=100% border=0 cellpadding=0 cellspacing=0&gt;
&lt;tr&gt;
    &lt;td style="width: 100px;" valign=top&gt;
        &lt;table style="width: 100px;" border=0 cellpadding=0 cellspacing=0 &gt;
            &lt;tr&gt;&lt;td class=lstheader&gt;Sorting&lt;/td&gt;&lt;/tr&gt;
            &lt;tr&gt;&lt;td class=firstsortitem&gt;&lt;a {SORT, FirstName, First&amp;nbsp;Name, &lt;div class="ListAsc"&gt;First&amp;nbsp;Name&lt;/div&gt;, &lt;div class="ListDesc"&gt;First&amp;nbsp;Name&lt;/div&gt;, ,0}&gt;{SORTHEADER,0}&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;
            &lt;tr&gt;&lt;td class=sortitem&gt;&lt;a {SORT, LastName, Last&amp;nbsp;Name, &lt;div class="ListAsc"&gt;Last&amp;nbsp;Name&lt;/div&gt;, &lt;div class="ListDesc"&gt;Last&amp;nbsp;Name&lt;/div&gt;, DESC ,1}&gt;{SORTHEADER,1}&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;
            &lt;tr&gt;&lt;td class=sortitem&gt;&lt;a {SORT, City, City, &lt;div class="ListAsc"&gt;City&lt;/div&gt;, &lt;div class="ListDesc"&gt;City&lt;/div&gt;, ,2}&gt;{SORTHEADER,2}&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;
            &lt;tr&gt;&lt;td class=sortitem&gt;&lt;a {SORT, Region, Region, &lt;div class="ListAsc"&gt;Region&lt;/div&gt;, &lt;div class="ListDesc"&gt;Region&lt;/div&gt;, ,3}&gt;{SORTHEADER,3}&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;
            &lt;tr&gt;&lt;td class=sortitem&gt;&lt;a {SORT, Country, Country, &lt;div class="ListAsc"&gt;Country&lt;/div&gt;, &lt;div class="ListDesc"&gt;Country&lt;/div&gt;, ,4}&gt;{SORTHEADER,4}&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;
            &lt;tr&gt;&lt;td class=lastsortitem&gt;&lt;a {SORT, Overall, Site&amp;nbsp;Usage, &lt;div class="ListAsc"&gt;Site&amp;nbsp;Usage&lt;/div&gt;, &lt;div class="ListDesc"&gt;Site&amp;nbsp;Usage&lt;/div&gt;, ,5}&gt;{SORTHEADER,5}&lt;/a&gt;&lt;/td&gt;&lt;/tr&gt;
        &lt;/table&gt;
    &lt;/td&gt;
    &lt;td width=100%&gt;
        &lt;table width=100%  border=0 cellpadding=0 cellspacing=0&gt;
&lt;tr&gt;&lt;td class=lstheader&gt;Contact&lt;/td&gt;&lt;/tr&gt;</ListHeader>
      <ListFooter>        &lt;/table&gt;
    &lt;/td&gt;
&lt;/tr&gt;
&lt;/table&gt;</ListFooter>
    </anyType>
  </listItems>
  <query>SELECT
Users.UserId UserId,
dbo.fn_GetProfileElement('FirstName',PropertyNames,PropertyValuesString) FirstName,
dbo.fn_GetProfileElement('LastName',PropertyNames,PropertyValuesString) LastName,
dbo.fn_GetProfileElement('Street',PropertyNames,PropertyValuesString) Street,
dbo.fn_GetProfileElement('City',PropertyNames,PropertyValuesString) City,
dbo.fn_GetProfileElement('Region',PropertyNames,PropertyValuesString) Region,
dbo.fn_GetProfileElement('PostalCode',PropertyNames,PropertyValuesString) PostalCode, 
dbo.fn_GetProfileElement('Country',PropertyNames,PropertyValuesString) Country,
isnull(LastDate,'4/1/2005') LastDate,
isnull(Hits, 1) Estimated,
case when (isnull(Hits,1) &gt; 30) then 64 else isnull(Hits,1) * 2 end Overall
FROM aspnet_Profile join aspnet_users on aspnet_profile.UserId = aspnet_users.UserId join Users on aspnet_users.UserName = Users.UserName
left join (Select UserId, max(DateTime) LastDate, count(UserId) Hits from SiteLog group by UserId) SiteLog  on SiteLog.UserId = Users.UserId
where Users.UserID not in (2,9,11) and [FILTERTAG]
order by [SORTTAG]</query>
  <filter>dbo.fn_GetProfileElement('LastName',PropertyNames,PropertyValuesString)  like '[FILTER]%'</filter>
  <listItem>&lt;tr&gt;&lt;td class=lstContact&gt;
&lt;table width=100% border=0 cellpadding=0 cellspacing=0&gt;
&lt;tr&gt;&lt;td width=100%&gt;&lt;b&gt;[FirstName] [LastName]&lt;/b&gt;&lt;/td&gt;&lt;td&gt;&lt;div style="background: url(/images/ratingplus.gif) repeat-x; width:[Overall]px; height:16px"&gt;&lt;img align=left src="/images/spacer.gif" width=[Overall] height=16&gt;&lt;/div&gt;&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;&lt;td colspan=2&gt;[Street]&lt;br&gt;
[City] [Region] [PostalCode] [Country]&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;
&lt;/td&gt;&lt;/tr&gt;</listItem>
  <listAItem>&lt;tr&gt;&lt;td class=lstAContact&gt;
&lt;table width=100% border=0 cellpadding=0 cellspacing=0&gt;
&lt;tr&gt;&lt;td width=100%&gt;&lt;b&gt;[FirstName] [LastName]&lt;/b&gt;&lt;/td&gt;&lt;td&gt;&lt;div style="background: url(/images/ratingplus.gif) repeat-x; width:[Overall]px; height:16px"&gt;&lt;img align=left src="/images/spacer.gif" width=[Overall] height=16&gt;&lt;/div&gt;&lt;/td&gt;&lt;/tr&gt;
&lt;tr&gt;&lt;td colspan=2&gt;[Street]&lt;br&gt;
[City] [Region] [PostalCode] [Country]&lt;/td&gt;&lt;/tr&gt;&lt;/table&gt;
&lt;/td&gt;&lt;/tr&gt;</listAItem>


  <Version>16</Version>
</xListSettings>

The following script block creates a set of SQL function which pull the extended attributes of the User Profile automatically. You can execute these scripts againsts your DNN database via standard database interaction tools like Query Analyzer. Alternatively, follow these steps under DotNetNuke.

1. Under your DNN Host Administration menu - Select SQL.
2. Paste the following script into the provided Text Area.
3. Check the Run As Script checkbox.
4. Click Execute. If you see no error, everything worked as described.

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetElement]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_GetElement]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[fn_GetProfileElement]') and xtype in (N'FN', N'IF', N'TF'))
drop function [dbo].[fn_GetProfileElement]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.fn_GetElement
(
@ord AS INT,
@str AS VARCHAR(8000),
@delim AS VARCHAR(1) )
RETURNS INT
AS
BEGIN
  -- If input is invalid, return null.
  IF  @str IS NULL
      OR LEN(@str) = 0
      OR @ord IS NULL
      OR @ord < 1
      -- @ord > [is the] expression that calculates the number of elements.
      OR @ord > LEN(@str) - LEN(REPLACE(@str, @delim, '')) + 1
    RETURN NULL
  DECLARE @pos AS INT, @curord AS INT
  SELECT @pos = 1, @curord = 1
  -- Find next element's start position and increment index.
  WHILE @curord < @ord
    SELECT
      @pos    = CHARINDEX(@delim, @str, @pos) + 1,
      @curord = @curord + 1
  RETURN    CAST(SUBSTRING(@str, @pos, CHARINDEX(@delim, @str + @delim, @pos) - @pos) AS INT)
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE FUNCTION dbo.fn_GetProfileElement
(
@fieldName AS NVARCHAR(100),
@fields AS NVARCHAR(4000),
@values AS NVARCHAR(4000))
RETURNS NVARCHAR(4000)
AS
BEGIN

  -- If input is invalid, return null.
  IF  @fieldName IS NULL
      OR LEN(@fieldName) = 0
      OR @fields IS NULL
      OR LEN(@fields) = 0
      OR @values IS NULL
      OR LEN(@values) = 0
    RETURN NULL

-- locate FieldName in Fields
DECLARE @fieldNameToken AS NVARCHAR(20)
DECLARE @fieldNameStart AS INTEGER, @valueStart AS INTEGER, @valueLength AS INTEGER

-- Only handle string type fields (:S:)
SET @fieldNameStart = CHARINDEX(@fieldName + ':S',@Fields,0)

-- If field is not found, return null
IF @fieldNameStart = 0 RETURN NULL
SET @fieldNameStart = @fieldNameStart + LEN(@fieldName) + 3

-- Get the field token which I've defined as the start of the field offset to the end of the length
SET @fieldNameToken = SUBSTRING(@Fields,@fieldNameStart,LEN(@Fields)-@fieldNameStart)

-- Get the values for the offset and length
SET @valueStart = dbo.fn_getelement(1,@fieldNameToken,':')
SET @valueLength = dbo.fn_getelement(2,@fieldNameToken,':')

-- Check for sane values, 0 length means the profile item was stored, just no data
IF @valueLength = 0 RETURN ''

-- Return the string
RETURN SUBSTRING(@values, @valueStart+1, @valueLength)
END



GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO