Chart at end of survey

Last Updated on Thursday, 29 November 2012 07:42 Written by Admin Thursday, 1 January 2009 09:36

Sometimes you will be asked to show a graph at the end of a survey showing the count or percents for a particular question. I guess this is a bit like a web poll, you are asked to vote on something and then after you have placed your vote you see a graph of the results. This is easily done in mrInterview and this article shows you how you can achieve this.

First of all we need two questions. In this example we will use a multiple response question to collect the data and an info question to show the results. Add this code to the metadata section of your MDD.

Metadata(en-AU, Question, Label)
DidDo "What did you do on the weekend"
    categorical [1..]
    {
        WashCar "Wash the car",
        DoWashing_ "Do the washing",
        WalkDog "Walk the dog",
        KidsPark "Take the kids to the park",
        BBQ "Have a BBQ",
        Beach "Went to the beach",
        PlayerSport "Player sport",
        - "Other" other(use Other_Other )
    }
    helperfields (
        Other_Other ""
        text [1..];
    );
    InfoResults "{REPLACE}"
    info;
End Metadata

So now we have our questions we need to ask them , so add this to your routing section of the mdd.

Dim sResults,sLabel

DidDo.Ask()

sResults = “”
Dim oConnection, oRecordset
SET oConnection = CreateObject(“ADODB.Connection”)
SET oRecordset = CreateObject(“ADODB.Recordset”)
oConnection.ConnectionString = “YourConnectionString”
oConnection.Open()
if oConnection.State = 1 Then
oRecordset.Open(“SELECT groupby.col[0] AS Description,” + _
“BASE(DidDo) AS ‘Base’ FROM vdata Group By DidDo on ” + _
“DidDo.DefinedCategories()”,oConnection,3,1)

do while oRecordset.EOF = false

sLabel = DidDo.Categories[CCategorical( _
CTEXT(oRecordset[0].value))].Label
sResults = sResults + sLabel + “/” + _
CTEXT(oRecordset[1].value ) + “

oRecordset.MoveNext()
loop

oRecordset.Close()
oConnection.Close()
End if

InfoResults.Label.Inserts[“REPLACE”] = sResults
InfoResults.Show()

You will need to provide YourConnectionString and once everything is working, activate the survey to the server and run the survey, what we will see is the question, 

Question

Question

and once you have clicked next past this you should see a list of numbers like the ones below. 

Output

Output

So what have we just done, obviously we have created a question and answered it. If we look at the routing section you can see that we have declared some variable and created some objects that allow us to connect to a database. The first object is “oConnection” and this is used to connect to the database. The second is the “oRecordset” object is the object that will store the actual data.

The next line of code is where we actually connect to the database. To get this to work in your case you will need to create a connection string to the database that has been created for this MDD file. To do this you can use the connection string wizard in mrStudio. It should be noted that this does assume that you have access to get to your database. For help creating your connection see these articles.

Q. How do I check my connection to my mrInterview SQL database.
Q. How do I create a connection string with the mrStudio connection wizard.
Q. How to use DMQuery to find out your connection string to your survey data.

Next we check to make sure that we have actually connected to the database, if the connection state is 1 then all is ok anything else means that the connection failed. So once we have connected to the database we need to get the data. In this example we will get the data in the form of a table. The select statement that we are using “SELECT groupby.col[0] AS Description,BASE(DidDo) AS ‘Base’ FROM vdata Group By DidDo on DidDo.DefinedCategories()” will return the record set in the following format.

Data in DMQuery

Data in DMQuery

so now we have to take this a stage further, what we want to try and do is produce a chart , so in this example we will produce 2 simple types of charts, but basically you , if  you know how to use some sort of charting object, could take it from here, we will provide a google chart api example also at the end of this article. Anyway , we will look at some HTML charting, so the first thing we need to do is to get a base or a total, we are going to loop the data twice, once to get the total and once to draw the chart. Copy this code and lets see what it produces.

if oConnection.State = 1 Then 
oRecordset.Open("SELECT groupby.col[0] AS Description," + _
               "BASE(DidDo) AS 'Base' FROM vdata Group By " + _
               "DidDo on DidDo.DefinedCategories()",oConnection,3,1)
           
do while oRecordset.EOF = false
iTot = iTot + oRecordset[1].value
oRecordset.MoveNext()
loop
          
oRecordset.MoveFirst()
          
do while oRecordset.EOF = false
sLabel = DidDo.Categories[CCategorical( _
CTEXT(oRecordset[0].value))].Label
sResults = sResults + "<table border=""1"" >"
sResults = sResults + "<tr><td>"
sResults = sResults + sLabel + "/" + _
CTEXT(oRecordset[1].value ) + "/" + _
CTEXT( _
round(( cdouble(oRecordset[1].value) / itot ) * 100,2)) + _
"%<br />"
sResults = sResults + "</td>"
sResults = sResults + "</tr>"
      sResults = sResults + "</table>"
     oRecordset.MoveNext()
loop

sResults = sResults + "TOTAL/" + CTEXT(iTot)
oRecordset.Close()
oConnection.Close()

End if

What we should see is as follows,

HTML Stage 1

HTML Stage 1

 

We can see that we now have the Label text for each response, followed by the total count for each possible response follwed by the percentage of the total for that response. All of this information is stored in an html table with a border of size one. Now from here we can begin to build our charts. In this article we are going to produce some HTML charts, nothing flash, but depending on your skills you could take this to the next level and start to use some of the charting objects that available. So Copy this code into your script, and lets see what it produces.

Dim sResults,sLabel
Dim iTot,iPerc

DidDo.Ask()

sResults = ""
Dim oConnection, oRecordset
Dim iCat, oArrColor[10]

