Forum Discussion
Run report with dynamically filter on sysdate
Hi,
I need to run one report everyday and i need that the day is changed automatically.
I have used a service to access on my database (SAP4/Hana), and I used an URL on ODATA .
Source = OData.Feed( xxxxxx
filter eq '1100' and IdateStart eq datetime'" & DateTimeZone.ToText(Date.AddDays(DateTimeZone.LocalNow(),-1) , "yyyy-MM-dd") & "T00:00:00' and IdateEnd eq datetime'" & DateTimeZone.ToText(DateTimeZone.LocalNow() , "yyyy-MM-dd") & "T23:59:59')", null, [Implementation="2.0"]),
Rename Columns" = Table.RenameColumns(Origine,{{"YieldStnd", "PzBuoniTarget"}, {"Yield", "PzBuoni"}, {"StrNSf", "ColpiPressa"}, {"StrPMin", "StrokeMin"}, {"StrPMinStnd", "StrokeTarget"}, {"MultiShape", "Figure"}, {"StrNYieldConf", "Colpi??"}, {"StrNYieldSub", "ColpiBuoniConf"}, {"StrNScrapSub", "ColpiScartoConf"}})
The problem is , if i try to scheduled a refresh of data, the Gateway return an error:
This Set of data includes a dynamic origin.
So the dynamic origin can't be updated in the service of Power BI.
How could i resolved it?
Thank you all for the
4 Replies
Hi, ffontana
You can refer to these links and check if they can be helpful:
https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-connect-odata
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Robert ,
thank you for your support.
So i must modify my URL using JSON.Document instead of ODATA feed , is it right?
OData.Feed("https://sapxx.com:8xxx/sap/opu/odata/sap/service/EventiSet?$filter=(Iwerks eq '1100' and IdateStart eq datetime'" & DateTimeZone.ToText(Date.AddDays(Today,-1) , "yyyy-MM-dd") & "T00:00:00' and IdateEnd eq datetime'" & DateTimeZone.ToText(Today, "yyyy-MM-dd") & "T23:59:59')", null, [Implementation="2.0"]),
I need to pass in my URL the variable TODAY with the value is changed automatically every day.
So how could i do it?
I would like update the set of data , using a gateway , because i need every day to plublic the report , with refreshed data of the day.
Please give me a solution for this, now if i try to use gateway, Power Bi give me an error connected to dynamic variable used.
Many thanks.
Hi robert I must use ODATA , because my service sap used it to extract data.
Hi, ffontana
To make the date changed automatically every day in your Power BI report, using the Date.IsInCurrentDay() in the Power query or using the relative time slicer and filter in Power BI can also be a good choice to achieve this if using the URL filter is failed.
You can also follow these links:
https://docs.microsoft.com/en-us/powerquery-m/date-isincurrentday
https://docs.microsoft.com/en-us/power-bi/create-reports/slicer-filter-relative-time
If you still have a problem with the URL filter with Odata, I suggest you to go to open a support ticket and ask the technical support team for their opinion about this question.
https://powerbi.microsoft.com/en-us/support/
Best Regards,
Community Support Team _Robert Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
