Designing, building and accessing Reports

Morfik makes it very easy to design and build great-looking Reports in a manner which is very similar to how you build the interface for your applications. In fact it is very easy for you to mistake a Report for a Form while using the Morfik development environment.

Morfik Reports, like Forms, are composed of bands. In the case of Reports there are, by default, five bands: Report Header, Page Header, Detail, Page Footer and Report Footer. The Morfik development environment includes a specialized Report editor called Report Designer which looks and works just like the Form Designer.


Creating a simple Report

In order to better understand how Reports are created in Morfik, consider an alphabetically sorted list of products such as any company might have. Such a Report exists in the Ajax SMB sample application that is available for download form morfik.com.

Every Morfik Report has two major components: a data source and a visual design. The data source filters which records will be considered in the Report while the visual design provides not only information on what the report should look like, but on how the data will be handled as well.

The visual part of a Report is the most obvious one as it has a very clear visual representation. In Figure 1 you see a screenshot of the Report developed for the purpose of getting a printout of the full list of products. Visible are the products starting with the letter A. Figures 1 and 2 assign numbers to different report bands so that you can see their corresponding appearance and position at both design and runtime. The numbers that are not present in Figure 1 are those that correspond to totally blank bands with no clear border definition.


designing-building-reports-product-list-bands-runtime.png
Figure 1: Product List report as viewed in the Morfik debug browser


If you look closely at the Report displayed in Figure 1 you will note that the data is grouped by the first letter in the product’s name. It seems reasonable that if you are going to print a complete list of products you should group them in some way in order to make the process of finding a product easier.


designing-building-reports-product-list-bands.png
Figure 2: Product List report at design time


In order to achieve this effect we need to use the concept of bands in our Report. The Report bands are very much like the Form bands you saw in the previous chapter, but you can have more of them in reports. In this particular case, as can be seen in Figure 2, we have seven bands (Report Header, Report Footer, Page Header, Page Footer, Group Header, Group Footer and Detail) of which we are, really, using five (the Group and Report Footers are not being used).

It is standard that a report will have a Page Header and a Page Footer band. It can also have a Report Header and a Report Footer band as well as a pair of bands for each grouping created in the Report. In the Report we are using as an example you can see a pair of bands that were created to group the Report by the calculated field expression.

In order to specify what group and sort options you will want in your Report you should right-click the Report Design area in the Report Designer and choose the "Sorting and Grouping..." option. Alternatively select the same option from the menu that is available under Report in the Home tab of the ribbon when you are in the Report Designer. This will bring up the "Sorting and Grouping" dialog which will let you specify which fields/columns in your Report will determine sorting and grouping and if new bands should be created as headers and footers for the specified groupings.


designing-building-reports-sorting-grouping.png
Figure 3: Sorting and grouping configuration for the Product List Report.


Notice that in Figure 3 there is a check box which gives you the option of showing a Group Header and Group Footer. By checking this option you will be making two new bands visible in your Report. These bands will work as header and footer for the group defined by the group properties of a particular field/column. If you check the option of Grouping for more than one field you will get more bands added to your Report. For each grouping field there is a pair of Bands added to the Report.

Where does the data come from?

We have been discussing the formatting of reports and how to design them, but in order to have a report you first need to have data from which to derive a report. So, where does the data for a report come from? A report, just like a Form, has a Datasource property which can reference a table or a query from your project. In order to set this property you should select the Report object in the report designer (click on the detail band and hit escape) and go to the Data page of the Properties Window. The Datasource property will have a small button on its edit area which when clicked will bring up a small dialog, as shown in Figure 4, that allows a selection from within the set of database objects in the project.

Just as with a Form, a report is natively data-aware and is directly linked to the source of the information it will be printing and does not need any auxiliary controls to establish the connection to a table or a query. Though you can access all the information you might come to need in your report by writing code, most report requirements are covered by what can be done with the report designer, which leaves little else to be specifically coded by the developer.


designing-building-reports-data-source-dialog.png
Figure 4: The Data Sources dialog for the Ajax SMB project.


In Figure 4 you can see the data source window in the Morfik development environment from which you can choose a table or query as the source of the information which will go into your report.

Listing 1 – SQL code for the data source of the Product List report.

SELECT ALL
  "Products"."UnitsInStock",
  "Products"."UnitPrice",
  "Products"."UnitsOnOrder",
  "Products"."Discontinued",
  "Products"."Product Name",
  "Products"."ProductID",
  "Products"."SupplierID",
  "Products"."QuantityPerUnit",
  "Products"."CategoryID",
  "Categories"."CategoryName"
FROM
  ("Categories" INNER JOIN "Products" ON ("Categories"."CategoryID"="Products"."CategoryID"))
ORDER BY
  "Products"."Product Name" ASC


In this particular example the leftchar function was used to extract the first character of the FirstName field, allowing us to group the contacts by this letter.

Calling Up a Report

After you have setup a report, how do you bring it up for the user to see? How do you call up a report? Well it is very simple: you do it using the OpenReport function. This function works very much like the OpenForm function. Below you can see a simple example of how to use it.


  OpenReport('"Alphabetical List of Product Report"','');


This line of code is used in the Ajax_SMB sample project to bring up the "Alphabetical List of Product Report" report. Notice that the report name contains spaces and this requires special delimiters which vary according to your language syntax of choice. Basically, all you have to do is add such a call to the OpenReport to the OnClick event of a TextLabel or Button control in your application's interface. When opening a report a Morfik application will automatically do so in a new window or tab, depending on the end user's browser configuration. When the application is running from within the Morfik debug browser, however, it will not open a new window or tab as the debug browser does not support having more than one browser view at the same time. In this case, the report will effectively replace your application in the debug browser. It is still possible to navigate back to the application using the Back button.

