Category Archives: Charts and Graphs

JQuery dashboards to SharePoint: How To


You may also be interested in: SharePoint Solutions In-A-Box from Alcero


 

Editor’s note: Contributor Peter Ward is a SharePoint Solution architect. Follow him @peter_1020

This post outlines the steps to apply JQuery dashboards to a SharePoint 2010 page.

  • No coding or administration is required.
  • Skillset: Power user, you should be familiar adding content to SharePoint pages. Eg: Content Editor web parts
  • The data has to reside in lists

- For further details see Alexander Bautz post. he did the heavy lifting, this is a more step by step approach to his code.

Steps

Add these components to your Site.

  1. Copy the contents of this JS file into the Site Assets library of the site: Click here

 2012-07-18-jQueryDashboards-01.png

  1. Create a list in the site called GoogleVisualization_InteractiveChartsConfig (This name has to be exact) . This list should be based from a list template called. If this templatge doesn’t exist in the site collection, email SharePoint Support and they will add it.

This is the template that needs to be added by the site collection administrator. Click here

  1. Goto the page were you want to display the charts
  2. Add 2 Content Editor web parts parts to the page where to want to display th charts

TIP: To understand how to add a content editor web part, click here.

2012-07-18-jQueryDashboards-02.png

  1. Add the following HTML code into the Chart 1 Content Editor web part

<div id="MyChart1"> </div>

  1. Add the following HTML code to the HTML footer code Content Editor web part

<script type="text/javascript">
// All charts must be represented by a container with a unique id. This container must be present in the page
arrOfChartContainers = ["MyChart1"];</script><script src="/sites/Sales_and_Trading/client_relationship_management/DashboardBoard/SiteAssets/js/jquery.min.js" type="text/javascript"></script><script src="http://www.google.com/jsapi" type="text/javascript"></script><script src="/sites/Sales_and_Trading/client_relationship_management/DashboardBoard/SiteAssets/js/ChartUsingGoogleVisualizationAPI_v2.9.3.5.js" type="text/javascript"></script>

NOTE: The path in the HTML above must correspond to the path of your site.

  1. Save content editor web part and the page.

2012-07-18-jQueryDashboards-03.pngThe HTML is now saved on the page. The chart must now be configured.

  1. Click on the download arrow of the web part.

 2012-07-18-jQueryDashboards-04.png

  1. Complete the Chart configuration menu:

From this:

2012-07-18-jQueryDashboards-05.png

To

2012-07-18-jQueryDashboards-06.png

  1. Click Save

The chart should display:

2012-07-18-jQueryDashboards-08.png

To add additions charts to the page. Add additional content editors:
With the HTML :


<div id="MyChart2"> </div>

And make the addition to the HTML footer:


= ["MyChart1", "MyChart2"];</script><script src="/sites

Further Reading:

The API -

Graph options:

2012-07-18-jQueryDashboards-09.png

2012-07-18-jQueryDashboards-10.png

Product Review: Collabion Charts for SharePoint


You may also be interested in: The SharePoint Shepherd’s Guide for End Users from SharePoint Shepherd


 

Editor’s note: Contributor Chris Howell is an independent contractor. Follow him @enigmaticit

Collabion Charts for SharePoint is a product that can be installed within your SharePoint farm and deployed to web applications/site collections of your choice. Collabion Charts for SharePoint provides a wide selection of charts that can be configured by end users to create dashboards of information.


Collabion Charts for SharePoint

Collabion Charts for SharePoint supports creation of charts from a number of different data sources including:

  • SharePoint Lists and List Views
  • CSV file
  • Microsoft SQL Server Database
  • Oracle Database
  • Business Data Catalog
  • Microsoft Office Excel File

Collabion Charts for SharePoint can be used with:

  • WSS 3.0
  • MOSS 2007
  • All versions of SharePoint 2010 including Foundation Server

Review Details

This review has been conducted using the free trial version of Collabion Charts for SharePoint. The version reviewed is shown as v 1.0.0.0. I installed and configured the product in a SharePoint 2010 environment hosted under a CloudShare ProPlus account.

Download

A trial version of Collabion Charts for SharePoint can be downloaded from the product web site after providing some registration information.
At the time that I downloaded the trial version, the file was only 7.7 MB in size and contains the installation files and a PDF relating to installation, migration and upgrade.

Installation

Administrative access to the server is required to install the product.

Setup for Collabion Charts for SharePoint to leverages the SharePoint Solution Installer that is available on CodePlex.


Collabion Charts for SharePoint Installation Checks

Provided all of the checks pass, you can proceed to selecting the Web Applications/Site Collections the product is to be deployed to:


Select Web Applications/Site Collections to deploy Collabion Charts for SharePoint to

The next step of the wizard is feature activation and after that has completed OK, the wizard can be closed.

This is the end point for any administrative access to the server. It is now over to the end user to configure the charts they need within their sites.

Configuration

Once Collabion Charts for SharePoint has been deployed to the site where the user wants to use it, the next step is to edit a page and add the Collabion Charts for SharePoint web part:


Collabion Charts for SharePoint Web Parts

After you insert the web part into a page, you will see the following and will need click the link to launch the Charts Wizard:


Need to configure web part

Charts Wizard

The Charts Wizard is available to run you through the wide variety of configuration options that are available. The key steps at the start of the wizard are defining:

  • Data Source
    • Data Provider
    • Data Fields
    • Filter Data
    • Group & Drilldown

As above, Collabion Charts for SharePoint supports a number of different data providers and the first action is to define the source:


Select Data Provider

In preparing this review, I tried the product with data within a SharePoint List and the content of a CSV file stored within a document library.

Depending on the data provider selected, you will have different configuration options. The screen below shows the additional configuration settings needed to read from the CSV file:


Data Provider Configuration Options

After you enter the required information, you can test the settings by clicking on "Connect". If the connection is successful, you can the click on "View Data" to see the content of the CSV file.

For the purposes of the review I’ve used an export of data from my Garmin cycle computer:


View CSV Data

Once the Data Provider is defined, you can move on within the wizard to selecting the Data Fields that are to be represented within the chart:


Select Data Fields

The next step in the wizard is to apply any filters that you wish to apply to the data:


Filter Data

If you don’t require any filters, click the red - icon to remove the filter field and proceed to choosing whether you want to group and drilldown in the chart data:


Group and Drilldown

With the Data Source defined, you now move on to further configuration options:

  • Chart Type
  • Series Customization
  • Chart & Axis Titles
  • Data Display Settings
  • Chart Appearance
  • Number Formatting
  • Axis Grid Lines
  • Chart Legend
  • Trend Lines
  • Export Settings
  • Other Settings

I’m not going to go into detail for each of these areas but I will say that there are a multitude of options available to deliver some very impressive charts as part of a dashboard.

Chart Type allows users to select from different categories including:

  • Single Series
  • Multi Series
  • Stacked Charts
  • Combination Charts
  • X-Y Charts
  • Scroll Charts

You can change the categories, select the chart type and click "Apply" or "Preview" to see how your data will be presented:


Applying Chart Type

Series Customization allows you to configure X-Axis date display format, sort order and font preferences.

Chart Appearance allows you to set a background for the chart if you wanted to include a relevant graphic. Under this section you also have the option to set a logo.

Export Settings allows you to configure options relating to whether a person viewing the chart in a browser can export chart data:


Export Data

Or export the chart as an image:


Export Image

Under Other Settings, you can configure several options including custom messages to be shown whilst your data loads or if there is an error:


Chart Messages

Another key feature that can be configured is Rendering Settings. The default value is Flash but if you want to support mobile devices, you can select JavaScript:


Rendering Settings

Finished Chart

After completing the wizard, you are returned to the page and shown the chart you have created. This chart is the one that was rendered from the CSV cycling data:


Cycling Data Chart

I also created a different chart using some statistics for Liverpool Football Club showing goals scored by Louis Suarez and Steven Gerrard across Premier League, League Cup and FA Cup:


LFC Goals

Mobile Support

As mentioned above, the charts can be rendered in JavaScript so they are available to mobile users:


Chart on iPad

Save Chart As

Depending on the configuration settings within the wizard, the chart data can be exported as an image or data. To export as an image, click on the icon in the top right of the chart:


Save as Image

There is also the option to export data by right clicking on the chart and selecting "Copy data to clipboard":


Copy to clipboard

Unfortunately, this option only appears to be present if the chart is rendered in Flash.

Export/Import

After spending all that time creating your charts, a colleague asks if they can have the same type of chart within their site. Rather than having to go through the entire wizard process again, you can use the export and import options.

The export process is simple, select the charts to export and click export:


Export Charts

All goes well and you see this:


Export Success

There is also a batch process to export charts that is available when editing a page to insert web parts.

At the site where you want to import the chart, use one of the options to launch the import process and you will be shown the charts available to import:


Import Chart

Caching

If your data is not changing frequently you can use the caching settings within the web part configuration. The web part allows you to configure the cache duration; the default is 15 mins.

Summary for Collabion Charts for SharePoint

In my opinion, Collabion Charts for SharePoint is very, very impressive. I experienced no issues or problems with the Collabion Charts for SharePoint whilst preparing this review.

Beyond requiring administrative access to install and deploy, the rest of the configuration of Collabion Charts for SharePoint is in the hands of the end user.

I see this as a large enabler for the business/end user to be more productive and creative. I also believe this product could help reduce a dependency on IT resources to produce dashboards.

The import/export options also provide a great deal of benefit as will the ability to support mobile devices with the use of JavaScript.

Pros:

  • Wizard process means great user experience
  • Huge assortment of chart types available
  • Support for a large range of data sources
  • Huge assortment of configuration options to tweak presentation of data
  • Enables business users and could help remove dependency on IT involvement in producing dashboards
  • Export/import option to allow deployment in other sites
  • Mobile device support

Cons:

  • Copy data to clipboard feature is not available within JavaScript version
  • I couldn’t think of more impressive data to use to better show the charts this can produce

SharePoint 2010 at Work - SPJS Charts for SharePoint - Alexander Bautz


You may also be interested in: Don’t SharePoint While Walking by harmon.ie mobile


 

2012-04-30-BookAnnouncement.pngEditor’s Note: Over the next few weeks we are running a series to introduce readers to our new book SharePoint 2010 at Work Tricks, Traps, and Bold Opinion: The Best of EndUserSharePoint.com. Each article will include a sneak peak of some of your favorite authors’ chapter’s from the book.

