RIMAS NT/P Software Installation

To: All Users of RIMAS NT/P for Windows

From: The SHARED LOGIC Group, Inc.

Re: Exporting Data to Excel

Date: August 16, 2004

RIMAS NT/P has the capacity to export its data to an Excel spreadsheet. By using a query over a selected database, each column or row in that database is available for inclusion in that query. The information from that query can then be exported to an Excel spreadsheet.

To begin, open Microsoft Excel.

1. From the Menu line, select the Data option.

2. A dropdown list will appear. Select either Get External Data or Import External Data, depending on which version of Excel you are using. Another dropdown list appears.

3. Select New Database Query.

Choose Data Source

The Choose Data Source screen, shown below, appears. From here you will begin to build your query.

1. From the list, select ‘New Data Source’. This should appear at the top of the list.

1. Click OK.

Create New Data Source

On the Create New Data Source screen you will be entering the name for your data source and driver.

If you are using RIMAS NT/P Version 2, you will always select SQL Server.

1. Enter the name of the data source that you want to access. In this example, it is RIMAS_NTP.

2. From a dropdown list, select the driver for the database that you will be using

3. Click the Connect button.

SQL Server Login Screen

The Server Login screen appears so that you can select the Server and Database that you want to work with. Enter the following:

NOTE: Click on the Options button. This will cause the Options section to appear so that you can enter your Database name.

1. Server – Enter the name of the Server where the database resides.

2. Use Trusted Connection – Always make sure that this is unchecked.

3. Login ID – Enter the ID name. For SQL this is ‘sa’.

4. Password – This is the password that is setup for user ‘sa’. Click the Options button.

5. Database – From the dropdown list, select the database that you want to use in your query.

6. Language, Application Name, and WorkStation ID – These will all default.

7. Click OK.

Create New Data Source

On the Create New Data Source screen you will be entering the name for your data source and driver.

NOTE: This is the database that you selected on the previous screen.

1. Check the ‘Save my user ID and password in the data source definition’ box.

2. You will receive a Microsoft Query note. Click Yes.

3. Click OK.

Choose Data Source

The Choose Data Source screen, shown below, appears. From here you will choose the data source that you want to work with. This data source will have been created previously by the user.

1. From the list, select the data source you will be working with by clicking on it to highlight it.

2. Click OK.

Query Wizard – Choose Columns

The Query Wizard Choose Columns screen contains a list of all tables and columns that are available in the RIMAS_NTP database. Whatever is selected on this screen will become part of your query.

Click on a + to expand it and show the fields in that table.

To select a table/field:

1. Click on the table to highlight it, or for a field click on the + to expand it to view all fields for that table.

2. Click on the > button. This will place your selection under ‘Columns in your query’.

3. Repeat this process until all of the tables that you want to include on your query appear in the screen at the right. (See the next figure.)

If you have placed a table in the ‘Columns in your query’ screen that you would like to remove, click on the table and the < button.

IMPORTANT: When constructing a query, know which tables and columns you want to use. A list of all tables and columns is located in Appendix E: Table Layout of the User Manual. Do not rely on your SLG technician to setup your query, however, they certainly will be willing to assist, if necessary.

Query Wizard – Choose Columns - Example

The screen below has tables selected that will run a query over an account and address.

NOTE: After you have selected columns for your query you can Preview the data by clicking the Preview Now button. This data will appear in the ‘Preview of data in selected columns’ field.

Query Wizard – Filter Data

The Query Wizard will also allow you to filter the columns using ‘and/or’ variables. These will become active when one of the selections in the left column is highlighted. The Filter screen allows you to filter up to three different columns.

To continue filtering your query:

1. Click on a selection in the ‘Column to filter’. This will enable the ‘Rows’ portion of the screen.

2. From the dropdown list(s), select the rows that you want to begin/end with.

3. Click one of the radio buttons: ‘and’ or ‘or’.

4. Click Next to proceed.

Query Wizard – Sort Order

The Query Wizard Sort Order screen allows you to perform a sort on up to three columns using either Ascending or Descending order.

1. Using the dropdown list, indicate how the data is to be sorted.

2. Click on either the Ascending or Descending radio button.

3. Repeat this for the other two ‘Then By’ field if you want to continue to sort further.

4. Click Next.

Query Wizard – Finish

The Query Wizard Finish screen gives three selections.

1. ‘Return Data to Microsoft Excel’ will take you back to the Excel spreadsheet with the information that was obtained from the query. (This is the recommended selection.)

NOTE: If you want to save the query that you just performed, click on Save Query and save it to an Excel file.

2. The View Data or edit query in Microsoft Query will allow you to return to the beginning of the Microsoft Query to view the data that was selected or to edit any of the selections.

3. Click Finish

Returning External Data to Microsoft Excel

After the data has been selected and the query constructed, the data needs to be placed somewhere in Excel. The options that you will consider using are the ‘Existing worksheet’ and ‘New worksheet’. Click OK.

Microsoft Excel Spreadsheet

The following spreadsheet shows the result of the list of columns that were selected while you where in the Query Wizard screen. These include the Account name and address. Data in the spreadsheet can be manipulated as it is typically done in Excel.

Below is an example from the previous query.