Prefiltered Reports in CRM 2011 Online - Keep It Simple! 

We recently encountered an issue in Dynamics CRM Online 2011 where a client wanted to embed a report on a Product page and filter it to only show information related to that Product.

There are a lot of blog posts out there on this one, and we tried several different approaches and wanted to share what worked for us. Generally, we learned to not overcomplicate it and don’t be afraid of the jScript – keep it focused on the following simple steps:

1. Create a FetchXML-based RDL file and add the filter clause
2. Deploy the RDL into your environment and drop in the JavaScript that will pass the filtered parameter

Here’s the details. As a very simple proof of concept, we created an RDL that returns all the Products in the system. Before starting, we assume that you’ve set up an RDL file with a data set in Visual Studio 2008 with BIDS and have a FetchXML handy with your query. Here’s a handy link on the basics from Richard Knudson, the Tom Brady of CRM bloggers. https://www.youtube.com/watch?v=R7uj6X3gflc&feature=youtu.be&hd=1

1. In your Dataset Properties, enter your FetchXML. You need to add a filtered parameter to the FetchXML.

Note that the “attribute” attribute is the technical name for the entity and the value is simply called “id”.

2. This will create a parameter called id. Set it to Hidden.


3. Upload your RDL to CRM Online as a new Report. Be sure to give it a unique Name, and not using spaces will make things a little clearer for you.

4. Run the report from Workplace -> Reports – even though it won’t run correctly – and use F11 to snag the URL. Note some critical components:

https://myorg.crm.dynamics.com/crmreports/viewer/viewer.aspx?action=filter&helpID=Test-Report.rdl&id=%7b79F9104C-2620-E111-905D-1CC1DEE87ACD%7d

In this case, the HelpID is the unique name you specified in Step 3. You will also recognize the GUID with the %7b and %7d characters (which you probably know resolve to curly braces – { } -  in a URL).

5. On the entity you want – in our case, Product – drop in an iFrame and jot down the name of the frame (“IFRAME_xxx”).

6. In your Form properties, add a jScript Form Library and add the following code, derived from several sources online, including Daniel Bergsten’s blog here: http://danielbergsten.wordpress.com/2011/02/09/crm-2011-javascript-to-set-iframe-url/

// JScript source code 

function Form_onload() {     

loadChart(); 

}   

function loadChart() {     

var productLookupItem = new Array();     

productLookupItem = Xrm.Page.data.entity.getId();          

var serverUrl = Xrm.Page.context.getServerUrl();       

var url = serverUrl + "crmreports/viewer/viewer.aspx?action=filter&helpID=Test-Report.rdl&id=%7b79F9104C-2620-E111-905D-1CC1DEE87ACD%7d";     

SetReportUrl(url, "IFRAME_JustinTest", productLookupItem); 

}   

function SetReportUrl(reportUrl, iFrame, productLookupItem) {     

if (productLookupItem == null) 

{         

Xrm.Page.getControl(iFrame).setVisible(false);     

}     

else {         

Xrm.Page.getControl(iFrame).setVisible(true);         

 Xrm.Page.getControl(iFrame).setSrc(reportUrl + "&p:id=" + productLookupItem); 

//alert (reportUrl + "&p:Id=" + productLookupItem);    

} 

}

You need to change three key things in these two lines of the script:

var url = serverUrl + "crmreports/viewer/viewer.aspx?action=filter&helpID=Test-Report.rdl&id=%7b79F9104C-2620-E111-905D-1CC1DEE87ACD%7d";     

SetReportUrl(url, "IFRAME_Test", productLookupItem); 

The helpID must match the Name you gave to your Report in step 3; the id must be the GUID you found in Step 4; and the IFRAME name must match the frame you’re using. Note that this is on the form properties screen, not the iFrame’s properties.

This URL is straightforward; note that there are no records types or recordsets required. Just three simple parameters – the report name, its GUID, and the action=filter command.

Also keep in mind that CRM can be finicky about character sets. The above can’t have slanted quotes, and it’s best if you edit it in basic Windows Notepad. Other tools such as Notepad++ may cause maddening and hard-to-diagnose scripting errors.

7. In the Event Handler, add a new property to invoke the script on the form’s load.


In the Handler Properties, just add the name of the jScript function Form_onload. Note that we aren’t passing parameters or anything else, just invoking the function when the page loads.

Handler Properties:


8. When you save and publish, your iFrame will now populate with the report you want run and send the report the GUID of the object you’re on as a parameter.

One word of caution: you can’t invoke a report like this from outside of an iFrame. We tried to get them to work in a new browser window in an attempt to eliminate potential issues and to zero in on the URL we needed to build. This caused headaches; the target URL you are looking for is the one assembled by the script above.

Special thanks to Justin Languirand of Altico Advisors for his help working out this tricky problem.

Comments

Name (required)

Email (required)

Website

Enter the code shown above:

Notify me of followup comments via e-mail