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