This chapter illustrates the process involved in creating reports. A report provides the end user with the ability to view data as a neatly formatted presentation of data. A typical report will be specific to a set of parameters or business concepts, such as a “Latest Orders” list, or in the case of accounts, an “Overdue Payments“ report.
The type of report created will be dependent on the nature of the tasks and reporting procedures required.
The reports can be designed specifically, for individual business cases or can be generated from the system. The following “Report Designer” section describes the process in more detail:
Upon opening Report Designer you will be presented with a screen similar to the following:
The units of measurement in the report can be determined from the horizontal and vertical rulers shown in the display. The units of measurement are usually based on centimeters, but this can be changed if required. This enables the user to design the exact layout of the data and titles on the report on paper before the report is designed. Therefore, it is possible to sue “X” and “Y” measurements.
Reports have three main aspects to them, as you can see on this illustration. These are described as follows:
As the name suggests, this is the name the report will be identified by, such as “Overdue Orders Report”. This title is global to the whole report and will not change.
This header will appear on every page of the report. The headings for each page should be relevant for the data you intend to print on the report. For instance, the heading on this report for each page will contain the “Product Name”, “Quantity Per Unit” etc.
Each report can be grouped by a specific category e.g. “group all prices by product type”. The groupings usually take place in the SQL driving the report. Grouping can also take place at a number of levels.
Hence, in this example there are two levels of grouping. The “CategoryName” and the “CompanyName” reflect the order of the groupings, i.e. group the data by “Category Name”, then by “Company Name”. The order of grouping can change, depending on the SQL or the groupings physically defined in the report.
You will also note the other two tabs in the report. These are described as follows:
This reflects the actual SQL code used in the report. Therefore, if you wish to change the data in the report, or the way it is grouped, for example, you can amend the SQL to reflect the changes you wish to implement in the report.
This feature allows you to see a list of all the data used in the compilation of the report. The information presented here will probably change as a result of any changes made to the “code” section.
To reset designer settings open registry editor for current user and delete "Fast Reports" Folder inside SOFTWARE folder
The above aspects are all described below:
The Report Properties hold the basic information about the report, such as the report description and any comments describing the report in more detail.
All reports are stored internally in the repository database. This facility allows provides the report writer with an easy way to save the report for later retrieval or amendment.
In order to retrieve information for the report, it is necessary to connect to a relevant database. The report connection tab shows which database the user is connected to at time of generating the report.
It is possible to have a number of different projects stored on the ETL configuration. If you wish only to show only connections for the current project you would click this icon.
Every report can have a number of versions associated with it. For instance, one version may show all the prices for a list of products, but a different version of the same report may only show a subset of these prices. Hence, there may be a requirement for different versions of the same report. The “Manage Versions” utility allows you to organize different versions of the report according to your preferences. These may include subsets of your report which can be “branched” e.g. you can have a version 1.0 or a version 1.1. Version 1.1 may have features which are slightly different from version 1.0.
This allows you to add a new version of the original report.
In order to demonstrate how the report process works, let’s create very simple report which returns a list of customers.
First, the user should select an appropriate report group from the objects tree and click “new”.
This will bring up the Report Properties tab as shown. Fill in description and comment edit box if necessary:
Provide a report description, and any comments in the Comment box. Once you are happy with this, click “OK”. The report Wizard Form will then appear. Once the report wizard commences, the user can enter the details necessary for production of the report. The first dialogue box requests the data “source”. The data source can be based on an SQL statement or it can be based on data contained within a table.
The example below will create a report based on a query. Therefore, click “next” and “new query”. At this point we have a choice. We can type in SQL directly or we can use the query builder.
If you are not very confident of your SQL skills, we recommend that the query builder it used in the process. However, in this example we will generate the report from an SQL query:
The example illustrated shows that we have chosen the “Customers” table and chosen from the list of fields within that table for the report.
Once you have the data you wish to include in the report the next step is to choose the report layout. The process of choosing the fields from the table does not actually mean they will be displayed in the report.
The objective here is to include appropriate data, perhaps to include in the query. However, not all fields will be needed in the report. This takes us on to the next step, i.e. deciding from the sub-set of fields which of these will be printed on the final report:
The “Fields” dialogue allows you to select the fields you would like to be printed in the report, from the query you have just created. In the field’s dialogue shown, you can see that all fields in the query have been chosen for the report in this case. We do not wish to group by anything in this report, therefore leave the “Groups” section blank as shown:
Here you can define the preferred layout of the report, including the orientation, and if you prefer a tabular (the standard report format), or if you prefer you can have the information printed in a columnar format. In this case we have chosen a portrait layout, a tabular format and so that the data in the report fits neatly on to the page, we have clicked the “Fit fields to page width” checkbox.
The other information about the report is shown, such as the company information and the address.
Next, we come the to “Style” dialogue. This allows us to specify how the report will “look and feel”, by the use of colours and fonts. In our example there are four report styles to choose from but it is possible to create your own styles. For our report we chose “Corporate”.
Finally, we click “Finish” to create the new report. Once we have done this we see the original Report Designer screen. From here we can choose the “Preview” button as shown:
We are then presented with the report as it will look when it is printed like so:
Using the preview button, the report can be exported to PDF, Html or into an Excel file as a CSV. Reports can also be emailed, either by saving them into one of the formats stated or directly from the preview facility.
It is possible to pass parameters into the Report using variables
It is very important to use valid object names otherwise variables will not be replaced with actual value
Defining SQL Parameters:
:P1 is an SQL parameter
Linking SQL Parameters to variables
Passing Parameters from Package into the report