Report Writing for an ECO Application

By: Dick Walker

Abstract: This article shows how easy report writing is with an ECO application, given suitable tools. It also demonstrates using derived attributes and OCLColumns in an application and adding ECO support to an existing unit.

One of the beautiful things about using ECO is the fact that the database and its table structure are abstracted away. There is no need to be thinking in both the “object” world and the “relational database” world. You can focus purely on your business objects and their relationships with each other, and forget about how they are persisted to storage.

One of the regular questions on the newsgroup is about how to report in an ECO application. Traditionally all report writing was done on the persistent data, which would mean having to understand how your objects are persisted and having to navigate the tables and make many tedious joins to view the data.

Thankfully, many report tools in the .NET world understand that not all reporting is going to be on the persistent data. Many .NET report tools allow you to use a collection of objects as the data source for reporting. This is done by binding to a list of objects that implements one of the following .NET interfaces: IList: ICollection or IEnumerable. These are simply structures that implement methods that allow them to be navigated through .NET reflection, then enables the objects within the list to be accessed.

In ECO, both an ExpressionHandle and a OCLPSHandle implement IList, meaning they can be used as the data source for a report tool.

In my Ski Hire application, I have used DevExpress’s XtraReports to report on my ECO objects. I will show you how easy this is using XtraReports. Any report tool that recognizes a collection that implements IList as a data source can be used instead of XtraReports.

Creating the Model

Firstly, let’s create a simple ECO model. Using Delphi 2006, go “File”, “New”, “Other”, select “Delphi for .NET Projects”, “ECO WinForms Application”. Call the new project “TimeSheet”.

Click the Model View tab, navigate down to the Package_1 folder, the select the Package_1 icon under the folder, right click and select “Open Diagram”

Hide image
Click to see full-sized image

Using the tool palette, drag 3 classes, name them and add the attributes as shown in the diagram. Create 3 associations and set the cardinality and name the ends as in the diagram.

Hide image
Click to see full-sized image

The model uses 2 derived attributes (a calculated, non-persistent attribute), Staff.FullName and Project.TotalMinutes. The images below show the OCL statements for these derived attributes.

Hide image
Click to see full-sized imageHide image
Click to see full-sized image

Persisting the Objects

Let’s create a database to persist our objects to. If you are using MS SQL Server or MSDE:

open a command prompt, click “start”, “run” then type “cmd” and enter.

type osql /Usa –Psa (substitute “sa” with your username and password)

type create database TimeSheetDB

type go to execute the create database statement

type exit to leave the osql command line

type exit again to leave the Windows command line

To persist our model, in the Project Manager, select TimeSheetEcoSpace.pas, then click on the design tab. Next click on the Data Explorer tab, under BDP, click MSSQL, right click and select “Add New Connection”. Give the connection the name “TimeSheet” and click OK. Expand the MSSQL node, click on TimeSheet, then right click and select “Modify Connection”. Change the Database property to “TimeSheetDB”, set both the username and password properties (in my example both are “sa”). You may have to set “OSAuthentication” to true if MSSQL was configured that way. Click the “Test” button to ensure your connection is correct. Now simply drag the “TimeSheet” connection from the Data Explorer tab onto the EcoSpace design surface.

We need another component to handle the object – relational mapping for use. It is called the PersistenceMapperBDP. Click on the title bar of the Tool Palette, type “Pe” and you will see the list of components filtered so that a selection of PersistenceMappers are visible. Click and drag the PersistenceMapperBDP onto the EcoSpace design surface. Set the Connection property to BDPConnection1, and set the SqlDatabaseConfig property by clicking “SQL Server Setup” in the grey panel below the main object inspector panel.

Hide image
Click to see full-sized image



The next step is to generate the schema in the database. To do this click the “Generate Schema” toolbar button at the base of the EcoSpace designer. It is the 4th button along.

Hide image
ScreenShot023

Now our model has been created and persistence has been configured. Let’s create a basic user interface so we can enter some data. Because the main goal of this article is look at using a report tool with ECO, we won’t spend much time on the interface.

Creating Some Objects

We need some objects to report on. Rather than spend a lot of time creating a GUI, we will simply add the objects when the application starts (but only if the list of work objects is empty).

