cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mark_Holtham
Regular Visitor

Dynamics 365 Related Table Filtering

Hi,

 

I've started to move existing report queries from "Tenancy/XRMServices/2011/Organization.svc" to  OData 9.0 and finding that existing related table filtering is not working. I suspect it is a syntax issue but cannot find a workable solution online.

 

In my example I am trying to get order lines relating to a specific data in the order header. There are is a custom date field I normally reference which differs from the main created date but this does not work either

 

 

Source = OData.Feed("<tenant>.api.crm.dynamics.com/api/data/v9.0/salesorderdetails?&$expand=salesorderid&$filter=salesorderid/createdon gt 2016-11-01

 

Any chance someone can advise me on URL filtering for this scenario?

1 ACCEPTED SOLUTION

It seems that I have hit a bit of a limitation on the current implementation of REST API 9.  The other forum didnt quite help but kindly presented enough information for me to find an alternative.

 

Instead of using a $filtering, I have referenced an existing system view in Dynamics 365. I know the report is at risk of any changes to the system view, but the approach offloads the query to Dynamics, which then is pulled in to Power BI.

 

let
Source = OData.Feed("https://<tenant>.api.crm4.dynamics.com/api/data/v9.0/invoices?savedQuery=00000000-0000-0000-0000-000000000000", null, [Implementation="2.0"])

in

Source

 

The savedqueryid was found in the savedqueries table.

 

I hope this can be of use to someone.

View solution in original post

4 REPLIES 4
v-yuezhe-msft
Microsoft
Microsoft

@Mark_Holtham,

This issue is more related to Dynamics 365, please post the question in Dynamics 365 forum to get correct OData URL, then use it in Power BI Desktop.


Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

A Microsoft engineer asked me to transition to the API 9.0 URL. Can you claify which one I should be using?

 

I have posted in the other forum but I'm sure other users would appreciate a solution here.  The Power BI forums is a great resource point.

It seems that I have hit a bit of a limitation on the current implementation of REST API 9.  The other forum didnt quite help but kindly presented enough information for me to find an alternative.

 

Instead of using a $filtering, I have referenced an existing system view in Dynamics 365. I know the report is at risk of any changes to the system view, but the approach offloads the query to Dynamics, which then is pulled in to Power BI.

 

let
Source = OData.Feed("https://<tenant>.api.crm4.dynamics.com/api/data/v9.0/invoices?savedQuery=00000000-0000-0000-0000-000000000000", null, [Implementation="2.0"])

in

Source

 

The savedqueryid was found in the savedqueries table.

 

I hope this can be of use to someone.

View solution in original post

@Mark_Holtham,

Glad to here the issue is solved, you can accept your reply as answer to close this thread.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors