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];

        else

            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, true, false, null);

}

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

action:

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.

helpID:

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.

Id:

This parameter is the report ReportId attribute value.

More details on Opening a Report by using a URL

https://msdn.microsoft.com/en-us/library/gg328483.aspx

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)

    END 

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.