SPJS Charts for SharePoint

This solution is a bridge between Google’s Visualization API and SharePoint. This enables you to get data from any SharePoint list or library within the site collection, and create dynamic charts with Google Visualization API.

The solution is used to get the data from the lists and present them to the Google Visualization API in the correct format. The charts are created with the Google Visualization API and therefore all chart-related issues must be checked with the documentation here: http://code .google.com/intl/en-EN/apis/chart/. You will find a link to the relevant configuration options for the selected chart from within the Edit Chart GUI.

This chapter will walk you through the setup of the solution and explain the various settings available in the Edit Chart GUI. It will not go into details about how you can create different charts. You will, however, find chart examples in my private blog, http://sharepointjavascript .wordpress.com.

The solution works in both SharePoint 2007 and 2010.

Technical Overview

The technique presented here is entirely client-side and all code and data are processed and rendered in the browser. No data is sent to any server.

The end user does not need to have more than design rights to the site to set it up for the first time. Creating charts after the initial setup can be done with contribute rights.

I have wrapped all code in one file to ease the setup process for the end user. The script-generated HTML that makes up the Edit Chart GUI accounts for the majority of the code lines.

The solution automatically creates the list necessary to store the configuration for the charts. We will use various web service calls to lists.asmx, views.asmx, webs.asmx, sitedata.asmx, and usergroup.asmx to gather the information required to configure the charts in the Edit Chart GUI and to render the charts when they are configured.

The inner workings of the code aren’t discussed in detail here, but the code is commented, so anyone interested can understand it fairly easily.

N O T E
As this solution is client-side, the rendering of charts from large datasets (thousands of lines) can take its toll when it comes to load time and background data traffic. When querying the web service for data, the full XML blob will be downloaded to the client machine. When this is complete, the scripts will iterate through the XML and format it as required for use in the Google Visualization API. The line speed and the processing speed of the end user’s computer must be taken into account.

The actual charts are made using the Google Visualization API, and this solution adds no extra functionality over that provided by Google. This solution may, however, need updates to support new features in upcoming versions of the Google Visualization API. The Google Visualization API team thoroughly tests each new version to ensure backwards compatibility, and I will do my best to keep this solution updated to support new features and fix potentially breaking changes.

Version History

I published the first version of my solution for using Google visualization API to create charts for SharePoint on May 4, 2010. Since then, I have updated the solution many times, but have never given the solution a distinct name. I figured in the event of writing this chapter, I needed to give the solution a name.

I have named it SPJS Charts for SharePoint.

Alexander Bautz is a featured author in SharePoint 2010 at Work: Tips, Tricks, and Bold Opinions by EndUserSharePoint and O’Reilly Media. In order to read the entire chapter on SPJS Charts for SharePoint use discount MILLERSP for 40% off the print edition or 50% off of the ebook

SharePoint Online Charting Part 6: Analytics - What If Analysis


You may also be interested in: SharePoint Smart Notifications by KWizCom


 

Editor’s note: Contributor Vijay Thiruvallur is an independent IT consultant. Follow him @kwikndirtybi

In this article, let’s look at a simple example of how to set sales targets based on What-if analysis.

As a reminder, note that the data has been hard coded into the example to keep it simple. Typically, this data would be added and maintained in a SharePoint list (or an external list ) and retrieved using SPWebservices from SharePoint.

Let’s go back to the Donut sales and trend analysis from the fourth article in this series. Let’s say we would like to play around with the sales data for the rest of the months in the year to figure out how many sales we need to make in order to meet our year end target. For this, we can introduce a form that is rendered with an input text box for the rest of the months. Then, we recalculate the sales figures and the trend based on the user’s input and draw the chart again.

We have used jQuery Templates again to render the input text boxes.

Notice the method to clone the array data of the original sales. This is important since otherwise the array gets copied as a shallow copy and would modify the original array each time the calculate button is clicked.

Here’s the screenshot of the application in action.

Here’s the link to the code and demo.

You will notice that this is a very contrived example, but this should give a sense of the complex types of reports (and reporting applications) that can be built using simple JavaScript, jQuery and a couple of popular open source frameworks.

SharePoint Online Charting Part 5: Slice and Dice data while using a page from social media to build intelligence into your reports


You may also be interested in: SharePoint911


 

Editor’s note: Contributor Vijay Thiruvallur is an independent IT consultant. Follow him @kwikndirtybi

In this article, let’s look at a simple contrived example of how to slice and dice the data and also use SharePoint lists to display and edit comments about report data.

This report is going to be a tabular report displaying Sales data by region. We will start off with sales data for the East and West regions. Then, we drill down into each region and view sales data by city for the selected region.

Also, for each region or city, we have a Notes column which displays comments that can be entered by a regional or city sales manager for example. Each note itself can be clicked on by the person viewing the report and edited if required. One of the advantages to this approach is that it’s easy for anybody (especially upper management) viewing the report to get the story behind the numbers quickly. This could potentially save a lot of time in terms of phone calls and chasing after people to get the same story.

