Fetch XML Vs SQL query
-
04 Mei 2012 22:27
We started using ms crm 2011
I would like to know how to continue using sql query during building custom reports?
I can not write complex queey statment i do have many custom tables coming from our other system that contains Financial data.
Regards,
Hussain
hussain
Semua Balasan
-
05 Mei 2012 0:18
Hi Hussain,
You are limited to using FetchXML for custom queries for CRMOnline. However, you can setup a custom report that uses SQL to query a database.
You will need to use Business Intelligence Development Studio (BIDS) to create the custom report and it will let you create a datasource that points to your CRM OnPremise database or another external database. Once you have created the datasource you can import the report through CRM by going to Reports > New and selecting Report Type of Existing File, which will let you upload the .rdl file for the report.
The problem I ran into the first couple times I tried to setup reports that pulled from an external datasource was that I used a shared datasource, and CRM does not automatically pull that in when you import the .rdl file. All you have to do is setup that shared datasource in the Custom Reports folder on the report server. You can do that by deploying it through BIDS or through the ReportServer website by:
1. Log into the report server through the url <a href="http:///Reports">http://<yourreportserver>/Reports
2. Select the <yourorg>_MSCRM folder .
3. Press the Details View button on the upper left to show the hidden files/folders.
4. Open CustomReports.
5. Click on New DataSource. Make sure you use the same name from your report.
- Disarankan sebagai Jawaban oleh CRM Nerd 05 Mei 2012 0:18
-
05 Mei 2012 10:22Does this mean that i can not use Report Builder 3.0 anymore?
hussain
-
05 Mei 2012 10:27
if you are using fetchxml reports then you have to install crm fetch extension components for BIDS
if you are using sql then you can directly query on FilteredViews
If the reports are simple then you can go with fetchxml but it has lot of limitations in terms of writing queries.
SQL reports are not supported in online they are supported only in on premise
-
05 Mei 2012 14:28Moderator
This article might be helpful in identifying the differences between SQL based reports and FetchXML reports: http://gonzaloruizcrm.blogspot.ca/2011/07/sql-vs-fetchxml-reports-in-crm-2011.html
In general, for really complex queries you might be better off with SQL Reports.
Gonzalo | gonzaloruizcrm.blogspot.com
-
05 Mei 2012 15:15Penjawab Pertanyaan
Hi Hussain,
That's right - unfortunately you cannot author FetchXml based reports in Report Builder 3.0 - you must use Business Intelligence Developer Studio (BIDS).
Scott
Scott Durow
Read my blog: www.develop1.net/public
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful" -
05 Mei 2012 15:20
Does this mean that i can not use Report Builder 3.0 anymore?
No, Report Builder 3.0 is fine. You just need to create an report file .rdl, it doesn't matter if you use BIDS or Report Builder as the authoring tool to create them. It would be the same process I outlined above if you create the report through Report Builder.
hussain
-
05 Mei 2012 15:32Penjawab Pertanyaan
Hi Crm nerd,
Have you managed to get Report Builder to work with FetchXml? I've never managed to get it working which has always frustrated me - it seems that the connection providers that are used by VS are different to those used by Report Builder.
Scott
Scott Durow
Read my blog: www.develop1.net/public
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful" -
06 Mei 2012 21:05
Cheers Scott,
I do not know of any tricks to use FetchXml with Report Builder. As far as I know the Report Authoring Extension that adds the FetchXML is only available for BIDS.
However, according to Hussain's original post he is looking to pull data from another system and wants to continue using SQL. In that case he can stick with Report Builder as he doesn't need FetchXML.
- Diedit oleh CRM Nerd 06 Mei 2012 21:06
-
06 Mei 2012 22:07Penjawab Pertanyaan
@Crm nerd - got it! My wires were crossed I think!
@Hussain - any progress with this one?
Scott Durow
Read my blog: www.develop1.net/public
If this post answers your question, please click "Mark As Answer" on the post and "Mark as Helpful"
