Filtering reports using Distribution Lists returns no data (4243713)

Filtering Mailbox reports using the Distribution List filter will return no data for some Distribution Lists but return data for others

WORKAROUND:

Follow these instructions to modify the function dbo.FU_DLExpand:

1. Launch SQL Server Management Studio
2. Connect to the SQL Server that is hosting the MessageStats Database (for example, Server Type = Database Engine, Server Name = SQL Server Name and Authentication = either Windows Authentication or SQL Server Authentication
3. Expand SQL Server Name
4. Expand Databases
5. Right click the MessageStats Database and select New Query
6. Copy and paste the following SQL script into the query window:

USE [MessageStats]
GO
/****** Object: UserDefinedFunction [dbo].[FU_DLExpand] Script Date: 04/08/2011 14:22:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER function [dbo].[FU_DLExpand](@inDistributionList NVARCHAR(256)) returns @ExpandedDL table (DisplayName NVARCHAR(255) PRIMARY KEY) AS BEGIN
DECLARE @RowsAdded int
-- table variable to hold accumulated results
DECLARE @DLMembers TABLE (DisplayName NVARCHAR(255),
ObjectGUID UNIQUEIDENTIFIER, ObjectType INT,
processed tinyint default 0)

--strip out incoming where clause

-- initialize @DLMembers with direct Mailboxes of the given DL
INSERT @DLMembers
SELECT E.DisplayName, E.ObjectGUID, E.ObjectType, 0
FROM T_DistributionListMembers M inner join
T_ExchMailEnabledObjects E ON E.ExchMailEnabledObjectGUID = M.ExchMailEnabledObjectGUID INNER JOIN
T_DistributionLists D ON M.DistributionListGUID = D.DistributionListGUID
WHERE (D.DisplayName = @inDistributionList OR D.DistributionListCN = @inDistributionList)
SET @RowsAdded = @@rowcount
-- While new DLs were added in the previous iteration
WHILE @RowsAdded > 0
BEGIN
/*Mark all DL records whose direct reports are going to be
found in this iteration with processed=1.*/
UPDATE @DLMembers
SET processed = 1
WHERE processed = 0
-- Insert mailboxes who report to DLs marked 1.
INSERT @DLMembers
SELECT E.DisplayName, E.ObjectGUID, E.ObjectType, 0
FROM @DLMembers T INNER JOIN
T_DistributionListMembers M ON T.ObjectGUID = M.DistributionListGUID inner join
T_ExchMailEnabledObjects E ON E.ExchMailEnabledObjectGUID = M.ExchMailEnabledObjectGUID
WHERE T.Processed = 1 AND T.ObjectType <> 1
SET @RowsAdded = @@rowcount
/*Mark all employee records whose direct reports have been found
in this iteration.*/
UPDATE @DLMembers
SET processed = 2
WHERE processed = 1
END

-- copy to the result of the function the required columns
INSERT @ExpandedDL
SELECT DISTINCT DisplayName
FROM @DLMembers WHERE ObjectType = 1
RETURN
END

7. Select the Query Menu and then Execute