I thought I would show you how you could make the CQWP list all documents in your Site Collection that are checked out to me.
The thing is, if you set the CQWP to search the whole site collection or a site for that matter, and then choose “List Type” “Document Library” you will not be able to choose the filter “Checked Out To”.
So how should we do to then?
First of all if you navigate to “Site actions” and “Manage Content and Structure” and then choose “Content and Structure Reports” you will find the reports and the CAML that is used for them. What you should do is to copy the CAML query from the report “Checked Out To Me”.
<Where><Eq><FieldRef Name="CheckoutUser" LookupId="TRUE"/><Value Type="Integer"><UserID/></Value></Eq></Where>
Now, add a CQWP to your page and set it to search a site of your choice, set the “List Type” to “Document Library” and give it a name, chrome type and so on, but don’t set any filters.
Now export the CQWP and open it with Notepad.
Lookup the property “QueryOverride” and replace that row with this one:
<property name="QueryOverride" type="string"><![CDATA[<Where><Eq><FieldRef Name="CheckoutUser" LookupId="TRUE"/><Value Type="Integer"><UserID/></Value></Eq></Where>]]> </property>
As you can see I have already added the CAML from the report for you.
Now save this file somewhere and upload it to the web part gallery.
Now you have a web part you could add that will list all documents checked out to me.
I would like to show you how to work with lookup columns between lists when you i.eg. would like to summarize the amount of orders that a sales person have made.
So we have a list called “Sales persons” with five columns, “Name”, “Region”, “Office”, “Sales ID” and “TotalAmount”.
We also have a list called “Sales orders” with three columns, “Product”, “Amount” and “Sales ID”.
In this list the column “Sales ID” is a lookup column to “Sales ID” in “Sales person”.
We also have a data connection to retrieve these three fields from “Sales orders”.
When we open a form for a sales person we want to see the total amount of orders that’s been made by that person. When we first look at this it looks quite simple. The “TotalAmount” should be the sum of “Amount” where “Sales ID” equals “Sales ID”, right?
The problem here is when we work with lookup columns it is not actually the value of the look up that is brought to your repeating table by your data connection, it is in fact the “ID” of the item in that list.
So to filter which amounts to summarize we should do like this:
The “TotalAmount” should be the sum of “Amount” where “ID” equals “Sales ID”.
Now when we open the form of a sales person, we could see the total amount that person has sold for.
Hope this helps someone!