Using DataLinks and the Excel Business Wizards

DataLinks allows you to generate custom reports on the fly from all your data sources, using drag and drop tools to build the information you need into a report. By directly linking your database(s) to a reporting tool like Excel, you can create up-to-the-minute reports for your business needs without rerunning database queries.

Once you’ve generated a report, the data in it is “live” if desired. You can also create “non refreshable” reports if desired. This means that when the database is updated, you can refresh the report or distribute reports that are a snapshot. You can also make the report refresh automatically each time you open it.

DataLinks supports all data types in its generic category but supports extended report generation from the following data sources:

SQL Server
Oracle
Access
DB2 Client Server
DB2 AS400
CSV
Excel

Normally, you’ll generate the report in an Excel spreadsheet, where you can manipulate the data further, create graphics, and print. You can also generate the report in XML or CSV, which allows you to manipulate the data with other third-party tools.

Using DataLinks is very simple. There are two configuration steps – you’ll only need to perform the first step once per user and the second step once per data source.

Configure your user preferences.
Configure the data source from which you’ll extract information.

There are three steps to creating reports:

Build queries.
Run queries
Generate a report.

Start DataLinks or Excel

Start DataLinks by selecting it from the Windows Start menu. You’ll see the following screen:
Opening screen

The panes you see have the following functions:

Database – shows the tables, table relationships, and database views available in the selected database.
Data sources – shows the types of databases on your network, as well as the names you have assigned to individual databases.
Selection – displays the fields from the tables or views you’ve selected from the current database to generate the report with.
Criteria – allows you to set criteria to filter the data at the query level when you generate the report. (Criteria are based on Boolean logic.)
Preview/Preview XML – allows you to preview the data in the query before you actually generate the report. You can preview the data either in Excel table form or in XML. For quick results the top 25 icon is selected by default.

Configure User Preferences

To begin using DataLinks, you must first set up user preferences. Since DataLinks uses a peruser authentication scheme, the preferences you set will be applicable for you only. To set up user preferences, select Tools, then Options from the menu bar at the top of the screen. The following screen will be displayed:
Options window

A screen displays, asking you to select a connection timeout, which specifies to the application how long you wish it to wait for a connection to the data source before giving up.

This screen also has a place for you to specify a maximum number of rows to a query. This can minimize performance problems when too many people query the database at once.

Use the up and down arrows to specify the number of seconds you want the application to wait.
Use the up and down arrows to specify the number of rows allowed to be returned by a query.
Press the Apply button to save the user preferences.

You can also set your preference for error display and for column descriptions from the database.

Configure Data Source

Each data source type (SQL Server, Oracle, Access, DB2 Client Server, DB2 AS400, CSV, Excel, and Generic) has a slightly different configuration screen. The following example uses the SQL Server configuration screen. Please note that it is important to enter information in all of the required fields within the configuration screen.

To configure a data source, select Tools, then Configure Data Source from the menu bar at the top of the screen. A screen similar to the one below will appear.
Configure the data source

To configure the data source, follow the steps below:

Type in a Friendly name of your choice. This is a name for you to simply identify the data source.
Select the type of database that will be used to create the connection.
Select
Select “Use the Data Link wizard to generate a Connection String”.
Select
Select “Launch Data Link wizard”
Type in the fields that are available in the window that will become activated – it will be a screen similar to the following:
Type in the Server Name under number 1. This is the name of the database server machine in your network. (If you do not know the name of your database server, contact your IT department for this information.) The Database list box for databases available on the Server will be populated.
For SQL Server, if you want Windows to use its authentication methods to make it possible for you to log into the server, check the box and enter your Windows Username and Password. (For other server types, the Windows NT Integrated Security checkbox may not appear.)
Select the specific database you want to query by name from the drop-down list. Press the Test Connection button to verify you have created the connection successfully. Then press the OK button to return to the configuration window.
Select Finish and your data source is configured and available for use through the DataLinks.

OR
You may also supply a connection string to connect to the database, but it’s likely that only more technical users will performs this task. (If you supply the connection, you can test the string by pressing the Test Connection button.) Simply select Finish once you have completed your connection string.
Select Finish and your data source is configured and available for use through the DataLinks.

Build a Query

The heart of what DataLinks can do for you is in the process of generating a report from specific queries that you make from a data source. Each query is a live connection to the database (if a refreshable report is the desired output). This means that whenever you open a report, the data refreshes on demand. If a report is already open, you can refresh directly too. You can build queries in two ways:

Drag and drop the tables, table relationships, or database views you want to include in the query from the Database pane to the Selection pane.
Press the Create a Query icon on the menu toolbar (the blank sheet of paper). This clears the Selection pane of any existing information and allows you to start dragging and dropping from the Database pane.

To save a query, press the Save icon on the menu toolbar, type in a name, and press OK.

To open a saved query, press the Open Folder icon on the menu toolbar, select the query you want to open, and press OK. This will flush whatever data is already in the Selection pane and populate it with the new query.

You can use Criteria to constrain the query. Drag queries into the Criteria pane and set the filters using the available selection fields.

Run a Query

You run a query by pressing the “Preview Query Results” icon

on the menu toolbar. This populates the Preview panes, where you can go to look at the data in Excel table form or in XML.

Preview Query Results

Once you’ve built your set of queries, you can preview the results.

To preview the query results in Excel:

Press the Preview tab. You will see the columns and rows of data displayed, just as if it were an Excel spreadsheet.

To preview the report in XML:

Press the Preview XML tab. The results will display in XML form.

Generate a Report

If you generate the report in Excel, the final output of the DataLinks application is an actual Excel spreadsheet that uses either query or pivot tables.

You can also choose to generate the report in XML, so that you can use the XML to manipulate the data with other tools. (Unless you are an XML expert, we suggest you don’t attempt to use the XML report without consulting someone who is.) The same is true for CSV.

To generate the report in Excel:

Select the File/Export Report To Excel option from the menu toolbar, or
Press the Export Query Results iconon the menu toolbar.

You’ll be prompted by a dialogue box that asks you to set the options for your export to Excel.
Export query

Excel Table Type options:

The Export Query Results to Excel Query Table option will result in your selected output creating a standard grid style report. This feature is limited to 65,535 rows.
The Export Query Results to Excel Pivot Table option will result in your selected output creating a standard pivot table style report. This feature is NOT limited to 65,535 rows and is the equivalent of a standard multidimensional report.

Export Options:
The Use Report Wizard to build worksheet option is used when assistance is needed for formatting the output of the resulting report.
The Build Refreshable Data Table option is used when a live link in the destination data set is desired.

Destination Excel Workbook options:
The Destination Excel Workbook option is used for selecting the workbook that your data set to be exported to.

After you have selected your desired options, press Export Query Results. The desired query table report or pivot table report will land where your active cursor is placed.

Exit DataLinks

You exit DataLinks by selecting File, then Exit from the main menu toolbar. You can also close the application using the X in the upper right corner.

Your Excel reports will be saved through the normal Excel file save process. Although the database connections will be severed once the reports are closed, when you reopen the report, the connection will be re-established and the data refreshed.