Email Router Configuration issues with GMAIL in MSCRM - Solutions Tricks

Configuring CRM Server Side Sync with Gmail - Problem & Solution

Problems in configuring server side sync with Gmail addresses in MSCRM, and it always seems to be the same setting in Gmail which is blocking the connection, so I'm writing about this to make it easier for anyone else setting up server side sync with Gmail (including myself when I have to do this again).

After seemingly setting up the Email Server Profile and Mailbox correctly, when it came to testing and enabling the Mailbox, both the incoming and outgoing tests failed. This doesn't happen all the time, as I tried to recreate the errors with another new Gmail account, and it tested and enabled first time. However, I had already been playing around trying to connect, so I might have done something to lock down the Gmail account etc.

The Email Server Profile is set up using the correct POP and SMTP settings as per other Gmail recommendations, e.g. using pop.gmail.com with port 995, and smtp.gmail.com with port 587. Credentials are specified in the Mailbox, with everything spelt correctly. However, when testing the mailbox I would receive errors stating that the credentials are incorrect.

SOLUTION:

There are two hidden settings within Gmail that may be blocking connections from third party apps. Make sure you access these links while logged into the Gmail account, and make sure they're turned on/enabled.

https://www.google.com/settings/security/lesssecureapps

https://accounts.google.com/DisplayUnlockCaptcha

In my case, the first one was already turned on, however after enabling the second one I was able to successfully perform the Test & Enable on the Mailbox.

The difference between UserId and InitiatingUserId in Plugin of Dynamic CRM 2011

I would like to discuss the difference between UserId and InitiatingUserId of IExecutionContext interface in Dynamic CRM 2011 plugin. The explanations of them are given in MSDN like this:
• IExecutionContext.UserId Property: Gets the global unique identifier of the system user for whom the plug-in invokes Web service methods on behalf of. ^[1]
• IExecutionContext.InitiatingUserId: Gets the global unique identifier of the system user account under which the current pipeline is executing. ^[2]
in Simple:

The context gives us both the GUIDs

• context.initiatinguserid: gets the systemuser GUID who actually fired the plugin
• context.userid : gets the  impersonated systemuser GUID

From the official definition, I feel it is still not very clear. So I set up an experiment in my development environment.

1. Create a Test Entity record and assign it to another user in system:

 

2. Add a plugin in the update stage of Test Entity

 

3. In this plugin, the code snippets of core part are:

 

 

This plugin is running as a system user and assigns ownership of the Test Entity to the IExecutionContext.UserId.

If we look at the Test Entity it is now assigned to someone else (on form refresh):

 

4. Now, uncomment 2nd line and comment out the 1st line so we use IExecutionContext.InitiatingUserId

5. Run plugin again and it will change the ownership:

 

Now we can see the difference between the two options

• With “UserId” the plugin is running under system context, and the “UserId” gives the ID of user we define in plugin, namely “System”. It focuses on “behalf of”.

• Whereas the “InitiatingUserId” is the ID of normal CRM user whose action triggers plugin at the first place.  

Hyperlink from PowerBI to Dynamics 365

Have you ever wished you could link from a table row in Power BI to over to Dynamics CRM – allowing users to filter and view the records in a grid or matrix in PowerBI and when they want more detail, they can click to open the corresponding record CRM?

Wish granted!

To begin: ensure you’ve added the entity’s primary id (GUID) to the dataset for the entity you want to build the hyperlink for.

Add a new column in the entity using this formula as a template:

Opportunity Link = “https://yourorganization.crm.dynamics.com/CRMReports/viewer/drillopen.aspx?ID={” & Opportunities[OpportunityId] & “}&LogicalName=opportunity”

1. Give the link a name (in this case it’s to the opportunity table, so I’ve named it ‘Opportunity Link’)
2. Change the first part of the URL (up to the /CRMReports portion) to match the URL for your CRM organization.
3. Opportunities[opportunityid] is the reference to the recordid field (guid) in the “opportunities” table
4. Replace ‘opportunity‘ with the schema (logical) name of the entity from CRM.
5. Select this new column and on the ‘Modeling’ tab, change the ‘Data Category’ to Web URL.
6. Bonus tip thanks to @CRMChartGuy – On the Formatting tab of the entity, expand the ‘Values’ area and change the ‘URL icon’ setting to “On” – This will change the URL to an icon that looks like a couple of links of a chain. – nice!

Enjoy!

Data Refresh in POWER BI Reports - Error and Solution

I was recently tasked with creating a Power BI dashboard based directly off  Dynamics 365 data. We were in a bit of a rush and even though the performance isn’t great we only needed a few charts and it was only a POC. Using Power BI Desktop you can add a datasource and select Dynamics 365 form the list.

Then it prompts you for the OData service (notice the format it is asking for)

So you fill in your org as prompted, connect up as a user and away you go. You can build you dashboards/reports/datasets and refresh them to your hearts content.

