Solved

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

Posted on 2013-01-08
7
878 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 2
7 Comments
 
LVL 53

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 53

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 53

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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 53

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 53

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

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

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

This article discusses the difference between strict equality operator and equality operator in JavaScript. The Need: Because JavaScript performs an implicit type conversion when performing comparisons, we have to take this into account when wri…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
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

623 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