220.127.116.11 Linking Text Labels to Data and Variables
Text labels can as simple as a static object or they can be dynamic objects that resolve by linking to a wide variety of project information including worksheet cell values, data file names, worksheet column metadata, or dataset statistics.
Whether considering (1) auto-generated objects such as axis titles, legends or data point labels or (2) user objects added with the Text or Annotation tools, we are generally relying on LabTalk Substitution Notation to insert specific text strings and numeric quantities into our text labels in real-time.
Within the context of the graph window, text labels can be lumped into two groups:
- Graph text labels: These include axis titles and graph legends, as well as labels added with the Text tool . See this page for information on user-added labels. See these pages for information on Graph Legends or Axis Titles.
- Data Plot Labels: These are labels applied to data points (single points or entire datasets) either using the Annotation tool or by enabling labels using Plot Details Label tab controls. See Labeling Data Points for information.
Substituting Worksheet Cell Values in Text Labels
There are several ways to display worksheet cell data in the text labels. The simplest (though also the most limited) is to simply Paste-Link the contents of a worksheet cell. More involved methods incorporate LabTalk Substitution.
Example 1. Insert cell value by Paste Link
This is the simplest way to combine a worksheet cell value and some literal text:
- Click on a worksheet cell and press CTRL+C (or right-click and Copy).
- Use the Text tool to create your literal text (e.g. The value of y is:), then while still in label edit mode, right-click and choose Paste Link. This inserts the cell value into the text object. Note that since you are paste-linking, a change to the worksheet cell value will trigger a change to the value shown in your label.
You can use this method to insert values from either the data or the column label row portions of your worksheet. More sophisticated methods for inserting cell values -- specifically, those that use substitution notation -- can be more flexible. However, if all you need is to return a value from a specific worksheet cell, this may suffice.
Example 2. Insert Cell Value using Substitution Notation
This example introduces basic substitution to return cell contents, using the form %(workbookName, column#, row#).
- To insert the cell value in Book1, column 2, row 5, type the following into a text label:
- The value of y is %(Book1,2,5)
- Right-click on the text label and choose Properties. On the Programming tab, set Link to (%,$), Substitution Level to 1 and click OK.
|Notes: Examples 1 and 2 have a limitation in that the source worksheet name cannot be changed, otherwise the link between worksheet and label is broken. In the next example, we show how to handle this.
Example 3. Insert Cell Value using @WT to Refer to Plot Source
Most data plots in Origin are created from a single column of data. All data in that column, including metadata in column label rows, is thus associated with that data plot. Building your substitution string using text label option @WT allows you to return a worksheet cell value by referencing the source data for the plot. Unlike Example 2, you avoid the limitation of having to reference a fixed book or sheet name.
Let's say, for example, that you want to create a text label that reads "The value of y is number," where number is the value read from cell at 2nd column and 5th row in the source worksheet of plot 1.
- Type the following text in WYSIWYG edit mode or enter in the Object Properties dialog box and click OK.
- The value of y is %(1, @WT,2,5)
- Right-click on your text label and select Properties... and go to Programming tab in the dialog. Or press the ALT key while double-clicking on the text label.
- Set Link to (%,$), Substitution Level to 1 and click OK.
The advantage of this method is that the worksheet column that @WT refers is identified by plot source. When you save the graph as a Graph Template and reuse it later, the display content of the text label will automatically update according to the new data, even if dataset name is changed.
- Alternately, you can refer to a column by name -- either Short or Long Name -- using %(1, @WT,ColName,RowIndex).
- The %(1, @WT,ColIndex,RowIndex) notation can be used to reference a matrixsheet value.
Example 4. Insert Cell Value and Modify Format
Some syntaxes support formatting of returned numeric values using these numeric formatting options. For instance, if you follow the method of Example 2, above, you will be returning a full-precision value from the sheet. To control the number of displayed digits returned, you could an optional format specifier to your expression. Here, there are multiple syntaxes that will work:
- $(expression [,format])
- %([workbookname]sheetname, column, row [,format])
- %([workbookName]sheetName, @WL, column[row] [,format])
the last syntax -- the @WL example -- supports these handy format options:
|| Return in the cell format.
| custom format
|| Return the full precision value of the cell, modified by one of these Origin Formats.
|| Return the full precision value of the cell.
So, for example, to format a numeric value with four decimal digits, you could use one of the following:
Substituting Column Label Row Values (Metadata) in Text Labels
In addition to data row cells, you can substitute values from column label row cells in your text labels. There are several syntaxes that will work, depending upon your needs.
- %(PlotIndex, @option)
- %(PlotIndex[PlotDesignChar], @option)
- %(PlotIndex,@WT,ColIndex, LabelRowChar)
The last option uses a Column Label Row Character (LabelRowChar).
Substituting Variable Values in Text Labels
Besides displaying worksheet values, you can include variable values in text labels. The basic procedure is to create a text label that uses LabTalk Substitution Notation, then enable variable substitution in the text label's Programming tab of the Object Properties dialog. The variable value will be substituted into the text label. You can insert both string and numeric variable values in this way.
Example 1. String Substitution
The first example inserts a string variable into a text label. When Origin runs, there are a number of reserved string variables that are used to store system data. Please refer to this page for a detailed list of Substitution Notations.
Assume that your current project MYPROJECT.OPJ is saved to C:\MYFILES\.
- Using WYSIWYG text labeling methods, type the following into a text label in a graph window:
- This project is saved to %X%G.OPJ.
- Exit the typing mode, right-click to select the label and choose Properties and go to Programming tab in the dialog.
- Set the Link to (%,$), Substitution Level list to 1, and click OK.
- Right-click on the text object, choose Properties and select the Verbatim check box (this causes the "\" character to be handled as a literal character instead of an escape character -- see below). The label should display the drive path and name of the current project.
- This project is saved to C:\MYFILES\MYPROJECT.OPJ.
For information on use of the Verbatim option see Use of the backslash "\" character in the Object Properties dialog box.
When adding text labels to graph windows, you aren't limited to using the string registers mentioned in the previous example. There are a number of @options you can use to insert worksheet metadata into your labels, including dataset names, comments, filter conditions, etc. For instance, in the following graph, we have added a text label that combines literal text "Make =" with a string "%(1, @LF)" -- which reflects the filter condition used to create the plot (works whether filter is locked or not).
Example 2. Numeric Substitution
Using LabTalk, you can define any number of variables for use in your work. As an introduction to the concepts of "variables" and "scope", we recommend that you read this topic. To illustrate basic numeric substitution, we give the following simple example:
- On the main menu, click Window: Script Window.
- Click inside the Script Window and at the cursor, type:
and press Enter.
- From the Tools toolbar, select the Text Tool, click in an empty space on a graph or worksheet window and enter the following:
- Right-click on the text label, choose Properties and go to Programming tab in the dialog, set the Link to (%,$), Substitution Level list to 1, then click OK. Your text label should now display the numeric value of MyVariable.
To see a list of available LabTalk variables, open the Script Window (Window: Script Window) and click Tools: Variables... or type "ed" at the command line.
Resolving math expressions in text labels
We've seen that Origin supports substitution notation when creating text labels using the standard WYSIWYG text editing methods or when editing text in the Object Properties dialog box. You can also use this notation to resolve a mathematical expression to a value in the text label:
In this substitution notation, expression is resolved to a value each time the text label is redrawn. The text label is redrawn whenever you move the label or redraw the graph window.
Example. Resolving pi in text label
- Type the following text in WYSIWYG edit mode or enter the expression in Text tab of the Object Properties dialog box and click OK.
- The value of X is $(pi^2)
- Right-click on your text label, select Properties... and go to Programming tab. Or, press the ALT key while double-clicking on the text label.
- Set Link to (%,$), Substitution Level to 1 and click OK. The label now reads:
- The value of X is 9.8696
You may need to click the Refresh button on the Standard toolbar to see the change.
When Origin starts, a text file in the program folder named ORGSYS.CNF is read. It is in this file that the value of pi is defined, in exactly the same way as you would define a constant or a variable at in the Script Window or the Set Column Values dialog box:
const pi = 3.141592653589793
If you have mathematical constants that you regularly use in your work, the CONST.CNF file under User File Folder is a good place to define them, as they will always be available when Origin runs (the .CNF file is a text file that you can edit in a simple text editor).