SSRS – Query for NULLs and Order by NULLs last

 Computer, Sharepoint, Software  Comments Off on SSRS – Query for NULLs and Order by NULLs last
Apr 022013
 

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)

JQuery – Get Value of DIV with Custom Attribute

 Computer, Sharepoint, Software  Comments Off on JQuery – Get Value of DIV with Custom Attribute
Jan 012013
 

I have a ProjectServer with a custom field (Project Status Slide) where users have a URL for an image.

I needed to retrieve the URL from the custom field and display it inline on the ProjectDetails.aspx page

<div GUID=”xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx”>http://myserver/PWA/Pictures/Slide1.png</div>

The source of the custom field resembles the above.

I put a Content Editor Web Part at the bottom of the ProjectDetails.aspx page with the following script.

&lt;img id="pss" src="/PWA/Project%20Detail%20Pages/" alt=""/&gt; -- Placeholder for the Slide.
&lt;script src="/PWA/Project%20Detail%20Pages/jquery.js" type="text/javascript"&gt;&lt;/script&gt; -- Jquery
  &lt;script&gt;
$(document).ready(function(){
var myDiv = $("div[GUID='xxxxxxx-xxxx-xxxx-xxxx-xxxxxxxx']"); -- Look for DIV with Custom Attribute
var URL1 = $(myDiv).text(); -- Get the URL between that DIV
URL1 = URL1.replace("http:", "https:"); -- Just in case some used HTTP
// alert(URL1); -- for debug
 $('#pss').attr('src', URL1); - Replace my image src above with the Slide URL
 });&lt;/script&gt;