Parameters with SQL

[Syndicate_Admin]

Hello, I'm working on a Report which has as a data source a query to SQL which is actually a stored procedure which receives a parameter (in this case it is a line of text that refers to a date: 202412), what I need is to know if it can (and if it can, know how to do it) I have already created a parameter with the list of suggested values to be able to change this parameter from the Data Transformation view, but I want to know if this parameter can be handled from the same report, that is, give a drop-down list to the user that is configured as a single selection that has the same values from the list of suggested values that I configured in the parameters or something similar, so that the user can select the date with which they want to work the data.

[AmiraBedh · Accepted solution]

Create a parameter in Power Query  and provide a list of suggested values (202401, 202402...) and set a default value like 202412.

Then replace the hardcoded parameter in your SQL query or stored procedure call with the parameter reference. For example:

EXEC dbo.YourStoredProcedure @DateParameter = '" & DateParameter & "'

The parameters created in Power Query are not directly editable by report viewers in the report view so if you want to provide a dropdown for the user in the report create a seperate table :

DateOptions = DATATABLE( "DateParameter", STRING, { {"202401"}, {"202402"}, {"202403"}, {"202404"}, {"202412"} }
)

Drag the DateParameter column from the DateOptions table into a slicer visual and configure the slicer to Single Select.

Create a measure to capture the selected value from the slice :

SelectedDate = SELECTEDVALUE(DateOptions[DateParameter])

Then modify your query to use the SelectedDate dynamically by creating a conditional binding or dynamic M query through query parameters.

https://learn.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameter

https://youtu.be/pXU_SpiBWUA?si=c2pE_ZAWzE2DL6b3

https://blog.crossjoin.co.uk/2020/11/08/handling-multi-select-in-power-bi-dynamic-m-parameters

https://community.fabric.microsoft.com/t5/Power-Query/Dynamic-Stored-Procedure-Call-based-on-user-in...