In this report we introduce jQuery templates to render the report in an HTML table. Look at the functions getSales and getSalesByRegion to see how the data is bound to the tables using jQuery templates. Note that we could quickly add a few more levels to this report and drill down starting from a larger region like North America, EMEA, etc and then drill down by country, city, etc. This will depend on the structure of the data being returned from SharePoint though. For the sake of simplicity we stick to just two regions and city sales data.

Here’s the link to the code and the demo.

This report is best suited for data that will be entered, by users, directly in SharePoint lists to take advantage of the rich features that SP lists provide (Easy to create an SP list and add columns, datasheet edit view, built in CRUD, export to Excel, easy to use, No code solution, etc.)

Note that if you have a large amount of data ( thousands of rows of data), and you want to drill down from the top all the way to a line item, it might be better to consider an alternate solution (Performance Point or just pivot reports using Excel are some options).

SharePoint Online Charting Part 4: Visualization - Packing a lot of data into a simple chart to yield maximum benefits


You may also be interested in: KnowledgeLake


 

Editor’s note: Contributor Vijay Thiruvallur is an independent IT consultant. Follow him @kwikndirtybi

In this post, we will try and pack as much data into a single chart as possible (and I’ll try not to make it too cluttered).

Let’s revisit the Donut sales chart from Part 3 and add a few more features.

Running the code should display the following chart:

Here are the new features added to the chart.

1. A set of targets have been added - Low range and high range. Let’s imagine that these targets were set at the beginning of the year and it was decided that if sales fall at or below the red range (8000 units), then it is to be interpreted as cause for panic. If sales fall above the green range (1000 units), then sales have exceeded target and measures have to be taken to meet demand.

2. You will also notice that a trend line has been added. This helps us in predicting a year end sales figure and plan accordingly. This trend is calculated based on a simple linear regression.

Here is the code:

<html>
 <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Flot Examples With SharePoint</title>
    <!--[if IE]><script  language="javascript" type="text/javascript" src="../excanvas.min.js"></script><![endif]-->
    <script language="javascript" type="text/javascript" src="../jquery.js"></script>
    <script language="javascript" type="text/javascript" src="../jquery.flot.js"></script>
 </head>
<body>
<h1> Visualization: Packing a lot of data into a simple chart to yield  maximum benefits </h1>
<p><h2>Donut Sales</h2></p>
<table >
<tr>
<td valign="top">
<div id="columnchart" style="width:600px;height:300px" >
</div>
</td>
<td width="300px" valign="top">
<div id="columnchartlabels"></div>
</td>
</tr>
</table>
<script>
function PlotGraph( d , id, labelId)
{
 $.plot($(id), d,
  {
    xaxis: {
        ticks: 11,
    tickFormatter: function(x) {
          var Months=new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
     return Months[x] ; }
   },
    legend: { 
		show: true,
		backgroundOpacity: 0.8,
		container: labelId,
	    labelFormatter: function(label, series) {						
		  return '<span class="legendLabel">'+ label + '</span>';
		}	
	}	
	});
}
function roundNumber(num, dec) {
  var result = String(Math.round(num*Math.pow(10,dec))/Math.pow(10,dec));
  if(result.indexOf('.')<0) {
result+= '.';
  }
  while(result.length- result.indexOf('.')<=dec) {result+= '0';}
  if (dec==0)
     result = result.replace('.','');
  return result;
}
function getFloatValue(n)
{
   x = parseFloat(n);
   if (isNaN(x))
   {
x = 0;
   }
   return parseFloat(roundNumber(x,2));
}
function getSales()
{
/*** This method is supposed to get data from SharePoint, but for the sake of simplicity, we are going to
skip that step and hardcode the data.
***/
var donutSales = [ [0,7000],[1,8200],[2,8300],[3,8200],[4,8000],[5,8300]];
var donutSalesMinTargetData = 0,8000],[11,8000;
var donutSalesMaxTargetData = 0,10000],[11,10000;
var donutSalesData = [];
var sales = {label: "Donut Sales", data:donutSales,lines: { show: true ,lineWidth:3},color:"black"} ;
var maxLimit = {label: "High Range", data: donutSalesMaxTargetData, lines: { show: true, lineWidth: 3, fill: 0.3 },color:"green" };
var minLimit = {label: "Low range", data: donutSalesMinTargetData, lines: { show: true, lineWidth: 3, fill: 0.9},color:"red" };
var trendData = LinearReg(donutSales,6 );
var trend = {label: "Sales Trend", data:trendData,lines: { show: true ,lineWidth:3},color:"blue"} ;
donutSalesData.push(minLimit);
donutSalesData.push(maxLimit);
donutSalesData.push(sales);
donutSalesData.push(trend);
PlotGraph( donutSalesData,"#columnchart", "#columnchartlabels");
}
function LinearReg(lndata )
{
  XintoY = [];
  XintoX = [];
  SumofX = 0;
  SumofY = 0;
  SumofXY = 0;
  SumofXX = 0; 
  for (var i=0; i < lndata.length; i++)
  {
     x = lndata[i][0];	 
	 y = lndata[i][1];
	 XintoY[i] = x * y;
	 XintoX[i] = x * x;
	 SumofX += x;
	 SumofY += y;
	 SumofXX += XintoX[i];
	 SumofXY += XintoY[i];	 
  }
   var n = lndata.length;
   b =  ((n * SumofXY) - (SumofX * SumofY))/(n*SumofXX - SumofX*SumofX );
   a = (SumofY - (b*SumofX)) / n ;    	
	retData = [];	
    for (var i=0; i < 12; i++)
	{
	  y1 = a + b * i;	  
      retData.push([i,y1]);
	}	
   return retData;
}
getSales();
</script>
</body>
</html>

The code in lines 92-112 are fairly straightforward in explaining how this chart is rendered.

Here’s the link to the demo.

One thing to look out for in this chart is the order in which the data is added and the parameter called "fill" in lines 100 and 101 as well as the colors chosen to represent the various data points. The fill parameter specifies the opacity. You will notice that the areas (low range and high range) are overlaid on top of each other, and the lines (sales and trend) are overlaid on top of these, so changing the order of these might make one of these data points disappear.

SharePoint Online Charting Part 3: Using tabs to build a dashboard and print reports


 You may also be interested in: KWizCom SharePoint Smart Notifications


 

Editor’s note: Contributor Vijay Thiruvallur is an independent IT consultant. Follow him @kwikndirtybi

Welcome to part 3 of BI with SharePoint and JavaScript. This is an ongoing series about building dashboard reports in SharePoint with just a few simple JavaScript frameworks and a Content Editor Web Part.

In this post we will cover the following topics:

  1. Creating a tabbed report to maximize real estate in the browser.
  2. Expanding the tabbed report so it can be printed with page breaks.
  3. Introduce a new JavaScript library called Underscore.js.
  4. Add mouse over to view the values in the simple graph.

Here’s the link to the demo. Here’s the link to the code. I have hard coded the data into the HTML instead of retrieving the data from SharePoint to make this post easy to understand. If you want to look at an example of retrieving data from a SharePoint list look at the previous two posts.

SharePoint Online Charting Part 1: Simple Online Column Chart
SharePoint Online Charting Part 2: Combination charts - Stacked bar chart with line

The code is quite self-explanatory, so I will go over just the main portions.


<html>
 <head>
    <meta http-equiv="Content-Type" content="text/html; charset=utf-8">
    <title>Flot Examples With SharePoint</title>
<style>
UL.tabNavigation {
list-style: none;
margin: 2;
padding: 1;
}
UL.tabNavigation LI {
display: inline;
margin: 2;
padding: 1;
color: #ffffff;
}
UL.tabNavigation LI A {
padding: 5px 7px;
background-color: #255B89;
color: #ffffff;
}
UL.tabNavigation LI A.selected,
UL.tabNavigation LI A:hover {
background-color: #616D7E;
color: #ffffff;
padding-top: 7px;
}
UL.tabNavigation LI A:focus {
outline: 0;
}
div.tabs > div {
padding: 5px;
margin-top: 3px;
border: 5px solid #333;
}
div.tabs > div h2 {
margin-top: 0;
}
</style>
    <!--[if IE]><script language="javascript" type="text/javascript" src="../excanvas.min.js"></script><![endif]-->
    <script language="javascript" type="text/javascript" src="../jquery.js"></script>
    <script language="javascript" type="text/javascript" src="../jquery.flot.js"></script>
    <script language="javascript" type="text/javascript" src="../jquery.flot.stack.js"></script>
	<script language="javascript" type="text/javascript" src="../underscore.js"></script>
 </head>
