Install Dynamics CRM Developers Toolkit

Problem:
Visual Studio must be installed before you can install Microsoft Dynamics CRM Developer Toolkit. or you get the dreaded “visual studio .net must be installed before you can install the Microsoft Dynamics CRM Developer Toolkit”

Prerequisites:

  • Microsoft Visual Studio 2012 (NOTE: Since VS2012 is also old, it needs supplements. Please download the service pack called “Update 4”)
  • Silverlight 4. (NOTE: This is a requirement for VS2010. You can create Silverlight projects in VS2012 OOB withing any additional installs.)
  • Windows Identity Foundation 3.5 (Download)

Solution:

As usual I started with most obvious path to solving all problems, Google. Results pointed to the obvious to scary.Here you go.

if you are not able to install Developer Toolkit for Microsoft dynamics CRM 2011 and faced an error as “visual studio .net must be installed before you can install the Microsoft Dynamics CRM Developer Toolkit”
Please try below, it works for me.
  • Go and “run as administrator” the PowerShell (x86)
  • Locate the folder that contains the .msi file (cd c:\…. etc)
  • run the powershell command: MSIEXEC /i crmdevelopertools_installer.msi
(/i argument stands for “install”)
Hope this helps.
Advertisements

How to obtain FetchXML

OK. I think this comes up more often that you can remember. I hope this helps with anyone trying to learn CRM and how to put FetchXML queries to use.

1. Login into the CRM Web Application.
2. Go to the Entity you want to build a FetchXML Query.
3. From HomeGrid view of the Entity, select “Advanced Find”.
4. If you want FetchXML of an existing View, pick the view or if creating a new one, select NEW for the view.
FetchXML.xml from Advanced Find

5. Once you have built your Query (aka Saved Query), click on Download FetchXML. Save FetchXML.xml file.

Open it in a text editor and Viola, you have your FetchXML Query.

In case you were wondering how to get Layout XML, look up my other post here.

Enjoy!.

Where is my Layout XML?

I’m writing this mainly because it gets asked so much and in some cases the newbies are trying to figure out where on earth am I going to get the Layout XML to add a custom view or modify the lookup to use a different view.

Here is a quick way of accomplishing this.

Problem:

Want to create a Layout XML string to be used in JavaScript on a form. Most of us know how to obtain the FetchXML using Advanced Find. that’s easy breezy.

Solution:

  1. Create an empty solution and add the Entity in question which you are trying to change. If you are querying deep and want to display related records field values.
  2. Create a Personal View with the required layout. Save and Publish.
  3. Export this solution as an UNMANAGED Solution. Save the ZIP file.
  4. Unzip the solution file.
  5. Open customization.xml file using Notepad++ and under SavedQueries node for the entity in question, look for the Personal View you created using Name.
  6. Viola, you will see the LayoutXML and FetchXML.

Happy Customization.

Dynamics CRM Report using Excel 2013, oData and PowerQuery.

Cannot say how excited was when I finally found a way to solve the problem of reporting Dynamics CRM Online data with all its restrictions. Hail Excel. Not any Excel, but Excel 2013. Well not just Excel alone, but it was the glue that is bringing different technologies together and made this possible. This is an extension of my previous post on getting around the 10K record limitation.

Working for my customer to build a report that brings Contacts and its associated Account information. These are the ways I tried and their limitations. This is a CRM Online deployment and we have close to 40K contacts and an almost equal number of accounts. I did narrow the record count to by using filters judiciously to not make the system crawl. With these restrictions in mind, here is what I did. Hopefully this keep head in proper shape by not banging on the wall.

a) Advanced Find: Very nice tool to quickly visualize if something possible and I was get all the columns required to provide the report from Contacts and its associated accounts. Viola, this was easy, so I thought. Ran the query and came back with, you guessed correctly, the first 5000 records only. OK, onto the next solution. (I know there is way and tool to increase this limit. Not an ideal scenario users running searches and ending up pulling so many records. )

b) Report to the rescue, so I thought. I took the FetchXML from the Advanced Find and fired up BIDS 2008 R2 and started to put together the report. (Check SDK for how to do this). I placed the correct columns from Contacts and Accounts in the report and hit preview and viola, I instantly got the report to run in Preview. Well, Preview was able to pull in the first 500 contacts that matched the criteria. So I created a NEW report in CRM, used the RDL file from BIDs and published it in CRM.  I was able to specify the criteria and clicked on “Run Report”. Well I should hand it to CRM, it did run, I’m guessing that it timed out due to heavy load of 33K records it was pulling for this report. So I was able to taste a small bit of the fruit, but the not the whole yet.

