ODATA service URL as dynamic parameter

[AmarishJayanth]

I am in the process of preparing the dashboards in Power BI. The data source is ODATA services from SAP. I am able to successfully get the data from SAP using the ODATA service call in Power BI. Now, i have a requirement. The Business user will change the date in PowerBI dashboard. I have to pass the selected date in the date slicer to the ODATA service URL as dynamic parameter. How to achieve this?

[v-ssriganesh]

Hi @AmarishJayanth 

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh · Accepted solution]

Hi @AmarishJayanth 

Thanks for reaching out to the Microsoft fabric community forum.

you can create a parameter in power query, then set the parameter with slicer, you can refer to the following link.

Dynamic M query parameters in Power BI Desktop - Power BI | Microsoft Learn

The following sample cannot allow the user to input the parameter, if you want to edit the parameter to change the filter, you can only do it in power query or edit it in data source setting when the report is published to Service, you can refer to the following link.

Edit parameter settings in the Power BI service - Power BI | Microsoft Learn

Sample data 

Step1: Create a parameter in power query

Step 2: Modify the ODATA Service URL:

Open Power Query Editor by navigating to the "Home" tab and clicking on "Transform Data".
Locate the query which retrieves data from the ODATA service.
In the query, replace the static date in the ODATA service URL with the parameter you create. For example:

let

    Source = OData.Feed("https://your-odata-service-url?date=" & Date.ToText(SelectedDate, "yyyy-MM-dd"))

in

    Source

Step3: Load the Data

• Close and apply the changes in the Power Query Editor.

• The data will now be filtered based on the selected date in the slicer.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hope this will help.

Thank you.

[SacheeTh]

Todate I don't have an idea how to pass a report level parameter to Mcode itself in service, but 
you can use Power BI Report Builder to achieve this dynamic parameterization for ODATA services, but it works slightly differently than in Power BI Desktop. Power BI Report Builder is optimized for paginated reports, and parameters are a native feature of these reports. Here's how you can achieve it:

---

I'll try to say this in Steps so that you can try in Power BI Report Builder & let us know the steps you can't achirve:

1. Set Up the Data Source:

• Open Power BI Report Builder.
• Create a new data source connecting to your SAP ODATA service.
• Use the ODATA URL in the connection string and ensure it's working.

2. Create a Dataset:

• Create a dataset using the ODATA data source.
• Include a placeholder for the dynamic date parameter in the ODATA URL. For example:

https://yourSAPServer/odataService?filter=date eq @SelectedDate

• Replace @SelectedDate with the parameter you will create.

3. Define a Parameter:

• Go to the Parameters section and create a new parameter:
  • Name: SelectedDate
  • Data Type: DateTime
  • Set the default value (optional).
  • Allow the user to select values.

4. Bind the Parameter to the Dataset:

• Edit the query of your dataset to include the @SelectedDate parameter where the date value is needed.
• For example: 

https://yourSAPServer/odataService?filter=date eq '@SelectedDate'

5. Create a Date Picker in the Report:

• In the report design, the parameter SelectedDate will automatically render as a date picker when the report is run.
• This allows the user to select a date dynamically.

6. Test the Report:

• Preview the report.
• Select a date using the date picker.
• Verify that the ODATA query fetches data for the selected date.