9.3.5 Update Graph By Reimport Data From Database


Summary

This tutorial is for illustrative purposes only. The connection string shown in the tutorial does not connect to an installed database. If you wish to set up an AdventureWorks database on your own server so that you can work through the process discussed in the tutorial, see this GitHub page.

This tutorial demonstrate how to import data from database and do analysis and graphing with data, then add a button in graph to update database query and reimport data using Origin's LabTalk script.

What you will learn

This tutorial will show you how to:

  • Import data from database with LabTalk variables in the SQL query to define date range of the query.
  • Create pivot table and plot graph to analyze data.
  • Add a button on the graph to modify date range of the query and reimport from database.

Steps

Suppose we have already set up a SQL server named AdventureWorks2008 on a server machine noho.

Import Data from Database

  1. Start a new project. Open SQL Editor by clicking the Open SQL Editor button on the Database Access toolbar.
    ImportDataDatabase 1.png
  2. Select File: Edit Connection String... menu and put the connection string below to the text box. 
    Provider=SQLOLEDB.1;
Password=labtalk2015;
Persist Security Info=TRUE;
USER ID=CONNECT;
Initial Catalog=AdventureWorks2008;
DATA SOURCE=noho
  3. Click the Test button to test if the connection is fine. If fine, click the OK button to connection to the database.
  4. Select Query: LabTalk... menu in SQL Editor to open LabTalk Support Settings dialog. In this dialog, check the Enable LabTalk (%, $) Substitution checkbox, and put the following script to the text box. 
    string pastFrom$ = "";
string pastTo$ = "";
string curFrom$ = "";
string curTo$ = "";
if(exist(strPast1$, 18) == 18)  // Check if strPast1$ has value
{
	// If strPast1$ has value
	pastFrom$ = strPast1$;  // Assign strPast1$ to pastFrom$
}
else  // If strPast1$ is not defined
{
	pastFrom$ = "1/1/2003";  // Assign a const string to pastFrom$
}
if(exist(strPast2$, 18) == 18)
{
	pastTo$ = strPast2$;
}
else
{
	pastTo$ = "3/31/2003";
}
if(exist(strCurrent1$, 18) == 18)
{
	curFrom$ = strCurrent1$;
}
else
{
	curFrom$ = "4/1/2003";
}
if(exist(strCurrent2$, 18) == 18)
{
	curTo$ = strCurrent2$;
}
else
{
	curTo$ = "6/30/2003";
}
    Update Graph Database 01.png
  5. Click OK to go back SQL Editor. In the right text box, put the following SQL statements. 
    SELECT OrderDateTotalDueAndProductSubcategoryID.OrderDate AS 'Order Date', 'Date Range' = 
	CASE 
		WHEN OrderDateTotalDueAndProductSubcategoryID.OrderDate < '%(curFrom$)' THEN 'Past'
		ELSE 'Current'
	END,
	Production.ProductSubcategory.Name AS 'Subcategory Name', 
	OrderDateTotalDueAndProductSubcategoryID.TotalDue AS 'Total Due'
FROM 
	(SELECT OrderDateAndTotalDue.OrderDate, OrderDateAndTotalDue.TotalDue, Production.Product.ProductSubcategoryID
	FROM
		(SELECT Sales.SalesOrderHeader.OrderDate, Sales.SalesOrderDetail.ProductID, Sales.SalesOrderHeader.TotalDue
		FROM Sales.SalesOrderHeader
		INNER JOIN Sales.SalesOrderDetail
		ON Sales.SalesOrderHeader.SalesOrderID=Sales.SalesOrderDetail.SalesOrderID
		WHERE (Sales.SalesOrderHeader.OrderDate BETWEEN '%(pastFrom$)' AND '%(pastTo$)'
				OR Sales.SalesOrderHeader.OrderDate BETWEEN '%(curFrom$)' AND '%(curTo$)')) AS OrderDateAndTotalDue
	INNER JOIN Production.Product
	ON OrderDateAndTotalDue.ProductID=Production.Product.ProductID) AS OrderDateTotalDueAndProductSubcategoryID
INNER JOIN Production.ProductSubcategory
ON OrderDateTotalDueAndProductSubcategoryID.ProductSubcategoryID=Production.ProductSubcategory.ProductSubcategoryID
    Update Graph Database 02.png

    As we can see that there are three LabTalk variables are used in the statements.

  6. Click the last button on the toolbar, we can see the substitutions.
    Update Graph Database 03.png
  7. Select menu File: Save to Active Worksheet to save these settings to the worksheet, and then click the Import data to worksheet button Button db Import Data.png to import the data into worksheet, and then close SQL Editor. We can see the imported data as the following image shows. A yellow icon will appear at the top-left of the worksheet to indicate there is database connection is saved in the worksheet.
    Update Graph Database 04.png

