Suppose new data is saved out to an Microsoft Excel file once a week, and you need to produce the same reports every week. Instead of performing these steps manually, you can create a script that imports the new Microsoft Excel file into JMP and automatically runs all analyses. The following example shows you how to set up your script and run it each week.
1. Open a new script window (File > New > Script).
2. In your script window, enter the Open() expression to open the Solubil.xlsx sample import data file. The file is located in JMP’s Samples/Import Data folder.
dt = Open( "$SAMPLE_IMPORT_DATA/Solubil.xlsx" );
Be sure to put the semicolon at the end of this expression, because you will add more expressions. The semicolon glues expressions together.
3. Run your script to import the Microsoft Excel file by selecting Edit > Run Script.
The Microsoft Excel file opens as a data table.
Notes:
• You can also include the Excel Wizard argument in the Open() expression to preview the worksheet before importing it. See “Import Data from a Microsoft Excel File”.
• You can specify an absolute or relative path to the file rather than using a path variable. For relative links, the script and file being opened must be in the same relative location each time you run the script. With absolute links, make sure that other users running the script have access to the file’s location. See “Path Variables” for more information about using pathnames.
You have three reports to produce: a distribution report, a 3D scatterplot, and a multivariate report. Perform each one using the GUI, and add its script to the script window.
1. With your new data table open, select Analyze > Distribution.
2. Select all the columns except Labels and click Y, Columns.
3. Click OK.
4. Press Ctrl, click the eth red triangle, and then select Histogram Options > Show Counts.
Bar counts are added to all six histograms.
5. In the Distribution window, click the Distributions red triangle and select Save Script > To Clipboard.
6. Click a line or two after your Open() expression and select Edit > Paste.
7. Type a semicolon after the last close parenthesis.
8. Select Graph > Scatterplot 3D.
9. Select all the columns except Labels and click Y, Columns.
10. Click OK.
11. Copy and paste the script for Scatterplot 3D into the script window just like you did for your Distribution report. Be sure to add the semicolon at the end.
12. Select Analyze > Multivariate Methods > Multivariate.
13. Select all the columns except Labels and click Y, Columns.
14. Click OK.
15. Copy and paste the script for Multivariate into the script window just like you did for Distributions and Scatterplot 3D.
You should see the following script:
dt = Open( "$SAMPLE_IMPORT_DATA/Solubil.xlsx" );
Distribution(
Continuous Distribution( Column( :eth ), Show Counts( 1 ) ),
Continuous Distribution( Column( :oct ), Show Counts( 1 ) ),
Continuous Distribution( Column( :cc14 ), Show Counts( 1 ) ),
Continuous Distribution( Column( :c6c6 ), Show Counts( 1 ) ),
Continuous Distribution( Column( :hex ), Show Counts( 1 ) ),
Continuous Distribution( Column( :chc13 ), Show Counts( 1 ) ),
);
Scatterplot 3D(
Y( :eth, :oct, :cc14, :c6c6, :hex, :chc13 ),
Frame3D( Set Grab Handles( 0 ), Set Rotation( -54, 0, 38) )
);
Multivariate(
Y( :eth, :oct, :cc14, :c6c6, :hex, :chc13 ),
Estimation Method( "Row-wise" ),
Scatterplot Matrix( Density Ellipses( 0 ), Shaded Ellipses( 0 ) )
);
You now have a script that reproduces all of the steps that you performed manually. Save the script, and close your data table and all its report windows.
1. In the script window that contains your script, select File > Save or File > Save As.
2. Specify a filename (for example, Weekly Report).
3. Click Save.
As long as your weekly updated Microsoft Excel file is saved in the same place and contains the same columns, you can run your script and automatically produce all your reports.
1. Open the script that you saved.
2. Select Edit > Run Script.
Your Microsoft Excel file is opened in JMP, and all three of your reports appear.
You can send this script to others. As long as they have access to the same Microsoft Excel file in the same location, they can also run the script in JMP and see your reports.
If you want a particular script to always be executed instead of opened into the script window, put the following command on the first line of the script:
//!
If this is not the very first line, with nothing else on the same line, this command does nothing.
You can override this command when opening the file.
1. Select File > Open.
2. Press Ctrl, select the JSL file, and then click Open.
The script opens into a script window instead of being executed.
The command is also ignored when you right-click the file in the Home Window and select Edit Script.