Life is not easy by any means while working towards the end of my SharePoint 2010 development book (Sahil Malik; Building Solutions for SharePoint 2010). I’m going to save a lot of wasted time for those who will follow this very same learning path.
Desktop Experience missing
In Chapter 11 page 319 you could face this cryptic instruction while trying to do the suggested Exercise “Once you have finished importing the data from SQL Server, choose to save the .odc file in a SharePoint “Data Connections Library” that you will need to create beforehand“. And this is by any means not as trivial as it sounds to be. First of all some basic prerequisites must apply, otherwise you are just doomed (like me was). Hence for make sure you have turned on Desktop Experience Feature. Many thanks in this last regard to Fabian Williams publishing the correct hints on his blog.
So check please first (Picture 1) that Desktop Experience Feature is installed on your system. This looks rather like a bad joke, as I couldn’t find any official hints how the Desktop Experience Feature is going to affect SharePoint Library publishing capabilities. Fabian Williams suggests it will install the WebDav-Protocol which is needed to make a SharePoint sites look like a file share. On the other side SharePoint is hosted in IIS and explicitly turning off WebDAV Publishing at IIS Role Services (Picture 2) will not affect in any way the publishing capabilities like described further below. I would appreciate any further hints to explain this optical discrepancy.
Let’s create now the Data Connection Library which will keep our Office Data Connection files. Therefore go to Site Actions >> More Options >> Filter by >> Library and take the “Data Connection Library” template (Picture 3). Add some compelling name and click lastly the Create button. Please note, do not try to do this via Site Actions >> New Document Library.
This way the Library is prepared and waiting for some ODC file to be uploaded (presumably via WebDav right?). You could now open up Excel and the existing Data Connection perhaps already created. Click thereafter the “Data ” pane and select Connections (Picture 4).
In the Workbook Connections Dialog Box click “Properties…” then the Definition tab and thereafter click the “Export Connection File…” button sitting at the bottom of the dialog (Picture 5). The Save As Dialog Box will appear whereas you could insert the previously created Data Connection Library’s http-link (Picture 6). Clicking the Save button would let you see the “Path does not exist” error message which could have been driving you crazy without knowing that the Desktop Experience has to be turned on.
The sign of good luck is to see the Web File Properties dialog appearing on the screen (Picture 7). Thereafter you could diligently change the Title add the Description and Keywords as well. The only minor problem remains (which looks for me rather like a bug) that changing the Title, this will be not reflected while browsing to the targeted Library (Picture 8). This however could be worked around additionally editing the properties of this uploaded item in the document library.
The author’s further instruction is again not precise enough. He suggests “At this point, Excel will prompt you to import the data as either a table, a pivot table, or a pivot table and pivot chart“. Unfortunately this is simply not accurate. Following the steps, after saving the ODC file into the dedicated Library within SharePoint, you rather have firstly to close Excel and reopen it again. After Excels opens up go to the Data pane and click on Existing Connections (Picture 9) and from the Dialog Box select the Orders you just saved previously. This is the moment the Import Data Dialog Box will show up whereas you could select the “PivotChart and PivotTable Report” option (Picture 10).
Formatting and publishing the Spreadsheet
Arriving at this point you could see right now a view similar to Picture 11. Start drag & drop on the right hand side “ShipCountry” to the Report filter Box (Picture 11). Next drag & drop “ShipCity” and “ShippedDate” into the Axis Fields Box and lastly drag & drop the “Freight” filed into the “Σ Values” Box. Lastly you could change the bar chart to line graph selecting the Design pane and clicking on “Change Chart Type” (Picture 12).
Furthermore on Page 320 we can find an error depicted in the Figure 11-11. The problem could be that Sahil Malik is rather a passionate developer like me and less likely an Excel freak. So while following the instructions be careful while adding the “Difference from Average” column.
Here how to do it correctly. Firstly just type that Title into the cell C3 and thereafter let collapse all the cities below “Row Labels” column. This makes sure all US cities’ “Sum of Freight” will be placed into the cells starting (top to bottom) from B4 to B15 (Picture 13). Next select the cell C4 which right now should be empty, and type into the formula bar “=B4-AVERAGE(B4:B15)” like Picture 14 shows.
Next please do not repeat the mistake the author did, namely making copy & paste of this formula over all selected cells (meaning C5 to C15). Doing so you would get the same wrong results like in the book’s Figure 11-11. For example in the book for the city Walla Walla the difference value –6875.945 is displayed which is wrong. Why? Let’s make first an average over the fright values. Average(B4:15) = 1147,6075, so the correct difference for Walla Walla is 19.4 – 1147,6075 = 1128.2075. The book’s mistake here is that doing Copy & Paste will increment all cell coordinates. Therefore for the cell C5 the formula will be B5 – AVERAGE(B5:B16), for the next cell it is incremented to B6 – AVERAGE(B6:B17) and so on. What we actually need, that the AVERAGE(B4:B15) remains constant while only the formula’s left hand side cell coordinates become incremented.
In order to have conditional formatting select the cells C4 to C15 and click on Home pane. Next click on “Conditional Formatting” in the Ribbon and choose “Data Bars/Gradient Fill” (Picture 15). Here green color is for above average freights and red bar depicts below average freights.
At this point you have to create in SharePoint a Document Library, this time via Site Actions >> New Document Library and taking “Microsoft Excel spreadsheet” as document template. Name this Library like “Sheets”. Next you could publish the created Charts into that Library. Switch back to Excel and in the backstage view File publish the created chart via Save & Send >> Save to Share Point >> Browse for a Location >> Save As (Picture 16). This time we know that the target location is composed of http://<server>/<path>/Sheet + Book1.xlsx