In the project manager, select WinForm.pas, slect design view, in the Tool Palette , select an ExpressionHandle (click on the text “Tool Palette”, which makes it appear in a bold font, then type “Ex”) and drag into onto the WinForm. Set its name property to “ehWork”, it’s RootHandle to “rhRoot” and it’s Expression to “Work.allInstances”.

Hide image
Click to see full-sized image

Switch to code view of the WinForm.pas class, and locate the constructor code. Add the following lines to the bottom of the constructor code:

  if (ehWork.Element.GetAsCollection.Count = 0) then
    CreateSampleObjects;

The first line is checking to see how many objects are in the ExpressionHandle ehWork. If there are none, we will add some, as well as some projects and staff.

Add a new private procedure called “CreateSampleObjects”. Here is the code to create all of the sample objects…

procedure TWinForm.CreateSampleObjects;
var
  Mal, Tim, Peter, Dick: Staff;
  EcoWhitePaper, EcoAspDotNetExample: Project;
  aWork: Work;
begin
  { Create 4 staff members. }
  Mal := Staff.Create(EcoSpace);
  Mal.FirstName := 'Malcolm';
  Mal.LastName := 'Groves';

  Tim := Staff.Create(EcoSpace);
  Tim.FirstName := 'Tim';
  Tim.LastName := 'Jarvis';

  Peter := Staff.Create(EcoSpace);
  Peter.FirstName := 'Peter';
  Peter.LastName := 'Morris';

  Dick := Staff.Create(EcoSpace);
  Dick.FirstName := 'Dick';
  Dick.LastName := 'Walker';

  { Create 2 projects. }
  EcoWhitePaper := Project.Create(EcoSpace);
  EcoWhitePaper.Name := 'Eco White Paper';
  EcoWhitePaper.Manager := Dick;

  EcoAspDotNetExample := Project.Create(EcoSpace);
  EcoAspDotNetExample.Name := 'Eco ASP.NET Example';
  EcoAspDotNetExample.Manager := Peter;

  { Create some work objects. }
  aWork := Work.Create(EcoSpace);
  aWork.StartTime := DateTime.Create(2005, 12, 19, 20, 10, 0);
  aWork.MinutesWorked := 120;
  aWork.Staff := Dick;
  aWork.Project := EcoWhitePaper;

  aWork := Work.Create(EcoSpace);
  aWork.StartTime := DateTime.Create(2005, 12, 20, 13, 05, 0);
  aWork.MinutesWorked := 155;
  aWork.Staff := Mal;
  aWork.Project := EcoWhitePaper;

  aWork := Work.Create(EcoSpace);
  aWork.StartTime := DateTime.Create(2005, 12, 21, 9, 35, 0);
  aWork.MinutesWorked := 165;
  aWork.Staff := Dick;
  aWork.Project := EcoWhitePaper;

  aWork := Work.Create(EcoSpace);
  aWork.StartTime := DateTime.Create(2005, 12, 15, 9, 5, 0);
  aWork.MinutesWorked := 145;
  aWork.Staff := Peter;
  aWork.Project := EcoAspDotNetExample;

  aWork := Work.Create(EcoSpace);
  aWork.StartTime := DateTime.Create(2005, 12, 15, 14, 20, 0);
  aWork.MinutesWorked := 90;
  aWork.Staff := Peter;
  aWork.Project := EcoAspDotNetExample;

  aWork := Work.Create(EcoSpace);
  aWork.StartTime := DateTime.Create(2005, 12, 16, 11, 15, 0);
  aWork.MinutesWorked := 125;
  aWork.Staff := Tim;
  aWork.Project := EcoAspDotNetExample;

  aWork := Work.Create(EcoSpace);
  aWork.StartTime := DateTime.Create(2005, 12, 16, 20, 20, 0);
  aWork.MinutesWorked := 160;
  aWork.Staff := Mal;
  aWork.Project := EcoAspDotNetExample;

  { Persist all of the objects to the database. }
  EcoSpace.UpdateDatabase;

  MessageBox.Show('Sample Objects Created');

Adding a Report

If you are using DevExpress XtraReports (which is available in the DevExpress component evalutation download from DevExpress and on your Delphi 2006 partner DVD), select File, New, Other, Delphi Projects, Delphi Files, XtraReport V3 Delphi .NET Class. If you are using another report tool, add a new form.

Hooking the Report Form and EcoSpace Together

