Representation of data in Sharepoint

Let’s be honest - it can be frustrating and difficult to have conversations with users over things like report colour, graphics and so on. For some very bright technically-focused people, a real pain point is the ‘soft stuff’ regarding the presentation of data in reports. If the data is right, then why does the presentation matter?

Well, let’s discuss ways to take some of the pain away. Sharepoint is a very effective and interactive way of presenting data to end users in a comfortable browser format. However, since the information is displayed in a simple way, it is tempting to think that the implementation does not involve a lot of planning or forethought. In the case of Sharepoint, it is essential to plan the process and carefully involve key users.

When we are dealing with data presentation, how is it possible to decide what’s the success criteria of the project? Measuring success is straightforward for reports. Regardless of their delivery mechanism, reports are either right or wrong in terms of their numbers. In Sharepoint, however, the ‘rightness’ of the display is a subjective matter. When evaluating the display, everyone brings their own preconceptions to the table. Where a pie chart might be right for some, a bar chart will be right for others. This does not start to take certain human factors into account such as colour-blindness. Ultimately, the presentation of the data is important to users, which in turn means that it is a critical success factor in the delivery of the project. Due it this, it is essential to define the key success criteria of a Sharepoint implementation and plan towards those goals.

However, producing pretty graphs isn’t the full story. It is also essential to clarify where the implementer’s responsibility ends and the organisation’s responsibility starts. Where do you draw the line between your responsibility and the customer’s responsibility? One area of confusion may be security, and this affects how the users interact with reports. Sharepoint can be implemented in a ‘stand-alone’ mode, where it is distinct from the SQL Server Reporting Services; or alternatively, Sharepoint can be implemented in an ‘integrated’ mode, where the reports security model is implemented in Sharepoint. If Sharepoint integrated mode is to be implemented, then the responsibility for security part of the implementation needs to be set out clearly since this will require input from the security team at the customer site. For example, the integrated mode requires Kerberos implementation, which can be difficult and may involve different parts of the IT infrastructure. If it is a difficult issue, perhaps implementing Sharepoint in stand-alone report mode will do.

One approach to consider is a phased approach. One suggestion is to use the Scrum methodology to manage projects like this. A key theme to Scrum is the involvement of one or more business representatives in the development process throughout the duration of a project. In an ideal ‘Scrum’ project, at least one key user should be part of the development team. Also, a key theme in the Scrum framework is the regular demonstration of deliverables aimed towards the project success criteria. Thus, early feedback can be captured and the development can be re-pointed to include this user input. The result is no surprises when training begins, better user buy in, and a solution which genuinely delivers and adds value to the enterprise.

This approach can be easily applied to the implementation of Sharepoint, and in particular to the implementation of reports. It could be possible to aim to deliver something similar to existing formats, with a small number of ‘whizz-bang’ features as a taster to future project phases. I once spent a whole afternoon discussing report colour schemes with a customer. The report numbers were bang on, and the charts were delivered as bar charts in accordance with the users’ request. However, the customer simply wasn’t happy with the colours and wanted them to match their previous report format. Initially I wasn’t convinced of the value of delivering the customer something very similar to their previous setup. However, as the project progressed, it was possible to see that the replication of their existing reports provided the customer with a degree of confidence about the new system. Once the confidence was earned, it was possible then to move on to more advanced displays and reports that really added value.

Depending on the organisations’ specific reporting requirements, it should be possible to consider some general guidelines regarding data presentation. If the organisation is a financial organisation, for example, then it should be possible to deliver Excel reports in box plot or ‘box and whisker’ formats. Excel does not offer this by default, but it can be easily produced. In order to explore how box plots can be created in Excel 2007, some instructions are given at the end of the current document.

Once the reports are produced in Excel or Reporting Services, it is possible to upload them to Sharepoint for a simple and interactive display. The final result for the user is comfortable, easy to access data that appears consistent via a web browser, although it could be taken from a disparate range data sources.

To summarise, using SQL Server 2005 facilities, the user is abstracted away from the data and presented in a familiar way via Excel and a browser via Sharepoint. The appearance of the data is important to users, and thus should be considered whilst planning a reports deployment; it is not enough that the data should be correct, it also needs to be displayed in a comfortable and user-friendly way. SQL Server and Sharepoint are an excellent way of moving towards a delivery of a project that will result in user satisfaction.

Excel ‘Boxplot’ Instructions

1.In Excel 2007, create a new spreadsheet.

2.Here is some sample data to use:

1.

<table>
<colgroup>
<col style="width: 33%" />
<col style="width: 33%" />
<col style="width: 33%" />
</colgroup>
<tbody>
<tr class="odd">
<td></td>
<td>Q3</td>
<td>Q4</td>
</tr>
<tr class="even">
<td>Q1</td>
<td><p>244001</p></td>
<td><p>290000</p></td>
</tr>
<tr class="odd">
<td><p>min</p></td>
<td><p>212000</p></td>
<td><p>220000</p></td>
</tr>
<tr class="even">
<td><p>max</p></td>
<td><p>410000</p></td>
<td><p>450000</p></td>
</tr>
<tr class="odd">
<td><p>meridian</p></td>
<td><p>307000.5</p></td>
<td><p>350500</p></td>
</tr>
<tr class="even">
<td><p>Q3</p></td>
<td><p>370000</p></td>
<td><p>411000</p></td>
</tr>
</tbody>
</table>

3.Insert a line graph by going to Insert on the main menu, and go through the ‘Chart’ wizard.

4.Select the line graph, and it will be necessary to reverse the row/column order. This can be done by clicking on the graph and selecting the ‘Design’ option. On the ribbon, it is possible to see a graphic designated ‘Switch Row/Column’, and this will need to be selected.

 

5. On the line chart, click on each line in turn and choose ‘Format Data Series’. The following window will appear. For the option ‘Line Color’, select ‘No Line’.

6. Click ‘Layout’ on the ribbon, and select ‘Up Down Bars’. From the option list, select the second option to ‘Show Up/Down Bars on a Line Graph’.

7.Click ‘Layout’ on the ribbon, and select ‘Lines’. From the option list, select the third option to ‘High-Low Lines’.

8. The final Boxplot can be seen below. This representation of data may be useful to financial services organisations who are accustomed to looking at these types of graphs.

Comments

• Anonymous
  January 01, 2003
  Jennifer Stirrup from Aspective has written another great business intelligence article for us on her

• Anonymous
  May 08, 2008
  This approach won't work. Examples are excel spreadsheets with references to the lists or SQL queries cannot be exposed with Excel Services.