Generating Prism graphs from a Platemaker Wizard Workbook

Unbeknown to many, Prism Graph supports a scripting language that allows the automation of graph creation once a single master graph has been defined. The Platemaker Wizard provides the functionality to slice the large multidimensional dataset contained in the Flat Table into graphable chunks using the “Create Statistical Summary Table” program. Once these Pivot tables are created, it is then possible to run the Platemaker Wizard function “Generate Prism Graphs”. This program automatically steps through each value of any selected filters in the Pivot table and exports the resultant data chunks to Prism Graph. The program also generates the appropriate Prism Graph Script file that runs inside Prism Graph allowing it to create the appropriate data holders and associated graphs as the incoming data is imported into Prism Graph data pages as described below.

Creating a Prism Graph Template File
This file is simply a normal Prism Graph file (not an actual Prism Template file which is different functionality supported by Prism but not relevant here).

If you navigate to the Platemaker Wizard Graph Templates folder, located inside the Platemaker Wizard Data folder (which by default is in the public documents folder) you will find three example templates that are included with this program.

Opening the Single Graph Template file and you will see it is just a standard Prism Graph file which contains one graph family (Figure 1).

The Prism Graph program functionality to create such a file is beyond the scope of this instruction manual but is easily achievable for those who know how to use Prism Graph to obtain the graphs with the formatting they desire. As you use the Platemaker Wizard for your own experiments, you will first export a single chunk of data using the Pivot Table functionality below into an empty Prism Graph project. The experimentalist should then create all the graphical visualizations (and possibly more advanced statistical analysis) they require using Prism Graph, before saving the graph file. Then, if they intend to use this file as a template for the Platemaker Wizard to guide the build of a much larger multi-graph Prism file, they should save it in the Graph Templates folder of the Platemaker Wizard.

Exporting the Contents of the Statistical Summary table to Prism Graph
Background
Although it would be possible to manually copy the contents of your statistical summary table across to Prism graph, the problem is that what you really want to do is move the many versions of that single statistical summary table as the pivot table filters are changed to the various possible values they contain. For example, if you want to plot the dose response curves of 60 test compounds in 60 separate graphs, then the compound filter requires 60 changes and the resultant data copied 60 times from the statistical summary table to a Prism Graph file that has 60 data sheets linked to 60 graphs preformatted to suit your requirements. The “Generate Prism Graph” option of the Platemaker wizard menu allows you to achieve this task very easily saving you hours of graph generation time.

The relationship between a 2-dimensional Pivot data table in a Platemaker wizard-built workbook and a Prism graph is as follows: 1) the row values of the data table always map to the x-axis values of the graph. If your table has multiple columns of data, where the columns are the values of one of your independent variables, then these data display as plots on the same graph with a graph legend that links each plot back to the value of the table column value. The Y-axis values are the mean values inside the main area of your data table. These values change depending on the filter settings of your pivot table. Finally, the whole graph itself captures the unique settings of your pivot table filters and so their values can also be included in the graph’s title or in the Y-axis title as in Figure 2.

Therefore, to use the Generate Prism Graphs function, you must first be on a statistical summary page that has been built by the Platemaker wizard. For example, if you have a statistical summary page similar to the one inside the demonstration workbook shipped with the Platemaker wizard then selecting from the main addin menu “Platemaker Wizard Generate Prism Graphs” displays the following user form.

From the introduction above, the row and column components of your statistical table are fixed so the only components that change the actual data inside the statistical summary table are the various values of the pivot filters your statistical summary table contains. Likewise, these Pivot filter values are recorded either in the main title of the graph or as different Y-axis labels. Therefore, the Create Prism Graphs form has 3 list boxes, one containing a list of all the pivot filters available on the statistical summary page, and two target list boxes that can receive any of these pivot table filters. If you leave the pivot table filter in “Pivot Table Filters” list box, then it will not be processed by the Generate Prism Graph program, nor will its values appear either in the main title or the Y-axis title of the Graphs you subsequently generate.

At a very minimum this program requires one pivot filter placed in either the “Prism Graph Y axis Title” or the “Prism Graph Main Title” list box. These list boxes, along with the other controls on this form, shall now be described.

Create Prism Graphs Form Controls
Pivot Table Filters: This list box contains all the pivot table filters that were included when you built the statistical summary page which is now your active worksheet.

Prism Graph Y axis Title: This can take any pivot table filter name although normally it only makes sense to put the dependent variable pivot filter name in this list box, indicating that you want to create a set of graphs for each dependent variable measurement in your workbook where each dependent variable name forms the y-axis title of your graph. It is also permissible to leave this box empty and supply the title of the Y-axis in a separate dialogue box which appears when you push either the “Generate Graphs” or “Generate Scripts” button.