Creating a Single Row/Record Report

There are certain situations when a report does not adhere precisely to our usual concept of what a report is i.e. a printout of a list of things. There are situations in which what we need is a single record or row of a table, such as when you print an email, or in our case the information for a specific contact.

Again a special query object was created to obtain the data we need to print our report. This time, in order to get only one specific record from a table, we have to use query and report parameters. First we will look at how to create a query with a parameter and then we will see how we create a report with parameters.

Listing 2 – SQL code for the QryRptSingleContact query.

SELECT ALL
    "tblContacts"."FirstName",
    "tblContacts"."LastName",
    "tblContacts"."WorkEmail",
    "tblContacts"."MobilePhone", 
    "tblContacts"."HomePhone",
    "tblContacts"."WorkPhone",
    "tblContacts"."HomeEmail",
    "tblContacts"."ContactID"
FROM
    "tblContacts"
WHERE
    "tblContacts"."ContactID" = ParamID


You can see the code for this query in Listing 2 and its definition in the screenshots that appear in Figures 5 and 6.


designing-building-reports-query-designer-contacts.png
Figure 5: Defining a query for retrieving a single record.


In order to define a parameter for a query all you need to do is to use it in the criteria of the query. It will be implicitly declared and you will be able to set test values for the parameters. To do so, you should right-click on the background of the Query Designer and select the "Parameters..." option. This option will bring up the Parameters window which allows the definition of as many parameters as you need in a simple list layout. You can see the Parameters Window in Figure 6, with this query’s parameters already entered.


designing-building-reports-query-parameters-contacts.png
Figure 6: Defining values for query parameters through the Parameters Window.


The report itself can be seen in the screenshot in Figures 7 and 8, which show the ContactSheet report in the Report Designer and at runtime, respectively.


designing-building-reports-contactsheet-design-time.png
Figure 7: ContactSheet report, designed for a single record as viewed at design time.


How Do I: Get Started with Morfik Reports?

In this video you learn how to create dynamic PDF reports in Morfik. At the start of the video you get to view Reports in other live Morfik applications before going through the details of creating similar reports in a project. You will see how a report could be launched from a form in a running application.


The player will show in this paragraph

Simple Morfik Report

Defining Report Parameters

Defining the parameters for a report is very simple. Actually, it is just like defining the parameters for a query in the way we have just seen. If you look at the window in Figure 8, you will notice that it looks very much like the one in Figure 6.


designing-building-reports-query-parameters-contacts.png
Figure 8: Parameters definition window for the single record report.


Actually, parameters are so consistent throughout Morfik that all you need to do for one of the Report’s parameters to be passed on as a query parameter is to make sure they share the same name. That is all it takes! In fact, when you assign a query with parameters as the data source for a report, Morfik automatically adds the correspondingly named parameters to the report itself, so that they can be forwarded to the query.

This means that this report, even though it is a bit out of the ordinary, did not require us to write a single line of code. If you look at its "server" module view in the development environment you will only find the, automatically generated, class code, as can be seen in Listing 3.

Listing 3 – Server source code for the ContactSheet, single record, report.

FX Code

Unit ContactSheet;
 
Interface
 
Type
ContactSheet = Class(Report)
    Label_WorkEmail     : TextLabel;
    Control_WorkEmail   : TextLabel;
    Label_MobilePhone   : TextLabel;
    Control_MobilePhone : TextLabel;
    Label_HomePhone     : TextLabel;
    Control_HomePhone   : TextLabel;
    Label_WorkPhone     : TextLabel;
    Control_WorkPhone   : TextLabel;
    Label_HomeEmail     : TextLabel;
    Control_HomeEmail   : TextLabel;
    Container1          : Container;
    Label_FirstName     : TextLabel;
    Control_FirstName   : TextLabel;
    Label_LastName      : TextLabel;
    Control_LastName    : TextLabel;
    Container2          : Container;
    Container3          : Container;
  Private
    { Private declarations }
  Public
    { Public declarations }
End;
 
Implementation
 
End.



In Figure 9, you can see what this report will look like when run from within the application. It duplicates, in a simple way, the style of a printed email as done by Outlook or Outlook Express.

designing-building-reports-contactsheet-runtime.png
Figure 9: ContactSheet report, with a single record at runtime, as viewed in Acrobat Viewer.

Passing Parameters to a Report

After creating a report that takes a parameter you will need to know how to call the report up, and pass the appropriate parameter values. This is very simple and is done by specifying the parameter values in the second parameter of the OpenReport function as can be seen in the example below.

FX Code

Procedure ContactList.Button1Click(Event: TDOMEvent);
Begin
      Cell_ContactID.BandIndex := GetEventSource(Event).BandIndex;
      OpenReport('ContactSheet', '"ParamId='+Cell_ContactID.Caption+'"');
End;


It is important that you notice that the parameters and their corresponding values are passed surrounded by double quotes, within a string. A general example would look like this: ’"ParameterName=ParameterValue"’. Note the double quotes within the single quotes for the Pascal language.

How Do I: Build Reports with Parameters?

See how you could generate dynamic Reports based on Parameters passed at run time by the end user. This is part two on Morfik reports and in this video, you will discover how to create intelligent parametric reports. A parametric Query is used as the data source to filter data and a simple form is created to select and pass a parameter to the report.


The player will show in this paragraph

Morfik Parametric Report

Related Topics

Back to top