Combination charts: Stacked bar chart with line

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.

I plan to post articles on the following topics:

1. Simple online column chart  (Demo)
2. Combination charts: Stacked bar chart with line. (Demo)
3. Using tabs to build a dashboard and printing reports.
4. Visualization: Packing a lot of data into a simple chart to yield  maximum benefits.
5. Slice and Dice data while using a  page from social media  to build intelligence into your reports. 
6. Analytics  -  What If Analysis.
7. Tips for extracting, transforming and loading data for online reports.
 

  The code for all reports can be found here

 

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 of 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: We are going to 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 here 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.