Then when publish your report to the online Power BI service, it allows you to do it no problem. However, if you then try and refresh your dataset, you get an error,

Your data source can’t be refreshed because the credentials are invalid. Please update your credentials and try again.

OK you might say, I’ll re-enter my credentials. So you change the Authentication Method from the default Anonymous to OAuth2 Some times it will work, but you still get the error in sometimes.  

After much searching I came across this TechNet article where it tells you that you must use the new web api in the format https://foo.api.crm.dynamics.com/api/data/v8.1 to connect to your org in PowerBI Desktop. Even though it prompts you for the older SOAP endpoints in the format https://bcrm4.dynamics.com/XRMServices/2011/OrganizationData.svc.

Using the new web api allows you to get at all your data and publish the reports out to PowerBI online and then you can successfully connect and refresh your data.

Microsoft Dynamics CRM 365 Excel Templates

To get started, select your Open Opportunities view and click EXCEL TEMPLATES in the command bar at the top.  Choose Create Excel Template.

Click on the Download File button and save the file to a place on your computer where you will be able to find it again.

Open the file in Excel and you will see a standard Excel spreadsheet.

 Click on Insert Tab in Excel and Add Pivot table

Add Columns and Values which you want to display in the chart.

Save this Excel Sheet and import into MS CRM: Settings-Templates-Document Templates.

 Upload Excel Template which you have saved it.

Once it uploaded go to Opportunities form and Refresh MSCRM application ctrl  + F5.
There you find your Excel Templates once you click you will find Custom Excel Templates which you created.

Click on Download Open Opportunities. 

Open downloaded Excel you can see the all Open Opportunities listed in Table and chart you have created. its almost like Report. Happy CRM'ing

MS CRM Advanced Find – Capabilities and Limitations:

Capabilities:

• Can be used to search any record type (Any Entities Records).
• Can be saved and turned into personal views.
• Results can be exported to Excel, Static Worksheet, Static Worksheet Page only, Dynamics Worksheet, and Dynamic PivotTable.
• Bulk operations can be carried out on Advanced Find results.
• Mass Operations: Mail Merge, Edits, Share, Assign, Follow/Unfollow, Activate/Deactivate records.
• Advanced Find can be able to do Grouping conditions (Group AND, Group OR) for Same Entity.
• Actions you can perform on Advanced Find results like Send Direct Email
• Report wizard that allows us to export the Report to XML, CSV, PDF, MSHTMAL, Excel, TIFF and Word formats.
• Add to Marketing List or create a dynamic marketing list
• Quick Campaign, Add Connection (One Record at a time).
• Run a workflow or start a dialog
• It can run a Report.
• Advanced Finds can Create Views, Excel Templates and Word Templates we can add columns which requires and sorting, Filtering can be applied on Column.
• Users can create complex filters and queries based on any fields within or related to the record being searched.
• Security is applied on Advanced Find Results, Based on Security role records will be displayed to users.
• Records can be shared with Users or Teams.
• We can download FetchXML Query and it can be used for retrieving records using Jscript and Plugins.

Limitations:

• We can not add Related entities columns to a view in 1:N relationship. And in N:1 related entities we can not add more than one related entity columns to the view. 
• It can able to Retrieve the records only N: 1 Related entities, It cannot retrieve the records if it has 1: N Relationship.
• Advanced Find cannot shows the N: N Related Entities records.
• Grouping Conditions: Can be applied within the same entity, Related entity conditions and groups cannot be grouped with Parent entity Conditions and Groups.
• Outer Joins are not possible with Advanced Find.
• Field Level Security: For the System entities, such as Account, Contact, Lead etc., only the custom fields can be setup to have field-level security, For Customer entities, and all fields can be setup to have field-level security.

MSCRM vs SSRS vs Power BI Reports: Dynamics CRM 365 - Reporting Limitations and Capabilities:

MS CRM Report Wizard (Out of Box Reports) – Capabilities and Limitations:

Capabilities:

• Report wizard that allows us to export the Report to XML, CSV, PDF, MSHTMAL, Excel, TIFF and Word formats.
•  Allows you to filter what entities and fields you want display, Ability to prefilter the records when running the report to give more flexibility to the results.
•  Group and summarizes data.
• Share with the entire organization.
• Add visuals such as charts and tables.
• Drill-down into the data
• Run reports from lists and forms of underlying entities, 
• Download and edit using advanced tools like Microsoft’s Business Intelligence Development Studio.
• Security is applied on MS CRM out of Box reports. It can be shared and Assigned with users or Teams. 
• It supports Group summary like Average, Maximum, Minimum, Percentage of total, Sum are the summary types that can be used in report wizard for a field.
•  It can aggregate count of Records.

Limitations:

