Extracting SSRS Report Definition Files from a Report Server

Had the need today to backup some report definitions from a SQL Server Reporting Services 2005 Server prior to terminating it.  I've linked and put it in pastebin as well.

CRM 2011: Mass Update Opportunity Estimated Closed Date

Today, I fielded a request from a collegue who was attempting to update large number of opportunities with the Bulk Edit feature.  They were attempting to update the "Estimated Close Date" field.

I noticed what they had as well -- in the bulk edit form the field was disabled.  I presume this is because behind the facade, this is a calculated field.

There's a quick and easy way to overcome this problem if you run into it.  A simple dialog (or workflow) can still manipulate this field.  The former can even be constucted so it prompts for a value.

I've included a screenshot tour of the dialog I constructed below:

CRM 2011: Whole Number fields -- dropping the comma

I fielded a request today that involved a CRM field that was configured as a whole number that's intended to store the year component of a date.  In the installation of CRM that I was working in -- numerics are formatted with commas.

The problem that the requestor wanted resolved was that 1995 was appearing in the UI as 1,995 -- they wanted to drop the comma.

I ran across a simple javascript one-liner here that solved the problem.

document.getElementById("fieldname").value = Xrm.Page.data.entity.attributes.get("fieldname").getValue();

The author was quick to point out that the resolution would likely be consider unsupported.  While this is true, I approach these as a measured risk.  It worked for me.

Unit Testing Long Running Workflows by Mocking the TimerExtension

Problem

I've been teaching myself more about .Net Workflow Foundation (WF).  One thing I've been working on recently is how to unit test long running workflows.  I have a few examples of workflows that delay for days.  I'd love to be able to advance the clock and simulate what will happen when time advances past the delay without the wait.  There's a number of techniques out there that I have found.  They fall into one of several camps.

• Roll your own Delay Activity
• Use the custom Delay Activity from Microsoft.Activities.UnitTesting
• Runtime XAMLInjection of modified Delay Timespan

None of these appeal to me.  I prefer the stock Delay Activity -- other than unit testing I have no real reason for modified behavior.  Runtime injection seems a bit of a kludge. I prefer to leave the logic of the workflow intact and only modify the dependencies from the outside.  In short, I have the same concern that is raised in this StackOverflow question about replacing the DurableTimerExtension.

I have the same interest as the original poster of this question.  How do I inject my own mocked variation of DateTime.Now()?  There are simlar, non-workflow implementations for Mocking DateTime.Now() and UnitTesting DateTime.Now(), just not a best practice for WF.

Here is one way to do it.

