SSRS Report giving error for Join expression

Question

I have declared a multi value parameter in my SSRS report. The parameter is a Date and it has multiple values like:

06/01/2021
05/01/2021
04/01/2021

Now when I select date as 06/01/2021, 05/01/2021 then the expression should show as Reportname_ with previous months first and last dates. Example: if I select date as

06/01/2021
05/01/2021
04/01/2021

then result should be

Reportname_05/01/2021-05/31/2021_04/01/2021-04/30/2021_03/01/2021-03/31/2021

I tried with Join but getting error. The expression I am using is

Join(dateadd(“m”,-1,dateserial(year(Parameters!date.Value(0)), month(Parameters!date.Value(0)), 1)) & 
"-" & 
dateadd(“m”,0,dateserial(year(Parameters!date.Value(0)), month(Parameters!date.Value(0)), 0)), "_")

Please provide more details about the expression that returns the error — niktrs, Jun 07 '21 at 09:00
@niktrs The Value expression for the textrun ‘textbox1.Paragraphs[0].TextRuns[0]’ contains an error: [BC30518] Overload resolution failed because no accessible 'Join' can be called with these arguments: 'Public Function Join(SourceArray() As String, [Delimiter As String = " "]) As String': Value of type 'String' cannot be converted to '1-dimensional array of String'. 'Public Function Join(SourceArray() As Object, [Delimiter As String = " "]) As String': Value of type 'String' cannot be converted to '1-dimensional array of Object'. — , Jun 07 '21 at 09:05
Join(dateadd(“m”,-1,dateserial(year(Parameters!date.Value(0)),month(Parameters!date.Value(0)),1))&"-"&dateadd(“m”,0,dateserial(year(Parameters!date.Value(0)),month(Parameters!date.Value(0)),0)),"_") — , Jun 07 '21 at 09:06

niktrs · Answer 1 · 2021-06-07T11:27:45.283

1

You have to use custom code to parse your parameter and create the string expression

Add the following custom code to your report

Public Function ParseDate(ByVal parameter as Parameter) as String

Dim s as String 
Dim seperator as String 
Dim dt as Date

    For i as integer = 0 to parameter.Count-1
     dt = DateSerial(Year(parameter.Value(i)), Month(parameter.Value(i)), 1)
      s = s + seperator + Format(DateAdd("m",-1,dt),"MM/dd/yyyy") + "-" + Format(DateAdd("d",-1, dt),"MM/dd/yyyy") 
      seperator = "_"

    Next
 
   Return s
End Function

For your textbox returning the report name and date values use the following expression

=Globals!ReportName & "_" & Code.ParseDate(Parameters!Date)

edited Jun 07 '21 at 11:27

answered Jun 07 '21 at 09:56

niktrs

9,858
1
30
30

Thank you, If the select date is 06/20/2021 then also the parsed expression date should be 05/01/2021-05/31/2021. Then what we need to change in above function? – Jun 07 '21 at 10:38
It needs to use DateSerial to get the first day of month. The rest of the calculations may remain the same. I have updated my answer to reflect the change – niktrs Jun 07 '21 at 11:23

SSRS Report giving error for Join expression

1 Answers1