Now we have a report page, we need to add access to the EcoSpace. To do this we need to add a local variable to the EcoSpace, a new constructor so we can pass a reference to the EcoSpace that was instantiated by the main form, and set the local variable to the passed in reference.

Add these lines to the Implementation uses clause:

    TimeSheetEcoSpace,
    Borland.Eco.Handles,

Add this constructor signature to the interface section and mark the existing constructor with the “overload” directive:

     constructor Create; overload;
     constructor Create(aEcoSpace: TTimeSheetEcoSpace); overload;

Add this variable to the private interface section

     FEcoSpace: TTimeSheetEcoSpace;

Add this property to the interface public section, below the constructors:

     property EcoSpace: TTimeSheetEcoSpace read FEcoSpace;

Add this constructor to the implementation section

constructor TXtraReport1.Create(aEcoSpace: TTimeSheetEcoSpace);
begin
  inherited Create;
  InitializeComponent;
  FEcoSpace :=  aEcoSpace;
  rhRoot.EcoSpace := FEcoSpace;
end;

Normally all of these steps are generated for you when you select a new ECO enabled WinForms unit from the file, new option. However the XtraReports forms have their own inheritance hierarchy, so we need to add the Eco references. These same steps apply to any form that you wish to Eco-enable.

Configuring the Report

We need to add some Eco components to access and select our objects:

Go to design view

Add an Eco ReferenceHandle, set it’s name to “rhRoot”, set it’s EcoSpaceType to “Eco2TestEcoSpace.TEco2TestEcoSpace”, which will be available in the combobox.

Go back to the new constructor and add this line to the bottom:

Add an Eco ExpressionHandle, set it’s RootHandle to “rhRoot”, set it’s name to “ehWork”, set it’s Expression to
“Work.allInstances->orderby(e|e.Project.Name)”.

The “e|e.Project.Name” looks a little odd, it is basically an iterator variable and is a great way of resolving the difference between a class name and an association end name. We want to order by the project name, because we will group the report by project name.

Save and build the project.

Click the report, and set DataSource property to “ehWork”. This will be visible in the combobox.

Now we can add databound labels to the report bands. Firstly we need to add some extra columns to our ExpressionHandle to make some attributes of the related objects visible to the report tool. This is similar to creating a view in a database, as we are creating a virtual object representation.

Click on the ExpressionHandle named ehWork

Click the columns property, and click the button to enter the “AbstractColumn Collection Editor”

Click the “Add” button, name the new column “Fullname” and in the expression property add “self.Staff.FullName”.

Click the “Add” button again, name the new column “ProjectName” and in the expression property add “self.Project.Name”.

Click the “Add” button again, name the new column “Manager” and in the expression property add “self.Project.Manager.FullName”.

Click the “Add” button again, name the new column “TotalMinutes” and in the expression property add “self.Project.TotalMinutes”.

Now we can use all of those object properties in the report design surface. The fields can either be dragged and dropped onto a band, or a databound label dragged and dropped and then bound to the data. In the case of XtraReports, an XRLabel can be dragged onto the report.

We will add a group header band to the report, so right click on the detail band, select “Insert Band” then select “Group Header”. Now click the GroupHeader1 on the report and in the object inspector select the “GroupFields” property and click the button to view the “GroupFields Collection Editor”. Click “Add” and in the “FieldName” property, select “ProjectName” from the combobox.

Add a XRLabel to the GroupHeader1 band, in the object inspector, expand the “databindings” property, expand “text” and select “binding”. Now select “Project” which will put the value “ehWork – ProjectName” into the object inspector. Also add labels for “Manager” and “TotalMinutes” to GroupHeader1 and set their bindings.

In the detail band add labels for “Fullname”, “StartTime” and “MinutesWorked”.

With a few extra labels and lines for presentation our report is finished. The finished design should look like this:

Hide image
Click to see full-sized image

Accessing the Report

To be able to view the report at runtime, add a button to the WinForm.pas design surface, name it “btnViewReport”, set the text to “View Report”.

Add “XtraReport1”, to the uses clause of WinForm.pas

Double click the button and add the following code:

var
  aReport: TXtraReport1;
begin
  aReport := TXtraReport1.create(EcoSpace);
  aReport.ShowPreview;
end;

The Finished Product

Now run the application, click the button and we have a basic report!

Hide image
Click to see full-sized image


Server Response from: ETNASC03