Solved

Google Charts API: need to fill in missing data for array

Posted on 2013-01-08
7
864 Views
Last Modified: 2013-01-09
GOod afternoon, Experts!
I have a classic ASP page and I am extracting data from SQL server and trying to build a Google chart.  The problem is that I am missing data in my table.

Google Charts is expecting a data table like this:

['Date'                        'BR'      'MW'       'SM']
[12/29/2012       10      0      0],
[12/31/2012       0      0      3],
[1/2/2013                   43      312      15],
[1/3/2013         70      171      19],
[1/4/2013                        21      90      17],
[1/5/2013            5      2      0],
[1/7/2013        22      133      0],
[1/8/2013                        28      177      6],


The problem is that my data is coming out of the database like this:

2012-12-29, BR, 10
2012-12-31, SM, 3
2013-01-02, BR, 43
2013-01-02, MW, 312
2013-01-02, SM, 15
2013-01-03, BR, 70
2013-01-03, MW, 171
2013-01-03, SM, 19
2013-01-04, BR, 21
2013-01-04, MW, 90
2013-01-04, SM, 17
2013-01-05, BR, 5
2013-01-05, MW, 2
2013-01-07, BR, 22
2013-01-07, MW, 133
2013-01-08, BR, 28
2013-01-08, MW, 177
2013-01-08, SM, 6


How can I go through the data and make it conform to the format Google's API needs?
0
Comment
Question by:dimmergeek
  • 5
  • 2
7 Comments
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38758629
Actually, google is expecting a json call.  I have used this with success to push data to json via asp  http://code.google.com/p/aspjson/
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38758654
If you look at their hello charts example

 // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Topping');
        data.addColumn('number', 'Slices');
        data.addRows([
          ['Mushrooms', 3],
          ['Onions', 1],
          ['Olives', 1],
          ['Zucchini', 1],
          ['Pepperoni', 2]
        ]);

Open in new window


and you have a simple data structure you could do this

 // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Topping');
        data.addColumn('number', 'Slices');
        data.addRows([
     <% 
if not rs.eof or not rs.bof then
do until rs.eof

          ['<%=rs("item")%>', <%=rs("count")%>]<%' place starting asp tag here for comma
          if not rs.eof then response.write "," end if 'add comma if more data is avail
rs.movenext
loop
end if
%>
         
        ]);

Open in new window

0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38758691
In order to get your data to display you need to make a new sql statement

Here I am using SUM but substitute for COUNT if you need counts instead

SELECT     TOP (100) PERCENT Date, SUM(BR) AS BR, SUM(MW) AS MW, SUM(SM) as SM
FROM         dbo.tbl_MyData
GROUP BY Date
ORDER BY Date

Now your output will look like
(date, BR, MW, SM)
2012-12-29, 4,2,5
2012-12-29, 3,2,8

and my example above would be

 // Create the data table.
        var data = new google.visualization.DataTable();
        data.addColumn('string', 'Date');
        data.addColumn('number', 'BR');
        data.addColumn('number', 'MW');
        data.addColumn('number', 'SM');
        data.addRows([
     <% 
if not rs.eof or not rs.bof then
do until rs.eof

          ['<%=rs("Date")%>', <%=rs("BR")%>, <%=rs("MW")%>, <%=rs("SM")%> ]<%' place starting asp tag here for comma
          if not rs.eof then response.write "," end if 'add comma if more data is avail
rs.movenext
loop
end if
%>
         
        ]);

Open in new window

0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 7

Author Comment

by:dimmergeek
ID: 38759794
I would love to be able to change my query to get the data; but it may not be as simple as you've suggested.
My schema is as follows:

DB schema
As you can see, BR, MW and SM are all values which can be placed into the 'Facility' column.
0
 
LVL 52

Accepted Solution

by:
Scott Fell,  EE MVE earned 250 total points
ID: 38759895
See if this works out. I tried it on one of my databases and it seemed to work out fine.  Basically we are making a new column for each of BR, MW and SM and converting the column to either a 1 or 0 if the field contains the code ('BR' for column BR).  Then summing up all the 1's.


SELECT     TOP (100) PERCENT Date, SUM(CASE WHEN isnull(Facility, 0) = 'BR' THEN 1 ELSE 0 END) AS [BR], SUM(CASE WHEN isnull(Facility, 0) = 'MW' THEN 1 ELSE 0 END) AS [MW], SUM(CASE WHEN isnull(Facility, 0) = 'SM' THEN 1 ELSE 0 END) AS [SM]
FROM         dbo.tbl_MyData
GROUP BY Date
ORDER BY Date
0
 
LVL 7

Author Comment

by:dimmergeek
ID: 38760137
PERFECT!

THANK YOU!

Here is the final query that worked:

SELECT TOP (100) PERCENT CONVERT(DATE, [Date], 101) AS [Unique Date], SUM(CASE WHEN isnull(Facility, 0) = 'BR' THEN 1 ELSE 0 END) AS [BR], SUM(CASE WHEN isnull(Facility, 0) = 'MW' THEN 1 ELSE 0 END) AS [MW], SUM(CASE WHEN isnull(Facility, 0) = 'SM' THEN 1 ELSE 0 END) AS [SM]
FROM [Purchasing Table]
WHERE CONVERT(DATE, [Date], 101) between '12/31/2012' AND '01/09/2013'
GROUP BY CONVERT(DATE, [Date], 101)
ORDER BY CONVERT(DATE, [Date], 101) ASC;
-- ------------------------------------------------------------------------------------------------------------------------------
Unique Date      BR      MW      SM
2012-12-31      0      0      3
2013-01-02      43      312      15
2013-01-03      70      171      19
2013-01-04      21      90      17
2013-01-05      5      2      0
2013-01-07      22      133      0
2013-01-08      29      178      6
2013-01-09      13      146      3
0
 
LVL 52

Expert Comment

by:Scott Fell, EE MVE
ID: 38760237
Great.  In the past I always struggle to put together a cross tab but this way was much easier.   Thank's for the question. I learned something too.
0

Featured Post

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article will give core knowledge of JavaScript and will head in to your first JavaScript program. I am Durvesh Naik and I am here to deal with this series of JavaScript. I will teach you JavaScript in part wise , as its quite boring to read big…
Today, the web development industry is booming, and many people consider it to be their vocation. The question you may be asking yourself is – how do I become a web developer?
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question