Slice and Dice data while using a page from social media to build intelligence into your reports

Welcome to part 5 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.

Here are the articles in the series.

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. (Demo)
4. Visualization: Packing a lot of data into a simple chart to yield  maximum benefits (Demo)
5. Slice and Dice data while using a  page from social media  to build intelligence into your reports. (Demo)
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

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 potential 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).  

Visualization: Packing a lot of data into a simple chart to yield maximum benefits

Welcome to part 4 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.

Here are the articles in the series.

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. (Demo)
4. Visualization: Packing a lot of data into a simple chart to yield  maximum benefits (Demo)
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

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 and add a few more features.

Here's the link to the code.  Running this 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.  

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.  Yo 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. 

Using tabs to build a dashboard and print reports

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.

Here are the articles in the series.

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. (Demo)
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

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.

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

 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.