Wednesday, May 30, 2012

"Error on page" redux

Recently I encountered this troublesome error yet again. And once again it proved to be the type of error that makes one wonder if perhaps the time has come to pursue another career... if you know what I mean.

The symptoms were as follows:

  • When navigating to an account, contact, etc. via the standard CRM UI navigation, everything worked fine.
  • When navigating via drill down from any CRM report (that adheres to the standard SDK report writing standard), the "Error on page" would show up. Other than the message everything loaded fine but of course, when the form was closed you would receive the standard "Send us the error report and have no fear that someone will indeed be reviewing, analyzing and making sure this gets resolveds asap back at Microsoft HQ"... or something like that.

When clicking on the "View the data that will be sent to Microsoft", the following is what appeared.


I should also mention that this occured on forms without any jscript libraries present (active or non-active). At some point, I even tried creating a completely vanilla CRM installation on the server and the error persisted even in that environment!

I first received this error with rollup 7 installed on the server, so I uninstalled that but the error persisted. Eventually I installed rollup 8 and magically this error went away. I am not sure if update rollup 8 truly resolved this issue, whether it coincided with another server reboot that somehow cleaned up whatever was polluting the environment, or whether the stars were once again aligned for me. But whatever it was I think it salvaged my dwindling supply of hair.

Wednesday, May 23, 2012

Report not showing under "Run on Current Record"

This has happened to me on a number of occassions. I modify an existing report making sure it adheres to all the CRM SDK reporting standards and in the report definition I make sure to specify that he appropriate entity/entities are entered in the "Related Record Types" and that the "Display In" is set to "Forms for related record types" and/or "Lists for related record types".

Yet when I open up the entity form (in the example above this would be a Marketing List record), the report appears under the "Run on All Records" section rather than under the "Run on Current Record". While this would be ok had the report not been linked using the context-sensitive "CRMAF" nomenclature - in this case, the report has been written such that it should be context-sensitive. So why does it show in the wrong section?

The behavior that I've observed is that if the report is created initially such that it does not adhere to context-sensitivity, it will rightly appear under the "Run on All Records" section. Later on, if you modify the report definition to include context-sensitivity it will still remain in this section.

If that is the case simply deleting and recreating the report will resolve this issue. At least that's been my experience.

Wednesday, May 16, 2012

CRM 2011 Document Management Settings: SharePoint Best Practice

This being a blog about Microsoft CRM it is perhaps a little bit of a reach to be discussing SharePoint best practices. But as the technologies overlap in this particular area, I think it might be worthwhile to briefly touch on some high level SharePoint best practices as those features could play in quite well with what we are ultimately driving towards i.e. a best practice recommendation in terms of integrating the 2 technologies. This post will be focused exclusively on the content management feature set of SharePoint as this is the area of integration that is provided out of the box in CRM 2011.

The way in which documentation is typically stored and classified on a File Server is by employing the use of folders. You physically stored different types of documentation in different folders in order to organize your content. This idea is still very entrenched in the thinking of most end users and is reinforced by the way in which we store content on our personal computers. Using this approach, a particular document is located in a single folder on a File Server.

SharePoint introduces the concepts of content types and metadata for tagging content. Using this approach, instead of storing content to a particular folder, you instead "tag" it to have certain defined characteristics when saving the document. The tags allow the document to be organized in different ways and the navigation can also vary depending on how the tags are grouped. By way of example, this is very similar in concept to how gmail works where instead of assigning an email to a particular folder as is the case with other email systems, you instead assign labels to the email. If 2 labels are assigned to a particular email, it can be navigated to using either of the labels. Another more immediate example is this very post which has been assigned two labels and therefore can be navigated to from each of those labels.

It should be mentioned that although SharePoint also allows the use of folders in the document libraries, it is generally best practice to avoid using them to the extent possible. This is because this feature is “retro” in terms of thinking; it also functions as a crutch to those who are more familiar with the old File Server approach and therefore may lead to bad content management practices. Having said all that, there may be valid reasons to use folders in SharePoint but generally they should be left to store imported or unstructured content.

Therefore, in summary, a much better content management practice is to leverage SharePoint content types and metadata for tagging content. Document tagging is a building block of SharePoint and can subsequently be used for many other SharePoint features such as:

  • Aggregation (for content navigation as described above)
  • Search scopes
  • Audience Targeting
  • Workflow
  • Templates
  • Office integration

All the above features can enable the improvement or elimination of internal inefficiencies within your organization. But that is beyond the scope of this discussion.

