Pass Dynamic Parameter via slicer/filter for Stored Procedure in DirectQuery

[voidbyname]

Hi, all.
I want to call stored procedure in DirectQuery using dynamically user parameter via slicer.....

I tried with openrawset and openquery but it gives me error while fetchung data and showing that metadata cant be recognized due to use of temp Table...

How can I acheive this requirement...




same if we can do this using dynamically calling the REST API


Thanks in Advance

[johnbasha33]

@voidbyname  

To call a stored procedure in DirectQuery mode with dynamically changing user parameters via a slicer, you'll typically need to use Power BI's M language (Power Query) along with a SQL query to achieve this.

Here's a general approach:

1. Define a Parameter Table: Create a table in Power BI that holds the parameters you want to pass to the stored procedure. This table will be connected to a slicer, allowing users to dynamically change the parameters.

2. Get the Parameter Value: Retrieve the selected parameter value from the slicer using Power Query. You can do this by referencing the slicer value in a Power Query step.

3. Compose the SQL Query: Create a SQL query string in Power Query that includes the parameter value(s) obtained from the slicer.

4. Execute the Stored Procedure: Use the SQL query string to execute the stored procedure in DirectQuery mode. You can use the Sql.Database function in Power Query to execute SQL queries in DirectQuery mode.

Here's an example of how you might implement this in Power Query:

let
// Step 1: Get parameter value from slicer
SelectedParameter = TableName[ParameterColumn]{0},

// Step 2: Compose SQL query with parameter value
SqlQuery = "EXEC YourStoredProcedure @Parameter = '" & SelectedParameter & "'",

// Step 3: Execute stored procedure in DirectQuery mode
ExecutedStoredProcedure = Sql.Database("YourServer", "YourDatabase", [Query=SqlQuery])
in
ExecutedStoredProcedure

Regarding calling a REST API dynamically, you can use the Web.Contents function in Power Query to make HTTP requests to a REST API. You can also pass parameters dynamically to the API using this function.

Here's a general approach:

1. Define a Parameter Table: Similar to the stored procedure approach, create a table in Power BI that holds the parameters you want to pass to the REST API.

2. Get the Parameter Value: Retrieve the selected parameter value from the slicer using Power Query.

3. Compose the API Request: Create the URL for the API request in Power Query, including the parameter value(s) obtained from the slicer.

4. Make the API Request: Use the Web.Contents function to make the API request. You can include additional options like headers, query parameters, etc., as needed.

5. Parse the API Response: Parse the JSON response from the API request using Power Query's JSON functions.

Here's a simplified example:

let
// Step 1: Get parameter value from slicer
SelectedParameter = TableName[ParameterColumn]{0},

// Step 2: Compose API request URL with parameter value
ApiUrl = "https://yourapi.com/resource?param=" & SelectedParameter,

// Step 3: Make the API request
ApiResponse = Web.Contents(ApiUrl),

// Step 4: Parse the API response
JsonRe

sponse = Json.Document(ApiResponse)
in
JsonResponse

Did I answer your question? Mark my post as a solution! Appreciate your Kudos !!