Advanced Find is a fantastic tool. I train and train all of my end users on it as much as possible in the hopes that they will see the potential they have at their finger tips. You can always tell who is really getting it and who isn't too based on the questions they ask. Unfortunately some of the hardest questions to answer are the ones that are for the coolest queries. For instance, I've had users ask for a list of all Vendor's (custom entity) that don't have an activity associated to them in the last X number of months. This requires Advanced Find to query data that isn't there. Unfortunately this is the one major limitation of Advanced Find. It can only query existing data.
Don't lose hope yet though. Microsoft has provided some views for users like this out of the box. If you have ever seen the out of the box view for Accounts titled "Account: No Orders Last 6 Months" you'll notice that you cannot see nor create that query in Advanced Find. That's because the query is checking against data that isn't there, it's actually looking for null values. So, if Advanced Find can't do it then how did it get in there?
SavedQueries is the response that I give you. The SDK goes over them and explains them as giving you the ability to create your own saved queries that users will be able to use as views. There isn't much code on them in the SDK. I suppose that's because "technically" you can create a SavedQuery in 12 lines of code. The complex part about making a SavedQuery is figuring out the query to pull the data you want. I'll go over creating a SavedQuery and leave the data query to your own design.
The reason I say that you can "technically" create a SavedQuery in 12 lines of code is because that's how many lines you need to fill the SavedQuery object and then call the CreateRequest/Response methods to actually create the view. However, that assumes that you already have some query done and coded that you can pass into the SavedQuery. Allow me to explain a bit more with some code.
First off you need to create a new savedquery object:
savedquery sq = new savedquery();
Simple enough. Now, let's fill it's properties so that we can create it.
sq.name = "Name of View that will be seen by users";
sq.querytype = new CrmNumber();
//All custom SavedQueries must have a value of 0 (zero) for QueryType according to the SDK
sq.querytype.Value = 0;
//ReturnedTypeCode is the name not the objecttypecode
sq.returnedtypecode = EntityName.<entityName>.ToString();
Now, the last and most crucial part of the SavedQuery, the actual data query:
sq.fetchxml = queried.FetchXml;
Strangely enough the SavedQuery takes FetchXml...I thought that was out the door in MSCRM v3.0 but for some reason it is carried over even into MSCRM v4.0. Oh well, it is what it is. When I first saw this I thought I was going to have to pull out my MSCRM v1.2 SDK to remember how to build FetchXml that MSCRM will accept.
Luckily, however, I found two methods that saved me TONS of time:
Look those up in the SDK for code examples. But the short of the long is that once you have your RetrieveMultiple query all dialed in you just pass your query into the QueryExpressionToFetchXml method and it converts it for you. That's how I got my "queried.FetchXml" bit of code.
Once I had that all that was needed was to actually create it. And to do that I just follow the typical TargetCreate methodology. Then run the code and you'll see a new view in the object that you wrote your code for. You can then edit the sorting and columns but you'll notice that you cannot edit the filter, just like the "Accounts: No Orders Last 6 Months".
So, the great news is that essentially, if you can write it in SQL, you can write it in C# (or VB if that's what you like). And if you can write it in C#/VB then you can give the view to users in MSCRM. SQL's really a really powerful query tool, heck that's why it's Structure Query Language! So, saying that if you can do it in SQL means you can get it into MSCRM is a BIG deal for showing useful data/info/trends to users. Your other option is SRS, or some other fully custom web page. SRS is just a static list that isn't as easy to make the report able to have actions (update records) performed against it. And a totally custom web page will work as well, it's just typically a lot longer to turn around the dev and actual use of the page because there is now more things to build than just the query that was requested.
Have fun with this...I know I have been and remember, when in doubt, use that SDK, it's probably got something you haven't thought the system could do.
Dynamic Methods Inc.