Previously we reached two conclusions with regards to the CRM/SharePoint configuration settings:

  1. The Individual Path is a generally favored approach
  2. Selecting too many entities for associating Documentation can lead to confusion and disorganization

Next we will combine the above SharePoint best practice to arrive at what could be considered to be a CRM/SharePoint integration best practice.

Embed Charts in a CRM Form

While still on the topic of reporting in general and more specifically with regards to reports embedded on the CRM form we should also briefly mention the very powerful out-of-the-box charting option enabled in  CRM 2011.

My previous post comes in handy when you have a reporting or charting requirement that cannot be modelled using the CRM charting overlay on views.

If however your visualization can be achieved through the CRM charts then you can embed this into a form without having to create a custom report as illustrated by the following screenshot:

Presuming that you have already defined the chart view in CRM, the way to achieve the above is very simple. Simply select the "Insert Subgrid" option in the form designer and toggle the Chart Options as shown.

Monday, May 14, 2012

Embedding Reports in a CRM Form

In CRM 4.0 we were able to embed CRM reports into directly into an entity form via the use of an IFrame. This old post walks through the configuration of that option. This is useful in several instances, such as:

  • Creating charts, tables, etc. to be able to easily visualize the data (e.g. a chart showing the total purchases by product line for the account being viewed right in the body of the form)
  • Pulling information from other applications into a form - a kind of "virtual" integration

Fortunately this can also be accomplished in CRM 2011 using html web resources.

We will start by referencing a Microsoft team post that walks through the process of embedding reports on a CRM 2011 dashboard (very useful in and of itself). This post references an html web resource that can be downloaded and used to configure the dashboard option.

The only difference between embedding a report into the dashboard versus is embedding into a form is "context sensitivity". That is, by it's very definition a dashboard is a high level view of data to enable the data to be visualized, analyzed and drilled down into. While the same kind of analysis could apply at the form level (e.g. viewing a summary of orders, invoices etc. for a particular customer), the report at the form level should (in most cases) be showing data that is linked/relevant to the entity that you are viewing.

So with that in mind, all we need to do with the html web resource referenced above is context sensitivity and then we can leverage that for to embed CRM reports into a form. And this is precisely what we have done - you can download the solution containing the html web resource for displaying embedded reports from here.