Create Pivot Table and Do Column Calculations

  1. Start with the imported data. Select column C, and then choose Worksheet: Pivot Table menu to open the Data Manipulation\Worksheet: wpivot dialog.
  2. Set the Recalculation as Auto.
  3. Select column B for Pivot Table Column Source by clicking the triangle button to the right.
  4. Choose Sum for Summarize by. Then you can see Pivot Table Data Source row shows above, just choose column D for it by clicking the triangle button to the right.
  5. Expand the Options node and set Sort Output Columns to None.
    Update Graph Database 05.png
  6. Click OK button to create a pivot table by using the settings.
    Update Graph Database 06.png
  7. Click Add New Columns button Update Graph Database 07.png twice to add two columns.
  8. Change the Long Name of them to Percent Change and Gain/Loss respectively.
  9. In F(x)= cell of column D,
    if you are using Orign 2017, enter
    B==0 ? 100 : 100*(C-B)/B
    If you are using versions before Origin 2017, enter
    col(B)==0 ? 100 : 100*(col(C)-col(B))/col(B)
  10. In F(x)= cell of column E,
    if you are using Orign 2017, enter
    D > 0 ? 12 : 2
    If you are using versions before Origin 2017, enter
    col(D) > 0 ? 12 : 2

    11. The result looks like:

    Update Graph Database 10.png

Create Graph and Customize Graph

  1. On Pivot1 worksheet, select column C and D and select Plot: Multi-Panel: Stack... menu.
  2. In the dialog, change the setting as follows.
    Update Graph Database 14.png
  3. Click OK. A two panel graph is created.
    Update Graph Database 15.png
  4. Double click bottom axis on left panel. On Tick Labels tab, set Divide by Factor as 1000000 (one million), set Suffix as M. On Scale tab, set From and To as 0 to 120. Set Major Tick Type as By Increment and Value as 20. Select Vertical on the left panel of the Axis dialog. Set Rescale as Auto. Select Click OK.
  5. Double click the bottom axis on right panel. On Scale tab, set From and To as -100 to 400. Set Major Tick Type as By Increment and Value as 100. Select Vertical on the left panel of the Axis dialog. Set Rescale as Auto. Go to Grids tab, now Horizontal shows on the left panel. Check Y=0 checkbox at the bottom of the tab. On Special Ticks tab, select Bottom on the left panel. On Axis Begin row, set Show as Hide. Otherwise the end tick label of left plot will run into beginning tick label of right plot. Click OK.
  6. Delete legends, right axis tick labels, etc. so the graph will look as follows.
    Update Graph Database 16.png
  7. Set the fill color of left bar plot to LT Cyan.
  8. Set the fill color of right bar plot to be indexed to Column E (Gain/Loss). Enable labels and set Label Form as Custom and Format String as $(Y, .1)% to show Y value with 1 decimal places with % suffix.
  9. You can further customize the axis such as delete those axis frames on the top and right and bottom. Turn on grid lines. Hide the major and minor ticks on the left axis, etc. The graph will look as follows.
    Update Graph Database 18.png

Add Button to Run LabTalk Script

  1. Right click at bottom-right corner of the graph and choose Add Text.... And type Update to create a text label.
  2. Right click on the text Update and choose Properties... context menu to open Text Object dialog. Go to Programming tab. Note: In versions before Origin 2017, choose Programming Control... from the menu to open the Programming Control dialog.
  3. Set Script, Run After to Button Up, and put the following script to the bottom text box. Click OK. 
    double pastDate1 = date(1/1/2003);
double pastDate2 = date(3/31/2003);
double currentDate1 = date(4/1/2003);
double currentDate2 = date(6/30/2003);
// Check if strPast1$, strPast2$, strCurrent1$, strCurrent2$ exist the same time
if((exist(strPast1$, 18) == 18) && (exist(strPast2$, 18) == 18) && (exist(strCurrent1$, 18) == 18) && (exist(strCurrent2$, 18) == 18))
{
	// If yes, set to double value, so to show on the dialog
	pastDate1 = date(strPast1$);
	pastDate2 = date(strPast2$);
	currentDate1 = date(strCurrent1$);
	currentDate2 = date(strCurrent2$);
}
// Dialog for date settings
GetN (Last Seaon) :@G
	(From) pastDate1:@FD0
	(To) pastDate2:@FD0 (-) :@G
     (Current Seaon) :@G
	(From) currentDate1:@FD0
	(To) currentDate2:@FD0 (-) :@G
(Set Date);
// Get the set dates, and convert to string
string strPast1$ = $(pastDate1, D0);
string strPast2$ = $(pastDate2, D0);
string strCurrent1$ = $(currentDate1, D0);
string strCurrent2$ = $(currentDate2, D0);
// Reimport data from database according to the set dates
dbimport iw:=[book1]sheet1!;
range rPivot = [Book1]Pivot1!;  // Pivot table range
layer.x.to = rPivot.maxRows+0.5;  // Set to value of vertical axis
rPivot.runfilter();  // Run the data filter

    The text object turns into a button and the script in it will be triggered when clicking on it.

    Update Graph Database 30.png
  4. Click this button, a dialog will pop up with the default dates (for the first time, if not the first time, the dates set last time are shown).
  5. Now, let's change the period, such as Last Season From 1/1/2004 To 3/31/2004, and Current Season From 4/1/2004 To 6/30/2004.
    Update Graph Database 31.png
  6. Click OK, data in the specified date range will be reimported into worksheet and Pivot1 worksheet will be updated. The graph is updated as well. The bar color is indexed to Gain/Loss column in Pivot1 sheet. You can easily tell there is a loss in one product.
    Update Graph Database 32.png
English | Deutsch | 日本語