Click here to Skip to main content
Click here to Skip to main content

Data Analyzer: HTML table to Chart

By , 14 Feb 2013
Rate this:
Please Sign up or sign in to vote.


Demo - http://jsbin.com/iqojut/1/ 

Introduction

"Data Analyzer", as the name says, it analyses the data available in the form of HTML table and provides you all possible options of grouping the available values to display it in the form of chart (bar chart / pie chart).

Background

Whether it was a stone age period or the period of super computers, well known saying - "picture speaks a thousand words" has proved correct in all kind of information conveyed to others.

Based on my experience in the field of development and designing, I have seen that majority of the web sites / web applications are generating HTML tabular contents in the form of reports. These reports could be generated using any of the tool/technology available in the market. Of those, there will be very few sites / applications that provides the option of displaying charts as well as reports, simultaneously. Even if they provide it, users of the sites / applications will have very limited scope in playing with the report contents thereby changing the charts, dynamically.

So, here, I am presenting you a complete JavaScript based solution, where you just need to add few lines to integrate your html table contents with a chart of your choice.

Sounds good? Let's start...

Imagine you have HTML table contents as shown in the figure-1 below -

Figure-1

It is good to provide users with detailed report about certain functionalities, but what about the search / filter facility? You might need to write some extra logic in the back-end / front-end tool for the same.

For the sake of understanding I have added an action button (Analyze) in this table as shown in figure-2 below -

Figure-2

There is no such need to add any button, you can invoke the script directly on page load. Now, when you click on this action button, some magic occurs and you will see some new drop-down boxes added along with the total record count as well as multiple chart options, as shown in figure-3 below -

Figure-3

Here, you can see that all columns available in the table are automatically added in the filter criteria, along with an added option of 'Global Search' which can perform search on all the text contents of the table. Using this filter utility, user can perform search globally or specifically for any column. For example, I would like to search for something called 'Alex'. Figure-4 below shows that the table contents are automatically filtered showing only the records having content - 'Alex'. It has also updated the total record count.

Figure-4

In Figure-3, you might have noticed that at the bottom of action button (Analyze), some extra drop-down boxes are added. These are - 

  1. Chart Type: Indicates what type of chart you would like to see. Possible values are - 
    • Bar Chart
    • Pie Chart 
    • Random (Any of the above chart types could be selected randomly)
  2. Possible Charts: Based on the analysis carried out, this drop-down box provides list of all possible charts from the data available. As per the logic, these charts are always based on the HTML table header columns. Also, this gets populated dynamically.
  3. Along with (Operation): [Optional] This is an add-on feature for version 2.0. It says what mathematical operation you would like to perform during the chart generation. Possible values as of now are -
    • Sum
    • Average
  4. Of (Numeric column): [Optional] This is again an add-on feature for version 2.0. It says on which numeric column you want to perform the operation specified above. 
All these values combine together to provide you the result as Chart.