When you place any filters into the “Prism Graph Y axis Title” list box, the “Y axis parameter(s) to separate Prism files” option enables. If you select this option then graphs with different Y-axis titles, which are uniquely created by the individual values of the pivot table filters you placed in the “Prism Graph Y axis Title” list box, will be sent to separate Prism Graph files using the Graph template specified in the “Prism Graph Master File Name” below. If you know that your Pivot filter selections are going to generate large number of graphs you should tick the “Y axis parameters to separate Prism files” option because Prism graph files do have an upper limit to the number of graphs and data sheets they can contain. Also, too mazny graphs in a single Prism Graph file can make them difficult to navigate. When the “Prism Graph Y Axis Title” list box contains pivot table filters and the “Y axis parameter(s) to separate Prism files” option is unchecked, the “Y-axis by Title” toggle button becomes active, allowing you to control how the individual graphs are ordered inside the single Prism Graph file that the Platemaker Wizard creates (Figure 4).

If you only place pivot filters in the Y-axis list box and leave the “Prism Graph Main Title” list box empty, then the Y-axis titles generated will simply be copied over to the main title of the graph so that each graph you produce still has a title.

Prism Graph Main Title: This list box can take any pivot table filter and once a filter is added to this list box then the program will step through all the available values of this filter recording the value of the filter(s) in the title of the graphs it generates.

For example, below we have a statistical summary table that contains a Pivot filter for compound. If you access the dropdown list of this filter it appears as follows:

Therefore, if this filter was added to the “Prism Graph Main Title” list box, then the program will step through each of the compounds in turn sending the appropriate data to Prism and setting the title of the graph to the compound that is currently selected by the Platemaker Wizard Prism Graph generation program.

Including the Zero Dose Control in every graph plot
When the 0 dose negative controls are separate on the plate, but you want to include them on your dose response curve, the correct setting for the Pivot Table filter is not a single drug, but the drug you wish to see the data for plus the zero dose control. Such a setting looks like this:

It is possible to set up the statistical summary table so that the Generate Prism Graph program steps through each drug whilst keeping the DMSO data continuously selected by first setting the compound filter to the negative control before you run the Generate Prism Graph program like this:

When you now run the Generate Prism Graph program, behind the scenes it will keep the DMSO data continuously selected while it steps through the other compounds. However, it is important that you do not try to run the program with the multiple items option already selected. For example, if you try to run the Generate Prism Graph program with the following setting:

This following dialogue box will be displayed.

If you hit yes, in this example, although you would generate graphs for all your compounds, because the Pivot table filter has been reset to “All”, the zero dose control curve will be absent from all your other compound graphs.

How the program interprets multiple filters in the Title or Y-axis list box.
Both the Prism graph main title and Y-axis list boxes can take multiple pivot filters. The order that the filters appear in the list box will determine the order in which the data is sent to Prism Graph. Consider for example the two possible orderings for Compound and experiment number.

Just as with the ordering of column names on page 2 of the “Create New Data Entry Workbook”, the vertical order in the above tables is translated from top to bottom into left to right when the graph title is generated. From our screen shot example above, the graph titles will be: “Compound, Exp No.” and “Exp No., Compound respectively”. Likewise, the program steps through the filters moving from right to left (or bottom to top in relation to the above list boxes) so that in the left list box, the Exp No pivot table filter is changed first while holding the compound filter constant and then when this operation is completed, the next compound is selected and the Exp No. filter again is fully processed for all experiments. In contrast, in the right list table, the compound filter is first changed while holding the Exp No. filter constant and when all the compounds have been processed, the next Exp No. is selected, and the Compound filter again is fully processed for all compounds Figure 5.

Add to Y Axis button: Any pivot filter labels that are selected in either the “Pivot Table Filters” or the “Prism Graph Main Title” list boxes are moved to the Prism Graph Y Axis Title list box.

Add to Main Title button: Any pivot filter labels that are selected in either the “Pivot Table Filters” or the “Prism Graph Y Axis Title” list boxes are move to the Prism Graph Main Title list box.

Delete: Any pivot filter labels that are selected in either the “Prism Graph Y Axis Title” or the “Prism Graph Main Title” list boxes are moved back to the Pivot Table Filters list box.

Y Axis By Title toggle button: See Figure 4.

Send Graphs to Powerpoint button: If you tick this option before generating your graphs an extra command is added to the script file that instructs Prism to send the graphs to Powerpoint slides producing a Powerpoint presentation similar to the one below.

