Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2013-01-08
7
Medium Priority
?
888 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction HTML checkboxes provide the perfect way for a web developer to receive client input when the client's options might be none, one or many.  But the PHP code for processing the checkboxes can be confusing at first.  What if a checkbox is…
Boost your ability to deliver ambitious and competitive web apps by choosing the right JavaScript framework to best suit your project’s needs.
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

824 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