[Disclamer: The code I have below uses the Microsoft.Activities.UnitTesting extensions.  This technique wouldn't require that framework -- but its handy enough.  So I use it.]

Approach

On instances of WorkFlowApplication and WorkflowServiceHost there is an Extensions collection.  In WF, this is way to register workflow extensions like Tracking, PersistenceParticipants, and services that your custom Activities can consume.  One of the methods on this collection is Add().  which accepts a Func.  This is a function that will return type T specified when that type is requested via context.GetExtension() inside the execute method of an activity.  I've used this in the code below to inject my own Mock of the TimerExtension used by the Delay activity.  I've done this with an actual class -- but with a little imagination this can be modified to use a mock in your favorite mocking framework and extended to suit your interests.

Code @ Pastebin

Conclusion

This isn't quite the ideal in my mind. I'm intercepting the call that the Delay Activity (and anything else that wants to use the TimerExtension) makes to schedule a timer to fire and modifying the actual timespan that is used to schedule the timer.  Any workflows that attempt to reconcile time inside and outside of the workflow will have side effects... but, well, that's kinda the point.

I'd much rather intercept a request to an imaginary service from the workflow for "What time is it now?".  A default (and production implementation would reply "Why its DateTime.Now(),  of course [or is that UTCNow()..],  A mocked implementation would reply "Its DateTime.Now().Add(new Timespan(0,15,0)) [15 minutes in the future for the less initiated].

All of these techniques have their warts.  In any case, what I have written up is the closest to satifying my needs -- so I decided to write up a post for the benefit of all -- including future me.  I'd love to hear of other successful approaches you might have taken in the comments.

CRM2011 Entity Form Navigation items -- Sending Params

Recently a colleague needed to add a navigational item on the left nav of an entity form. No big deal right? CRM2011 has made this a relatively trivial "configure-it-through-the-ui" capability. But this requirement had a hang up. We were trying to pass form-level parameters to the target url similar to what one can do with IFRAME integrations in CRM. This could be accomplished in prior releases of CRM (3/4) by exporting ISV.config doing some modest editing and then importing the customization. 

Why not use an IFRAME?
Good question. File this one under constraints. We have similar capability on the left nav already that we carried over from CRM 4. We really wanted to locate the features near each other -- and the entity form we're working with was already pretty tall.

I found a handful of links that suggested using a form.onload script and modifying the link via the DOM. Or modifying the onclick behavior. These might work too -- but something that I observed while doing ribbon development gave me some hope. Here is a summary what I did and what I found.

*** Disclaimer: You just found this on some random blog. Be smart. Backup, create a new test CRM Org. Test it outside of Production. No warranties, expressed or implied.***

• Create a "seed" navItem through the traditional, supported way. (Customize form, navigation..)
• Create an Unmanaged solution
• Include the entity in question
• Export the Solution
• Unzip the Solution Package
• Open the customizations.xml file in a text editor
• Search for "FormXML"
• once found, search for the name of your link (by display name is fine)
• You'll see something that resembles the following:

• The "PassParams" attribute is what I added.
• Zip up the assets that made up the original exported solution -- especially the modified customizations.xml
• Import this package into CRM2011 through the traditional Settings>>Solutions feature

Publish after successful import.  That's it.  Verify using Fiddler.  Good luck.

CRM: Making fields required without being "required"

I ran into a set of requirements recently that called for a CRM field to be required for data entry, but not expressly required for workflow. Here's a Javascript quickie that sets the field requirement level -- just drop in the associated entities Form.Onload ScriptBlock. Not sure what makes something business recommended (the blue askerisk)... but this is a good start

crmForm.SetFieldReqLevel("statecode", 1);

1 = Required
0 = Not Required
2? = Recommended

CRM 4.0, Outlook Client, and Syncronization Panic. How to not sweat the post upgrade login rush.

***Disclaimer -- Use at your own risk. No warranties expressed or implied ***

If you want to skip the commentary -- jump to the bottom for the skinny.

I've been planning doing the planning for my company's CRM 4 upgrade for the last couple weeks. We've got a fair amount of ISV code that's also revving, but one thing has stuck in the back of my mind throughout the planning process. Outlook Synchronization. Ironically I've been panning for a (supported?) way to administratively disable synchronization of certain CRM entities with Outlook. Why? Well the problem's a bit deep rooted. Here's a shot at not making it completely boring.

Basically my company works under a B2C model so Contacts are a central entity. Exceptions exist, but largely we see customers as "Contacts". We have a contact ownership model that puts the ownership of several thousand contacts in the hands of no more than 100 people. For most users that own contacts -- there are easily hundreds of contacts for each user. There is do distinguishable benefit for these contacts to sync with outlook -- outside of the address book. In fact, most users would see the creating of all of their "customers" as Outlook Contacts as wasteful info pollution. They don't want it to happen. Even if they don't know it yet. So, the problem at hand it how to administratively disable the synchronization of contacts. Because even though there is an options panel that is available to disable this via a check box -- we'd really feel better if we took care of this for everyone.

Sprinkle in the fact that we are an RDP shop (Citrix actually) and that user sessions are actually taking place on about 8 servers in our data center. So, if long running sync processes soak up out processing capacity on the Citrix farm -- it's bad news in IT land. People aren't just freezing up their work laptop or desktop while they sync. They have potential to do collateral damage to their fellow worker who just happens to be working on the same Citrix box. We have a vested interest in "managing" this sync process across the enterprise of about 250 users. Preliminary testing shows that at least 10% of our user base is looking at potential multi hour syncs. Most of this is because the 4.0 client now syncs completed activities, which was not the case in the past.

So what to do? RegMon turns up no leads. There doesn't appear to be any files that get written to according to FileMon. But Fiddler and SQL Profiler reveal some hope. The settings are stored in the database. Perfect. Here's the details.


*** The Magic Starts Here ***

YMMV. I encourage you to backup your database often while fiddling. Remember that manipulating the CRM Database via SQL script is unsupported. Please, please, please, test, test, test in a non-production environment. Totally unsupported. Please don't call me or Microsoft if you screw it up.... If you're at all squeamish or risk adverse stop right now, close your browser and never return to this post. Its better for both of us if you do.

There, with that out of the way, apparently the sync settings are implemented using User Queries. User Queries are best known as Advanced Find definitions that the user has saved. I suspect a Users' custom list views fall in here too. Even better, the User Queries that power the sync process with Outlook have a "querytype" of 256. Doing some investigative work I watched what happens to these user query records as I changed and saved my sync settings in the UI. The only significant change is to a pair of fields (StatusCode and StateCode). Here's some more of the particulars.

The Database:
[YourOrganizationName]_MSCRM

The Table:
UserQueryBase



The Structure:
There's a bunch of stuff here, but the three fields of particular interest are:

• UserQueryId: Primary ID of the UserQuery
• OwningUser: FKey to SystemUserBase.SystemUserId... basically the user that this query is for
• StatusCode: Not totally sure... but the value of this is 2 when the corresponding checkbox is Unchecked, 1 when it is checked
• StateCode: Again, not really sure, but this value is 1 when the checkbox is unchecked and 0 when checked.
  ReturnedTypeCode: The Entity for which the User Query is for. Using the SDK, you'd know that the TypeCode for the Contact entity is "2".
• QueryType: Distinguishes between the different type of user query. I've found that the ones affecting sync have a value of 256.
• Description: If the User query was created by a user, this is the description field that is on the UI. An example is "Contacts Syncing to Outlook"
• Name: If the User Query was created by the user, this is the field that would be the given name to the query. An Example is "My Outlook Contacts"

The Hack:

Running the following query disables the checkmark for contacts for a given SystemUserID (a join to SystemUserBase can function as a more friendly limiter). Leaving that clause off runs it up for everyone.


update userquerybase
set statuscode = 2, statecode = 1
where owninguser = '[SystemUserID of the user you want to change settings for]'
and querytype = 256
and ReturnedTypeCode = 2

... and this one would check it.....


update userquerybase
set statuscode = 1, statecode = 0
where owninguser ='[SystemUserID of the user you want to change settings for]'
and querytype = 256
and ReturnedTypeCode = 2

Obviously, if you alter the query and drop some of the additional clauses (ReturnedTypeCode) you can apply this behavior more broadly. I have not yet done this. See disclaimer above.

Extending the Hack:

So, the whole thing started with the premise of just disabling Contacts. But as I uncovered the volume of syncable objects owned by our users it became clear that some throttling of the sync must take place. Hate to crush those Citrix servers and people's Monday productivity. Our approach for the weekend upgrade is to extend the hack to the other syncable entities to the point that everyones settings for synchronization will basically be all unchecked. Our plan is to treat Outlook synchronization as opt-in. So, we'll give users a resource -- probably a MOSS survey to fill out and opt in. We'll provide a link as a part of our communication plan for the weekend's events. This way as users log into the system come Monday, the impact of the Outlook client will be very slight and allow us to manage the sync across the enterprise. As people log in, we'll run SQL scripts to check the appropriate boxes in users' Sync options and watch performance on the Citrix Servers. If the boxes respond well, we can ramp up the sync to more users that have responded to the survey. Eventually, after the rush, we'd have followup communications to remind folks that they can get Sync turned on.



Of course an enterprising user *CAN* just enable the features on their own -- essentially pulling the pin on the grenade. With any luck we can detect these folks and rebalance other users away from them if need be. Most users in our enterprise are not going to be so adventuresome -- and for those that are we may get them to log in on Sunday to get their sync out of the way.

Additional thoughts:

Extra credit to those readers who noticed the FetchXml and LayoutXml as a part of the UserQuery record. An added benefit would be if you have the option of using a filter to limit the quantity of records synced to.... only "Open" or "Scheduled" ones. I'm continuing my fiddling to see if I can effectively use a carefully crafted saved Advanced Find and use the FetchXml and LayoutXml to limit the available records for sync. I'd implement this into our upgrade this weekend by having all user's sync settings set to only sync open or scheduled items -- perhaps only as of a certain date. Over time, as the sync processes calmed down, relax the query to a point where eventually there is no filter. If it works it would be an even more graceful means to cushion the processing blow, allowing all users to effectively sync right away in the morning and at least get something.

*** More to come on this ***