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
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.
