Reporting in CRM Online 2013/2015 – Part 2

After setting up our development area and importing the .rdl file into the area, we are now ready for part 2: customizing the report.

Customize the .rdl report in VS

Add images, change the lay out, delete columns or add new ones from the existing dataset. This is pretty straight forward and does not really require more info. If you want to add data which is not already in the existing dataset, you will need to add new fields. You can do this by writing queries in FetchXML or add data from a FetchXML Query in CRM Online. When your credentials are correct, you will be able to make a preview of the report in Visual Studio. This eliminates the need to export and reimport the report in to CRM before you can test it.

Add data from FetchXML query

When you want to add data from the CRM Online environment, you will need to change the current query which fetches the data. Right click on the current dataset, choose “query”. When you execute the query, you will see all the data which is currently imported through the FetchXML-Query (this is based on the meta data from the .rdl file which you imported earlier).

Query Designer.png

You now have two possibilities:

  • You can either write new FetchXML code if you know how to do it.
  • Or you can export FetchXML from CRM Online advanced view and import this into the query.

When you make an advanced view in CRM Online you are actually writing a query. You can export the FetchXML query by “download Fetch XML” in the Advanced Find view.

Fetch XML

You will then get an XML file like this:

<fetch version=”1.0″ output-format=”xml-platform” mapping=”logical” distinct=”false”>
  <entity name=”quote”>
    <attribute name=”name” />
    <attribute name=”customerid” />
    <attribute name=”quoteid” />
    <attribute name=”createdon” />
    <attribute name=”statuscode” />
    <attribute name=”statecode” />
    <order attribute=”name” descending=”false” />
    <link-entity name=”opportunity” from=”opportunityid” to=”new_entityname” visible=”false” link-type=”outer” alias=”a_7bb812150d29e411a132d89d67640040″>
      <attribute name=”createdon” />
    </link-entity>
  </entity>
</fetch>

You can then copy paste the XML file (or parts from it) into the query window in Visual Studio.

So this:

Fetch query 1

Becomes this:

Fetch query 2

Your dataset will then contain the new fields you queried and you will be able to use them in your report. When you are finished, save the .rdl file and import it into CRM.

Save and reimport the customized report in CRM Online.

You can import the .rdl file in to CRM Online and make a new report or you can import it and replace and old one (which is recommended when you are developing).

Creating a new report: go to “sales” – “reports” and the “+ NEW”. Choose for an existing file as the report type and browse to the .rdl file you created in Visual Studio.

New report

Update and existing report:  go to “sales” – “reports” and instead of choosing “+ NEW”, select a report and choose “EDIT”. Again, choose for an existing file as the report type and browse to the .rdl file you created in Visual Studio. Select ok and the created .rdl file will become your new CRM Report.

Possible errors you might encounter

So everything is going well and then you decide to import/modify/… your report and suddenly you get an error:
Error

In 95% of the cases you used a shared datasource (as I mentioned earlier), but CRM online can’t read this. You you need to head back to your report and change the data source to an embedded one.

The other 5% of the cases I discovered that if you rename your data source (e.g. [Your organisation]), you sometimes get this error to. So don’t rename it and just keep the name “CRM”.

What if you want to create a report with custom parameters?

The guys at PowerObjects wrote a nice guide on how you can use custom parameters and report prefiltering, to build a report without the need for a sub-report.

Step 1

Create a Dataset i.e FilterDS to pull accountid while enabling prefiltering. This will enable the report to grab the GUID of the account the report is run from.

Step 2

Create a parameter called account of datatype text.

Step 3

Right click the parameter account. Under properties, set the available and default value to point to the FilterDS and the accountid field as the value.

Step 4

Now create the main dataset (master dataset) for the account as you see below and apply the filter accountid equals the parameter @account.

 

Step 5

Also create the detail dataset for contact and filter on parentcustomerid as you see below.

Notice the @account parameter is key in both of these datasets. @account contains the ID (GUID) of the account record that the report is run for.

If you put everything together, it looks something like this:

Sources & Read more:

Leave a Reply

Your email address will not be published. Required fields are marked *