This blog is now obsolete. Go to scott.arbeitman.id.au for all new content.

Showing posts with label sharepoint. Show all posts
Showing posts with label sharepoint. Show all posts
0

Dynamics CRM 4: Discovering the SQL behind an "Advanced Find"

| Wednesday, November 26, 2008

Have you ever wanted to know exactly what SQL was generated by the advanced find? This question might just be because of shear curiosity, but the results of this query by also be the starting point for most sophisticated reports using SQL Server Reporting Services.

The process is straightforward. All you need is Dynamics CRM 4 and Excel 2007 (2003 should work similarly). [See update below for doing this without Excel]

Perform an advanced find  
Run the query, and export the results to Excel. Make sure you select "Dynamic Worksheet" although I'm pretty sure "Dynamic Pivot Table" will work image
In Excel, once you have data displayed (which might mean enabling data connections), go to the Data tab, and click "Connections" image
You should have a single connection called "Connection". Look the properties for this connection. image
Go to the definition tab. The command text contains the SQL behind the advanced find. image

This approach works with SharePoint lists as well. Using this technique with SharePoint list data helps you quickly determine the GUID of the list and the view you are using.

Update: Dynamics exports the data as an XML file, not a binary XSL file. Therefore, you can simply open the downloaded "Excel" file with a standard text editor and view the SQL within. 

0

Microsoft versus Thoughtworks. SharePoint versus Mingle

| Wednesday, February 20, 2008
This past week has been dominated by our portal project. SharePoint is our platform of choice for the portal, and Mingle is the documentation and project management tool that I'm using for this project. As a business analyst (my job title), it makes sense to use tools like SharePoint where I can address business requirements without relying on developers; I can often gather requirements and configure the system in real time if required. This is a tremendous asset.

So yes, SharePoint is a nice tool insofar as it satisfies the 80-20 rule for off-the-shelf-software: 80% of what you need is available out of the box, and 20% is missing and requires customization. Luckily, it seems SharePoint doesn't suck too much at customization, so we are not stuck trying to reinvent the wheel and hack into areas where we shouldn't. Still, there isn't much of an open-source community around this project as I would like to see, and it's difficult to get a grasp of what is an easy customization, and what is nearly impossible. The amount of blog posting from non-Microsoft employees regarding SharePoint, for example, is lacking.

Aside from poor community support and documentation, the other gripe I have is that finding out how do the 80% out-of-the-box functionality very time consuming. SharePoint doesn't actually do much, but finding out what it does and how to do it is quite challenging. Things that would seem natural to be able to do (reference the Now() function in calculated columns, have global lists, changing the link to text for document library items) are nearly impossible. Since you can't easily determine what's possible and the system is so unintuitive, a lot of time is spent searching for options which do not exist but presumably should.

Contrast this with Mingle.

Granted, Mingle does a lot less than SharePoint, although one could argue that there is much overlap. But the learning curve is negligible and the user interface, despite feeling less like a real desktop application than SharePoint, is actually a pleasure to use. Mingles features are easy to find and configure, the documentation and screencasts are well though-out so it's easy to see what Mingle can -- and more importantly -- cannot do, so little time is wasted searching for ways to do the impossible. Mingle comes with meaningful templates that actually show off "advanced" features like card transitions, graphing, and well constructed card properties.

(As a side point, the Mingle concept of "cards" is really not all the different from the SharePoint concept of lists. The both offer custom, typed columns and they both offer different views.)

There are a few lessons here for me:
  • ship your application with meaningful out-of-the-box samples. Even better if some sample content could actually be applied to whatever project you're about to begin. (The SharePoint logo as an image web part is not really helpful)
  • web applications should not try to feel like desktop applications; the should feel like web applications. This way, when I want to move the location of something on a page, I'll be pleasantly surprised that it drags instead of frustrated I have to click the "Move Up" button on a "toolbar" 30 times
  • product marketing pages should actually show how to use the product, and relevant scenarios. Otherwise, I can't get started quickly, and I'm not sure what's possible.
Are you using Mingle or SharePoint (or both)? I'd love to hear about your experiences.