Content and Structure Reports: All Checked out documents

Ever wanted to see all documents that are checked out right now and deal with them all in one go? Not only the ones checked out to you? here is the CAML syntax for your Content and Structure Reports Library located at the root of your publishing enabled SiteCollection:

<Where><Geq><FieldRef Name="CheckoutUser" LookupId="TRUE"/><Value Type="int">0</Value></Geq></Where>

Simply create a new Item in that list with the above text for the CAML syntax, give it a Title and meaningful description and you're set!

Content and Structure Reports: Stale documents

Ever wanted to find out where your unused documents lie in MOSS?
This is the syntax to find all documents that have not been modified in the last year. The field to check against is the Last Modified Date. Best is to use it's ID.

<Where><Leq><FieldRef ID="{173f76c8-aebd-446a-9bc9-769a2bd2c18f}"/><Value Type="DateTime" IncludeTimeValue="TRUE"><Today OffsetDays="-365"/></Value></Leq></Where>

Remember not to have any whitespace in between the tags, especially no CRLF.

The trick here is not to use [Today] but instead also use the OffsetDays to go back and forward in time and if you want to make sure that the time is matched, include the IncludeTimeValue="TRUE" attribute. This attribute comes in handy in many cases when you want to check against "Now".

50047 Advanced IT Pro SharePoint Course

Oh man,
I'm so hyped up, it is unbearable. Just finished the last day of teaching this course and can't believe how great this course is. It covers everything you need to know and more. It is jam packed with best practices, advice and tips. Follows best practices, teaches all relevant skills and is fun, fun, fun.
Anybody who wants to know how to get the most out of their MOSS installation MUST go on this course
Anybody who is starting a career as MOSS Admin must go on this course
Anybody who is planning on creating a MOSS infrastructure, is a Enterprise Analyst, Enterprise Architect, Solutions Architect or Information Architect/Analyst must go on this course.
Any MCT who wants to teach 5060 or 5061 should have read this manual thoroughly.

It is the bible for a good SharePoint Architecture. It is the mother of all SharePoint courses!

Always asked yourself how index and query roles affect performance? Wanted to know what actually goes into the SSP Search DB? Wanted to know what Raid configuration works best for the different Application Roles? Wondered how easy or hard it is to setup Database mirroring in combination with SharePoint? It is all in there.
Which Performance counters to monitor (ok, some are missing...), Which Components to worry about, which platforms to choose when architecting your farm. All in there.
How to create your own BDC App config, How to work with scopes, crawl rules, managed properties and different content sources. All in there. Hey even how to create a new search tab.
Get Excel Services up and running, publish workbooks and install Adminisrtator approved infopath form templates. All in there. Best practices when doing capacity planning. Real world scenarios when thinking about content analysis. Good ideas on approaching taxonomy issues.

Geez. there is so much in the course that I actually ended up skipping around 25% of the content across the board and asked the students to read up at home.

I'm soooo looking forward to teaching this course again soon. Shame only two people turned up for the last one, but then I was happy, as it was my first teach and I can experiment a bit when the class is small.

There is no excuse anymore for lousy SharePoint Specialists out there. Anybody who wants to call themselves a SharePoint Specialist and is lacking the knowledge and experience must go on this course.

Changing the AssetPortalBrowser Behaviour

Following are some instructions on how to change the default behaviour of the AssetPickerDialog also known as AssetPortalBrowser.aspx

1) Change the paging size: That's an easy one!
go to the ProgramFiles\....\12\Template\LAYOUTS folder and edit AssetPortalBrowser.aspx. BEWARE. Do not edit with SharePoint Designer. It will change all the paths and nothing works no more.
On that page you will find a control called ObjectList1 of Type SMObjectList. It has a PageSize attribute wich you can change from 15 to anything you like really.

