Report Builder, SSRS Not Displaying One Column Data When Running

Question

What the table looks like prior to running:

What part of the report looks like after running:

All of the fields seem to be populating correctly with the exception of the Notes column. The notes field should contain a few sentences of data for each row, a mixture of numbers, dates, and words. I've included an example of my sql query below. Most of it was written with the help of the Query Designer, but I added the two JOINs myself.

Currently, I have the expression =Replace(Fields!text.Value, vbCrLf, " ") in the Notes column. I've also tried these expressions:

=Fields!text.Value

=Replace(Replace(Fields!text.Value, Chr(13), " "), Chr(10), " ") =IIf(IsNothing(Fields!text.Value), "", IIf(IsNumeric(Fields!text.Value), CDbl(Fields!text.Value), Fields!text.Value))

None of these made the data populate in the Notes column when running the report. I've tried expanding the size of my notes column, just in case the data is too large for the space. I've got "Allow height to increase" checked, and I've got "CanGrow" set to True. I'm not sure what else I can try, and I'm not sure what other information might be necessary when trying to understand what I've got going on here. Anyone have any suggestions? In advance, I appreciate the help!

SELECT
  Reporting.CFS.Id
  ,Reporting.CFS.Number
  ,Reporting.CFS.CreatedOn
  ,Reporting.CFSEvent.address_streetAddress
  ,Reporting.CFSEvent.address_zipCode_Description
  ,Reporting.CFSEvent.startDate
  ,Reporting.CFSEvent.address_city_Description
  ,Reporting.CFSEvent.type
  ,Reporting.CFSEvent.address_state_Code
  ,Reporting.CFSEvent.reason_V2
  ,Reporting.CFSNarrative.text
FROM
  Reporting.CFS
  LEFT OUTER JOIN Reporting.CFSEvent
    ON Reporting.CFS.Id = Reporting.CFSEvent.CFS_Id
  LEFT OUTER JOIN Reporting.CFSNarrative
    ON Reporting.CFS.Id = Reporting.CFSNarrative.Id
WHERE
  Reporting.CFSEvent.startDate >= @startDate
  AND Reporting.CFSEvent.startDate <= @startDate2

Answer 1

I notice that you're using a left join which means all the rows in CFS are being returned and only those that have a corresponding narrative row would be included. However I notice that your join is combining CFS.Id to CFSNarrative.Id. Shouldn't the CFSNarrative have a CFS_Id column that contains the Id from the CFS table? That is how your previous join works. The CFSNarrative.Id I assume is a PK and therefore isn't the CFS.Id value.

Answer 2

Hi @DJ

I don't know much about T-SQL, can you try to run it in the query designer of the dataset to see if the data in the Text column can be displayed?

You can also try to run the report on the report server or export it as a file instead of using the report builder preview to check whether the data is displayed normally to determine whether it is a problem with the report builder.

Best regards,

Aniya