Data Analyzer Version 2.0 onwards, I have removed the JSChart library. And hence, the selection of chart library has been removed. By default, the output will be rendered using the Open flash chart (OFC) v1.0 (http://teethgrinder.co.uk/open-flash-chart/). 

On selecting any of the option from the Possible charts drop-down box, you will see that the chart is displayed as shown in figure-5 below -  

Figure-5

Up to this point, most of you might be thinking that this is wasteful effort in redeveloping things already available for free. Now the step ahead - 

Select the operation and any of the numeric column from the list (as shown in figure-3 above). When this information combines with that of the selected chart, the outcome will be as shown in figure-6 below -

Figure-6  

While figure-5 was showing the total count of records when grouped by Vendor Name, figure-6 is showing the aggregation of service charges incurred by the Vendors. Isn't that exciting ?? 

You saw the filter utility in the figure-4 above, right?, So, once you filter your data, how can you draw another chart based on the filter records? Here is the solution. In this demo, after performing the filter operation, again click on the Analyze action button. Aaha !!! your charts are ready to go. The Possible charts drop-down box will be refreshed with new contents, based on the changes in the table content, as shown in figure-7 below -

Figure-7

And related chart is shown in figure-8 below - 

Figure-8

Lets perform some operation on this filtered data. Figure-9 below shows the average service charges the citizen- Alex paid for the services offered to her -

Figure-9

Add more complexity ? Let us perform multiple filter condition. The output would be something as shown in figure-10 below -

Figure-10

Let us see how the "Random" Chart type looks like? and what will happen when you select "Show All Charts" option from the possible charts drop-down box? The output will be random display of all the charts based on the operations selected, as shown in figure-11 below -

Figure-11 

How about error handling? what if no records found? Well, everything is handled in the code. If there are no records available, or if all available records are distinct, due to which, its not possible to generate the chart, you will see the message accordingly. Have a look at the figure-12 below -

Figure-10

Well, that was all about the features of Data Analyzer. Hope you found it interesting. If so, read a bit more to learn how to integrate it with your code.

Moving forward: the integration steps

To start with, following steps are required to be carried out in order to integrate Data Analyzer with your current HTML code.

  1. Include the following CSS and JS files in your HTML page HEAD section:
  2. <link rel="stylesheet" type="text/css" href="dataanalyzer.css" />
    <script language="JavaScript" src="DataAnalyzer.js"></script> 
  3. Include following scripts for Open Flash Chart. Add / replace it with your chart script (but you might need to modify the code of Data Analyzer accordingly).
  4. <script language="JavaScript" src="swfobject.js"></script>
  5. Create two DIV place-holders for displaying chart and error messages:
  6. <div id="AnalysisStatus"></div>
    <div id="chartContainer"></div>
    
  7. Create object for DataAnalyzer and pass the required parameters :
  8. <script language="JavaScript">
      var analyzer = new DataAnalyzer('analyzer', 'InvoiceTable', 1, true);
      function doAnalyze() {
        analyzer.start();
      }
      // invoke function doAnalyze() on-load or on user event (button or link click).
    </script> 

And that's it. We are ready to roll the ball !!!

Understanding the API

Well, if you have found this interesting till this point, it would be nice have proper understanding of the APIs used in Data Analyzer.

As you might have noticed, Data Analyzer object is created just like other JavaScript objects. Syntax is -

var analyzer = new DataAnalyzer(thisObject, dataSourceId, vHeaderStartsAt, vAddFilter, vBypassCols, vAnalyzeCols);   
Meaning of the parameters
  • thisObject: [Mandatory] Self referencing object name. It will be the name of the variable of Data Analyzer object.
  • dataSourceId: [Mandatory] Id of the HTML table to be referred and analysed.
  • vHeaderStartsAt: [Optional] Row index of the header row of the HTML table. Default is 1.
  • vAddFilter: [Optional] Flag to determine whether you want to include the filter option or not. Default is false.
  • vBypassCols: [Optional] Array of either column indexes or 'named' columns (i.e. name of the header column AS-IS), or mixture of both. Columns listed in this array will neither be considered for the filter criteria nor it will be considered for the 'Possible charts' option. This parameter value has low priority if value is supplied for the parameter vAnalyzeCols.
  • vAnalyzeCols: [Optional] Array of either column indexes or 'named' columns (i.e. name of the header column AS-IS), or mixture of both. Columns listed in this array will only be considered for the filter criteria. But for the 'Possible charts' option, they may or may not get included based on the analysis. This parameter value has more priority compare to the previous vBypassCols parameter value. If supplied, value for vBypassCols will be ignored. Reason for adding this as a last parameter is that the parameters required for analysing are more compare to that needs to be bypassed.

Next in the API list is the set of public methods supported by Data Analyzer. These are -

(A) Start method

This is the very first (and only) method that developer will need to handle/invoke. Remaining methods are only required to be invoked manually if developer has written some custom code on top of it. Syntax is -

analyzer.start(vFilterOnly, vAutoAnalyzeOnFilter); 
Meaning of the parameters
  • vFilterOnly: [Optional] Default value is false. If set to true, and vAddFilter parameter in the constructor above is also set to true, it will disable the charting option. Only HTML table content filter option will be available. if vAddFilter parameter is false, then any value of this parameter is irrelevant.
  • vAutoAnalyzeOnFilter: [Optional] Default value is false. If set to true, the HTML table content will be automatically analysed and related 'Possible Charts' drop-down box will get populated. When set to false, developer needs to invoke analyzer.start() method based on any user event (button click etc). if vFilterOnly parameter is true, then any value of this parameter is irrelevant.

Table below shows possible outcome for various combinations of using these parameters -

vAddFilter vFilterOnlyvAutoAnalyzeOnFilterPossible outcome
false (or null)Not applicableNot applicable Only Charts will be displayed
truetrueNot applicableOnly Filter will be displayed
truefalsefalseUser need to trigger some event to analyse and generate charts post filtering
truefalsetrueTable content will be automatically analysed post filtering

(B) Filter Table method

This method is generally gets automatically binds with the search text-box that gets created based on the vAddFilter parameter defined above. There won't be any explicit need to invoke this method. Syntax is -

analyzer.filterTable(phrase, tableId, searchIn, headerStartsAt);
Meaning of the parameters
  • phrase: [Optional] Filter phrase to search the HTML table contents. If left blank, All records will be displayed AS-IS.
  • tableId: [Mandatory] Id of the source table whose contents will be searched for.
  • searchIn: [Optional] Column index of the table to be looked for the phrase for filter. If left blank, supplied phrase will be searched in all the columns.
  • headerStartsAt: [Optional] Numeric field to identify from which point the table content starts. Default is 1.

(C) Draw method

This method is generally gets automatically binds with the 'Possible Charts' drop-down box. There won't be any explicit need to invoke this method. Developers in need to integrate their custom chart library might need to look at the code of this method to modify it as required. Syntax is -

analyzer.draw(reportIndex);
Meaning of the parameters
  • reportIndex: [Mandatory] Index of the chart to be drawn on screen. It could be individual chart index or all possible charts.

Customization

If you like this concept of displaying tabular reports and generating charts from it, I am sure you would be thinking of customizing it based on your application / web-site. Below are some points where you need to do changes that suites your requirements -

  • Modify dataanalyzer.css as required.
  • Include your custom chart library JavaScript file
  • In the DataAnalyzer.js file, following changes can be possible -
    • Modify private method - addAutoFilter() to change the text, alignment, format etc.
    • Modify private method - report() to change the chart configurations - Chart type, text, alignment, format etc.
    • Modify public method - draw() to implement custom chart and related formats
    • Modify public method - filterTable() to customize the style for alternate row color, text etc.

Browser compatibility

This code is compatible to all major browsers - IE, Chrome, Firefox, Safari etc.

Behind the scene: Algorithm 

This section is for the people who loves algorithms and would like to go deep into the actual logic implementation of the solution / tool. Below are the steps I followed for designing this Data Analyzer -

  1. analyzer.start() method internally calls the private method - init().
  2. init() method will validate - 
    • the parameters passed,
    • whether the HTML table contains enough rows to render chart,
    • Specific columns to analyze or bypass 
    Based on the validation, it will either return success or error code.
  3. Next, analyzer.start() method will call the private method - analyze()
  4. Actual data analysis is handled in this method. It is again grouped into four sub-routines -
    • Analysing the table headers 
      • This is done using the private method - analyzeHeaders(). It will parse the header row (based on the index supplied in the constructor parameter). This parsing will prepare a METADATA for each and every column.
      • Metadata attributes are - columnRow, columnIndex, columnLabel, columnType (initially unknown or null), and columnData (intially blank) 
      • There won't be any metadata for the header columns with no text contents (these columns might be used for formatting and spacing purpose).
      • If vAnalyzeCols parameter is passed, metadata will only contain the header columns that matches with the values available in this array. 
      • If vBypassCols parameter is passed, header columns matching with the values available in this array won't be inlcuded in metadata.
      • At the end, it will check for the flag - vAddFilter If true, then it will invoke another private method - addAutoFilter()
        • addAutoFilter() method iterates through the metadata and will create the dynamic HTML code that will be placed above the HTML table. This is how the automatic search/filter functionality is added in.
        • When particular text is searched, all those rows that does not contain the search text will be made hidden from the table. On clearing the search text, these hidden rows will again gets visible.   
    • Analysing the table contents 
      • This is done using the private method - analyzeData(). It iterates through every visible row and every individual column in it. 
      • Table contents are only analysed when any of the flag - vAddFilter or vFilterOnly is set to false. When both are true, there is no need to process and generate charts.  
      • Empty columns will by bypassed from the analysis 
      • Metadata attribute - columnData will be updated with the column information which identifies the total occurrences of the cell text within whole column. This information is used for generating the 'Group by Chart' for that column.
      • Again, a uniqueness check will be carried out to see if any of the column contains unique records. Rule is - When total number of cell text identified in above step equals the total number of rows in the HTML table, then that column is a unique column. And hence it will be bypassed from the 'Group by Chart'     
    • Preparing datasets for chart 
      • This is done using the private method - prepareDataSets(). Datasets are nothing but the format in which every chart takes input.
      • The content of dataset will be - chart title, X-axis label, Y-axis label, category and count
      • This method will iterate through the metadata records in order to prepare the dataset.
      • Due to filter condition, it is possible that one or more column will have the uniform values. These values will be used to identify the extended grouping of data.
      • This method will dynamically prepare the chart title based on set of all uniform values identified.  
    • Populating the chart drop-down box with all possible chart options or showing the error message 
      • This is done using the private method - report().
      • Based on the datasets prepared, this method will populate the 'Possible Charts' drop-down box with list of all available charts. 
      • In case there are no charts possible, reason being all records contains unique values, this method will display the error message stating all records are distinct.
      • User can select appropriate chart from the drop-down to see it in action.
  5. Next and final step is to render the chart based on user selection. The onchange event of the drop-down will call the analyzer.draw() method, with the selected chart option passed as parameter. If the "Operation" and "Numeric Column" are selected, then this method will invoke the private internal method - updateDataSet(operation, numColumn, reportIndex). This method will perform the required calculations and update the dataset that will be used for rendering the chart.    

History

  • 10th January, 2013: Initial post. Version 1.0
    • You can view the details of version 1.0 from the revisions link at left side of this article.  
  • 15th January, 2013: Version 2.0 released 
    • Changes are -
      • Added support for selecting the mathematical operations to generate chart
      • Removed JSChart library  
      • By default, the charts will be displayed using Open Flash Chart library. 
  • 14th February, 2013: Added demo site link 

Note 

Please rate this article if you liked this. Also, let me know your feedback for the same. 

If you have some suggestions about the features to be included in this utility, please do let me know so that everyone can use it.  

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

About the Author

Niral Soni
Business Analyst Tata Consultancy Services Ltd.
United Kingdom United Kingdom
I started my career as Java Developer. But now more inclined towards web designing, JavaScript, HTML and CSS.I have good level of expertise on Java, Oracle and JavaScript. Designing Generic components is my main expertise.
Follow on   Twitter   Google+

Comments and Discussions

 
GeneralMy vote of 5 PinmemberDixitAnuj16-Jan-13 14:18 
AnswerRe: My vote of 5 [modified] PinmemberNiral Soni17-Jan-13 2:00 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web04 | 2.8.140421.2 | Last Updated 14 Feb 2013
Article Copyright 2013 by Niral Soni
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid