Had a request to create a report for Project Server milestones. There was a custom enterprise column added for tasks which contained a location code. My report had to return all values including where location was NULL.
The first step was to get a valid NULL parameter in my Site parameter drop-down menu.
SELECT DISTINCT(LT.MemberValue) as Site
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P ON P.ProjectUID=T.ProjectUID
left join MSP_EpmCustomFieldAssociation on EntityUID = T.TaskUID
left join MSP_EPMLookupTable LT on lt.memberuid = lookupmemberuid
WHERE T.TaskIsMilestone=1
AND TaskFinishDate > GETDATE()
AND LT.MemberValue IS NOT NULL
— Here’s where we add NULL for the menu.
UNION
SELECT ‘(NULL)’ AS Expr1
ORDER BY LT.MemberValue
Second step was to create the dataset query to accept all values including NULLs.
SELECT ISNULL(Lt.MemberValue,'(NULL)’) as Site, P.ProjectName AS Project, P.PM AS Author, P.ProjectStartDate AS Start, P.ProjectFinishDate AS Finish, T.TaskName, TaskStartDate, TaskFinishDate
FROM MSP_EpmTask_UserView AS T
INNER JOIN MSP_EpmProject_UserView AS P ON P.ProjectUID=T.ProjectUID
left join MSP_EpmCustomFieldAssociation on EntityUID = T.TaskUID
left join MSP_EPMLookupTable LT on lt.memberuid = lookupmemberuid
WHERE T.TaskIsMilestone=1
AND TaskFinishDate > GETDATE()
AND P.ProjectName in (@Project)
AND TaskStartDate >= @TasksStart
AND TaskFinishDate <= @TasksEnd
GROUP BY Lt.MemberValue, P.ProjectName, P.PM, P.ProjectStartDate, P.ProjectFinishDate, T.TaskName, TaskStartDate, TaskFinishDate
order by project, ISNULL(Lt.MemberValue, ‘(NULL)’)
I then added a Filter on this dataset for the @Site parameter.
Third: There was a requirement that when sorting the table, Sites with NULL values should be LAST in the list.
The dataset query works in SQL Management Studio, however, not so much in SSRS. I had to put an expression in the Row group sorting field.
=IIF(Fields!Site.Value = “(NULL)”, “ZZZ”, Fields!Site.Value)
