In the last two articles we took our log files and converted them into an MDD and a DDF file that we can use for reporting. In this article we will create a tables script and produce 3 tables that we will then export to excel.
So the first thing we need to do is to create a new mrScriptBasic file. Once we have this we can start typing in the required code. You should note that we produced this code in Reports for Surveys to start with , then copied it into the mrScriptBasic file and edited it, anyway type in the following.
Dim TableDoc, Table, Rule,iTable
' Create a new Document object
Set TableDoc = CreateObject("TOM.Document")
' Load the dataset
With TableDoc.DataSet
.Load("D:DATASDLogsMRITimeSlots.mdd" '! Path removed !', ,
"D:DATASDLogsMRITimeSlots.ddf", "mrDataFileDsc", , "{..}")
.View = 1 ' HDATA
End With
What the above code has done is to declare some variables that we will use and then create the tables document object. Once the object has been created successfully we then load in the data using the files that we created in the previous examples. Next we need to setup the language, Context and Label type,
' Set the properties of the table document
TableDoc.Language = "ENA"
TableDoc.Context = "Analysis"
TableDoc.LabelType = "Label"
' Define the variables
With TableDoc.DataSet
Dim Field
Set Field = .MdmDocument.Fields["Duration"]
Set Field.Script = "!
Duration "Duration"
long
axis("{'Mean' mean(Duration),'Min' min(Duration),
'Max' max(Duration),'stddev' stddev(Duration)}");
!"
.Variables.Update(Field)
End With
And then for ease of use , we will make a modification to the duration field and add an axis expression to it so that when used in tabulation it will show us the Mean,Min,Max and stddev values. This change is not saved back into the MDD and will only last while this script is being run. Next We Add some filters,
TableDoc.ProfileDefault.Clear()
TableDoc.Global.Filters.AddNew("Filter0",
"iYear.ContainsAny({_2009})", "Year 2009", "HDATA")
In our case we only want to look at 2009 data so we have added in a global filter to ensure that only 2009 data is used. Next Type this in,
' Define the tables
With TableDoc.Tables
Set Table = .AddNew("Table1", "TimeSlot * DOY{base(), _1, _2, _3, _4,
_5, _6, _7, _8, _9, _10, _11, _12, _13, _14, _15, _16, _17, _18,
...... _181, _182 [IsHidden=True],
_183 [IsHidden=True], _184 [IsHidden=True], ......
_363 [IsHidden=True], _364 [IsHidden=True], _365 [IsHidden=True]}",
"January to June")
Set Table = .AddNew("Table2", "TimeSlot * DOY{base(), _1 [IsHidden=True],
_2 [IsHidden=True], ...... , _182, _183, _184, _185, .... _363,
_364, _365}", "July to December")
From the above code you should be able to work out that we create some tables , the first , Table1 has the TimeSlot question down the side of the table and the Day Of The Year question along the top. We have also set any of the days from June to December to be hidden so that we dont have any export issues. Next Comes Table2 which has the same questions on each axis, however this time January to June is commented out. next we finish off the tables code.
For iTable = 1 to 12
Set Table = .AddNew("Tablem"+ CTEXT(iTable),
"TimeSlot * iDay", "Month"+ CTEXT(iTable))
Table.Filters.AddNew("Filter"+ CTEXT(iTable),
"iMonth.ContainsAny({_" + CTEXT(iTable) + "})")
Next
End With
The code above shows you that we use a loop and add 12 new tables to our tables document setting a filter for each table. Each filter is only going to allow that particular months worth of data , so if iTable = 9 then that is the month of September. Each of these tables has the TimeSlot question down the side and the iDay question across the top. Next type the following into your script
' Populate the tables
TableDoc.Populate()
TableDoc.save("D:DATASDLogsMRIOutputOutput.mtd")
' Export the tables
debug.echo("Export the tables")
With TableDoc.Exports["mrExcelExport"]
SetProperty(.Properties, "Destination", "D:DATASDLogsMRIOutputData.xls")
SetProperty(.Properties, "DisplayCharts", False)
SetProperty(.Properties, "LaunchApplication", True)
.Export()
End With
This code is the standard export code , first off we populate the tables, then we save out the mtd file for later use. You dont have to do this , but it can be an advantage sometimes if you want to load this set of tables into Reports for Surveys. Next we display to the screen a message saying the export is about to begin. The export we have chosen to run is a simple excel export , we have specified an output file, and decided not to show any charts , we are going to do all our charting by hand in Excel. We have told the script to launch Excel at the end , so that you can see the data produced. All these properties are set with the SetProperty function,
' ---- Helper functions ---- Sub SetProperty(Properties, Name, Value) Dim Property Set Property = Properties.FindItem(Name) If IsNullObject(Property) Then Set Property = Properties.CreateProperty() Property.Name = Name Property.Value = Value Properties.Add(Property) Else Property.Value = Value End If End Sub
So , what do you get when this script has finally run and what does it look like. In our case we wanted something that was re-usable and easy to operate so we created another excel sheet that reads data from the one just produced. If you would like the excel sheets we produced then just drop us an email or leave a comment and we will be happy to send you them. This is some of the example outputs we have produced.
Now that we have this process in place the next thing we thought we would do would be to write a program that created the Concurrents.csv file from information we provide it.
The full code for this article can be found in :
http://CodeCorner.SmaterDimensions.Com @ Concurrents Monthly Tables