<body>
<h1> Tabbed Reports and Printing </h1>
<div class="tabs">
<table width="100%">
<tr>
<td>
<ul class="tabNavigation">
<li><a class="selected" href="#Donuts">Desserts</a></li>
<li><a href="#Fruits">Fruit</a></li>
</ul>
</td>
<td valign="top" align="right">
<a href="javascript:void(0)" id="PrintReport">Print View</a>
</tr>
</table>
<div id="Donuts" class="tab_content" >
<p><h2>Donut Sales</h2></p>
<div id="simplecolumnchart" style="width:600px;height:300px" >
</div>
</div>
<div id="Fruits" class="tab_content" >
<p style="page-break-before: always"><h2>Fruit Sales</h2></p>
<table>
<tr>
<td valign='top'>
<div id="stackedcolumnwithline" style="width:600px;height:400px" >
</div>
</td>
<td valign='top'>
<div id='labels'></div>
</td>
</tr>
</table>
</div>
</div>
<script>
function enableTooltip(id)
{
//add tooltip event
$(id).bind("plothover", function (event, pos, item) {
if (item) {
if (previousPoint != item.datapoint) {
previousPoint = item.datapoint;
$('#tooltip').remove();
var x = item.datapoint[0];
//All the bars concerning a same x value must display a tooltip with this value and not the shifted value
if(item.series.bars.order){
for(var i=0; i < item.series.data.length; i++){
if(item.series.data[i][3] == item.datapoint[0])
x = item.series.data[i][0];
}
}
var y = item.datapoint[1];
showTooltip(item.pageX+25, item.pageY+25, y);
}
}
else {
$('#tooltip').remove();
previousPoint = null;
}
});	  
}
function showTooltip(x, y, contents) {
        $('<div id="tooltip">' + contents + '</div>').css( {
            position: 'absolute',
            display: 'none',
            top: y + 5,
            left: x + 5,
            border: '1px solid #fdd',
            padding: '2px',
            'background-color': '#ccc',
            opacity: 0.90
        }).appendTo("body").fadeIn(200);
}
function PlotGraph( d , id)
{
 $.plot($(id), d,
  {
    series: {
    bars: { show: true, barWidth: 0.2, align:'center',fill :0.8, fillColor: { colors: [ { opacity: 0.9 }, { opacity: 0.6 } ] }}
    },
    xaxis: {
        ticks: 5,
    tickFormatter: function(x) {
          var Months=new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");
     return Months[x] ; }
   },
    legend: {
        show: true
     },
	grid: { hoverable: true } 
});
enableTooltip(id);
}
function PlotStackedColumnWithLine( data , id)
{
    $.plot($(id), data , {
series: { stack: true },
colors: ["red", "orange", "black"],
xaxis: {
        ticks: 5,
tickFormatter: function(x) {
var Months=new Array("Jan-11","Feb-11","Mar-11","Apr-11","May-11","Jun-11","Jul-11","Aug-11","Sep-11","Oct-11","Nov-11","Dec-11");
return Months[x] ;
}
},
legend: {
show: true,
backgroundOpacity: 0.8,
container: "#labels",
labelFormatter: function(label, series) {
return '<span>'+ label + '</span>';
}
}
});
}
function roundNumber(num, dec) {
  var result = String(Math.round(num*Math.pow(10,dec))/Math.pow(10,dec));
  if(result.indexOf('.')<0) {
result+= '.';
  }
  while(result.length- result.indexOf('.')<=dec) {result+= '0';}
  if (dec==0)
     result = result.replace('.','');
  return result;
}
function getFloatValue(n)
{
   x = parseFloat(n);
   if (isNaN(x))
   {
x = 0;
   }
   return parseFloat(roundNumber(x,2));
}
function getSales()
{
/*** This method is supposed to get data from SharePoint, but for the sake of simplicity, we are going to
skip that step and hardcode the data.
***/
var fruitSalesData = [ ];
var donutSalesData = [];
var lineItem;
lineItem = {label: "Donut Sales", data:[ [0,3000],[1,3200],[2,2500],[3,2200],[4,1200],[5,2000]] };
donutSalesData.push(lineItem);
PlotGraph( donutSalesData,"#simplecolumnchart");
lineItem = setbars ("Apples", [ [0,3000],[1,3200],[2,2500],[3,2200],[4,1200],[5,2000]] );
fruitSalesData.push(lineItem);
lineItem = setbars ("Oranges", [ [0,1000],[1,1200],[2,1500],[3,1200],[4,200],[5,200]] );
fruitSalesData.push(lineItem);
lineItem = {
data: [ [0,4000],[1,6200],[2,5500],[3,5200],[4,4200],[5,5200]],
label: "Trend",
lines: { show: true, steps: false },
stack: null
};
fruitSalesData.push(lineItem);
PlotStackedColumnWithLine( fruitSalesData,"#stackedcolumnwithline");
init();
}
function setbars(title, d)
{
   var bardata = {
label: title,
data: d,
bars: {
show: true ,
lineWidth: 0,
barWidth: 0.4,
fill: true,
fillColor: { colors: [ { opacity: 0.9 }, { opacity: 0.9 } ] }
}
};
return bardata;
}
var showMenu = true;
function gotoPrintReport()
{
	var tabs = ["#Donuts","#Fruits"];
	 showMenu = !showMenu;
	 if (!showMenu)
	 {
		$(".tabNavigation").hide();		
		_.each(tabs, function(x){$(x).show(); });
	 }
	 else
	 {
		$(".tabNavigation").show();		
		_.each(tabs, function(x){$(x).hide(); });
		$(tabs[0]).show();
	 }	
}
function init()
{
/**Initialize tabs  **/
var tabContainers = $('div.tabs > div');
$('div.tabs ul.tabNavigation a').click(function () {
tabContainers.hide();
tabContainers.filter(this.hash).show();
$('div.tabs ul.tabNavigation a').removeClass('selected');
$(this).addClass('selected');
return false;
});
var tabContainers = $('div.tabs > div');
tabContainers.hide().filter(':first').show();
}
/**Initialize Print **/
	$("#PrintReport").click(function() {
		gotoPrintReport();
	});
getSales();
</script>
</body>
</html>

The entire contents of the reports are enclosed in a div called "tabs" . Look at the init() - line 287 function to understand how the tabs work using jQuery.

The PrintReport method (line 268) toggles between showing all the page contents and showing the tabs.

Notice that we make use of the Underscore.js library’s each method here to cycle through each element in the array and either hide or show the tabs. In a later post we will look at another great use with the Underscore library , which is the groupby method.

Finally, notice the code in line 75 and 81. The page break in 81 makes the report print in two pages.

SharePoint Online Charting Part 2: Combination charts - Stacked bar chart with line

 

Editor’s note: Contributor Vijay Thiruvallur is an independent IT consultant. Follow him @kwikndirtybi

Welcome to part 2 of BI with SharePoint and JavaScript. This is an ongoing series about building dashboard reports in SharePoint with just a few simple JavaScript frameworks and a Content Editor Web Part.

This time , we are going to build a slightly more sophisticated chart. It’s a stacked bar chart with a trend line. In Excel, it’s relatively easy to create this kind of a chart. However, I haven’t seen any free/open source web based library out there where you can build a chart like this.

