January 30, 2015 at 6:10 pm
1. There are total of five (5) fields to be combined into a single textbox field.
2. Each field might or might not be blank.
3. vbCRLF command will be used afterward if the field is not blank.
4. We have a lot of records. I would like to provide some example:
Record 1:
Fields!Field1.value = 'aaaaaaa'
Fields!Field2.value = Blank
Fields!Field3.value = Blank
Fields!Field4.value = 'dddddddddddd'
Fields!Field5.value = 'eeeeeeeeeeeeee'
Record 2:
Fields!Field1.value = Blank
Fields!Field2.value = Blank
Fields!Field3.value = Blank
Fields!Field4.value = 'dddddddddd'
Fields!Field5.value = Blank
Record 3:
Fields!Field1.value = Blank
Fields!Field2.value = 'bbbbbbbbbbbbbb'
Fields!Field3.value = Blank
Fields!Field4.value = 'dddddddddd'
Fields!Field5.value = Blank
Record 4:
Fields!Field1.value = Blank
Fields!Field2.value = 'bbbbbbbbbbbbb'
Fields!Field3.value = 'ccccccccccccc
Fields!Field4.value = Blank
Fields!Field5.value = 'eeeeeeeeeeeeeee'
What they want to see:
1. The first field that is not blank will be on the top. Then, it will be separate by the carriage return. The next non-blank field will be moved up without adding extra space.
So, using record # 1 as an example:
The result will display:
'aaaaaaa'
'dddddddddddd'
'eeeeeeeeeeeeee'
Record # 2 will display:
'dddddddddd'
Record # 3 will display:
'bbbbbbbbbbbbbb'
'dddddddddd'
All white spaces will be shrunk to minimize the row height.
I cannot think of any coding to make this to work, either by LTRIM or IS NOTHING.
Can anyone help? Thanks.
January 30, 2015 at 9:23 pm
It worked when I based the report on this T-SQL statement:
SELECT field1,
field2,
field3,
field4,
field5,
COALESCE(Field1 + CHAR(10) + CHAR(13),'') + COALESCE(Field2 + CHAR(10) + CHAR(13),'') + COALESCE(Field3 + CHAR(10) + CHAR(13),'') + COALESCE(Field4 + CHAR(10) + CHAR(13),'') + COALESCE(Field5 + CHAR(10) + CHAR(13),'') AS Test
FROM dbo.tblWithNulls;
The field1...field5 stuff wasn't necessary - it was just so I could compare what was in each cell in the table to the final result.
February 2, 2015 at 7:31 am
Even though I could not write this statement in the SSRS expression, however, I was able to use it in the SQL level and just pull the fields that I need to complete the report.
Thanks again.
February 2, 2015 at 12:14 pm
Sorry, I meant to add that part - you can't use COALESCE in SSRS directly. It's a T-SQL keyword and not available in SSRS, so you would have to do it in the stored procedure that your report is based on.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply