SSRS use a dynamic SQL query with parameter

Question

I have been facing a problem for several days, I have not found a solution on the various forums and in the documentation that I have read

I need to create a SQL query that allows me to retrieve data from an Excel file. This file has a first column that I always have to retrieve, and 38 others columns which correspond to integers. I need to retrieve only one of these 38 columns using a user entered parameter in the SSRS report

For example, the Excel file has the following columns: Col1, 547, 589, 512, 596, ... I have to get "Col1" and "512" if the user entered 512 in the parameter field

The query I wrote works in SSMS, but as soon as I use it in the report the parameter no longer works

DECLARE @Param AS INT = 552

 DECLARE @SQL NVARCHAR(MAX)

 SET @SQL = 'SELECT * FROM OPENROWSET 
                    (''Microsoft.ACE.OLEDB.12.0'',''Excel 12.0 Xml;HDR=YES;Database=W:\Test\Test.xlsx''
                    ,''SELECT [Year] + 1 AS [Year] , ROUND([' + cast(@Param as varchar(10)) + ']/1000,0)  AS Val  FROM [Sheet1$]
                     
                        ;'') '
DECLARE @ParmDefinition NVARCHAR(500) = '@Param NVARCHAR(20)';

EXECUTE sp_executesql @SQL
,@ParmDefinition
,@Param = @Param

image1

If i let the parameter in the SQL query as state above, it will works, bun when i comment the declaration and use SSRS parameter it is recognized as null

screen2

screen3

This window appears and the parameters doesn't work

screen4

Does anyone have an idea why parameter passing doesn't work (it seems to be related to the fact that it's a dynamic query with an openrowset, is there an alternative?) Thanks in advance for the help

I tried to create a scalar function user but it doesn't work