One of the uses for this chart is that at least more than three data points are packed into a small space without sacrificing usability. This chart can prove useful if you need to show the actual values of more than one data point and then compare the sum of these data points against a predefined target . In our example, we are going to continue with the sales theme from the last post. Eating too many donuts is not really healthy, so let’s sell some fruit this time.

We are going to measure the sales of apples and oranges and compare the sales of apples vs oranges! We are also going to compare the target we set against the total sales in the same chart.

Data Source: Use the same SharePoint list we used in part 1 if this series. Go ahead and delete all the data in that list and then insert the following data into the list.

                      Jan       Feb       Mar       Apr        May     Jun   

  Apples          200      250      300        350         250     500

  Oranges        300      350      250        350         250     400 

  Target           800      700      600        600         600     800

Tip: If you have data in an excel list or a CSV file, you can edit the list in datasheet view and copy and paste data into the datasheet view of the list. There are numerous other ways to import external data (especially in SP 2010) , but we’ll stick to the basics for now.

Note that we only have a couple of rows of data to deal with. What if we had to report on a hundred products? Well, we would not display that many items as a graphical chart. If there is a way to categorize the data, we could then show the sales data rolled up by category in a tabular report and then drill down from there. We’ll visit this topic in an upcoming post.

Here’s the link to the code to retrieve and render the chart.

In addition, you also need to include the flot.stack plugin to your page. This plugin comes along with the flot library.

<code><script language="javascript" type="text/javascript" src="../jquery.flot.stack.js"></script></code>

Also, in line 62 , replace the server name with the path to your server.

Notice that we have added very little to the previous example. Let’s discuss the new elements added briefly.

The setBars method is used to add a stacked bar data point. Refer to the flot API for more options.

<code>
function setbars(title, d)
{
   var bardata = {
          label: title, 
          data: d,
          bars: { 
              show: true ,
              lineWidth: 0,
              barWidth: 0.4, 
              fill: true, 
              fillColor: { colors: [ { opacity: 0.9 }, { opacity: 0.9 } ] }          
          }
        };
    return    bardata;  
}
</code>

We pass the title and data to this method and it returns a JSON object formatted to be plotted as a bar.

The following line sets the colors for the data series.

colors: ["red", "orange", "black"]

You can also use hex colors to add fancy colors or colors from your company’s branding standards to your chart.

<code> 
       var Target = {
            data: d3,
            label: "Target",
            lines: { show: true, steps: false },
            stack: null
        };
   </code>  

Notice that the stack attribute is set to null. This is the trick to drawing a line in a stacked bar graph.

