Overload resolution failed because no accessible 'Join' can be called with these arguments-CodePudding

I have a dataset in ssrs in which one of the parameter has expression as

=IIF(Parameters!EquipmentTypeID.Value(0)=-1,NOTHING,JOIN(CInt(Split(Parameters!EquipmentTypeID.Value," ").GetValue(0)),","))

'Parameters!EquipmentTypeID.Value' has value in the form like '38 612-FTQ-> Wheels' from which I want to extract 38 in my expression.

But when I run the report, I get following error:

Overload resolution failed becuase no accessible 'Join' can be called with these arguments.

Please help. Thanks in advance.

CodePudding user response：

I'm not sure you can do what you are attempting to do using JOIN as it expects an array so even trying to join LEFT(Parameters!EquipmentTypeID.Value, 2) would not work.

You might want to consider a custom function to handle this or even do it in the database.

You could create a dataset that returns this value something like this.

-- assumes a table called myTableWithEquipmentTypeIDs with a columne called EquipmentTypeID
declare @result nvarchar(1000) = ''

SELECT @result = @result
                   CASE @result WHEN '' then '' ELSE ', ' END 
                   e
            FROM (SELECT DISTINCT left(EquipmentTypeID, 2) as e 
                    FROM myTableWithEquipmentTypeIDs
                    WHERE EquipmentTypeID IN (@EquipmentTypeID)
                    ) a

SELECT @result as myResult

Then all you have to do to reference this result is set the textbox expression to =FIRST(Fields!myResult.Value, "myDataSetName")