Follow @dporton

JustCode : Delete cases from the output file of a dms script

This script shows you how to delete a case in the OnNextCase Event in the output data file.

' ****************************************
' Designed by : Smarter Dimensions
' Last Updated : 4th August 2009
' Delete cases from the output file of a dms script.
' ****************************************
InputDatasource(Input, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrDataFileDsc; _
        Location=C:tempshort_drinks.ddf; _
        Initial Catalog=C:\temp\short_drinks.mdd"
    SelectQuery = "SELECT * FROM VDATA"
End InputDatasource

OutputDatasource(Output, "")
    ConnectionString = "Provider=mrOleDB.Provider.2; _
        Data Source=mrSAVDsc; _
        Location=C:\temp\Output.sav; _
        MR Init MDM DataSource Use=2; _
        MR Init Overwrite=1"
       MetaDataOutputName = "C:\temp\Output.MDD"
End OutputDatasource

Logging(MyLog, "")
    Group = "DMA"
    Alias = "datamgmt"
    FileSize = 500
    Path = "c:\temp"
End Logging

Event(OnNextCase, "")

    If Respondent.Serial > 10 Then
        dmgrLog.LogError_2(Ctext(Respondent.Serial) + " - Case Deleted")
    End If

End Event

JustCode : Delete Empty columns of data

This mrs script shows you how you can use mrs files to make and run a dms file that will strip out any columns that do not have any data in.

Step 1: Open the VQ file and make an MDD FILE.

Dim mrDSCs, mrMdsc, oDoc,sVQ

sVQ = "C:\temp\FILE"
    Set mrMdsc = createobject("mrdscreg.components")
    Set mrMDSC = mrMdsc["mrScDsc"]

    Set oDoc = mrMdsc.Metadata.Open(sVQ + ".vq")
        oDoc.Save(sVQ + ".mdd")

Set oDoc = Null

Step 2: Take the MDD file and loop the variable instances

Dim oMDM, oVar
Dim sSQL,oConnection,oRecordset

' Create the MDM object
Set oMDM = CreateObject("MDM.Document")
    oMDM.Open("C:\temp\FILE.mdd", , 1)

    For Each oVar in oMDM.Variables
        'if oVar.IsSystem = False then
            debug.Log("Question Name : " + oVar.FullName)

            ' Start code to check if the field has data in it.
            Set oConnection = CreateObject("ADODB.Connection")
                oConnection.ConnectionString = "Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.VQ;Initial Catalog=C:\temp\FILE.mdd"

            If ( oConnection.State = 1 ) Then
                Set oRecordset = CreateObject("ADODB.Recordset")
                    oRecordset.Open("SELECT count(respondent.serial) FROM VDATA WHERE " + oVar.FullName + " is not null" ,oConnection,3,1)
                    If ( oRecordset.EOF = true and oRecordset.BOF = true ) Then
                        sSQL = sSQL
                        sSQL = sSQL + oVar.FullName + ","
                    End if
                Set oRecordset = Null

            End If

            Set oConnection = Null
    ' end if

sSQL = Left(sSQL,len(sSQL)-1)

Step 3: Create the dms file with the select statement in it.

Dim oFSO, oFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set oFile = oFSO.CreateTextFile("C:\temp\FILE.dms", True)

oFile.WriteLine("InputDatasource(Input, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrScDSC;Location=C:\temp\FILE.vq;Initial Catalog=C:\temp\FILE.mdd"" ")
oFile.WriteLine("    SelectQuery = ""SELECT " + sSQL + " FROM VDATA""")
oFile.WriteLine("End InputDatasource")
oFile.WriteLine("OutputDatasource(Output, """")")
oFile.WriteLine("    ConnectionString = ""Provider=mrOleDB.Provider.2;Data Source=mrSavDsc;Location=C:\temp\Final.sav""")
oFile.WriteLine("    MetaDataOutputName = ""C:\temp\Final.mdd""")
oFile.WriteLine("End OutputDatasource")


Set oFSO = Null

Step 4: Run the file

Dim oDMOMJob

Set oDMOMJob = CreateObject("DMOM.Job")

oDMOMJob.Load("C:\temp\FILE.dms", null)


Creating a TOM table within an interview

Recently the following question was asked in LinkedIn :

KG: Some years ago I saw an interview script that produced a TOM table and presented the information to the respondent. You might use this to show poll results when a respondent has answered a question. For the life of me I can not find that script. Does anyone have an example of this that I can use?

and it got the following response from

CB : I haven’t found the example, but here’s code to connect to a database and pull in the results. You’d need to adjust the code inside the If but I’m sure you could take that result and display it:

Dim rsquery, sSQL, oConnection, oRecordSet
Set oConnection = CreateObject("Adodb.connection")
Set oRecordset = CreateObject("ADODB.Recordset")

    oConnection.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Initial Catalog=MyDatabase;Data Source=MyServer"
    sSQL = "Select * FROM [MyDatabase].[dbo].[MyTable]"

    Set rsQuery=oConnection.Execute(sSQL)
    If rsQuery.BOF=False Then'Found record(s)
        sampBusunit =cText(rsQuery.Fields["Busunit"])
    End if

This is a great reply as it shows one way , not using TOM , but a way that will work. If however you realy need to use TOM then you can do something like this once your table is created. To see how to create the table take a look at this post , but once you have your table object you can use the HTMLRenderer to do the rest for you.

Dim HtmlRenderer,sTomHTML

Set HtmlRenderer = CreateObject("ExportHtml.Renderer")
    HtmlRenderer.DisplayAnnotations = False
    HtmlRenderer.DisplayOption = 0              ' 0 = Table Only
    HtmlRenderer.EmbedCss = true
    HtmlRenderer.UseFormattedLabels = True
    HtmlRenderer.IncludeTitleBlock = False
    HtmlRenderer.IncludeTOC = False
    HtmlRenderer.OutputBodyOnly = True
    sTomHTML = replace(HtmlRenderer.Render(oTableDoc.GetTablesXml(sTable)),mr.newline,"")
    Set HtmlRenderer = Null

I do this sort of stuff all the time and it works well , I normally take the output data and run it through the Highcharts api. to produce charts etc. Oh and don’t forget this will only work in your survey if you have TOM on the server. To get an idea of what I do , take a look at this.

Just Code : Run MRS from an MRS

This shows us how we can run an MRS from within an MRS file.

Dim oScriptEngine, sScript, oProgram, sResult

' Define the test script
sScript = "Dim x, y, result" + mr.CrLf + _
"x = ""Hello """ + mr.CrLf + _
"y = ""World""" + mr.CrLf + _
"result = x + y"

' Create the script engine and parse
Set oScriptEngine = CreateObject("mrScript.ScriptEngine")

Set oProgram = oScriptEngine.Parsers[0].Parse(sScript, oScriptEngine, 0)

' Execute the script
oScriptEngine.Execute(oProgram, 0)

' Get the result variable value
sResult = oProgram.Variables["result"]  

I am more that just a survey : The Login System ( 3 / 3 )

In this final article about the login system we will see how we can validate the user login credentials via a system we have created. We will create a user survey and get our menu system to validate against that. I will show you how you can use ODBC to connect the two surveys together and ensure that only the correct users are allowed to login

Read more

I am more that just a survey : The Login System ( 2 / 3 )

In the last article we saw how to create the questions that would hold our login info and the script to handle the actual logon process. In this article we will explore using the DPM object to authenticate our users to decide if they can login or not and take a look at the IsInRole feature.

Read more

I am more that just a survey : The Login System ( 1 / 3 )

In this next set of articles I am going to show you how we can take Data Collection Scripts and get them to make some cool things. To me a survey can be so much more than just a survey, it can, with the use of some simple concepts, be a dashboard , online comments cleaner, panel or report delivery system. So what skills do we have to have to make let’s say a very simple dashboard system. Well if you know how to write a data collection survey , you know how to edit JavaScript , you have read my posts on ODBC connections and have learnt HTML you have all the tools you need.

Read more

Speeding up your Excel Exports

With speed about the mrExcelExport always a hot topic when you write your TOM scripts i thought i would take a look at another possability of getting your tables into Excel. In this article i will show you how you can use the “mrTextExport” to export your tables , and also sugest something you could do to the exising Excel Export to speed it up.

Read more

Add Context , Populate & Strip HTML out

You would have recently seen in LinkedIn that Barb asked the following question. “When you add html code into your survey, it displays beautifully but then it displays as code in your tabs. Is there a way in a DMS to globally eliminate any HTML code?” and I pointed him to a post that had been written awhile ago. Also in my comment I mentioned that I had a script that would add a context to an MDD and then strip out the HTML and put just the text in it. This article shows you the code for that script.

Read more

%d bloggers like this: