Document # 759
====================================================================
Graph R:BASE data with Excel
====================================================================
Product: All Version: All
====================================================================
Catalog: General Information Area: All
====================================================================
Many users have a need a represent their data graphically. One way
to do this is to export the data to a graphics program. Excel's
extensive graphing capabilities and easy-to-use importing features,
make it a great program for creating complex graphs from your R:BASE
data. Although Excel will not read your R:BASE database directly, it
is very easy to transfer data from R:BASE to Excel.
A relational database such as R:BASE does not store its data in a
format that makes it easy to graph. Spreadsheets like Excel
typically store data in an easy to graph format. For example, in the
Concomp sample database, sales data is stored in the Transmaster
table. There is an invoicetotal column, a date column, and a column
identifying the employee who made the sale. A desired graph might be
sales by employee by month -- the x-axis is the month, the y-axis
the sales amount, the body of the graph containing a separate line
or bar for each employee. The data needed for this graph is the
month from the date column, and separate columns of invoice totals
for each employee. We need to convert the data to a spreadsheet
format for graphing. The data can be manipulated in R:BASE or in
Excel.
Two methods of manipulating the data in R:BASE are described here.
Both create a delimited ASCII file of the summarized data. The ASCII
file is imported into Excel and the graph created.
Using a view to format the data
-------------------------------
A view can be used to format the data in R:BASE into the desired
columns for graphing. All summarizing and other necessary
organization of the data is done inside R:BASE. Because of the ease
of exporting data from R:BASE and importing into Excel, this is the
best method to use for automating the task of creating charts in
Excel.
There are many different ways that data summarization can be done in
R:BASE. You can write a program and store results in a temporary
table, for example. One of the easiest is to create a view using the
SELECT functions to summarize the data. Here's a view that
summarizes three months of data:
CREATE VIEW vtabulate (empid, January, February, March) +
empid, (SUM(IFEQ(IMON(transdate)),1,extprice,$0.00)), +
(SUM(IFEQ(IMON(transdate)),2,extprice,$0.00)), +
(SUM(IFEQ(IMON(transdate)),3,extprice,$0.00)) FROM PRODVIEW +
GROUP BY empid
The IFEQ function tests the data and accumulates a total for each
employee by month. Here's what the data generated by the view looks
like:
empid January February March
---------- --------------- --------------- ---------------
102 $176,000.00 $110,000.00 $98,000.00
129 $76,800.00 $34,125.00 $307,250.00
131 $0.00 $152,250.00 $319,750.00
133 $27,000.00 $0.00 $88,000.00
160 $9,500.00 $194,750.00 $140,300.00
After the data has been summarized in a table or view, export it to
a file. To create a delimited ASCII file, use these commands in your
application:
OUTPUT sales.asc
UNLOAD DATA FOR vtabulate AS ASCII
OUTPUT screen
Alternatively, the data can be exported in dBASE, Lotus 1-2-3, or
ASCII delimited format through the Import/export utility
(FileGateway). Excel can easily take any of these file types and
convert them into an Excel spreadsheet with no work on the users
part.
Using a report to format the data
---------------------------------
Create a report in R:BASE as a template for creating an ASCII
delimited file. The steps to accomplish this are as follows:
1.Use the view PRODVIEW as the driving table for a custom report
2.Create a breakpoint for the column empid
3.Place a report header line and a break footer line; delete all lines
for the other sections
4.Create the following variables to summarize the data
1: CURRENCY : D: rmonth1 =
(ifeq(IMON(transdate),1,extprice,0.00)),$0.00))
2: CURRENCY : D: rmonth2 =
(ifeq(IMON(transdate),2,extprice,0.00)),$0.00))
3: CURRENCY : D: rmonth3 = (ifeq(IMON(transdate),3,extprice,0.00))
4: CURRENCY : D: rsum1 = sum of rmonth1
5: CURRENCY : D: rsum2 = sum of rmonth2
6: CURRENCY : D: rsum3 = sum of rmonth3
7: TEXT: F1 : rname = (empfname & emplname)
5.Reset the variables rsum1, rsum2, rsum3 at the empid breakpoint
6.Locate fields rname, rsum1, rsum2, rsum3 on the break footer line,
F1, with commas between each of the fields
7.Type the following text on the RH line: January, February, March
In you application, use the following commands to print the report
to a file. An ASCII delimited file of data with column headings is
created.
OUTPUT prodtab.asc
PRINT prodtab
OUTPUT screen
This is by far the easiest and most powerful method for transferring
data from R:BASE to Excel and then creating graphs. Using this
method, all that needs to be done in Excel is to open the data file,
and start the graph creation process.
Importing ASCII delimited files into Excel
------------------------------------------
Follow these steps in Excel to import an ASCII delimited file:
1.Choose File....Open
2.Choose Text
3.Choose Comma.....Choose Dos or OS/2 (PC-8)
4.Enter Filename to open -- this is the ASCII delimited file
created in R:BASE
5.Choose O.K.
Next, highlight the area to graph and graph the data
Importing dBASE or Lotus files into Excel is as simple as choosing
File...Open, Click on Type. Choose appropriate format.
In addition to manipulating the data in R:BASE and exporting it to
Excel, Excel is one of the few programs that can be used for
graphing R:BASE data that does not require summarization of the data
inside R:BASE. This is accomplished by exporting raw data from any
R:BASE data into Excel and then using Excel's CROSSTAB wizard to
summarize the data. The results can then be graphed. Use this option
if your data is already in the needed columns for graphing.
Creating a Bar Graph in Excel
-----------------------------
There are different ways to turn an Excel spreadsheet into an Excel
graph. You can choose File....New....Chart, or use the shortcut key
[F11]. The best, and easiest, method to start a graph is to
highlight the area to graph, then click on the Chart Wizard Icon
inside Excel. You can follow these steps to create the graph:
1.Use the mouse to draw the area on the spreadsheet to paste the
graph
2.Choose Next
3.Choose 3-D Column
4.Choose 1
5.Data Series In: -- check columns
Use First Column for:-- check category
Use First Row:-- check legend text
Choose O.K.
6.Chart Title:-- 1994 Sales by Employee
Value (Z) --$Sales
Choose O.K.
7.To open as an Excel Chart, double click anywhere in the graph area
8.If needed, go back to Excel Spreadsheet and delete graphed area
(this has no effect on the graph created.
This is how the finished chart looks:
** Place CHART.PCX here
Automating Data Transfer/Chart Creation
If graphing data from your R:BASE database is a weekly, monthly or
even quarterly procedure, you may want to automate this procedure.
Then you won't need to remember all the steps. First, include the
steps to export the data in your R:BASE application; use ASCII
delimited, dBASE or Lotus format. Then, after opening the file in
Excel, the data area is highlighted and then PASTE LINKED to a new
Excel spreadsheet. A chart can be created from the Excel spreadsheet
which is linked automatically. After this is done, the original
spreadsheet should be saved in it's native format (dBASE, Lotus, or
CSV) with the second spreadsheet being saved in the Excel format.
After doing these steps once, all you need do to update the chart is
export the current data from R:BASE, start Excel and open both
spreadsheets and the chart. Excel will automatically update the
links, doing all the work for you.
Excel is a registered trademark of and is available from Microsoft
Corporation, One Microsoft Way, Redmond, WA 98052, 206-882-8080.