February 2008 - Posts
Oskar asked me today if I could walk some folks on his team through what can be done with the CQWP out of the box with no code. So before I write that up tonight, I figured I'd quickly describe what I know how to do and what I don't. Unfortunately, the later list is larger and more formidable than the former.
So the problem that I solved was this:
- Project portal has four sub-sites for sub-projects.
- Each sub-site has a list of Scrum sprints. The actual names of the lists are not important, what is important is that the lists have the same column names.
- I needed to get all items from all Scrum sprints and show them in one place. The output values included calculated columns, numeric columns and text columns.
- The values I pulled did not include multi-value or multi-choice columns. I may include these in my write up but I don't think they are particularly useful in this context.
- Each sprint line item had a status value that was manually set through a hard wired drop down list in that site. I did not use a site column or a content type for this.
- Each line item status string also corresponded to the file name of an image. This allowed me to semi-dynamically show a KPI-style indicator of the sprint status.
So what you can do with the CQWP:
- Pull many items from many lists into one view.
- Dynamically make simple decisions about each line item in a list to change individual item displays.
- Sort all data returned, regardless of what list they originated from.
- Group data based on the list that items were pulled from.
What you cannot do (or more likely I don't know how [yet]) with the CQWP:
- Combine multiple items from multiple lists into one display item. By this I mean that I haven't found a way in the xsl to take manager from site list A, subordinate from site list B and and hourly rate from site list C and show all items on one line as if they were the same list item.
- Aggregate, roll up or summarize list items in a group header or in the CQWP header. I know that xsl is pretty powerful but I am not sure how this would work. I seem to recall that xsl is a forward-only processing engine. If that's true, although you could aggregate values in variables, you would have no where to show them unless you were able to somehow create a group footer. Caveat emptor here.
- Still, there is a group header style in a WSS site collection so there may be hope. If anyone knows how to do this, please spill it!
- BDC Content: I don't know how BDC data is presented in SharePoint so I can't say whether or not this is possible. I have a project coming up that will probably be hitting Oracle business data from a MOSS platform so I'll test this out in the future if the opportunity presents itself.
So in the true reporting sense, this is not a reporting tool but it is certainly a tool that would be handy as part of a dashboard where you need line items from many sources coalesced into a single view.
I'm currently going through some study materials for the 70-630 exam. I think it's going to take a pretty long time before I am there because my foundational knowledge of this technology is still pretty slim. At any rate, I located a bunch of tech-net links describing Shared Service Providers and security. Here they are in no particular order. I intend to walk through each of these as I have time (hence the time to get the cert). If I have the time and patience and I find other links I'll pass them on as well.
The Administrator's Companion book mentioned in a previous post is also a solid starting point for lots of this kind of information.
My plan, based on others who have these certifications, is to go for 630 first since people generally think its easier and then do 70-631 (WSS Config). There really isn't much in the way of training materials for the app-dev certifications at the moment.
Configure connections to Profile Services
Plan content deployment
Configure targeted content
Plan for audiences
Plan for people and user profiles
As opposed to Link Love, that is...
Here you go Oskar now stop calling me that...
Have you ever had one of those guys who you could ask any technology question to and he'd know the answer right off? Like he had just did this very same thing 20 minutes ago and could rattle off what it took to make it happen? That's Tad. And now he's blogging. Some of his friends have been on him to do this for a long time but I'm glad to say he's finally out here in space. I look forward to watching his content...
Took a second to realize what the heck was going on here. I think that this was due to a number of tries at installing VS 2008 and some MS BI tools and then uninstalling VS 05 and then putting it back. Everything still works but the UI looks like something out of 1992...
Go to the list settings page. Right click on "Title, description and navigation" and copy the URL. Paste that into notepad and copy everything after "List=" in the string. That's your URL Encoded GUID for the list. All you need to do is decode it. I found this php page that does it for you so you don't have to whip up a quick program to do it. I'm sure there are zillions of others around.
Or maybe there is a URL decoder somewhere in the MS stack that I'm not aware of. Anyone? Anyone?
There are lots of articles on the net about doing this but there were some things that I found really problematic in my efforts to get an update out.
My Business Problem
My company uses Scrum (generally) for project management. Therefore, we have planned "Sprints" that break down into tasks. These sprints have start and end dates and a status value. There is a specific project that is quite large and will involve a number of sub-projects that will be managed individually. So, naturally, we set up sub-sites from the top level project portal (MOSS). Now, the PM needs to see a listing of sprints across all sub-projects (sub-sites' sprint lists). The requirement here is that the sprint lists need to all have the same column names but they do not have to be built from a site template, though that would help speed their creation.
So in a portal page the PM wants to see each project with each sprint across all sub-sites. He wants to see sprint name, start date, end date, current status and an icon that corresponds to that status value.
This solution involved importing a customized .webpart file and updating the ItemStyle.xsl file with a new template element.
I'll do a longer blog if I have time of how I did the complete solution but for now, here are the things that I found that didn't show up on the other site's tutorials.
In the ItemStyles.xsl file, the name and match values cannot include spaces.
If you copy the Default template, make sure you modify the match="*" to be the name of the template. Otherwise, any testing stuff you put in will show up for every web part that uses the default template. Could suck big...
Unlike HTML, the ItemStyles.xsl file is absolutely syntax-sensitive. I accidentally put in a duplicate element. Thud! I put in an HTML image tag with no "alt" tag. Splat!
To format a date:
Then this to the xsl variables - <xsl:variable name="StartDate"><xsl:value-of select="ddwrt:FormatDateTime(string(@Start_x0020_Date), 1033, 'M/d/yyyy')" /></xsl:variable>
Then this to render it: <div><xsl:value-of select="$StartDate"/></div>
If you add HTML tags to your item template and it bombs, put it into an HTML file in VS 2008 and see if you get squigglies indicating a "standards" issues with the element. (e.g. img tags require an alt element)
Divs seem to always take up an entire row even if their styles have hard widths set. Try nesting spans in divs and setting the styles there. Not sure how this will fly with FireFox but it works in IE. Nobody uses FF anyway, right ;)
Find the style templates by going to the portal page --> View All Site Content --> Style Library -- XSL Style Sheets
In the .webpart file, if you use the ListOverride property, the lists indicated by the GUID's appear to be available across the site collection no matter where you place the webpart in that collection.
In the .webpart file, using the CommonViewFields property, don't put any spaces anywhere in the property value.
In the .webpart file, using the CommonViewFields property, use this link
to see what the SPFieldTypes available are. If you don't put the correct field type string, it will not work and it will not error out.
When you're constantly importing and re-importing the .webpart file, try appending an integer to the title. This will tell you if your update worked and when you get back from lunch you'll remember whether or not you actually deployed the last changes.
For all files that you work with, before you edit them, MAKE A COPY!!!
Hope this helps a bit...
This is what my finished web part looks like in test:
Some colleagues have recently turned me on to two new tools for SharePoint development. Both fit perfectly my personal analogy of what software should be. For me, its a tool that gets the job done. The better the tool, the less you notice it. Think journeyman carpenter with expensive hammer. He just gets the job done better and faster and doesn't spend a lot of time admiring his tool. ;)
Both of these tools are open source which is also nice.
The first tool is STSDev, a console utility that will create SharePoint projects for you with all the various xml files, GUID's and settings already set up. You can just start coding. Two nice things about it are that it does not integrate with VS as a project template so you can use it for VS 05 or VS 08 and target .Net 2.0, 3.0 or 3.5 and there are very brief videos on the codeplex site that walk you though the basics of making it work. This was written by a short stack of SharePoint MVP's and it shows. The output of the app is pretty comprehensive. Custom build targets allow you to build to whatever deployment scenario you need and, if you're not accustomed to stsadm commands, you can echo the commands executed to the output window and copy them off to a batch file for use elsewhere.
SharePoint Manager (SPM)
Need to know the CAML for a list? Need to know where the heck that list acutally exists in the server topology? What's the static name for a column and why can't I reference it? The SPM gives you a very simple, very fast tree view of the entire server and all of its content. I have hit a few "object not set to an instance of an object" error messages but for the speed and power that you get, its worth the annoyance.