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.<\/p>\n
The first step was to get a valid NULL parameter in my Site parameter drop-down menu.<\/p>\n
SELECT DISTINCT(LT.MemberValue) as Site
\nFROM MSP_EpmTask_UserView AS T
\nINNER JOIN MSP_EpmProject_UserView AS P ON P.ProjectUID=T.ProjectUID
\nleft join MSP_EpmCustomFieldAssociation on EntityUID = T.TaskUID
\nleft join MSP_EPMLookupTable LT on lt.memberuid = lookupmemberuid
\nWHERE T.TaskIsMilestone=1
\nAND TaskFinishDate > GETDATE()
\nAND LT.MemberValue IS NOT NULL
\n— Here’s where we add NULL for the menu.
\nUNION
\nSELECT ‘(NULL)’ AS Expr1
\nORDER BY LT.MemberValue
\n![\"\"](\"http:\/\/www.markwheeler.com\/images\/sites.png\")
<\/p>\n
Second step was to create the dataset query to accept all values including NULLs.<\/p>\n
SELECT ISNULL(Lt.MemberValue,'(NULL)’) as Site, P.ProjectName AS Project, P.PM AS Author,\u00a0P.ProjectStartDate AS Start, P.ProjectFinishDate AS Finish,\u00a0T.TaskName, TaskStartDate, TaskFinishDate
\nFROM MSP_EpmTask_UserView AS T
\nINNER JOIN MSP_EpmProject_UserView AS P ON P.ProjectUID=T.ProjectUID
\nleft join MSP_EpmCustomFieldAssociation on EntityUID = T.TaskUID
\nleft join MSP_EPMLookupTable LT on lt.memberuid = lookupmemberuid
\nWHERE T.TaskIsMilestone=1
\nAND TaskFinishDate > GETDATE()
\nAND P.ProjectName in (@Project)
\nAND TaskStartDate >= @TasksStart
\nAND TaskFinishDate <= @TasksEnd
\nGROUP BY Lt.MemberValue, P.ProjectName, P.PM, P.ProjectStartDate, P.ProjectFinishDate, T.TaskName, TaskStartDate, TaskFinishDate
\norder by project, ISNULL(Lt.MemberValue, ‘(NULL)’)<\/p>\n
I then added a Filter on this dataset for the @Site parameter.<\/p>\n
![\"\"](\"http:\/\/www.markwheeler.com\/images\/sites2.png\")
<\/p>\n
Third: There was a requirement that when sorting the table, Sites with NULL values should be LAST in the list.
\nThe 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.<\/p>\n
=IIF(Fields!Site.Value = “(NULL)”, “ZZZ”, Fields!Site.Value)<\/p>\n
![\"\"](\"http:\/\/www.markwheeler.com\/images\/sites3.png\")
<\/a><\/p>\n","protected":false},"excerpt":{"rendered":"