2)Changing the Default Sort behaviour: Tricky!
Sadly enough setting the SortDirection ad SortExpression on the Control will not come into effect as the sort is overridden every time the context changes. And the context changes every time the page is loaded. Thus, no matter what you set here it does not seem to work.
The Workaround includes some C# and visual Studio knowhow.
a) Create a new Class Library
b) Add References to the the Microsoft.SharePoint, Microsoft.SharePoint.Publishing and System.Web dlls
c) Create a new Class that inherits from Microsoft.SharePoint.Publishing.Internal.CodeBehind.AssetPickerDialog
d) override the OnLoad event and set the SortDirection, SortExpression and ExplicitView of the ObjectList1 Control programatically. Do this after calling the base.OnLoad() event to make sure the control is loaded correctly.

public class CustomAssetPicker: Microsoft.SharePoint.Publishing.Internal.CodeBehind.AssetPickerDialog
{ protected override void OnLoad(EventArgs e){
  base.OnLoad(e);
  this.ObjectList1.SortDirection = "DESC";
  this.ObjectList1.SortExpression = "Modified";
  this.ObjectList1.CancelSortOperation = false;
  this.ObjectList1.ExplicitViewName = "All Documents";      
 }
}

e) sign the assembly

f)install the assembly into the GAC either using gacutil or drag&drop it into c:\windows\assembly
g)fix up the web.config to allow the new assembly by I)adding it to the SafeControls list and II)the compilation\assemblies list
h)Change the Page directive on AssetPortalBrowser.aspx to inherit from your new class instead of the Codebehind class.

Voailla, you now changed the default sort to always show the last modified files and images first.

Data Views and the AdventureWorks Database

If you ever tried to get Data Views to work with the AdventureWorks DB you probably have had as much pain and frustration as I have had in the past.
First you'll need to get SSO set up to connect to the DB. That's simple once you know how!
Then you select the table you want and when you try to show the data you keep on getting error messages! The problem here lies in the fact that AdventureWorks uses Schemas everywhere. And you need to fully qualify your tables to be able to access them. SarePoint Designer is not clever enough and forgets to put the schema name infront of the select command.

Here are two possible solutions:

1. simply write the select statement yourself instead of picking the table from the list. A simple statement like Select * From Production.Product will get you going in no time.
2. Create a deidacted login for a specific schema and define the defauls schema.

Here are the steps for the latter option:

1. Create a login on the SQL Server called AWProduction.
2. Set its default database to AdventureWorks.
3. In its user mappings give it pubilc access to AdventureWorks and specify the Default Schema to be Production.
4. go to the schema definition on the AW database and give AWProduciton all the required permissions on the Production Schema.
5. Set up a new SSO Application that uses the new AWProduction Login
6. Use that SSO login for your Data Connection

Hope that helps!

Using Dataviews to edit and update SQL data

If you hooked up your dataviews with SQL data and enable editing you most probably have come across following error message:
The data source control failed to execute the update command

The reason for this can be manyfold, either you might have a data type issue where you have non numerical data being pushed into a numerical field or in my case as simple as a database constraint getting in your way.
Dealing with the select, update, insert and delete commands manually can be a bit of a pain. So I really like the ability to pick my table and let SharePoint Designer worry about the rest. Just a shame that SharePoint designer does not understand one simple rule. You can not update a column which is the identity column of a table. SQL server reserves the right to auto increment that column. How to fix it? two ways:

The simple way:
Start with a blank Data View and make sure not to add the ID column to the list.

What if you want to show the id though?
The hard way:
same as above but you then you add the ID not by using the Edit Columns... task but simply by adding a new column to the dataview itself (click into first column of the DataView, right click and choose Insert...Column to the left) and injecting the ID manually. Easiset way to do that is to copy one of the other fields and then changing its databinding to point to the ID field.

People Search for WSS

Recently I attended a presentation by Ken Biswell from RedVespa and totally fell in love with their approach to People Search. He was presenting at the Wellington SharePoint Usergroup giving us insider tips and tricks for how to tackle a SharePoint project from the Business Analyst point of view and it was a great session.
So what was their approach to People Search? Simple! They generated WCMS driven profile pages which make it easy to update and copy/paste whole CVs into and created a separate content source which only indexes those pages. Thus the PM who is now looking for a person with a specific skill like ITIL or SCRUM can simply search the limited content source and get only the results that interest him/her without being bombarded with irrelevant results.
Simple, elegant and most importantly effective! Saving time keeping profiles up to date and saving time searching for key people.