• Entities could be involved in generating a report: One primary entity and its first level related entity. It cannot have more than one related entity. 
• Use of Sub Reports, Hyper Link and Lookup Functionality does not support.
• It cannot allows you to add Header - Body – Footer.
• No Page Nos. in footer.
• Does not have Report filters, Parameters, Hide Show based on expressions.
• There is no way to add new parameter to the report through report wizard.
• It doesn’t support change in Design, Font, and Color Change. Adding extra rows and columns. 
• It cannot be Accessing Current Date Time.
• Don’t have Tools like line, list, image, sub report, gauge, map, indicator and Spark-line. 
• Adding extra rows and columns is not possible.
• Only vertical bar chart, horizontal bar chart, line chart, pie chart are supported in report wizard.
• Supports only limited group summary like Average, Maximum, Minimum, and Percentage.

SSRS Reports – Capabilities and Limitations:

Capabilities:

• SSRS overcomes all the limitations of MS CRM Report Wizard.
• These reports can be scheduled, delivered by email and other mechanisms.
• Reports can achieve complex requirements as you can use any feature from SQL Reporting Services.
• SSRS reports supports all Aggregate Functions and Grouping.
• Add visuals such as charts and tables.
• SSRS Reports supports user if sub reports, Hyper Link and Lookup functionality in Reports.
• We can Header, Body and Footer and page Nos in Footer.
• It supports change in Design, Font, and Color Change. Adding extra rows and columns.
• We can add any number of entities to a report unlike out of box CRM reports.
• It has tools like line, list, image, sub report, gauge, map, indicator and Spark-line.
• It can supports all types of charts.
• It can uses custom code in order to generate reports as per the requirement.
• Report filters are available in SSRS reports.
• Can access current date and time.
• Hide and show based on expressions.
• Once we added SSRS reports to MS CRM, CRM can provides security and data will be displayed based on the security roles like MS CRM out of box reports.

Limitations:

• Till now I didn't find any limitations on CRM related Reports, It overcomes all the limitations of MS CRM Reports. 
• Suggestions accepted if any one find out.

POWER BI Reports – Capabilities and Limitations:

Capabilities:

• It’s a reporting tool with a friendly interface for creating dashboards that can connect to multiple data sources within your organization.
• Power BI can create dashboards with charts & graphs that each connect to a different database, it can connect to multiple on premise and online data sources within your organization.
• Filter/drill down into charts with highlighting capabilities:  When selecting a point of interest in one chart, the related chart will automatically drill down into the same area.
• In addition, it offers interactive chart capabilities and allows end users to drill into a particular chart using their natural language. For those who want to report on more than just their Dynamics CRM data, this tool could be invaluable.
• The dashboard visualizations are best in class and continually updated from the community. Interactive geo-map visualizations are empowered by Bing Maps.
• Power BI puts business intelligence creation into the hands of analysts who can extract source data, create a dataset, transform or manipulate the data, visualize the data and publish the resulting reports and dashboards.
• We can export the data in Excel and CSV formats.
• The Question and Answer (Q&A) function may be the top cited benefit and capability in achieving self-service BI.
• Entities could be involved in generating a Power BI report: There is no limitations like MSCRM reports.

Limitations:

• In MS CRM reports and SSRS reports -it retrieves live data automatically when an end user views the dashboard but in Power BI we have to set auto refresh frequency.
• Exporting data only supports Excel and CSV file formats, it is not supporting other formats like MS CRM or SSRS reports.
• The integration to Dynamics CRM is limited. Power BI can extract CRM data via an OData feed, so getting the CRM data into the data model is easy, but thereafter there's no supported method to view the CRM reports and dashboards within the CRM application. Requiring users to log in to a different application to view dashboards creates a barrier to utilization and adoption.
• Dashboards and reports can only be shared with users who have the same email domains or email domains listed with your Office 365 tenant.
• While a dataset can include multiple data types, Power BI reports and dashboards can only source data from a single dataset.
• Custom visuals, and R visuals, are not currently supported.
• The maximum number of rows that can be exported to .csv is 30,000.
• Power BI only supports export in visuals that use basic aggregates. Export is not available for visuals using model or report measures.
• Similarly, Power BI cannot mix imported data with data accessed from real-time connections. It's one or the other.
• Power BI will not accept files larger than 250 MB. Power BI files - .pbix
• Provides less security over the data when you compared with MS CRM and SSRS Reports.
• While a dataset can include multiple data types, Power BI reports and dashboards can only source data from a single dataset. Similarly, Power BI cannot mix imported data with data accessed from real-time connections. It's one or the other.
• This Microsoft solution is normally used to extend ― not replace ― other reporting tools. In most cases, it will not replace your enterprise data warehouse. For most companies, it is likely that their enterprise data warehouse tools will continue to be used for high volume data processing reports which do not change much, while Power BI may be used for one time, progressive or more frequently changing analysis on smaller data sets.

Conclusion: 

                    If client wants to have good reports with visualization, complex queries, Data exporting options, very good security etc., without any  easily we can go for SSRS reports compared to MSCRM and Power BI reports.