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