<code>
        legend: { 
            show: true,
            backgroundOpacity: 0.8,
            container: "#labels",
            labelFormatter: function(label, series) {
              return '<span>'+ label + '</span>';
            }
</code>

The above lines instruct Flot to add the legends in a separate div outside the chart area.

Here’s the result you should see:

That’s it. We are done, as I promised last time, with a shorter post. We now have a fancy graph with three data points represented in a compact area which gives a clear picture.

Here’s the link to the code and the demo

As usual, I encourage you to send me feedback and questions.

In the next post, we will take a break from building charts and organize the two reports we have built so far into a set of tabs. Additionally, we will also address printing reports that are displayed inside tabs.

SharePoint Online Charting Part 1: Simple Online Column Chart

 

Editor’s note: Contributor Vijay Thiruvallur is an independent IT consultant. Follow him @kwikndirtybi

I encountered a similar situation as the SharePoint Hillbilly‘s client requirements. They wanted fancy solutions to be built without deploying any code using Visual Studio. The only tool I had at my disposal was SharePoint Designer. My requirements were to build a set of automated reports to replace the manual and tedious work of building reports using MS-Excel/VBA.

MS-Excel/VBA have been around for a couple of decades or so, and have matured quite a bit and you can build great reports if you know the tool which can keep you employed in the financial industry if you are an expert.

However, I quickly realized that we ended up spending a lot of time retooling our Excel reports due to constant changes to the dozens of dashboard reports which we were generating on a monthly basis. In addition, we were dealing with multiple data sources (Excel files, SharePoint lists, Other databases) where the schema was constantly changing. If you have a similar scenario, I hope to share my experiences of converting these reports into online reports with you and learn from you in return.

Let’s Build Something Already

Here are a few resources to help you get familiar with some of the online charting options:

Flot Charting library - This canvas based library is an excellent free charting library that is actively maintained and has a vibrant community that not only posts many solutions, but numerous plugins as well. This has worked great even on IE6 for me.

Here are a few other solutions that you can explore if you are adventurous:

  1. gRaphael - Canvas based JavaScript charting library based on Raphael
  2. JSCharts
  3. Highcharts

In addition, there are other Flash/Silverlight/webpart based solutions, but since this blog is dedicated to simple JavScript based solutions, I will let you research those on your own.

Now, let’s get started building a simple online column chart. This is going to be our "Hello World" of online charts.

Tip - Test this example on a local HTML file with hard coded sample before trying it in SharePoint.

Include the following JS libraries in your page in the order shown.

Excanvas - Used to support canvas tag in IE
jQuery
flot

<!--[if IE]><script src="<Path to file on your server>/excanvas.js"></script><![endif]-->
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script src="<Path to file on your server>/jquery.flot.js>"></script> 

Now, let’s build the chart using sample hard coded data before we use a SharePoint list to retrieve, process and build the chart.

Add a placeholder for the chart in our content query web part.

<h1> Total Sales </h1> 
<div id="simplecolumnchart"  style="width:600px;height:300px" >
</div>

Add some JavaScript code to build the chart.

<script id="source" language="javascript" type="text/javascript">
//Define a method to call the flot plot method to draw a bar chart. 
 function PlotGraph( d )
  {
 $.plot($("#simplecolumnchart"),     d,
  {
    series: {
    bars: { show: true, barWidth: 0.2, align:'center',fill :0.8, fillColor: { colors: [ { opacity: 0.9 }, { opacity: 0.6 } ] }}
    },
    xaxis: {
        ticks: 5,
    tickFormatter: function(x) {
          var Months=new Array("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");           
     return Months[x] ; }
   },
    legend: {
        show: true
     }
});
}
// setup some random data
    var d1 = [];
    d1.push([0,3000]);
    d1.push([1,3200]);
    d1.push([2,3500]);
    d1.push([3,3200]);
    d1.push([4,3000]);
    d1.push([5,2800]);
  donutSales =   { label: "Donuts",  data: d1} ;
 //Call the Plot function with our donut sales data
 PlotGraph( [donutSales]);
</script>

Loading this on your browser should yield the following chart:

Add the data into a SharePoint list and read from this list and generate the chart.

For the sake of simplicity, I am going to include all my code in a Content Editor web part.

Create a SharePoint list called SalesList and add the following columns (of type numeric):

Jan, Feb, Mar, Apr and so on up to Dec.

Creating your list this way helps you save on the amount of rows you need and eventually retrieve when the report is generated.

Add the following data as a new item in the list:

Title: Donuts, Jan: 3000, Feb: 3200, Mar: 3500, Apr: 3200, May:3000, Jun: 2800

Now, let’s read from this list using an ajax call and generate the chart.

Here’s the link to the code. Remember to replace the server path in the getSales function. Note that the GetSales method is commented out and we have hardcoded the data for simplicity.

I hope you found this post useful. I realize that this is rather long, but now that we have gone through a lot of the basics, I’ll try and be more concise in the upcoming posts.

Do write back to me and share your own experiences as well as feedback about this post.

Resources

Here are some resources you can use to get started if you are an absolute newbie with JavaScript/jQuery.

  1. JavaScript Tutorial
  2. jQuery Tutorial
  3. JSON

Also, the following resources can help you learn how to use JavaScript/jQuery in SharePoint.

  1. SharePoint Hillbilly - Mark gave an excellent presentation on using jQuery in SharePoint at the SharePoint Saturday NYC event, last summer, and inspired me to get started with my blog quicker.
  2. SPServices - This free library is an excellent abstraction tool, but I have to admit that I only recently found this. I’ll try and weave this into my later posts.
  3. jPoint - Another library that I discovered recently and hope to play with and hopefully provide some feedback.

SharePoint 2010 Pie Chart with Counts

 

So you want to use an out of the box (OOB) SharePoint pie chart to tell you how many list items have a certain status (or other column choice)? Trying to add that web part to a page and setting its target list as the list you want will not work. Many 3rd party tools exist to deal with this exact limitation of the OOB charting web part.

The good news is that in SharePoint 2010 there is an easy way to get this data graphed with the OOB pie chart using a little known trick. For this example I will show you how to set up a Task list where you want to show the Status field count values in a pie chart.

Step 1

Create a custom list called StatusLookUp and add an item for each status you want to have. The items in the list will be used as statuses in the “Status” column via a lookup column.

Step 2

Create the list you want to use to track your tasks, for this example I am calling it ProjectStatus. If you already have a list this will work as well, you will need to migrate your status column data though. After you have your list setup, create a column called StatusLookup. It will be a Lookup column type and you will use the StatusLookUp list as your input. After creating this column you will need to migrate your current Status column data to this column if you already have a list, if not create a few test items and set their status using this column.

This will be a small burden to migrate the statuses of each item but well worth it. I recommend doing it programatically via .Net as a console app or ASPX page. The code for that will simply loop through your list and set the new column data as the current column data with the exception that you will need to format that data to fit into a lookup field type.

This is my basic list with 4 projects that I am tracking and what the status is of each. Note that the StatusLookup field is a lookup of the StatusLookUp list and not the OOB Status field.

Step 3

Create a new Lookup column in your StatusLookup list. Call the column Count and reference the column that you just created in your ProjectStatus list. Be sure the column contains “(Count Related)” in the name. Now that you have created your new Status field in your list and migrated the data over, go back to your original StatusLookup list and create a new column that is a lookup of the column that you just created as a lookup to this list. It sounds like a crazy loop but when you do this it returns a count of the number of occurrences instead of the same data.

Step 4

Add the chart web part to the page and connect it to your Status Lookup list. Set the Count as your Y axis and the Title as your X axis. Finally, customize your chart to be a pie graph and set your data to display as you wish. Viola! Since this post is about the data and not really about the pie chart I skimmed this part but trust me, you can totally just add the web part and walk through the setup wizard and get the data you need.

Hope this helps, enjoy.