Tuesday, May 19, 2015

Hide / Show Export to Excel button for specific Entity / Specific View

In MS CRM, Export to excel feature is enabled or disabled from security role.
You can have this feature for all applicable entities or none of the entity.
But sometime we have requirement to hide export to excel from particular entity, or show only for particular entity.
With Security role this requirement is not possible, but will little JavaScript code and ribbon customization we can achieve this.

Case 1: Hide Export to Excel button from contact Home grid.

1.      Create one custom solution
2.      Add Application Ribbons – from client Extensions

3.      Create one JavaScript web resource in this solution and add following code

function ShowHideExport2Excel(SelectedEntityTypeName) {
    if (SelectedEntityTypeName == 'contact')
        return false// hide button
        return true//show button

4.       Now Open this custom solution into Ribbon workbench
5.      In the Entities list you will see ApplicationRibbon
6.      Select Export to Excel button from Home, right click and Customize command

7.      In Commands, you will see Mscrm.ExportToExcel
8.      Now add custom Enable Rule to this command. Keep existing enable rules.
9.      When adding enable rule, add Custom JavaScript rule.
Default: True
FunctionName: ShowHideExport2Excel
invertResult: False
Library: your JavaScript library
Parameters :  [Crm Parameter] = SelectedEntityTypeName

10.  Add Crm Parameter to Custom JavaScript rule, value of this parameter must be SelectedEntityTypeName

11.   Save and publish your customization.
12.  Now Export to Excel button is hidden for Contact home grid.

Export To Excel button is not showing now.

 Export To Excel button is showing now.

Case 2: Show Export to Excel button only for contact Home grid.

In this case you just need to change JavaScript code little bit as

function ShowHideExport2Excel(SelectedEntityTypeName) {
    if (SelectedEntityTypeName == 'contact')
        return true// show button
        return false//hide button

Case 3: Show Export to excel button for particular entity and particular view only

[To do this need to use Unsupported JavaScript code]
To do this, we need to add one more Crm parameter to enable rule, and select value to this parameter is SelectedControl

And modify JavaScript function as

For MS CRM 2013 :

function ShowHideExport2Excel(SelectedEntityTypeName, selectedCtrl) {
    if (SelectedEntityTypeName == 'contact') {
        var view = selectedCtrl.get_$1X_3();
        var viewName = view.selectedViewName;
        if (viewName == 'My Active Contacts') //Change "My Active Contacts" with Your view name to show Export To Excel button
            return true; // show button
            return false; // hide button
        return true;

For MS CRM 2015:

function ShowHideExport2Excel(SelectedEntityTypeName, selectedCtrl) {
    if (SelectedEntityTypeName == 'contact') {        
        var viewName = selectedCtrl.get_viewTitle();
        if (viewName == 'My Active Contacts'//Change "My Active Contacts" with Your view name to show Export To Excel button
            return true// show button
            return false// hide button
        return true;

Wednesday, May 6, 2015

Running CRM Dialog from Custom Ribbon button

 1.      Add custom button on form
Say Run Dialog

     2. Now create command for custom button

     3. Add  Action to command

For the action add “Open Url Action”

3. For Url Command set your dialog URL
         To get dialog URL run dialog using “Start Dialog” button

Then select dialog you want to run on button click, once dialog started copy URL of dialog from address bar

 5. Your dialog url will be look like 

https://{CRM Server}:444/cs/dialog/rundialog.aspx?DialogId=%7b83D90936-EBEC-4CE5-8A58-A1530DE30680%7d&EntityName=new_test&ObjectId=%7b0FCB7F66-5FA6-E411-B1DF-0050568C6D7D%7d

It has Dialogid, Entityname and ObjectId
ObjectId is the record id on which this Dialog is running.

 6. If you use this URL as it is on button command then dialog will be run on same record again and again. 

      7.  To pass ObjectId / record id dynamically, need to pass one parameter to Url command, defined for custom button. This parameter should be like

    8. Now update your URL command address to

https://{CRM Server}:444/cs/dialog/rundialog.aspx?DialogId=%7b83D90936-EBEC-4CE5-8A58-A1530DE30680%7d&EntityName=new_test

Noticed that I removed &ObjectId=%7b0FCB7F66-5FA6-E411-B1DF-0050568C6D7D%7d, this objectId is passed by parameter, that’s why Parameter Name is important. 

    9.  Save and publish your customization.

   10.   Now dialog will be run for record you are in from button.

Tuesday, May 5, 2015

Convert FetchXML to SQL query

Some time we need to get SQL query for fetchxml. For MS CRM application you can get FetchXML‘s SQL query by running profiler on SQL server and find the query from profiler.
But if there are more users accessing CRM, then there are lot of queries are executing and we need to go through each query and need to relate to our fetchXML.
This tool converts existing entities system view FetchXML queries to SQL queries. Also you can enter directly fetch XML and it will convert to SQL query.

1.      Convert System View FetchXML to SQL query
a.      First select Entity from drop down
b.      Select system view, FetchXML of selected view will be converted to SQL query.
c.       Click on Get FetchXML, FetchXML for selected view will be retrieved from CRM and displayed in Textbox
d.     If required you can modify fetch xml as per requirement

e.      Click on Get SQL query, your FetchXML will be converted to SQL query and shown in textbox.

2.       Manual FetchXML to SQL
a.      Enter FetchXML in first text box, you want to convert into SQL query
b.      Click on Get SQL query, FetchXML converted and shown in textbox. 

User or Team filters
-          Based on current user all conditions are shown.

-          If any filter condition for user and/or userteam, then current user id and user teams will be retrieved and Id’s will be shown in SQL query.

Date time operator
-          Most of the time showing CRM database functions in SQL query. Because date time is varying based on user settings.

-          For some of the date time operators it is very hard to find exact value. So just showing what will be the possible values.

e.g. Operator  = last-x-fiscal-periods, every CRM might have different fiscal period setting, so user need to put those dates.

For Managed solution : https://fetchxml2sql.codeplex.com/

Tuesday, April 28, 2015

Run report from custom ribbon button for selected records

MS CRM has feature to run report on selected records, when you create report using SQL queries you need to specify one report parameter with prefix CRMAF_, this parameter will allow you to pre- filter report.
And when user selects records from grid and run report from report menu, then CRM asking to select records / use these records dialog, in this dialog user need to select either all applicable records, all records on all pages in the current view or selected record.
But sometime user doesn't want to see this dialog or wants custom button to run specific report on selected records.
To do this, first need to add custom button on ribbon and add call JavaScript function on button click.
When calling JavaScript function from ribbon pass Crmparamter SelectedControlSelectedItemIds
 as parameter to JavaScript function, so that we will get all selected record ids in JavaScript function.

And JavaScript function should be like

function RunReportforSelectedRecords(selectedRecords)
    var selectedIds;
    for (var i = 0; i < selectedRecords.length; i++) {
        if (i == 0)
            selectedIds = selectedRecords[i];
            selectedIds = selectedIds + "," + selectedRecords[i];
     var rdlName = "Your Report Name.rdl";
    var reportGuid = "8ca0d6b4-f8ec-e411-8ca2-0050568c6d7d"// Report GUID - Replace with your report GUID
    var entityType = "10012"// Entity Type code - Replace
    var para = encodeURIComponent(entityGuid);
    var url = Xrm.Page.context.getClientUrl() + "/crmreports/viewer/viewer.aspx?action=filter&helpID=" + rdlName + "&id={" + reportGuid + "}&p:selectedContactIds=" + selectedIds;
    window.open(url, null, 800, 600, truefalsenull);

This will open new window and report will be executed.

To open report I used report URL

"/crmreports/viewer/viewer.aspx?action=filter&helpID=" + rdlName + "&id={" + reportGuid + "}&p:selectedContactIds =" + selectedIds

This URL contains

Two possible values for this parameter are run or filter. When run is used, the report will be displayed using the default filters. When filter is used, the report will display a filter that the user can edit before choosing the Run Report button to view the report.
This parameter is optional. For reports that are included with Microsoft Dynamics CRM the value in this parameter allows the Help button to display appropriate content about this report when Help on This Page is chosen. The value should correspond to the report FileName attribute value.
This parameter is the report ReportId attribute value.

More details on Opening a Report by using a URL

p: CustomParameterName
This is custom parameter I used in SSRS report.
To pass parameter value using url we need to specify p:{parameter name}

In this example I am getting string of selected record GUIDs and I am passing it to report.
And I am using these selected GUID in report query like below

CREATE TABLE #selectedContacts(ContactId uniqueidentifier)

 declare @delimiter CHAR(1) =','
  DECLARE @start INT, @end INT
    SELECT @start = 1, @end = CHARINDEX(@delimiter, @selectedContactIds)
    WHILE @start < LEN(@selectedContactIds) + 1 BEGIN
        IF @end = 0 
            SET @end = LEN(@selectedContactIds) + 1
              INSERT INTO #selectedContacts (ContactId) 
        VALUES(SUBSTRING(@selectedContactIds, @start, @end - @start))
        SET @start = @end + 1
        SET @end = CHARINDEX(@delimiter, @selectedContactIds, @start)

select FirstName, LastName
         , EMailAddress1
         , Telephone1
 from Contact
where ContactId in
  select contactid from #selectedContacts

When use this query as it is in report SSRS will create report parameter with name @selectedContactIds as this is the only variable not declared in this query.

 In this approach passing record ids in URL, generally URL will support up to 2048 characters, and that’s limitation of around 50 records will come when executing report this way.