Example: Custom Snapshot Report
This example is for readers who are interested in knowing more about VTScada script code to build complex custom reports.
Of particular interest in this example is the use of arrays. Assume that the operator selects N tags for the report. Correspondingly, the result will have an array of data. Assume also that the operator selects a time span that includes many 15-minute intervals. For each tag in the result array, there will be an array of snapshot data. Read the example carefully to see how those arrays are handled.
- Start with a report template:
( { Parameters } Reporter { Object value for call-backs }; Start { Starting time (local, not UTC!) }; End { Ending time }; Tags { List of tag names to report on }; ) [ { Variables } { Set this module to become a plug-in for the reports } [(POINTS) Shared Report; ] Constant TypeFilter = "Loggers" { Tag Type filter }; Constant ReportName = "Quarter Hour Snapshot Report" { Title }; ] Init [ If 1 GetData; [ { Set up the titles and prepare for the loop } ] ] GetData [ { The "GET..." states pull tag data for use in the report. } { If using this file as a template for new reports, you might } { need more states, replacing the code as required } { to retrieve data from your application's tags. } If 1 WriteData; [ { Retrieve the data for the tag } ] ] WriteData[ { Every report will need a state like this to write the data. } { All that will change from one report to the next is the } { formatting and tag names, as appropriate for the data that } { was collected. } If 1 { Wait for the next tag data } Done; [ { Output the data } ] ] Done [ { Clean up and stop. } If 1; [ Slay(Self, 0); ] ]
- Save the file in your application folder.
- Declare the module in the Plugins section of your application's AppRoot.SRC.
- Import File Changes. Fix any typos. Do not try to run the report yet, as the code must still be written.
- Add variables as follows:
Titles { Array of ODBC database field names }; Types { Array of ODBC column type names }; Data { Actual data for the body of the report }; Formats { Array of format strings which will be used in successive SWrites to write Data }; TitleStrm { Stream in which to build the title line }; Fields { Array of what to include in the return array }; NTags { Number of tags selected for report }; i { Tag counter }; TagObj { Current tag object of the (possible) array of tags }; NRows { Number of rows of returned data }; { depends on the number of snapshots within the selected time frame } j { Row counter }; TagData { Retrieved historical data }; RetCodes { Array of return codes (errors) from queries }; Constant QueryTPP = 900 { This is a quarter-hour snapshot }; Constant Mode = 4 { Mode 4 == Value at start of TPP }; { The mode value of 4 is what makes this a Snapshot-type report. } UTCStartTime { Start time of the report in UTC }; UTCEndTime { End time of the report in UTC }; RowTimestamp { Array with start and end of each snapshot }; { This report assumes that the operator gives the start and end times of the report using their local time, and that they want results for that same local time zone. } Constant NUMFMT = "%13.2f"; Constant TEXTFMT = "%13s";
- Save your work so far and Import File changes to ensure there are no typos.
- Edit your Init state as follows:
Init [ If 1 GetData; [ { Initialize the arrays for the tags } NTags = ArraySize(Tags, 0) { Need to know how many columns }; { Set up the titles array and prepare for the loop } Titles = New(NTags + 2) { Add two columns for time and date }; Formats = New(NTags + 2); Types = New(NTags + 2); Data = New(NTags + 2); TagData = New(NTags); RetCodes = New(NTags); Fields = New(2); Fields[0] = "TimeStamp"; Fields[1] = "Value"; { Historian data is in UTC. Translate from operator's local time } UTCStartTime = ConvertTimestamp(Start, 0, False, Invalid); UTCEndTime = ConvertTimestamp(End, 0, False, Invalid); NRows = Ceil((UTCEndTime - UTCStartTime) / QueryTPP); { Build the title lines } TitleStrm = BuffStream(""); SWrite(TitleStrm, "%s from %s %s to %s %s\r\n\r\n", ReportName { Title for the report }, Date(Start / 86400, "MMM d, yyyy"), Time(Start, "HH:mm:ss"), Date(End / 86400, "MMM d, yyyy"), Time(End , "HH:mm:ss")); { Add the Headers to the title } Titles[0] = "Date"; Titles[1] = "Time"; Types[0] = Types[1] = "TEXT"; Formats[0] = Formats[1] = TEXTFMT; { Set first 2 columns of the title line } SWrite(TitleStrm, Formats[0], Titles[0]); SWrite(TitleStrm, Formats[1], Titles[1]); i = 0; { Remaining column headers are tag names } WhileLoop(i < NTags, Titles[i + 2] = PickValid(Scope(\Code, Tags[i])\Name, "Unknown"); Types [i + 2] = "NUMERIC"; Formats[i + 2] = NUMFMT; SWrite(TitleStrm, TEXTFMT, Titles[i + 2]); i++; { ensure that the loop counter increments } ); { Add CR, LF to title line, write the titles and reset the stream } SWrite(TitleStrm, "\r\n"); Reporter\ODBCColumns(Titles, Types); Reporter\TitleLine(TitleStrm); Seek(TitleStrm, 0, 0); ] ]
- Import File Changes again.
- Run the report, selecting one or two tags and displaying the output to the screen. You should get the titles.
- Write the GetData state as follows:
GetData [ If 1 WriteData; [ { Retrieve the data for the next tag } i = 0; WhileLoop(i < NTags; TagObj = Scope(VTSDB, Tags[i]); { Launch GetTagHistory to get the data for the current tag. An array index is specified for RetCodes and TagData for each launch, so that we don't conflict with results from other tags. } { Note that each element returned in &TagData[i] will itself be a two-dimensional array } \HistorianManager\GetTagHistory(&RetCodes[i], &TagData[i] { Return code, result }, TagObj { Tag Object }, "Value" { Field to return }, UTCStartTime, 0 { Start, End (Ignored) }, QueryTPP, NRows { TPP, Num Entries }, Mode { Mode }); i++; { ensure that the loop counter increments } ); ] ]
- Ensure that your code will compile. There is no point running it again at this stage unless you also use the Source Debugger to examine your arrays.
- Write the WriteData state as follows.
WriteData [ { GetData launched NTags instances of GetTagHistory. Wait until all have a valid return code before proceeding. } If AValid(RetCodes[0], NTags) == NTags Done; [ { Set the row counter } j = 0; { Loop through retrieved data, creating each report row } WhileLoop(j < NRows; { Loop through TagData array to get the data } i = 0; { First two columns of the row will be date and time } RowTimestamp = UTCStartTime + QueryTPP * j; Data[0] = Date(RowTimestamp / 86400, 7); { UTC Date at start of TPP } Data[1] = Time(RowTimestamp % 86400, 2); { UTC Time at start of TPP } WhileLoop(i < NTags; { Fill in data array } Data[i + 2] = Cast(TagData[i][0][j], \#VTypeDouble); Formats[i + 2] = NUMFMT; { Increment loop counter } i++; ); { Pass the report line to the VTS Reporter module } Reporter\DataLine(Formats, Data); { Increment the data time index } j++; ); ] ]
- Import file changes and run your report.
- Open the Source Debugger and set a breakpoint on the inner WhileLoop of WriteData.
- Examine the value of TagData each time the breakpoint is hit. Match what you see in the Source Debugger to the array indexes used in the code.