Solved

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

Posted on 2013-01-08
7
861 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
Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

 
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

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Avoid defining the variables in the global scope; trying to define them in a local function scope. Because:   • Look-up is performed every time a variable is accessed.   • Variables are resolved backwards from most specific to least specific scope…
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…

776 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