SSRS 2008 R2 Snippets

FILTERS

?


Expression Date Time in M, d yyyy hh:mm AM/PM format =MonthName(Month(Fields!Most_Recent_Inspection_Date.Value),false)
& " " &
Day(Fields!Most_Recent_Inspection_Date.Value)
& ", " &
Year(Fields!Most_Recent_Inspection_Date.Value)
& " at " &
Switch(
Hour(Fields!Most_Recent_Inspection_Date.Value)=12,Hour(Fields!Most_Recent_Inspection_Date.Value) & ":" & IIF(Minute(Fields!Most_Recent_Inspection_Date.Value)<10,"0" & Minute(Fields!Most_Recent_Inspection_Date.Value) & " PM",Minute(Fields!Most_Recent_Inspection_Date.Value) & " PM"),
Hour(Fields!Most_Recent_Inspection_Date.Value)=0,"12:" & IIF(Minute(Fields!Most_Recent_Inspection_Date.Value)<10,"0" & Minute(Fields!Most_Recent_Inspection_Date.Value) & " AM",Minute(Fields!Most_Recent_Inspection_Date.Value) & " AM"),
Hour(Fields!Most_Recent_Inspection_Date.Value)>12,Hour(Fields!Most_Recent_Inspection_Date.Value)-12 & ":" & IIF(Minute(Fields!Most_Recent_Inspection_Date.Value)<10,"0" & Minute(Fields!Most_Recent_Inspection_Date.Value) & " PM",Minute(Fields!Most_Recent_Inspection_Date.Value) & " PM"),
Hour(Fields!Most_Recent_Inspection_Date.Value)<12,Hour(Fields!Most_Recent_Inspection_Date.Value) & ":" & IIF(Minute(Fields!Most_Recent_Inspection_Date.Value)<10,"0" & Minute(Fields!Most_Recent_Inspection_Date.Value) & " AM",Minute(Fields!Most_Recent_Inspection_Date.Value) & " AM"))


Owner Full Name
COALESCE(CASE
WHEN isnull(dbo.B3OWNERS.B1_OWNER_FNAME, '') + isnull(B3OWNERS.B1_OWNER_MNAME, '') + isnull(B3OWNERS.B1_OWNER_LNAME, '') <> ''
THEN CASE
WHEN isnull(B3OWNERS.B1_OWNER_MNAME, '') <> ''
THEN CASE
WHEN ISNULL(dbo.B3OWNERS.B1_OWNER_TITLE, '') <> ''
THEN dbo.B3OWNERS.B1_OWNER_TITLE
ELSE ''
END + B3OWNERS.B1_OWNER_FNAME + ' ' + B3OWNERS.B1_OWNER_MNAME + ' ' + B3OWNERS.B1_OWNER_LNAME
ELSE B3OWNERS.B1_OWNER_FNAME + ' ' + B3OWNERS.B1_OWNER_LNAME
END
ELSE NULL
END, CASE
WHEN isnull(B3OWNERS.B1_OWNER_FULL_NAME, '') <> ''
THEN B3OWNERS.B1_OWNER_FULL_NAME
ELSE NULL
END) AS 'Owner Full Name'

Contact Full Name
COALESCE(CASE
WHEN isnull(dbo.B3CONTACT.B1_FNAME, '') + isnull(B3CONTACT.B1_MNAME, '') + isnull(B3CONTACT.B1_LNAME, '') <> ''
THEN CASE
WHEN isnull(B3CONTACT.B1_MNAME, '') <> ''
THEN CASE
WHEN ISNULL(dbo.B3CONTACT.B1_TITLE, '') <> ''
THEN dbo.B3CONTACT.B1_TITLE
ELSE ''
END + B3CONTACT.B1_FNAME + ' ' + B3CONTACT.B1_MNAME + ' ' + B3CONTACT.B1_LNAME
ELSE B3CONTACT.B1_FNAME + ' ' + B3CONTACT.B1_LNAME
END
ELSE NULL
END, CASE
WHEN isnull(B3CONTACT.B1_FULL_NAME, '') <> ''
THEN CASE
WHEN ISNULL(dbo.B3CONTACT.B1_TITLE, '') <> ''
THEN dbo.B3CONTACT.B1_TITLE
ELSE ''
END + B3CONTACT.B1_FULL_NAME
ELSE NULL
END, CASE
WHEN ISNULL(dbo.B3CONTACT.B1_BUSINESS_NAME,'') <> '' THEN dbo.B3CONTACT.B1_BUSINESS_NAME ELSE NULL END) AS 'Contact Full Name'