?
Solved

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

Posted on 2013-01-08
7
Medium Priority
?
893 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 54

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 54

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 54

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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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 54

Accepted Solution

by:
Scott Fell,  EE MVE earned 1000 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 54

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: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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

In my daily work (mainly using ASP.net), I need to write a lot of JavaScript code. One of the most repetitive tasks I do are the jQuery Ajax calls. You know: (CODE) I don't know if for you it's the same, but for me is soooo tedious to write the …
Having worked on larger scale sites, we found out that you are bound to look at more scalable solutions to integrating widgets, code snippets or complete applications and mesh them into functional sites, in any given composition. To share some of…
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…
Suggested Courses

592 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