Dashboard with two related datasources and charts.
1,020 views
Skip to first unread message
Onno Benschop
Sep 22, 2011, 7:45:55 PM9/22/11
to Google Visualization API
Before I start coding I'm trying to get my head around how this is
intended to hang together. Links and/or sample code would be a bonus,
but really I'm trying to "get it".
I have a working Dashboard that uses a datasource to draw controls, a
map and a table (and thanks to this forum, you can now click on the
table and highlight the map and vice versa). I started with the
Airport, Country, Region, City example and took my lead from there.
I have another datasource that contains related information that I'd
like to display in the same Dashboard in such a way that the existing
controls also affect the filters of the second datasource and its
charts.
I could make another handleQueryResponse() handler and link the two
with onReady eventListeners, but that seems clunky, given that there
are join() and bind() functions which clearly give you the notion that
you can join two tables and deal with the result.
Alternatively, in my initialization handler I could fire off another
query - to my second datasource, have it be handled by the original
handleQueryResponse() handler and somehow (since this is asynchronous)
join that to the other datasouce, or should this be dealt with in
another way?
How is this expected to be glued together?
intended to hang together. Links and/or sample code would be a bonus,
but really I'm trying to "get it".
I have a working Dashboard that uses a datasource to draw controls, a
map and a table (and thanks to this forum, you can now click on the
table and highlight the map and vice versa). I started with the
Airport, Country, Region, City example and took my lead from there.
I have another datasource that contains related information that I'd
like to display in the same Dashboard in such a way that the existing
controls also affect the filters of the second datasource and its
charts.
I could make another handleQueryResponse() handler and link the two
with onReady eventListeners, but that seems clunky, given that there
are join() and bind() functions which clearly give you the notion that
you can join two tables and deal with the result.
Alternatively, in my initialization handler I could fire off another
query - to my second datasource, have it be handled by the original
handleQueryResponse() handler and somehow (since this is asynchronous)
join that to the other datasouce, or should this be dealt with in
another way?
How is this expected to be glued together?
Riccardo Govoni ☢
Sep 26, 2011, 11:09:15 AM9/26/11
to google-visua...@googlegroups.com
So you want a set of controls on dashboard 1 to drive the contents (filters and charts) of a second dashboard, and the two dashboards are fed with different datatables, right ?
If so, there isn't a pre-cooked way of joining together the two, because of the nuances of 'translating' the filtering criteria defined by the filters in dashboard1 into an equivalent filtering operation over dashboard2, which is however populated by a different datatable that may be structurally different.
Citing your post, the key operation is disambiguate what "related" means in "another datasource that contains related information".
That said, are a couple of ways in which you can accomplish this. For example:
- you populate each dashboard separately, having each dashboard query its own datasource.
- whenever a control in dashboard1 fires a 'statechange' event (indicating it received user interaction), you manually filter the datatable that powers dashboard2 and then call dashboard2.draw(datatable) again, forcing dashboard2 to align itself to the filters' status in dashboard1.
Alternatively:
- you create, in dashboard2, a set of 'hidden' controls that mimic those of dashboard1 that you want to affect the second dashboard, and you wire together controls from the first dashboard to propagate their state to controls of the second.
Have a look at this example: https://gist.github.com/1242360 . I create 2 dashboards. The first contains a 'stringfilter' on the Name column. The second one contains another stringfilter, but on a different column (Fruit) and I keep it hidden via a display:none style. When the user interacts with the former, I propagate the change to the latter via:
google.visualization.events.addListener(control1, 'statechange', function() {control2.setState(control1.getState());control2.draw();});
The end result is that the same filtering criteria the user entered on the first dashboard, is applied to the second as well.
Does this resemble what you're trying to achieve?
-- R.
--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
Onno Benschop
Sep 26, 2011, 7:34:27 PM9/26/11
to Google Visualization API
Hi Riccardo,
That is an interesting implementation that I had not considered and
I'll have a think about that path too. I'm on the way out the door,
but I'll have a look-see at your code when I return, much appreciated.
What I was attempting to do is do all this in one dashboard. I have a
table with events that occur near towns. I have a table with towns. I
am showing a map with the location that the events occur. I am also
showing a table which lists the events. The controls allow you to
filter by event type, town, etc. (You have already helped me make the
table and map select work in another post - thanks!)
I want to add a graph showing all towns and a count of the number of
events happening nearby. If I use the main table, all I'll get is the
towns where there are events currently happening, not the rest of the
towns.
In a relational database I'd join the two tables together, and get the
town and the count, something like this:
SELECT t.town, count(e.*) as near from towns t LEFT JOIN events e ON
t.town=e.town GROUP BY t.town
So, I'd get each town and a count (or NULL for those towns where there
is no event) in a table which I can then graph.
The API indicates that there is a join() method, but I have yet to
determine how I'd go about doing that. How would I create another
datasource (since this is asynchronous) and join that with the current
set-up?
I suspect that the intent is that this could work given the published
methods without resorting to building a separate dashboard.
I should also point out that the towns graph is only one of a few that
I'd like to produce, so I have to keep that in mind when I'm building
this.
PS. I currently cannot publish this, as it's part of a government
department project, but I do intend to make it available within the
next month (since it is expected to be public in about three weeks).
Onno
That is an interesting implementation that I had not considered and
I'll have a think about that path too. I'm on the way out the door,
but I'll have a look-see at your code when I return, much appreciated.
What I was attempting to do is do all this in one dashboard. I have a
table with events that occur near towns. I have a table with towns. I
am showing a map with the location that the events occur. I am also
showing a table which lists the events. The controls allow you to
filter by event type, town, etc. (You have already helped me make the
table and map select work in another post - thanks!)
I want to add a graph showing all towns and a count of the number of
events happening nearby. If I use the main table, all I'll get is the
towns where there are events currently happening, not the rest of the
towns.
In a relational database I'd join the two tables together, and get the
town and the count, something like this:
SELECT t.town, count(e.*) as near from towns t LEFT JOIN events e ON
t.town=e.town GROUP BY t.town
So, I'd get each town and a count (or NULL for those towns where there
is no event) in a table which I can then graph.
The API indicates that there is a join() method, but I have yet to
determine how I'd go about doing that. How would I create another
datasource (since this is asynchronous) and join that with the current
set-up?
I suspect that the intent is that this could work given the published
methods without resorting to building a separate dashboard.
I should also point out that the towns graph is only one of a few that
I'd like to produce, so I have to keep that in mind when I'm building
this.
PS. I currently cannot publish this, as it's part of a government
department project, but I do intend to make it available within the
next month (since it is expected to be public in about three weeks).
Onno
asgallant
Sep 27, 2011, 9:24:28 AM9/27/11
to google-visua...@googlegroups.com
I haven't used the join method, but I think something along the lines of this should work for you:
I don't think there is anything that works like the RLDB count() function, but you could calculate it yourself and input it into the DataTable. You can get count() in the query itself, though, if you are using the API Query language (or code it on your back-end if you're not). It may take some hacking to get the AJAX returns synchronized, though. Perhaps something like this?
/* this joins two DataTable objects
* assumes:
* towns and events are two DataTable objects
* joinType is the type of join to perform (full, inner, left, right)
* keysArray is an array of key column indices to join on, where each element
* is a 2-element array in the form of [townKey, eventKey]
* townCols and eventCols are arrays of column indices to include in the joined table
* key columns are always included in the output
*/
var joined = google.visualization.data.join(towns, events, joinType, keysArray, townCols, eventCols);
* assumes:
* towns and events are two DataTable objects
* joinType is the type of join to perform (full, inner, left, right)
* keysArray is an array of key column indices to join on, where each element
* is a 2-element array in the form of [townKey, eventKey]
* townCols and eventCols are arrays of column indices to include in the joined table
* key columns are always included in the output
*/
var joined = google.visualization.data.join(towns, events, joinType, keysArray, townCols, eventCols);
I don't think there is anything that works like the RLDB count() function, but you could calculate it yourself and input it into the DataTable. You can get count() in the query itself, though, if you are using the API Query language (or code it on your back-end if you're not). It may take some hacking to get the AJAX returns synchronized, though. Perhaps something like this?
/* place this code in the success handler function for your AJAX calls
* (queryResponseHandler if you are using the API queries)
* assumes:
* sync is a boolean variable, in scope for both AJAX success handlers, initially false
* both DataTables are in scope for both success handlers
*/
if (sync) {
// join DataTables and draw your dashboard
}
else {
// set sync to true so the other handler can draw the dashboard when it returns
sync = true;
}
* (queryResponseHandler if you are using the API queries)
* assumes:
* sync is a boolean variable, in scope for both AJAX success handlers, initially false
* both DataTables are in scope for both success handlers
*/
if (sync) {
// join DataTables and draw your dashboard
}
else {
// set sync to true so the other handler can draw the dashboard when it returns
sync = true;
}
Brian Smith
Feb 27, 2012, 9:56:30 AM2/27/12
to google-visua...@googlegroups.com
I'm trying to do something like this where I have multiple google spreadsheet datasources with a "school name" column. I'd like to be able to do what you are doing here, but this method doesn't seem to work as written for that situation. Any ideas on what I could/should try? Thanks for the help!
To post to this group, send email to google-visualization-api@googlegroups.com.
To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.
asgallant
Feb 27, 2012, 11:14:04 AM2/27/12
to google-visua...@googlegroups.com
Keep in mind that the arrays are of column indices, not labels. If 'school name' is the first column in the first table and the second column in the second table, then the keysArray would look like this:
var keysArray = [
[0, 1]
];
If that doesn't solve the problem for you, post your code or a link to your code and I'll take a look.
Brian Smith
Feb 27, 2012, 11:33:56 AM2/27/12
to google-visua...@googlegroups.com
I'm sorry, I wasn't clear that I was trying to post a reply under Riccardo Govoni's post! Thanks for your reply and offer of help asgallant. What I'm trying to do is make a control that will apply a filter to two (or more) dashboards. I tried using the method that Riccardo posted above, but it doesn't seem to work as written because of the fact that I am querying a datasource (google spreadsheets) rather than declaring a data variable inside of the dashboard1 function.
Onno Benschop
Feb 27, 2012, 5:33:23 PM2/27/12
to google-visua...@googlegroups.com
Brian, I never got it to work, but my handy work can be seen here - in the end I combined the data from multiple spreadsheets into one, then ran the dashboard across that, ugly but functional:
--
Onno Benschop
()/)/)() ..ASCII for Onno..
|>>? ..EBCDIC for Onno..
--- -. -. --- ..Morse for Onno..
ITmaze - ABN: 56 178 057 063 - ph: 04 1219 8888 - on...@itmaze.com.au
(Check the Dashboard, the other pages are derivative stats - and the data is now static.)
Tip for young players, Google Spreadsheets will die if you throw 4 million people at a page like this. The caching is all-but non-existent. In the end, I needed to double-cache, a linux web-server got the data from Google, then my display spreadsheet got it from there.
Tip for young players, Google Spreadsheets will die if you throw 4 million people at a page like this. The caching is all-but non-existent. In the end, I needed to double-cache, a linux web-server got the data from Google, then my display spreadsheet got it from there.
If you expect to run this out for a large audience you need to look for an alternative data-source. Fusion Tables was suggested by Google, but during the Google Developer Day in Sydney I was told any data published in that is *public*. Google App Engine is a potential alternative that I never investigated for this solution.
I never said thanks to the people here - we had an event on at the time, so herewith. Thank you to the combined and individual efforts people in this forum supplied to get my solution working.
O
O
--
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/Dv57vTDoN44J.
To post to this group, send email to google-visua...@googlegroups.com.
To unsubscribe from this group, send email to google-visualizati...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
Onno Benschop
()/)/)() ..ASCII for Onno..
|>>? ..EBCDIC for Onno..
--- -. -. --- ..Morse for Onno..
ITmaze - ABN: 56 178 057 063 - ph: 04 1219 8888 - on...@itmaze.com.au
Brian Smith
Feb 28, 2012, 9:52:00 PM2/28/12
to google-visua...@googlegroups.com
Thank you Onno for the update on your work and the link to your final project! Nice work. The dashboard I'm building is just for internal use by our staff. I have about a dozen spreadsheets I'm trying to pull together so I may just have to be satisfied with what I can quickly get running. I have one more idea to get something working that I'll try, unless someone else has come up with a solution to this problem....
On Monday, February 27, 2012 4:33:23 PM UTC-6, Onno Benschop wrote:
On Monday, February 27, 2012 4:33:23 PM UTC-6, Onno Benschop wrote:
Brian, I never got it to work, but my handy work can be seen here - in the end I combined the data from multiple spreadsheets into one, then ran the dashboard across that, ugly but functional:(Check the Dashboard, the other pages are derivative stats - and the data is now static.)
Tip for young players, Google Spreadsheets will die if you throw 4 million people at a page like this. The caching is all-but non-existent. In the end, I needed to double-cache, a linux web-server got the data from Google, then my display spreadsheet got it from there.If you expect to run this out for a large audience you need to look for an alternative data-source. Fusion Tables was suggested by Google, but during the Google Developer Day in Sydney I was told any data published in that is *public*. Google App Engine is a potential alternative that I never investigated for this solution.I never said thanks to the people here - we had an event on at the time, so herewith. Thank you to the combined and individual efforts people in this forum supplied to get my solution working.
O
On 28 February 2012 00:33, Brian Smith <bsm...@cis-ne.org> wrote:
I'm sorry, I wasn't clear that I was trying to post a reply under Riccardo Govoni's post! Thanks for your reply and offer of help asgallant. What I'm trying to do is make a control that will apply a filter to two (or more) dashboards. I tried using the method that Riccardo posted above, but it doesn't seem to work as written because of the fact that I am querying a datasource (google spreadsheets) rather than declaring a data variable inside of the dashboard1 function.
To view this discussion on the web visit https://groups.google.com/d/msg/google-visualization-api/-/Dv57vTDoN44J.
On Monday, February 27, 2012 10:14:04 AM UTC-6, asgallant wrote:Keep in mind that the arrays are of column indices, not labels. If 'school name' is the first column in the first table and the second column in the second table, then the keysArray would look like this:--var keysArray = [[0, 1]];If that doesn't solve the problem for you, post your code or a link to your code and I'll take a look.
You received this message because you are subscribed to the Google Groups "Google Visualization API" group.
To post to this group, send email to google-visualization-api@googlegroups.com.
To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
Tim Kuhn
Jun 11, 2013, 4:31:25 PM6/11/13
to google-visua...@googlegroups.com
This sample is gold and I have plugged it on a production site. Its works great everywhere but in IE8.
In IE8, the statement "control1 = createDashboard1();" generates an error 'Object doesn't support this property or method' which causes prevents the listener from being able to keep the two string filters bound together. I don't have the chops to be able to troubleshoot the error in IE.
Other than that, its a great sample that opens up lots of doors for me.
To unsubscribe from this group, send email to google-visualization-api+unsub...@googlegroups.com.
asgallant
Jun 11, 2013, 4:41:54 PM6/11/13
to google-visua...@googlegroups.com
If you can post the code you are using or a link to the page, I can take a look and see what might be causing the problem in IE8.
Tim Kuhn
Jun 11, 2013, 4:48:50 PM6/11/13
to google-visua...@googlegroups.com
If you pull it straight from Riccardo's sample on GitHub hopefully you will be able to reproduce: https://gist.github.com/battlehorse/1242360
Tim
--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/7bxuFEPjSy0/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
To post to this group, send email to google-visua...@googlegroups.com.
Visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
asgallant
Jun 11, 2013, 7:09:59 PM6/11/13
to google-visua...@googlegroups.com
I'm not entirely sure why IE is complaining, but the error goes away if you use the "var" keyword before "control1" (this is a good idea for all variables, so they don't clutter up the global namespace):
function drawVisualization() {
var control1 = createDashboard1();
var control2 = createDashboard2();
google.visualization.events.addListener(control1, 'statechange', function() {
control2.setState(control1.getState());
control2.draw();
});
}
Tim
To unsubscribe from this group, send email to google-visualization-api+unsubscr...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/google-visualization-api?hl=en.
--
You received this message because you are subscribed to a topic in the Google Groups "Google Visualization API" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/google-visualization-api/7bxuFEPjSy0/unsubscribe?hl=en.
To unsubscribe from this group and all its topics, send an email to google-visualization-api+unsub...@googlegroups.com.
Tim Kuhn
Jun 11, 2013, 7:24:31 PM6/11/13
to google-visualization-api
Brilliant. You are totally right. That solved it and the dashboard now works in IE8 as well as any other browser.
To unsubscribe from this group and all its topics, send an email to google-visualizati...@googlegroups.com.
Reply all
Reply to author
Forward
0 new messages