Ssrs 2012 data display issue

SSAS and SSRS
1

In a new ssrs 2012 report, I am having problem the report displaying the data from a sql server 2016 report. Basically I wrote a query in t-sql 2012, and I am obtaining the results I want to see when I run the query in Sql server management studio.
However when I run the report, I am not getting the data results I am expecting see. I know the problem is the data being displayed in the report since I placed the selected data from a date field called ‘enddate’ in a textbox field. The field in the database is in type smalldatetime and nulls are allowed.
When I run the ssrs 2012, the data being is ‘null’. In the sql listed below the field call
‘ImpactAidEmployment.enddate’
Can you tell me what I can do to s'olve the problem?
Here is the sql:
--debug
--DECLARE @endYear SMALLINT = 2016
--DECLARE @calendarID INT = (SELECT calendarid FROM OPS.dbo.Calendar WHERE name LIKE '%central h%' AND endYear = @endYear AND summerSchool = 0) --6153
--end debug

SELECT DISTINCT Common.identityID, Common.personID, Common.structureID, Common.enrollmentID, Common.calendarID, Common.startYear, Common.endYear,
Common.schoolID, Common.schoolType, Common.schoolName, Common.studentNumber, Common.grade, Common.gradeSeq,
Common.fullName, Common.firstName, Common.middleName, Common.lastName, Common.suffix, Common.gender, Common.birthdate, Common.hispanicEthnicity,
Common.raceid1, Common.raceid2, Common.raceid3, Common.raceid4, Common.raceid5, Common.homeLanguage, COPPA.COPPAyesno
,Case when (GuardianMilitary2.guardianFirstName is null) or (GuardianMilitary2.guardianLastName is null)
then ltrim(substring(coalesce(GuardianMilitary.guardianFirstName,'') + ' ' + coalesce(GuardianMilitary.guardianLastName,''),1,55))
else ltrim(substring(coalesce(GuardianMilitary.guardianFirstName,'') + ' ' + coalesce(GuardianMilitary.guardianLastName,'') + ', ' + coalesce(GuardianMilitary2.guardianFirstName,'') + ' ' + coalesce(GuardianMilitary2.guardianLastName,''),1,55))
end as GuardiansNames
,ImpactAidEmployment.startdate,ImpactAidEmployment.enddate
FROM CampusOps.dbo.vSaFIGetCommonStudentInfo AS Common

LEFT JOIN (SELECT personID, value AS COPPAyesno --Census > People > Demographics custom tab
FROM OPS.dbo.CustomStudent WITH (NOLOCK)
WHERE attributeID = 3781) AS COPPA
ON COPPA.personID = Common.personID
JOIN ops.dbo.RelatedPair RelatedPair
on RelatedPair.personid1 = Common.personID
LEFT JOIN ops.dbo.ImpactAidEmployment ImpactAidEmployment
on ImpactAidEmployment.personID = RelatedPair.personid2
and ImpactAidEmployment.startdate is not null
LEFT JOIN OPS.dbo.v_GuardianMilitaryConnections GuardianMilitary -- contains guardian information that is needed
on GuardianMilitary.personID = Common.personID and GuardianMilitary.guardianStatus like '%Active Duty%'
LEFT JOIN OPS.dbo.v_GuardianMilitaryConnections GuardianMilitary2 -- contains guardian information that is needed
on GuardianMilitary2.personID = GuardianMilitary.personID
and (GuardianMilitary2.guardianPersonID <> GuardianMilitary.guardianPersonID)
and GuardianMilitary2.guardianStatus like '%Active Duty%'

WHERE Common.calendarID = @calendarID
AND Common.personID = @personID
AND Common.serviceType in (@serviceType)

ica.

2

So what is the problem again please state it clearly as in

  1. What do you expect to
  2. What are you seeing
3

In ssrs, I am expecting to see a date in smalltime format. When I place the result in a textbox field, I see nothing. If have a check to see isdate and the result I get is nothing.

4

Please post the actual data coming back from the query that feeds this report?

5

Could the problem be with the filters you are using in SSRS? Are they the same as what you are using in SSMS?

Based on how you are joining - the only rows you will get are those rows where you have a matching row in ImpactAidEmployment on the person ID and the startdate is not null. Is it possible that - for those specific parameters you either don't have any matching people or those that do match don't have an enddate (yet)?

I would assume that an employment table would not have an end date for active employees - so even if there is a start date there may not be an end date...