MainBoss Newswire

February 2006

 

Topics:

  1. Exporting MainBoss 2.9 Reports to Microsoft Excel using ODBC
  2. Upcoming Tradeshows Baltimore, Chicago and Boston

 

******************************************

1. Exporting MainBoss 2.9 Reports to Microsoft Excel using ODBC

Many users export their MainBoss reports to Microsoft Excel so that they can create their own customized reports. MainBoss can export reports either as a .dbf or as a .csv file, both of which are readily opened using Excel. However, Excel is unable to read memo fields which are used for the description of a Work Order and for comment fields found throughout the MainBoss program. Users who are looking at an exported MainBoss report in Excel will frequently call in asking why the work order description or the closing comments fields are blank. Microsoft defines the ODBC (open database connectivity) driver as “a program file used to connect to a particular database”. With ODBC you are able to view all fields in MainBoss, including text fields that would otherwise not be shown (such as descriptions and comments).

 

Following are step-by-step instructions on setting up ODBC. For additional information, please refer to your computer’s Help option (Start->Help). Don’t let the number of steps scare you away! Parts 1 and 2 described below only need to be done once and being able to view your memo fields is definitely worth the effort.

 

Part 1: Creating a Common Directory:

This directory will contain all of the reports you export from MainBoss. The ODBC setup process requires one specific folder where the files to imported into Excel can be found. Therefore, create a new directory on your computer’s hard drive, for example My Documents\MBExports.

 

Part 2: Setup of ODBC

In order to use ODBC you have to install and set up the appropriate driver. Since MainBoss 2.9 is based on FoxPro tables, you need the Microsoft Visual FoxPro Driver. Follow these guidelines or refer to your Windows Help for assistance (keyword ODBC):

    1. Open Microsoft Excel and go to the Data menu (note that these steps are given for Microsoft Excel 2003). Choose ‘Import External Data’ and then click on ‘New Database Query’.
    2. Under the Databases tab, click on <New Data Source> and click OK.
    3. The new window contains four questions, the first being: What name do you want to give your data source? Type in an appropriate name such as ‘MainBoss Exports’.
    4. The second question asks: Select a driver for the type of database you want to access. From the pull-down menu, choose ‘Microsoft Visual FoxPro Driver’.
    5. The third step is to click on the Connect button. This opens a new window where you choose a Database type (choose ‘Free Table directory’). You also specify the path to your directory that you have set up for the ODBC process (See Part 1 above). Click on Browse to locate that directory (i.e. My Documents\MBExports). Now click OK. Verify that you have the correct path and click OK again.
    6. The fourth step in this Setup is optional and need only be used if you plan on using the same export file over and over again. For our purposes we can leave this field blank. Simply click the OK button at the bottom of the window. Your setup is now complete and you are back in the ‘Choose Data Source’ window. Click the OK button to save your setup.
    7. A message will appear: “The data source contains no visible tables”. Just click ok to this message and cancel out of the windows (when asked if you want to continue editing this query in Microsoft Query, choose NO) until you are back in the main Excel screen. You can now exit Excel or leave it running in the background.

 

Part 3: Using ODBC to view your MainBoss export in Excel

Now you are ready to use ODBC to view your exports, including any text/memo fields.

  1. Export the desired data from MainBoss. For example, run the history report, setting up all the options to get the desired output, and then click on Export. Save the resulting file to the specific directory that you created for all MainBoss Exports, as outlined in Part 1 above (for example: My Documents\MBExports). Give your file a name that is easily recognizable (for example: HistReportFeb06). Make sure the Save as Type field refers to Database files (*.DBF). Exit MainBoss or leave it running for later use.

 

  1. Open Microsoft Excel and go to the Data menu. Choose ‘Import External Data’ and then click on ‘New Database Query’. Find the Data Source that you created in Part 2 above (i.e. MainBoss Exports). Then click on OK.

 

  1. Now the Query Wizard will ask you to choose your columns. You will notice that your exported file ‘HistReportFeb06’ is shown in the left-hand window. Click on the plus sign beside this file to reveal all the columns that are available for adding to the Query. If you want to add all the columns simply highlight the ‘HistReportFEb06’ entry and click on the > button. Otherwise highlight each individual entry (such as wokey, wonum, etc) and click the > button each time. When all desired columns show up in the right-hand window, click on Next >.

 

  1. Here you can add further filters to your query. For example, you only want to display work order numbers greater than 1200. Highlight ‘wonum’ and enter the appropriate criteria. For example you might enter: ‘wonum’ ‘is greater than’ ‘02100’ (type 02100 in manually). If you are not sure what all the columns (i.e. wonum) refer to, please see the MainBoss 2.9 Database Documentation file, which is available on the MainBoss CD. When finished click Next.

 

  1. The next window will allow you to choose the Sorting Order. Enter your preferences (i.e. sort by ‘wonum’) and click Next >.

 

  1. The final step in creating your query asks what you would like to do with it. Choose: ‘Return Data to Microsoft Office Excel’ (the first option). Click Finish.

 

  1. You will now be asked where in Excel to put the Data. Choose the appropriate worksheet and location (i.e. existing worksheet, location =$A$1). Click OK.

 

  1. The resulting information will allow you to view all the data in your report, including text fields such as the description (wodesc) and any comments (wocomment).

 

  1. Now format the information to your preferences to create customized reports and charts.

 

******************************************

2. Upcoming Tradeshows

Thinkage will be attending the following tradeshow to demonstrate MainBoss:

·        National Facilities Management and Technology (NFMT) Show at the Baltimore Convention Center, Baltimore, MD from March 7 to 9, 2006. Visit us at booth 1379. http://www.nfmt.com/

·        The TFM Show at the Navy Pier, Chicago, IL from April 10 to 11, 2006. Visit us at booth 525. http://www.tfmshow.com/

·        The New England Facilities Expo at the World Trade Center in Boston, MA from April 19 to 20, 2006. Visit us at booth 610. http://web.fminnovations.com/News/newengland.asp