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.