CRM Online + SharePoint Online + FetchXML?

(1) Share

Report

Posted on by Rick Towns

Hi everyone,

We have a couple of clients that will be going with CRM Online and we want to use SharePoint Online for their document management/storage. Integration is easy and works well. My issue is that both clients require SSRS reports that will involve referencing the documents they are storing. Currently, with CRM On-Prem and using it's Notes facility, this is simple. Everything required can be retrieved with a FetchXML call, and can be used in SSRS directly.

My issue is there seems to be no way to query the SharePoint documents using FetchXML. I have tried with both CRM On-Prem and Online, and doing an advanced search for anything related to the SharePoint docs other than their basic location results in an error: "SharePoint document entity does not support any view other than associated grid view".

Is there any advice concerning this? I would love to build a basic report that simply gets a list of Accounts, and any SharePoint documents associated with that Account. The FetchXML for getting the Accounts is easy:

Is there a way to query the SharePoint documents (ie: get their Names) from within a single FetchXML query?

Thank you so much!

Rick

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (1)

Verified answer

Bas van de Sande 4,383 on at

Like (1)

Report

Hi Rick,

unfortunately not.

you have 3 entities within CRM:

sharepointsite : contains all sites/libraries on which documents are stored

sharepointdocumentlocation: contains the relative path (on folder level) to the document

sharepointdocument: not used in the standard integration (at least I've never seen it being used)

You need to figure out another solution.

Bas

Was this reply helpful? Yes No
Rick Towns on at

Like (0)

Report

Hi Bas,

Thanks for that info! At least I can stop banging my head against the wall trying to make this work. Perhaps I can try combining a JQuery against SharePoint's XML feed? If I figure it out, I will post it here.

Thanks again!

Rick

Was this reply helpful? Yes No
Bas van de Sande 4,383 on at

Like (0)

Report

Hi Rick,

SharePoint offers a rich REST API. That is the way you should go to retrieve the documents

check the reference over here:

msdn.microsoft.com/.../dn292553.aspx

On my blog, I already did a lot of research on connecting SharePoint and CRM

Happy coding

Was this reply helpful? Yes No
Rick Towns on at

Like (1)

Report

Hi Bas,

Yes, the REST API looks good, and may eventually be my solution. I am using Visual Studio 2012 with BIDS to create my .rdl file that I will eventually upload to CRM Online. My problem now is that regardless of whether I create a SharePoint List datasource, or an XML datasource (pointing to xxx.sharepoint.com/.../Account for example), I get authentication errors (ie: 403 Forbidden). I assume this is because SharePoint Online is using Claims Based authentication which isn't supported with the SSRS I am using. Why does Microsoft make this stuff so difficult? ;)

If I can get this authentication issue figured out, I will try to use the LookUpSet function to combine my CRM and SharePoint datasources. I want to avoid coding a custom integration solution. It kind of defeats the purpose of storing information in SharePoint Online if it is not easy to report against from CRM Online.

Thanks!

Rick

Was this reply helpful? Yes No
Bas van de Sande 4,383 on at

Like (0)

Report

Hi Rick,

I already figured the authentication issue out. On my blog (also syndicated here) I posted a better SharePoint Connector. journeyintocrm.com/.../1152

The code is written in C#, but might help you to solve your connection problems.

You happen to be fluent in C#??

Bas

Was this reply helpful? Yes No
Rick Towns on at

Like (0)

Report

Hi Bas,

Yes, we do 95% of our development in C# here (the other 5% is VB for SSRS!). I will have a look at your post - thank you very much for the link! If I have to do a coded solution, I will - I was just trying to see if it could be done out of the box to simplify deployment.

Thank you so much for your effort! I do appreciate it. :)

Rick

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Any solution ?

Was this reply helpful? Yes No
Rick Towns on at

Like (0)

Report

Unfortunately no. However, D365 is now out, and I plan on investigating to see if its support of SharePoint is better integrated.

Was this reply helpful? Yes No