oArrColor[0] = "Red"
oArrColor[1] = "Blue"
oArrColor[2] = "Pink"
oArrColor[3] = "Green"
oArrColor[4] = "Black"
oArrColor[5] = "#003300"
oArrColor[6] = "#ff3300"
oArrColor[7] = "#AB33ff"

SET oConnection = CreateObject("ADODB.Connection")
SET oRecordset = CreateObject("ADODB.Recordset")
      oConnection.ConnectionString = "YourConnectionString"
      oConnection.Open()
      
if oConnection.State = 1 Then 
         oRecordset.Open("SELECT groupby.col[0] AS Description," + _
              "BASE(DidDo) AS 'Base' FROM vdata Group By " + _
              "DidDo on DidDo.DefinedCategories()",oConnection,3,1)
            
         do while oRecordset.EOF = false
            iTot = iTot + oRecordset[1].value
           oRecordset.MoveNext()
         loop
            
         oRecordset.MoveFirst()
         sResults = sResults + "<table border=""0"" width=""100%"">"
         sResults = sResults + "<tr>"  
         iCat = 0
         do while oRecordset.EOF = false
           sLabel = DidDo.Categories[CCategorical( _
                           CTEXT(oRecordset[0].value))].Label
           iPerc = round(( _
               cdouble(oRecordset[1].value ) / itot ) * 100,2)
           sResults = sResults + "<td bgcolor=""" + oArrColor[iCat]+ _
                  """ width="""+ CTEXT(iPerc)+ "%"">"
           sResults = sResults + "<font color=""white"">" + sLabel + _ 
                  "<br />" + CTEXT(oRecordset[1].value) + "/" + _
                  CTEXT(iPerc) + "%</font>"
           sResults = sResults + "</td>"
           oRecordset.MoveNext()
          iCat = iCat + 1
         loop
         sResults = sResults + "</tr>"
         sResults = sResults + "</table>"
      
         sResults = sResults + "TOTAL/" + CTEXT(iTot)
        oRecordset.Close()
        oConnection.Close()
           
      End if

InfoResults.Label.Inserts["REPLACE"] = sResults
InfoResults.Show()

So with the above code we get the following output

HTML Stage 2

HTML Stage 2

The idea behind the above code is to create an html table, with 1 row and 8 cells in it , each cell is a percentage of the base and has a different background color. what we do is to loop each value in the recordset and build up the 1 row table , adding the text and values as we go. The next example uses the same principle but makes the graph a bit more presentable for this type of results.


if oConnection.State = 1 Then 
   oRecordset.Open("SELECT groupby.col[0] AS Description," + _
             "BASE(DidDo) AS 'Base' FROM vdata Group By DidDo on " + _
             "DidDo.DefinedCategories()",oConnection,3,1)
            
   do while oRecordset.EOF = false
      iTot = iTot + oRecordset[1].value
      oRecordset.MoveNext()
   loop
            
   oRecordset.MoveFirst()
    
   iCat = 0
   do while oRecordset.EOF = false
      sLabel = DidDo.Categories[ _
               CCategorical(CTEXT(oRecordset[0].value))].Label
      iPerc = round(((cdouble(oRecordset[1].value) ) / itot ) * 100,2)
      sResults = sResults + "<table border=""1"" width=""100%"">"
      sResults = sResults + "<tr>"
      sResults = sResults + "<td width=""200px"">" + sLabel + "/" + _
                 CTEXT(oRecordset[1].value ) + "</td>"
      sResults = sResults + "<td bgcolor=""" + oArrColor[iCat]+ _
                 """ width=""" + CTEXT(iPerc)+ "%"">"
      sResults = sResults + "<font color=""white"">" + _
                 CTEXT(iPerc) + ""
      sResults = sResults + "%</font></td><td></td>"
      sResults = sResults + "</tr>"
      sResults = sResults + "</table>"
      oRecordset.MoveNext()
      iCat = iCat + 1
   loop
            
   sResults = sResults + "TOTAL/" + CTEXT(iTot)
   oRecordset.Close()
   oConnection.Close()

End if

So with the above code we get the following output 

HTML Stage 3

HTML Stage 3

This example has used the same principles as before we are using the background of the cell and the width of the cell to show the bar of the chart, but you could quite easily give the cell a background image and that way make the chart more graphical.

I am not sure if you are aware, but google, have developed an Application Programable Interface ( API ) for charting that is free to use, so you could also use this to display your results. Click here for more information. You will also need to read this armed with all this information you can produce something like this ,

Google API

Google API

Whith this very simple code at the end of your survey.

if oConnection.State = 1 Then
   oRecordset.Open("SELECT groupby.col[0] AS Description," + _
               "BASE(DidDo) AS 'Base' FROM vdata Group By " + _
               "DidDo on DidDo.DefinedCategories()",oConnection,3,1)
     sLink = "<a href="http://chart.apis.google.com/chart?&cht=p&chs=500x200&chd">http://chart.apis.google.com/chart?&cht=p&chs=500x200&chd</a>=">
     sLink = sLink + "t:[x-Data-x]&chl=[x-Labels-x]"
     sLabel = ""
     sResults = ""
     do while oRecordset.EOF = false
          sLabel = sLabel + _
               DidDo.Categories[CCategorical(CTEXT(oRecordset[0].value))].Label + "|"
          sResults= sResults + CTEXT(oRecordset[1].value) + ","
          oRecordset.MoveNext()
     loop
     sLink = replace(sLink,"[x-Data-x]",left(sResults,len(sResults)-1))
     sLink = replace(sLink,"[x-Labels-x]",left(sLabel,len(sLabel)-1))
     oRecordset.Close()
     oConnection.Close()
End if
IOM.Texts.EndOfInterview = _
   "<meta http-equiv=""REFRESH"" content=""0; URL=" + sLink + """/>"

Happy Charting !!!

VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)