How To: Insert Multiple NetSuite Aggregate Saved Searches to Google Charts

This article is relevant if you seek to produce charts or other graphical representations of your data in NetSuite and you prefer not to purchase third party charting tools.

Background

Because of the power of our Content Renderer Engine (CRE) which has solved the challenge of multiple joined NetSuite saved searches, including aggregates, we now have the possibility to bring information together that can be represented visually. NetSuite’s built-in graphical elements are good; yet they work in a particular manner and if you want to have more control, you have to look elsewhere.

In contrast, Google offers a Charts API which is free to use and very flexible. It is driven by basic JavaScript and it gives you great results. The Content Renderer Engine (CRE) allows us to easily use a NetSuite style Advanced HTML template to generate the chart.

CRE Profile with NetSuite Aggregate Saved Search to Generate Google Chart HTML

The key to this solution is that we can now take as many saved searches as we want, including aggregates that contain many formula fields, hand it to a template engine and output HTML. The resulting document contains basic JavaScript to automatically draw the chart once the HTML page is rendered.

With HTML output, it is straight forward to output this in a NetSuite Dashboard Portlet, as an inline HTML field on a form, in a SuiteLet, or in other creative ways.

The template is below. We have two saved searches driving this chart:

  1. margin: A summary of all income, cost and margin produced for the year by a professional.
  2. weekly: A summary of the last three weeks of billable work in contrast to a target.

In this case, we spin through the saved search results to generate a flattened array of information to feed into Google’s Chart API to produce a nice Combo Chart. Note, we are using the TrimPath scripting engine versus NetSuite’s FreeMarker due to limitations of formula fields imposed by their implementation. The TrimPath notation is very close to FreeMarker’s.

<?xml version="1.0"?><!DOCTYPE pdf PUBLIC "-//big.faceless.org//report" "report-1.1.dtd">
<html>
	<head>
		<script type='text/javascript' src='https://www.gstatic.com/charts/loader.js'></script>
		<script type='text/javascript'>
			google.charts.load('current', {'packages':['corechart']});
			google.charts.setOnLoadCallback(drawChart);
			//use the template to calculate the summary of all income, margin and costs
			//due to NetSuite's FreeMarker limitations, use the TrimPath template engine as an option set in CRE
			{var c = 0}
			{var m = 0}
			{var i = 0}
			{var p = 0}
			{for s in margin}
				{eval}
					c = parseFloat(c) + parseFloat(s.columns.Cost.value)
					m = parseFloat(m) + parseFloat(s.columns.Margin.value)
					i = parseFloat(m) + parseFloat(c)
				{/eval}
			{/for}
			{eval}
				p = m / i * 100;
				p = p.toFixed(1) + '%';
			{/eval}

			function drawChart() {
				//find related items in 3 week average by connecting up data
				var data = google.visualization.arrayToDataTable([
					['Resource', 'Cost', 'Margin', 'Weekly Target', '3 Week Trailing Average']
					{for r in margin}
						,[ '${r.columns.Item.value}', ${r.columns.Cost.value}, ${r.columns.Margin.value}
						{var weekval = false}
						{for w in weekly}
							{if r.columns.item.internalid == w.columns.item}
								, ${w.columns.Target.value}, ${w.columns._3WeeksAvg.value}
								{var weekval = true}
							{/if}
						{/for}
						{if weekval == false}
							, 0, 0
						{/if}

						]

					{/for}
				]);

				var options = {
					title: 'Margin by Resources - Income: $' +  formatNumber(${i}) + '; Cost: $' + formatNumber(${c}) + '; Margin: $'+  formatNumber(${m}) + ' @ ${p}',
					vAxes: [{title: '$Billable as of: ' + new Date().toLocaleDateString()}, {title: 'Billable Hours', maxValue: 50} ],
					seriesType: 'bars',
					isStacked: true,
					series: [{type:'bars'},{type:'bars'},{type:'line', targetAxisIndex: 1, lineWidth:5}, {type:'line', targetAxisIndex: 1, lineWidth:5}]
				};

				var chart = new google.visualization.ComboChart(document.getElementById('chart_div'));
				chart.draw(data, options);
			}
			function formatNumber (num) {
				return num.toString().replace(/(\d)(?=(\d{3})+(?!\d))/g, "$1,")
			}
		</script>
	</head>
	<body>
		<div id='chart_div' style='width: 100%; height: 600px;'></div>
	</body>
</html>

Take Control and Present Data Visually

Once we solved the challenge of crafting multiple NetSuite linked saved searches which can output to a template engine, we then opened a world of possibility to overcome yesterday’s limitations without needing to break out SuiteScript to produce one-off programs to get the results we wanted. This allows a less technical person, one with good HTML skills, to be productive. We give away our CRE tool to NetSuite end users who have real-world problems to solve. If you have a challenge that you believe CRE could solve, let’s have a conversation.

Be Sociable, Share!

Marty Zigman

Holding all three official certifications, Marty is Southern California's NetSuite expert and leads a team of senior professionals at Prolecto Resources, Inc. He is a former Deloitte & Touche CPA and has held CTO roles. For over 25 years, Marty has produced leadership in ERP, CRM and eCommerce business systems. Contact Marty to setup a conversation.

More Posts - Website - Twitter - Facebook - LinkedIn - Google Plus - YouTube

| Tags: , , | Category: NetSuite, Reporting, Technical | 3 Comments

2 Comments

  1. Bharath P
    Posted June 19, 2016 at 10:58 pm | Permalink

    Hi Marty

    Thank you for writing the article on integration of Charts in Advanced PDF.

    I would like to know how you integrated the Trimpath engine into Advanced PDF?

  2. Posted June 26, 2016 at 8:38 pm | Permalink

    Hello,

    We have created a tool (CRE: Content Renderer Engine) that allows us to use different Template engines to drive content generation. In the case above, because NetSuite’s implementation of the FreeMarker template engine does not know how to work with Aggregate Search column names, we solve it by using the TrimPath engine. This give us full control over the reaching the data from the search. See this article:

    http://blog.prolecto.com/2015/06/01/supercharge-netsuite-advanced-pdfhtml-templates/

    Here is an older article that discusses Trimpath and how it can be used in NetSuite. We wrote it before we built our CRE tool:

    http://blog.prolecto.com/2014/01/04/framework-for-generating-custom-netsuite-pdf-files/

    Marty

One Trackback

  1. […] How To: Insert Multiple NetSuite Aggregate Saved Searches to Google Charts […]

Post a Comment

Your email is never published nor shared. Required fields are marked *

*
*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>