Once you have done so, all you need to do display the embedded report is to insert the web resource into the form as shown and to pass in the report ID in the Custom Parameters (see Microsoft's post on how to obtain the report ID).

 After publishing, open your form and you should see something that looks like this:

It would be nice too if we could hide the report header information (i.e. everything shown in blue in the screenshot above) however that appears to be somewhat challenging. If you're using a reporting services URL then you can tack on parameters such as the one below to suppress this (see the following post for a complete list of reporting services URL parameters):


However if you're trying to render the report using the "/crmreports/viewer/viewer.aspx" approach (which is how all reports natively get called from CRM and therefore is preferred and is also used that way in the web resource), then the parameters above will not work. If anyone has figured this one out I'd appreciate if you could enlighten me.

Friday, May 11, 2012

Advanced SSRS Reporting Part 2

The following post walks through what can be viewed as a kind of "template" for advanced reporting in MSCRM as introduced in my previous post. This example leverages the reporting scenario that I described there by way of example. However, the point of this post is to describe the relevant parts of the template rather than describe the processing logic relevant to that specific example - the processing logic will of course vary greatly from report to report so I do not want to focus on this at all as that is besides the point.

I have segmented the relevant parts of the template into Sections A-G - the entire declarative statement which is input as a Data Set into the report appears at the bottom of this article - you should reference that in terms of the rest of this article. The following screenshot shows how this statement ties into the SSRS report.

  • Section A - The majority of this report effort is probably going to be in terms of retrieving and formatting the source data that you want to output in the report. So you need to have a way of testing it outside of the report to ensure that it's retrieving the correct data inside SSMS (SQL Server Management Studio). As the parameter that this report relies on is "@CRM_FilteredList" we define this variable here which should pretty much mirror how the variable is defined in SSRS (see below - this format also adheres to the CRM SDK reporting standard). This section is commented out as it is only used for testing in SSMS. It should be uncommented when testing the declarative statement but should be commented out when inputting in SSMS (please do not forget to comment out this section when inputting into the report!)

  • Section B - Create a temp table for processing the report data. This is not required in all cases of course but comes in very handy when performing complex processing.

  • Section C - Populate the temp table with a query that will be used as the basis for subsequent processing logic.

  • Section D - This command executes the query in Section C

  • Section E - Run a cursor to process through the records that were populated into the temp table

  • Section F - Perform the processing logic specific to your report including lookups, updates, deletes etc. and update your temp table as necessary.

  • Section G - Once your temp table has been updated via the above processing logic output the results joining to other CRM views to bring in whatever additional attributes are required. Remember to join using the CRM filtered views.

And that pretty much is that. Once you have this data you can then input as a Data Source into your report and use whatever fancy reporting techniques you want for rendering your report. As long as you adhere to using filtered views and respect the naming conventions of the CRM parameters, you should be able to add this report in a context sensitive ways to forms and views with the standard Advanced Find pre-filtering.


drop table #report
declare @CRM_FilteredList nvarchar(100)
set @CRM_FilteredList = 'select * from FilteredList where listid = ''92F129FB-4099-E111-9EFF-006073502237'''

declare @SQL nvarchar(max)
 create table #report
  contactid   nvarchar(36),
  fullname  nvarchar(100),
  processed       bit,
  ord             int,
  inlist          bit

 set @SQL = 'insert into #report(ord, contactid, fullname, processed, inlist) ' +
    'select (ROW_NUMBER() OVER(ORDER BY c.Fullname))*10, c.contactid, c.fullname, 0, 1 ' +
    'from  (' + @CRM_FilteredList + ') AS list ' + 
    'inner join FilteredListMember m on m.listid = list.listid and EntityType = 2 ' +
    'inner join FilteredContact c on c.contactid = m.EntityId '


    declare @cid     nvarchar(36)
 declare @sid       nvarchar(36)
 declare @fullname  nvarchar(100)
 declare @ord       int
 declare @inlist    int
 declare ProcessTemp cursor for
 select r.contactid, con.record2id, con.record2idname, r.ord
   from #report r
 inner join FilteredConnection con on con.record1id = r.contactid and con.record1objecttypecode = 2
 inner join FilteredContact c on c.contactid = con.record1id
 where con.record2roleid in ('2C110BC6-168D-E111-8DD6-006073502237','EE375944-5415-437D-9336-7698CF665B26')  /* Partner/Spouse */
 order by r.ord
 open ProcessTemp
    fetch ProcessTemp into @cid, @sid, @fullname, @ord

 while @@fetch_status = 0
  update #report
  set processed = 1
  where contactid = @cid
  and processed = 0
  if (@@ROWCOUNT != 0) -- if update wasn't successful must be because contact was already there
   -- delete if it appears in the marketing list since it will be added above the partner record 
   delete from #report
   where contactid = @sid
   and processed = 0
   if (@@ROWCOUNT = 0) -- No record deleted ==> was not part of original marketing list 
    set @inlist = 0
    set @inlist = 1
   -- insert/reinsert above partner record 
   insert into #report (ord, contactid, fullname, processed, inlist)
   values (@ord, @sid, @fullname, 1, @inlist)

  fetch ProcessTemp into @cid, @sid, @fullname, @ord

 end --while
 close ProcessTemp
 deallocate ProcessTemp

 from #report r
 inner join filteredcontact as c on c.ContactId = r.ContactId
 order by r.ord


Thursday, May 10, 2012

Advanced SSRS Reporting Part 1

In my previous post, I made the following comment with regards to SSRS Reporting:
"However the sky is pretty much the limit when it comes to SSRS Reporting as you can leverage the full capability of both SSRS and SQL in order to manipulate the data in exactly the way you want."

In this post, I'm going to expand on this concept a little. Let's use a sample requirement to illustrate this:
  • Report to be run against the contacts of a marketing list
  • Report to be ordered by contact name (fullname)
  • Contacts are related to one another by means of connections and we want spouses to break the ordering logic specified above. That is, whenever a contact has a spouse, the spouse record should appear immediately after the first spouse record (bear in mind, a spouse may not always have the same last name as their partner).
  • We even want spousal contacts to appear in the report as long as a single spouse appears in the marketing list. That is, the report is against all contacts in the marketing list + spouses who may or may not be members of said marketing list.
  • Include some tags to indicate whether the spouse was a member of the marketing list (or just brought in as a result of his/her connections!)

The problem with a requirement like the above is that it cannot be met by any simple query means. You simply cannot write a SQL query that will be able to retrieve the data and manipulate the data as specified.

So can something like the above be achieved using SSRS reporting? Absolutely!

Well of course you might say that this is fairly obvious as you can write a stored procedure to perform all the various querying, re-querying and data manipulation and finally output the results in a select statement that SSRS can subsequently manipulate. And if you said that, you would be right.

But such a report would not adhere to the CRM SDK which would not enable you to link the report into CRM forms, views and Advanced Find queries in a context sensitive manner. Nor would it be technically supported as saving stored procedures in the CRM database is... verboten.

So the follow up question is - can something like the above be achieved using SSRS reporting while accomodating the CRM SDK requirements? And the answer once again is... Absolutely!

The key point to recognize is that just as SSRS can consume a stored procedure as part of a data source it can similarly consume declarative statements as part of the data source (as opposed to queries) and those declarative statements can have all the complex processing power of Transact SQL. Once we realize this, we can leverage the full power of T-SQL to run multiple queries, inserts and updates into temp tables in order to get the data into the format that is desired before sending to SSRS for rendering.

As an aside - I personally find that manipulating the data prior to sending to SSRS and using SSRS mostly as a rendering rather than data manipulation tool is a much easier way of going about it. Especially when it comes to the more complex report requirements. And the above technique can come in most handy in this regard.

In my next post, I'll attempt to provide a template that can be used for constructing and testing the declarative statement for the report data source.

Wednesday, May 9, 2012

CRM Reporting Options

There are many and varied ways of reporting out of CRM. Most report requirements can be achieved using the CRM front end without having to resort to writing custom reports using SSRS and the like.Very briefly the following outlines the reporting options in order of complexity:

  • CRM Views and Advanced Find - Being able to defined and save Advanced Find and system queries is by far the most common type of "report" in CRM. As long as your requirements are just to return records from a single entity and the criteria for the report can be supported by the Advanced Find query options, this is the report to be using. This report has been enhanced significantly in CRM 2011 by including visualization and drill down options using the built in charting capability

  • Excel Reporting - Excel reporting is an extension of the CRM Views allowing for the data to be dynamically exported and then once you are in the Excel environment, you can use the full capability of Excel visualization to render pivot tables, charts etc. These can be saved as actual reports in the Reports area of the application as they can be connected to live data like all of the other reporting options.

  • Charting/Dashboards - With the introduction of CRM 2011, this method of reporting/visualization has to be next in terms of level of complexity (i.e. not very). Anyone who is familiar with creating charts using the likes of Excel (as illustrated above) should be able to use the CRM front end tools to create charts and, by extension, dashboards (which are just a bunch of charts, views etc. grouped together in a single presentation to present key metrics in a visualized format). Charts are of course also then used to integrate and extend the CRM views as already illustrated above.

  • Report Wizard - This walks you through the process of building a report. This is not too dissimilar from building a pivot table report. This approach is often useful as a starting point to build reports as you can then export the generated "rdl" and use SSRS to tweak the report further and add features not supported by the wizard.

  • SSRS Reporting - Typically you would use SSRS to create a custom report because the query, format, grouping, layout etc. that is required cannot be supported by any of the above. Ensuring your report adheres to the SDK guidelines will allow the report to integrate in a context sensitive manner into your CRM forms and views. Most times a SSRS report is also fairly straight forward that involves creating a query joining several tables together and then rendering the results into a table. However the sky is pretty much the limit when it comes to SSRS Reporting as you can leverage the full capability of both SSRS and SQL in order to manipulate the data in exactly the way you want.

  • Data Warehousing/Data Mining/Cubes etc. - This gets us into the most sophisticated area of reporting which typically involves extracting the operational data into a separate data warehouse and building cubes against that data to be able to create Key Performance Indicator dashboards, view trending etc. Customers typically do not get to this level of reporting until they have been using the CRM system for some time and have accrued lots of data that can be analyzed to identify interesting patterns, trends etc.There are a number of 3rd party ISVs that directly integrate with MSCRM and generally greatly simplify this activity.  

Thursday, May 3, 2012

Web Resource Utility for CRM Live

As previously described, the Web Resource Utility is a very useful tool for uploading well... web resources. But likely the most common web resources that you are going to want to bulk upload are image files that you will no doubt use to spruce up all your custom entities that you have added to your installation.

Getting this utility to work against CRM Live is perhaps a little bit unintuitive. So by way of example, let's say you're connecting to Contoso live environment using:

Then you're going to want to enter in the following connection information:

As illustrated:

  • Organization - This is the organization shown under Developer Resources
  • Domain - This is the first part of your URL i.e. contoso in our case