How to create a drill through Link from a SSRS Report in CRM 2011 Online

How to create a drill through Link from a SSRS Report in CRM 2011 Online

Steps:

1.    Create a Report Parameter called CRM_URL, Make it hidden and with a default value of https://YOURCRMORGANISATION.crm4.dynamics.com/main.aspx?

2.    View the Text Box Properties of the cell you wish to add the link to, go to the Action tab, then select Go To Url, then expression and add in

=Parameters!CRM_URL.Value & "?ID={" &
Fields!customeridValue.Value.ToString() &"}&OTC=2"

          Replace customerid with your value, keep the Value part as this is the GUID of the record.

OTC = 2 is contact, so change this to 1 for account, 3 for opportunity. To find the other OTC codes go to the entity in question, open a record and in the URL you will see etc=3, this number is the OTC (Object Type Code).

3.  Save and Publish the report into CRM

Enjoy!

SSRS Report - UTC / UK Time Issue

I came accross an issue recently, I created a SSRS Report from scratch and set the locationisation to en-GB which displays the dates etc in a dd/mm/yyyy format. This presented me with a problem, when connecting to a crm online server it likes the format to be in utc so when I passed 01/08/2011 (1st August 2011) it thought i was passing 08/01/2011 (8th January 2011).

The solution it seems was to edit the rdl file in notepad and change the query parameter, which once I found out where/how was a 10 second job.

The steps were:

Find the location of the Report in question
Right click on the report and select Open with
Choose Notepad
Do a find for <QUeryParamater>

Original Code:<QueryParameters>
 <QueryParameter Name="@StartDate">
        <Value>=Parameters!StartDate.Value</Value>
</QueryParameter>

New Code:<QueryParameters>
 <QueryParameter Name="@StartDate">
        <Value>=Format(Parameters!StartDate.Value,"MM-dd-yyyy")</Value>
</QueryParameter>

Notice the change in the Value, this simply formats the value into a mm-dd-yyyy format and then your away.

Enjoy

Return all attributes without having to list them all in the query

Sometimes you might want to return all attributes on an entity without having to list every single one of them in the FetchXML, to do this is very simple in FetchXML, just add in <all-attributes /> where you would normally add in the attributes. If you have a linked entity then you need to add this into the linked entity as well otherwise it will only bring back the primary entities fields.

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">
   <entity name='account'>
      <all-attributes />
      <filter type='and'>
         <condition attribute='createdon' operator='today' />
      </filter>
   </entity>
</fetch>

How to display Images that are attached to Notes for CRM 2011 Online – Using FetchXML

How to display Images that are attached to Notes for CRM 2011 Online – Using FetchXML

Create a Query using the Advanced Find in CRM 2011, download the FetchXML from it and edit the file. The actual image is stored in a field which isn’t visible in the CRM GUI, it’s called documentbody. Here’s an example with it in, if you add this code below to your SSRS Dataset then it will show you all notes created this month with the image. Then simple add an <Image> from within BIDS and set the image properties field to the documentbody and MIME type to jpg, gif, png etc..

<fetch version="1.0" output-format="xml-platform" mapping="logical" distinct="false">

  <entity name="annotation">
    <attribute name="subject" />
    <attribute name="notetext" />
    <attribute name="filename" />
    <attribute name="annotationid" />
    <attribute name="overriddencreatedon" />
    <attribute name="documentbody" />
    <attribute name="ownerid" />
    <attribute name="annotationid" />
    <attribute name="filesize" />
    <order attribute="subject" descending="false" />
    <filter type="and">
      <condition attribute="createdon" operator="this-month" />
    </filter>
  </entity>
</fetch>

Works a treat J

Reporting Services - Custom Sort Orders in Tables

SSRS allows you by default to sort on a value  either by A to Z or Z to A, which is fine for most scenarios however what happens if you want to sort in a specific way.

Example, you have 3 values and you want them sorted as per below:

Low

Medium
High

A to Z would give you

High

Low
Medium

Z to A would be a reverse so neither of these would achieve the required sort order.

The answer is so add a IFF Statement to the Sort Expression and select A to Z as the Order, so for example

=iif(value=”Low”,”1”, iif(value=”Medium”,”2”, iif(value=”High”,”3”,””)))

 

The order is then sorted on the numeric value as opposed to the Alphabetical value, thus giving you your desired sort order J

Error when Suming values from CRM2011 - String not Dec

If you try to sum a value that you’ve queries through FetchXML from CRM 2011 then you need to make sure you choose the FieldNameValue.Value and not the FieldName.Value, the first is the actual value stored as a decimal/integer etc. and the second is simply stored as a string. If you try to sum a string you will see an error similar to this:

Error:

Warning  1 [rsRuntimeErrorInExpression] The Value expression for the textrun ‘extendedamountValue8.Paragraphs[0].TextRuns[0]’ contains an error: Conversion from string "£‎1,614.13" to type 'Decimal' is not valid.      C:\SSRS Reports\Sales Pipeline.rdl           

Correct Answer for the Purposes of Totaling:
=sum(Fields!new_weightedestsalesValue.Value)

Wrong, unless you simply want to display the value as a string.:
=sum(Fields!new_weightedestsales.Value)

             

Fetch XML limitations

There are some limitations which I've come accross which are hopefully MS will change for future releases.

• You cant specify left outer join, i.e Show me all Accounts without an Activity.
• Group/Sum isnt supported i.e Select Account, count(accountid), sum(estimated value) from FilteredAccount Group by Account

If these are show stoppers then you will need to look at one of the following alternatives

• Move to CRM On Premise
• Create a plugin to store a date field on the record of the last activity etc.
• Retrieve data using a webservice to store the data locally and then query that instead

Let me know if you find anymore that I havent listed here.