After a very eventful weeks snowboarding, I’m back in Blighty again and once more on the SharePoint development trail. The project suffered a minor set-back as I return from holiday with a broken nose and a re-injured back from a couple of bad falls. Nothing too major, but I also picked up a chest infection on the plane home, and ended up collapsing at home as a result of coughing too much!!!
So anyways, a week extra holiday later and I’m back at the grindstone..
This weeks project is to revisit the KPI forecasting that I set-up back in November. At the time we were trying to be quite fancy using Excel services, the idea being to be able to have a nice pie chart from the stats produce. If you’ll remmber I waxed lyrical at the time about the lack of support for SharePoint as a data source to excel.
Well in the revisit, I’ve dropped Excel services for the meantime and written a couple of webparts to provide the same functionality. The first provide two simple dropdowns and a submit button that allows the user to select a Month an Year. This then delivers the selected date in the format MMM-YY to the receiving webpart.
In this case, the receiving webpart takes this date and uses it in a cyclical calculation that iterates through an array of library url’s and counts the total of items returned by a CAML query.
This data is then used to display the require KPI data for the business.
The basic process I’ve used is to firstly check that we’re receiving an input fro the other webPart. If we are, then we create the 3 main objects that we need, An instance of KPIreports class to hold our results, A hashtable of CAML queries and a String array of library URL’s to iterate through in the calculations.
The first collection of results is done using a For Each loop and the library array. For each of the URL’s, we connect to the list, Submit the require CAML query restricting the data returned to just the dates that we want. Capture the totals into the class instance, and then move on to the next query.
Following this, we then run a few seperate queries against the Cancellations library to count which stages the cancellations occured, and finally we trap items that have been billied.
Once all the data is in place, we can then output the results to the webpart.
The gotchas? Well anyone using CAML is going to hit the odd problem, although the U2U caml creator tool is very helpful. The main problem is the error “A field is not installed properly”. What this basically means is that the CAML query can’t find the column by the name you’ve given. Make sure that you’re using the internal name with the wonderful format like so “Internal_x0020_Approval_x0020_Month”.
To make the CAMl queries a little bit more readable, I added them to the hashtable as a concatenated string over several lines:-
NB: Each of the CAML statements should have the ole greaterthan/lessthan signs around them, but Wordpress strips them out.. I’ll re-edit this tommorrow once I know what I need to use to make them display..!
CAMLQueries.Add("cancellationsImplementation", "Where And" +
" Eq FieldRef Name='Cancellation_x0020_Month' Value Type='Text' " + returnKPIResults.DateToCheck + " /Value /Eq" +
"Eq FieldRef Name='Current_x0020_Stage' / Value Type='Text' Implementation /Value /Eq" +
"/And /Where");