Formatting SSRS output with multiple dates

Question

I have used a Join LookupSet expression to return multiple dates in a cell with a carriage return separating them. There can be between 0 and 4 results in the output. Unfortunately this refuses to use anything but the default date output which is MM/DD/YYYY. I'm in the UK so need DD/MM/YYYY. Do I need to add something to the expression to force the UK format? TIA

Expression:

=Join(LookupSet(Fields!HOUSEHOLD_ID.Value,Fields!HOUSEHOLD_ID.Value,Fields!Placement_Start_Dates.Value, "Placements"),vbcrlf)

Example output:

2/14/2020
12/11/2019
12/24/2019

Are you able to include formatting around the return field within the `lookupset` function? If not, you may be falling foul to how `lookupset` returns objects and not values, for which you will require custom code in your report to iterate over the list and format the values as you require, [similar to how you would `sum` the results of a `lookupset`](https://stackoverflow.com/questions/36131860/need-help-in-calculation-using-two-datasets-using-expression-ssrs/36143175#36143175) — iamdave, Mar 30 '20 at 09:09

score 0 · Accepted Answer · answered Mar 30 '20 at 09:10

0

I think you just need to wrap your returned value with Format().

So this should work.

=Join(LookupSet(Fields!HOUSEHOLD_ID.Value,Fields!HOUSEHOLD_ID.Value,Format(Fields!Placement_Start_Dates.Value,”dd/MM/yyyy”), "Placements"),vbcrlf)

answered Mar 30 '20 at 09:10

Alan Schofield

19,839
3
22
35

That has worked! I had tried similar things but not in the right place in the expression so ended up with nonsense output. Many thanks. – Mark Adams Mar 30 '20 at 11:30
That's great!. Can you please mark the answer as accepted as this helps others find answered questions. – Alan Schofield Mar 30 '20 at 11:54

Formatting SSRS output with multiple dates

1 Answers1