The other day I posted about a Google Charts class I created to make it easier to put a Google Chart in your Lotus Notes/Domino application. Well, this SnTT will show you how you can incorporate this class and display some pretty charts in the Lotus Notes client. I've done other articles about using charts in your Lotus Notes applications, but I think this is probably the easiest and most elegant way of showing a chart. But, let's get to it shall we...

OK, we'll keep this simple so this article will fit within the 65k limit of lookups and such. But we'll need the following:

• A form to display the chart on inside a Microsoft Web Browser control. Actually this could probably be a page without any ill effects
• A form to store our HTML in
• Some views properly sorted to get our data from
• A view to store our HTML form in sorted by UNID
• About an hour to put all of this together
• A server to place this db on, this does not work locally!

Now, we'll start with our first form and call it GoogleCharts. We need to put a Computed field called SaveOptions and give it a value of "0". We then place a Microsoft Web Browser control on the form with the properties displayed below:


The "SizeBelowFields" property will make the browser take up the entire window. I do this because I usually open these types of things within a frame and I want it to take up the whole frame so it looks nice. So, this is all that's needed for this form.

We now need another form which we'll call HTML. This form will contain 1 field named GoogleHTML. This should be hidden from web browsers. Now add below that field (ensure no hide-when is enabled) a computed text item with a value of "GoogleHTML" and mark it as Pass-Thru HTML and that's it for this form.

OK, let's create our view that will hold the HTML documents. This view should have a selection criteria of "SELECT Form="HTML"" and have one column with a formula of "@Text(@DocumentUniqueID)" and sort it ascending.

So our framework is there, we just need a view that we will get the data from that we want to use to populate our chart(s). Since we want to keep this simple we'll just create 2 views, one for categories (luCategories) and one for data (luData). The categories view will contain 5 documents so our chart isn't too cluttered and then the (luData) view will contain the data that we will get the raw numbers from.

Keep reading for the rest of this...

Now the fun begins, we'll need to put some code in the PostOpen event of the GoogleCharts form. Be sure to change the URL to point to your server and wherever you placed the demo database.

Sub Postopen(Source As Notesuidocument)
	Dim w As New NotesUIWorkspace		
	Dim tmpDoc As NotesDocument	
	Dim HTML As String
	Dim browser As Variant
	Dim luUNID As String, url As String
	
	Set db = w.CurrentDatabase.Database
		
	HTML = |<center><h1>Google Dashboard</h1></center><table width="100%"><tr>|
	
	HTML = HTML + |<td><img src="| + getChart("Totals") + |" alt="Goolge Chart">|
	HTML = HTML + |</br><center>Totals By Company</center></td>|
	HTML = HTML + |</tr></table>|	
	
	Set tmpDoc = New NotesDocument(db)
	tmpDoc.Form = "HTML"
	tmpDoc.googleHTML = HTML
	Call tmpDoc.Save(True,False)
	luUNID = tmpDoc.UniversalID
	
	Set browser = Source.GetObject("Browser")
	url = "http://www.yourdomain.com/thisdb.nsf/HTML/" + luUNID + "?OpenDocument"
	browser.Navigate(url)
End Sub

And here's the function that gets called:

Function getChart(dataSel As String) As String
	Dim luDataView As NotesView
	Dim luCatView As NotesView
	Dim doc As NotesDocument
	Dim luDocCol As NotesDocumentCollection
	Dim gc As GoogleChart
	Dim i As Integer
	Dim gcDataList List As String
	Set gc = New GoogleChart
	Set luCatView = db.GetView("(luGoogleCategoryName)")
	Set luDataView = db.GetView("(luGoogleTotals)")
	For i = 1 To 10
		Set doc = luCatView.GetNthDocument(i)
		Set luDocCol = luDataView.GetAllDocumentsByKey(doc.coName(0),True)
		If luDocCol.Count > 0 Then
			gcDataList(doc.coName(0)) = Cstr(luDocCol.Count)
		End If
	Next
	gc.ChartData = gcDataList
	gc.ChartSize = "450x150"
	gc.ChartType = "p3"
	getChart = gc.ChartURL
End Function

I made a big assumption on how your views are formatted and how many category items you might want to get. I guess the best thing would be to use the EntryCount property of the category view, but you should get the idea of how all of this works, it's pretty straight forward.

• First, we get the NotesDatabase and then start building our html.
• The getChart function just loops through each category and then gets all the corresponding category names from the data view and records the count from a document collection gotten from the data view. This gets recorded in a list formatted like "gcDataList("categoryName") = 20". Of course you can get as complicated or as simple as you like, but we must spit out a list with the listTag being a lable which will show up on the chart and the number being the number that corresponds, in this case, to the piece of the pie for this category.
• We then pass this list to the GoogleChart ChartData property, which gets used to build the URL.
• We then use the GoogleChart ChartSize property to set the size of the chart. The width should be 2.5 times larger than the height so that the labels will fit properly.
• Then, we define the chart type we want to return, in this case we're using a 3d pie chart which corresponds to "p3"
• And then we get the url using the GoogleChart property ChartURL.
• The url gets returned to the PostOpen event that then gets put into an img tag.
• Once all of this is done, we populate the GoogleHTML field in the HTML form, save it and then navigate our browser to the HTML document we just created.

If all of this is clear as mud, which it just might be, I've included a sample database so you can see all of this in action. You can get the sample database over there on the left in the downloads section. The only thing you should need to change is the url to your server in the getChart function. Hopefully you'll get some mileage out of this handy little class and demo.