Paginated Report - Parameter Issue - Invalid length parameter passed to the LEFT or SUBSTRING

[StefanKleinhans]

Version:

Simple Query - Not Main Query (Troubleshooting)

SELECT TOP 1
A.TenantId, A.CustID, A.OBNumber, A.SignalTime, A.MainArea
FROM tArchiveHistory(@Id) AS A
WHERE A.OBNumber = @OB
--WHERE A.OBNumber = '123456789'
--WHERE A.MainArea = @MainArea
AND A.OperatorTime IS NOT NULL

Remove @OB or hard code the works, Change it to A.MainArea = @MainArea Fails again
Change to diffrent table diffrent query and parameters. same thing happens, pass one parameter it works pass two it breaks

SQL Query Works - The Report builder Query Designer Works
Only when I run the report and the parameters needs to pass though does it brake.

Any expert out there that can advice me what the issue is

[Anonymous]

Hi,@StefanKleinhans .I am glad to help you.

It looks like you are having problems getting the dataset in Report Builder.

I think it may be an incorrect SQL statement or an incorrect parameter setting!
I noticed that you may be using CLR Table-Valued Functions
Because in your code the @Id parameter is after the table name (I think the incorrect use of the parameter is the most likely cause of your problem).
In your code

A.TenantId, A.CustID, A.OBNumber, A.SignalTime, A.MainArea
FROM tArchiveHistory(@Id) AS A
If tArchiveHistory is not a real, ordinary table, but a function table generated by a custom function run.
You need to ensure that the table function definition is correct
You need to check that the length of the function's argument is valid. If the length parameter passed to the LEFT function is negative or greater than the actual length of the string, it will result in an error. Regarding the parameter settings (types) in SQL Server and in Report Builder, you need to check.
I hope the link below will help you.

CLR Table-Valued Functions - SQL Server | Microsoft Learn
Paginated report parameters in Report Builder - Microsoft Report Builder & Power BI Report Builder |...
If your table is a real table, then you can refer to my test below

For Parameters, you need to additionally create separate DataSets for them and be careful about the type of Parameter set and whether multiple values are allowed or not
=@Par (Par is a single value)
in(@Par) (Par is now a list, containing multiple values)

Final Report DataSet

SELECT TOP 1
A.TenantId, A.CustID, A.OBNumber, A.SignalTime, A.MainArea
FROM tArchiveHistory AS A
WHERE A.OBNumber in (@OB)

AND A.OperatorTime IS NOT NULL
AND A.MainArea in (@MainArea)
AND A.CustID =@Id




--WHERE A.OBNumber = '123456789'
--WHERE A.MainArea in @MainArea

Here is my test data:

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.