Forum Discussion

AmarishJayanth's avatar
AmarishJayanth
New Member
1 year ago
Solved

ODATA service URL as dynamic parameter

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,...
Need Help
Show and Tell
Tutorial Requests
  • v-ssriganesh's avatar
    v-ssriganesh
    1 year ago

    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's avatar
SacheeTh
Resolver II
1 year ago

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.