Thursday, 28 October 2010

Conditional Formatting bug in SharePoint Designer 2010

We have found a bug in Conditional Formatting in SharePoint 2010.  You set up the conditional formatting logic as you normally would and it just doesn't work (e.g. if you set up a Hide condition for certain circumstances it always hides).

If you click the Advanced button and look at the XPath information you'll see that the field names have been prepended with $thisNode/ and have an extra full stop (period) at the end.  To resolve this remove the extra text and magically it works!

Using Multiviews

Will update later but just wanted to stick this up as an aide memoirw:

Thursday, 14 October 2010

Creating piecharts in SharePoint

Having recently discovered how to produce bar charts from SharePoint data we have been getting requests for Pie Charts.  Bar charts are no great shakes, just an XSLT stylesheet controlling the relative width of two cells in a table, one with a coloured background and one with a transparent background, pie charts are altogether more complex (unless you have MOSS and so Excel services, but we don't).

Initially we found an article on EndUserSharePoint on how to do this using a service from Google.  This works well but has the disadvantage that the data has to be sent to Google's servers which then return the piechart so the user has to be connected to the internet and there's a chance of data leakage.

We then found a solutions for Secure Pie Charts on SharePoint which gets around this by using a solution from Yahoo!, storing the files locally and callugn them from some Javascript.  Well worth a look, I think.

We implemented it, I got it working on Monday and one of my colleagues struggled to get it implemented on Thuresday.  We eventually got it working but only when I remembered something else we had to edit to get it working, which isn't mentioned in the original blog entry (or if it is I missed it) and is easy to miss in the Javascript.

The below assumes that you have read the  blog entry linked above, downloaded the files, unpacked them as recommended and have the Javascript that calls the files open and ready to paste into the page in SharePoint Designer.  We tested this on Sharepoint 2007/WSS3.0 using Sharepoint Designer 2007 no warranty is given or implied, try it first on a test/sandpit server and if you screw up your production or any other server following these suggestions then it's your problem not ours. 

If you paste just the Javascript into a text editor before pasting it into the page the lines you need to edit are 27 (line 27 starts "<link rel="stylesheet" type="text/css" href=") through 39 to point to the local copies of the various files.  The original blog entry recommends putting the files in a document library (create one that isn't shown on the Quick list for this purpose) then using the "Copy Shortcut" on the Right-Click menu to get the URL to paste, I would recommend following that advice.  Then do the same for line 47 (setting the jQPath variable) with the path to jquery.js. but delete the filename leaving the final forward slash (so if the full shortcut is "" the line should read "var jQPath="";).  The penultimate step is to edit line 123 to change the URL for charts.swf to the one for that file on your local server.

What caught us out the second time around is line 71 which sets the value of the variable chartkeyword.  Set this to the name of the field you are grouping by in the view.

Paste the resulting code into the Content Editor Web Part (use Code view) the original blog told you to put on the page, making sure to put it between the <![CDATA and ]] tags.  You should be ready to go and able to view the pie chart in IE.

If you can't see the chart first try pasting in the Javascript unedited then just changing line 71 to the name of the grouping field.  If that doesn't work check where you've pasted the code and you've used the correct field name.  If it does work recheck the various URLs ands change them one by one to the marching ones for your own server, retesting each time.  When it stops working that's the one you got wrong.

We found that initially after pasting in the code Sharepoint Designer 2007 crashed when we clicked back in the Display pane.  If this happens to you see if Microsoft Office Diagnostics starts up and if it does let it run and fix your installation.  This happened on 3 different machines so we figure there was some patch or fix that was missing (or possibly it was because we had Sharepoint Designer 2007 installed along side Office 2003 an there was some library mismatch).  If Microsoft Office Diagnostics doesn't start up make sure you're got the latest patchsets for SharePoint Designer 2007.

The question we're looking at now is if we can control the colours of the pie chart segments.  A number of the lists we're looking to have pie chart views on are for things that have colour coded statuses and that's what we want to display (how many red, how many amber and how many green).

This could be really cool and useful.

Friday, 8 October 2010

ODBC connectivity to Sharepoint 2007

We were asked about getting an ODBC connection to Sharepoint for reporting purposes (i.e. using something like Crystal Reports or even a BI tool like Business Objects) to carry out reporting.  After some research it seems that you cannot use a generic ODBC connection to access Sharepoint, you can however use Microsoft Access to link to Sharepoint Lists and then use an ODBC connection to the Access database.  This does mean that you need to have Access on the machine and it adds an extra layer so it might be a bit slower than ODBC but it does at least give a solutions.

If the link is two way then this also opens up the opportunity to create 3rd party tools to update Sharepoint, possibly via a batch job, to keep another system in sync or to enforce business rules that Sharepoint lacks the sophistication to do.

Wednesday, 6 October 2010

Need to dynamically filter a calendar view

We have a list of project risks where each item (i.e. Risk) is associated with one and only one project, each item also has a proximity date (i.e. roughly when we expect the risk to hit).  We have been asked to product a report page with a calendar view showing the risks against their proximity date with a dynamic filter that will let management select which project they want to view risks for.

Were this just a normal list view, no problem.  Just convert the list view to an XSLT DVWP in SharePoint Designer (or create the DVWP from scratch), drop a datasource and drop down box on the page and feed the selection into the filter on the view.  Job done.  Unfortunately it appears that you cannot convert a calendar to an XSLT DVWP and cannot find a way to create a Calendar DVWP from scratch.

We searched but the results either had nothing to do with what we wanted to do or were in the negative.

Posted this as a question in the Microsoft Sharepoint help forums.

Monday, 4 October 2010

Linking Webparts

We have a report that is basically a bunch of dataviews in webparts pulled from various lists. Serveral of these have filters based on a text field selected via a dropdown.

All well and good until we needed to filter a dataview on fields that are not part of the dropdown. In database terms we neded to do an inline view so that the results of one query were the parameters of another. We were looking at Webpart connections as a possibility but documentation is not exactly abundant. I did find a YouTube video that explained how Webpart connections work and allowed me to work out the rest.

The process is:
  1. Open the .aspx page in Sharepoint Designer (this uses Sharepoint Designer 2007, other versions may differ)
  2. Create a webpart dataview to do the 'inline view'  sub-query, this only has the fields need for parameters for the filters on the main webpart dataview.  This will be referred to as the providor webpart and the main webpart dataview that displays the actual data will be referred to as the consumer webpart.
  3. Set any filters required, the result of this should be to return only one row of data for each value..
  4. Make sure that both webpart dataviews have a meaningful name.
  5. On the providor webpart open the "Common Dataview Tasks" menu and select Webpart Connections.
  6. A dialogue should open giving the name of the Providor Webpart as Source Webpart and 'Send Row of Data To' as the Source action.  The source action can be selected via a dropdown box in the lower part of the dialogue, leave it as 'Send Row of Data To' or change it to that if it has not defaulted to that.  Click Next.
  7. The next step give you the option of connecting to a webpart on the same page or another, select to use a webpart on the same page and click Next.
  8. You can now select the consumer web part in the Target WebPart drop down box.  In the Target Action select 'Get Parameters From'.  Click Next
  9. The next step will show a two column table in the lower part of the dialogue, the left column should be named "Columns in [Name of Providor WebPart]" and the right column should be named "Inputs to [Name of Consumer WebPart]".  In the right column there will be listed any existing parameters on the Comsumer WebPart (there may be just the default one of ListID) and at the bottom <create new parameter>.  Select <create new parameter>, the normal prameter creation box should appear.
  10. For each parameter you need create a parameter with a short but meaningful name.  The 'Parameter Source' drop down should be greyed out and the 'Default Value' should be blank, leave them as they are.  Once you have created your parameters click OK to return to the Webpart Connection dialogue.
  11. Your parameters should now be listed in the right column.  in the left column cell against each one click to select the cell, this will cause a drop down to appear.  Open the dropdown and select the field from the Providor WebPart that you want to populate the parameter.  Repeat for each parameter then click Next twice then finish to close out the dialogue.
  12. Now go to the Consumer webpart and open the Common DataView Tasks menu.  Select Filter.
  13. The normal filter dialogue will open and you can create the filters as normal but instead of creating the parameters you should select the parameters that you have just created.  Click OK to save the filters.
  14. You probably won't want to display the Providor webpart so select the table it is embedded in and open the Table Properties.  Set width and height to zero and click OK.  Finally, select the webpart and open WebPart Properties, open the Layout area and select hidden then OK.  The webpart will still be visible in Designer but won't be when the page is viewed in a browser.