At this point, I’m wracking my brain on how can I pull 60K records from CRM Online and produce this report. If anyone can come up any other ways of producing this report, please do let me know. Maybe I’m doing something wrong.

c)These two articles from Surviving CRM Blog gave me this idea on dealing with ta large set of data. (http://niiranen.eu/crm/2013/06/dynamics-crm-data-visualization-with-geoflow/ and https://www.youtube.com/watch?v=1er6bu9nmlk ).

SOLUTION:

  1. Download and install PowerQuery for Excel 2013. (Update: PowerQuery has become a part of Excel 2013 with sp1).
  2. Export to Excel a small list of records, can be anything within CRM Org as a Dynamic worksheet. CRM will spit out an .xml file.
  3. Open this in Excel 2013. Under the DATA tab, click on Refresh from CRM to fetch the data and also complete authentication. Save the file as an .xlsx file. (This step is very important to our goal).
  4. Create a oData query using XRM Tools solution withont the org. Note: You cann create the query with related records. Using this in feed definition does not bring in the related record fields. oDate_Query
  5. In my case, I had to create two oData queries, one for Contacts and one for Accounts. In the contacts query, i did pull in the parent Customer Id field in order for me to join with Accounts in Excel.
  6. After you have the queries created and tested in the Browser, in the open Excel 2013 Workbook, create a new Sheet and select “From oData Feed” under DATA/GetExternal Data Tab/From Other Sources”.
  7. Paste the oData query into the link section and complete the dialog with default options. Create a New Sheet when fetching the data executing the oData query.
  8. Do the same for accounts as step 7 with query created in step 5.
  9. In order for records in these two sheets called “Contacts” and “Accounts” to be used in PowerQuery, they have to brought in as a Table from PowerQuery ribbon tab.
  10. You will end up creating two more sheets called “Table_Contacts” and “Table_accounts” for each PowerQuery Table that you create.
  11. There is a MERGE option in PowerQuery ribbon, that will first ask you to establish a unique relationship between these two Powerquery Tables. In my case I selected “Table_Contacts.Parentcustomerid” and “Table_Accounts.Account.
  12. Result is a a new Column with an embedded Table in it. Well this is good that it is presenting as such, however I want the embedded Table column to be expanded into its own fields aslongside with fields from Contacts.
  13. Once you complete the above steps, click on “Done” from PowerQuery.
  14. Bang, you have a report of all Contacts with account data for all 33K contacts.

Let us review what we have accomplished. we have created a report in Excel 2013 with master detail relationship for more than 5K or 10K record, which are a limitation in CRM Online. PowerQuery is awesome tool and thanks the SQL server BI teams for building this tool.

NOTE: all of this possible only when you CRM for Outlook client configured to talk to CRM Online or CRM On-prem instance.

Hope this helps.

CRM 2011: Increase export to excel limit (10000) – Different Approach

Installation Type: CRM Online an/or On-Premise.

Here is the solution if you have CRM Online. I know this works. Using Excel 2013(will not work on 2010).
1. Export a very small subset (say 100 records) of data using Web Client(Browser) using Export to Excel functionality.
2. In the Export dialog, choose “Dynamics Worksheet”. When finished, CRM will spit out an xxx.xml file.
3. Save to Desktop and open it within Excel 2013. Under the Data ribbon tab, select “Refresh from CRM”.
4. Once this is done and you see the exact same set as Export criteria, you can save this file as “.xlsx” file.
5. Create a oData query using open source tools to retrieve say contacts modified in last 30 days.
6. Under Data tab in Excel, “Choose a oData feed from ‘From Other Sources’ under ‘Get External Data’ group”.
7. Paste the oData query into the Browse text box and click NEXT, NEXT, OK.
8. Viola. It will be slow if you have lots of data and you might get kicked out during the import. Be careful with this approach since there is no way to cancel the download. If you have large sets of data, you might want to write oData query that will break it down into smaller pieces.

What I like with approach is it is not a change at Organization level. We can provide this as a feature for Power Users who is more interested in analytics using Power BI (PowerPivot/PowerView/PowerQuery). This approach will bypass the 10000 record limit with Export to Excel from CRM. Just now exported almost 33K contacts record with 19 columns of data. Not sure where the limit is for this approach.

Enjoy!
Prabhakar