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
      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
      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
       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
      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
      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 =
      2: CURRENCY : D: rmonth2 =
      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
      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
      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.