Forum Discussion
TDS Endpoint multi value parameter PBRS
Hi,
I'm using Power BI Report builder and the dataverse, and I'm querying a TDS endpoint via t sql and I'm trying to allow my parameter dataset to read in multi value parameters.
My query below works fine in SSMS but when I run it as the dataset parameter it gives me this error, even though there's a Team parameter present.
query:
t.name IN ( SELECT value FROM OPENJSON('["' + REPLACE(@Team, ',', '","') + '"]') j )
error:
{"Message":"The parameterized query
'(@Team nvarchar(200),@userId uniqueidentifier,@languageCode int)' expects the parameter '@Team', which was not supplied.
As its a TDS endpoint the database is read only so I can't use any function or string_agg to read in the parameter and I'm at a loss how to allow the multi values to pass on a TDS endpoint.
Does anybody know if there's a way, or a better way to read in multi values?
t.name in (@Team) doesn't work.
Thanks
Liam
Hii lherbert501
The TDS endpoint expects strongly typed scalar parameters, so IN (@Team) or JSON parsing won’t work, which is why it throws “@Team was not supplied.”
Correct approach: Let Report Builder handle the multi-value expansion automatically by using:
t.name IN (@Team)Then ensure the parameter is configured as Multi-value and the dataset query type is Text (not stored procedure). Report Builder will internally expand it to multiple scalar parameters (@Team1, @Team2…).
If this still fails, the limitation is from the Dataverse TDS endpoint, which does not fully support multi-value parameterization in that case, you must switch to FetchXML or Power BI semantic model instead.
10 Replies
Hii lherbert501
The TDS endpoint expects strongly typed scalar parameters, so IN (@Team) or JSON parsing won’t work, which is why it throws “@Team was not supplied.”
Correct approach: Let Report Builder handle the multi-value expansion automatically by using:
t.name IN (@Team)Then ensure the parameter is configured as Multi-value and the dataset query type is Text (not stored procedure). Report Builder will internally expand it to multiple scalar parameters (@Team1, @Team2…).
If this still fails, the limitation is from the Dataverse TDS endpoint, which does not fully support multi-value parameterization in that case, you must switch to FetchXML or Power BI semantic model instead.
Hi rohit1991 ,
Can I just confirm, you said that's why IN (Team) won't work but then said the correct approach is
t.name IN (@Team)
Is this not the same?
Thanks
Hi lherbert501 ,
Thanks for reaching out to the Microsoft Fabric Community forum.
Please make sure you have allowed multiple values for the parameter.
In the General tab, select Allow multiple values to allow a user to select more than one value for the parameter.
Additionally,
The text for the query variable must include the IN operator, as shown in the example below.WHERE Production.ProductInventory.ProductID IN (@ProductID)Finally, Be sure to include the parentheses around the variable as shown above. Otherwise, the report fails to render and the "must declare the scalar variable" error is displayed.
I hope this information helps. Please do let us know if you have any further queries.
Thank you
Hi lherbert501
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
Thank you.Hi @lherbert501
May I check if this issue has been resolved? If not, Please feel free to contact us if you have any further questions.
Thank youHi v-nmadadi-msft ,
I will work on putting some dummy data in over the weekend to help explain the issue
Hi lherbert501 ,
Thank you for the update, and please do share the query as well.
Regards
Hi lherbert501
As we haven’t heard back from you, we wanted to kindly follow up to check with you regarding the requested information. Please feel free to contact us if you have any further questions.
Thanks and regards
