Working with SQL Reports

• Overview

• Selecting the Data Source

• Specifying a SQL SELECT Statement

• Design-Time Properties

• Editing an Existing Report

• Refreshing the Report's Data

• Redefining the Report's DataSource

Overview

There are 2 different approaches to building a SQL Report.

• Create a named data source, then build one (or more) reports based on that data source definition. In this case you may edit the data source definition separately from the report. The report definition includes the name of the data source.

• Create an ad hoc data source and report. In this case the report definition includes the definition of the data source. You cannot share this data source definition.

In addition there is an important distinct between the data source definition and the report definition.

• The data source definition retrieves a set of records from a SQL database. The process of retrieving these records can use filter (WHERE or HAVING) and order (ORDER BY or GROUP BY) expressions.

• The report definition works with the set of records specified by the data source definition. It can apply additional filter, order, or grouping expressions. These expressions will define what you see on the report.

Selecting the Data Source

To create a new report:

1. Display the Reports tab of the Control Panel.

2. Click New to display the New Report dialog.

3. Select "<SQL Data Source>" and click OK to display the Specify DataSource dialog.

4. There are 2 methods for connecting to a data source:

• Use a named DataSource

• Create an ad hoc DataSource

Specifying a SQL SELECT Statement

1. Click Connect.

2. If you successfully connected to your database, click Next > to display the Specify SQL SELECT Statement dialog.

3. There are 2 ways to define a SQL SELECT statement.

• choose columns from a table or view

• enter a custom SQL SELECT statement or the name of a stored procedure

Design-Time Properties

When designing a report, it is not necessary or desirable to retrieve all the records that the report will ultimately display. You only need sufficient records to display a preview of how the final report will appear.

The advantage of retrieving fewer records is that your report will open up in the Report Editor more quickly because Alpha Five will not have to retrieve all of the records that your SQL query returns. If your query runs quickly, and does not return a very large number of records, then this advantage might not be important. [Picture]

1. Leave the default value of 0 to retrieve all records, or enter the number of records you want to work with.

2. Click OK to display the Report Genie dialog or Cancel to exit from the report design process.

3. Select an option and click OK.

• The Quick Report genie is a preliminary step before using the Report Editor that simplifies the process of selecting, grouping, and ordering records for a report.

• The Report Editor allows you to start specifying all the properties of your new report. These are the same properties set by the Quick Report genie. [Picture]

Note : When you create a report in Alpha Five, you may specify new filter and order criteria that select and order the records that appear in the report. These criteria apply to the records that you have downloaded from the SQL database. The result is that you may create a report that displays fewer records that you initially downloaded, and they may be ordered in a different way.

Editing an Existing Report

Once you have created and saved a SQL report, you will find it listed on the Reports tab of the Alpha Five Control Panel. Note that the Table/Set column will list "<SQLDataSource>". [Picture]

In this case <SQLDataSource> is a passively linked table. If you right click the report ("Customer Locations" in the picture above), you will see several menu selections.

1. Select the report and click Design to open the Design-Time Properties dialog.

2. Optionally, if you want to retrieve new data to use to design the report:

1. Clear the Use cached data check box. The advantage of using cached data is that you will not have to wait for the Report Editor to execute the report's SQL query and retrieve new data.

2. Enter the maximum number of records to retrieve.

3. Click OK to display the Report Editor.

Refreshing the Report's Data

At any time you can refresh the data that you are using for your report design.

1. Open the report in the Report Editor.

2. Click or Report > SQL Datasource > Refresh data.

Redefining the Report's DataSource

1. On the Tables/Sets tab:

1. Right click the passive link table.

2. Select Passive Link Table > Edit DataSource definition... .

2. On the Reports tab:

1. If the Report Editor is open, close it to display the Report tab of the Control Panel.

2. Right click the SQL report and select SQL DataSource > Edit DataSource definition... .

3. Make a choice and click OK. The choices are:

• Edit the existing data source (ad hoc or named)

• Select a named data source

• Define a new data source

Show DataSource Definition

The Show DataSource Definition dialog box shows where and how Alpha Five retrieved the data for a report.

1. Display the Reports tab of the Control Panel.

2. Right click a SQL Report and select SQL DataSource > Show DataSource Definition... to display the Show DataSource Definition dialog box.

3. Optionally, click Edit DataSource to display the Edit DataSource dialog box. Here, you can respecify the data that is retrieved for the report. [Picture]

Preview Records in DataSource

To preview the records that a SQL Report will retrieve from a SQL database:

1. Display the Reports tab of the Control Panel.

2. Right click a SQL Report and select SQL DataSource > Preview Records in DataSource... .

Save Records in DataSource to a Local Table

Refer to Working with Passive Linked Tables.

See Also

Enterprise Reporting

Supported By

Alpha Five Version 7 and Above (Requires Enterprise Reporting License)