Do not add graphs to template: Any Prism graph file can serve as template including Prism graph files which already contain many graphs and previously built layouts (see the 56 Drug Layout Graphs.pzfx as an example). If you are using a Prism Graph file that already contains all the data container sheets and related graphs to receive your exported table data, then you do not want to be adding new container sheets with their related graphs to a copy of that Prism Graph file. Ticking this option means that the script file will take the data and add it to already existing container sheets in the copy of Prism Graph file it made from the selected template. Note: if the selected Prism Graph template file has only 5 data containers, and your pivot table filters result in seven separate tables, then the Prism Graph script file will error when it attempts to import the 6th data table because there is no corresponding container in the receiving file that is based on that particular Prism Graph template.

In contrast, if this option is not checked then it is assumed you are working with a Prism graph file that contains a single datasheet and graph and the script file replicates these the number of times required to receive all the data tables the Platemaker Wizard is exporting to the new Prism Graph file.

Use Bar Graph: Tick this option if your graph template file contains bar graphs rather than line graphs.

Prism Graph Master File Name and Path: Requires the Prism Graph file name (and the full folder path) that will serve as the template for your new Prism Graph file build.

“Push to Select Path” Button: Push this button to bring up a standard explorer window that allows you to select the appropriate Prism Graph file anywhere on your computer that can serve as a template for your new Prism Graph file you about to create. When you push this button the file explorer window will first open in the Platemaker Wizard’s default Graphs Template folder whose folder path is set in the Program Options menu of the Platemaker wizard. However, you can navigate to any Prism Graph file anywhere on your computer and it will serve as a template for your new data export.

Generate Scripts button: this option will only generate the script file(s) that are used to build your Prism Graph files. These script files will be saved inside the active workbook’s folder as shown.

If you now double click on one of the script files, it will run Prism Graph and build the individual Prism Graph file the script file specifies. It might sometimes be useful to create your graph files using this two-step process, however, be careful to run the script files before you attempt to use the “Generate Prism Graphs” program again as the individual script files refer to exported text files named En.txt (where n is an integer) located in the c:\Programdata\PlateMaker Wizard folder of your local computer.

The contents of these text files change each time you run the “Generate Prism Graphs” program. Therefore if you run the program and generate script files only, and then, before you run the script file(s), you run the program a second time using a different workbook and/or pivot table filters, then the original script files will now be importing a different set of data to the data that was intended to be imported when the original script files were created. The contents of a Prism Script file created by the Platemaker Wizard is shown in Figure 6.

Generate Graphs Button: This option runs the Generate Prism Graphs program in its entirety, first creating all the export files needed to create the new Prism Graph file or files and then all the script file(s) necessary to instruct Prism graph to build these new graph file(s). Finally, the program opens Prism Graph and commands it to execute the script file(s) so that the new Prism Graph file(s) are created. Finally, once the new file(s) are created, the program adds appropriate hyperlink(s) to the Statistical Summary worksheet that open the related Prism Graph file(s) so the graphs can be easily viewed from within the Excel workbook.

Pivot table Filters not included in either the Prism Graph X Axis or Main Title List boxes
Pivot table filters that are not included in either the Y-axis Title or the Main Title list boxes will not be changed, meaning that whatever setting they are on when you run the program, that is the setting for all the graphs generated by the Platemaker Wizard Prism Graph Generator program. This is why when we do not put the Dependent Variable Pivot table filter in the Y-axis or Main title list box, we must first manually set the filter to the desired plate measurement we want to graph. If we fail to do this, the data sent to Prism Graph will be the average of all the different plate measurements (including derived calculations) of all the different dependent variables in the current workbook. Obviously, such data would be meaningless.

Trying to run Generate Prism Graphs with Prism Graph already Open on your computer.
If you try to run “Generate Prism Graphs” and Prism Graph program itself is already open on your computer, you will get the following warning:

In this instance you can choose to just create the script file (see above) by selecting “Yes” to this prompt. Note if you choose to close Prism, you might find that when you try to run the Platemaker Wizard Generate Prism Graphs a second time, the message about the Prism Graph program being open persists. This is because the latest versions of Prism Graph also run part of the program as a background process. To close down a background process, you need to run the task manager. You can do this by first right clicking the mouse while your cursor is in the Windows task bar at the bottom of your screen. Then from that context menu select “Task Manager” and the window like that of Figure 7 will appear. Find the background process of “GraphPad Prism”, select it, and then right click your mouse a second time to select “End Task”.