Creating Your First Logi Report

Next you’ll add a new Logi report to the project created in the previous section.

Step 1: Create a new database connection

To add a database connection, select the _settings definition, then select its Connections element and double-click the Connection.OLEDB element in the Suggestions panel.

Figure 2.7: Definition Editor Panel after adding Connection element

When an element is selected in the Definition Editor panel, all its possible sub-elements (or “child” elements) are displayed in the Suggestions panel.  To add a new element, double-click the desired element in the Suggestions panel.

Set the Connection String attribute to a valid database connection string. In this example, we’ll use the following connection string to the Northwind SQL Server database.

Provider=SQLOLEDB;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=<machine name>

As an alternative, use the following connection string to the Northwind Traders Access database.

  

Provider=Microsoft.Jet.OLEDB.4.0;Data Source=<folder path>Nwind.mdb;Persist Security Info=False

If you don’t know your connection string, then launch the Set the Connection String attribute wizard, from the Suggestions panel.

Hint: Save yourself some grief; build and test connection strings using the wizard! 

Generally, the database connection created in this step is shared by many reports. In fact, multiple database connections are normally only necessary whenever reports require data from different databases.

Finally, give the Connection.OLEDB element an ID attribute of “connNorthwind”.

About Element Naming Conventions

As you build reports, you’ll be adding elements to them and giving those elements names or IDs. I’d like to recommend that you use “Hungarian Notation” when naming elements.  In this notation, an element ID starts with one or more lower-case letters which are mnemonics for the type or purpose of that element, followed by whatever else you choose. 

For example, we used the ID “connNorthwind” above. The conn part represents the element type, a Connection, and Northwind is the database name. 

Similarly, a table column that displays user names might be named “colUsername”.

This is a very good practice that’s used widely in the programming world and it has a lot of benefits, including consistency and clarity. It also helps IDs be unique, which is frequently required in Logi reports.

Appendix B of this book provides you with some suggested guidelines for formulating these names and provides a suggested standard. You can adopt those suggestions as your standard naming convention if you like or you can make up your own. What’s important is that you follow some standard and be consistent about it. If you have multiple Logi report developers at your office, you may care to agree upon some standard naming convention for your company. 

The examples in this book will follow the Appendix B conventions. 

Step 2: Add a new report definition

To add a new report, right-click the Report folder in the Application panel, and select the Add New File… pop-up menu option. Enter a name for the new report, such as “Chapter2Report”.

Figure 2.8: Application Panel Report folder pop-up menu

The name of the report is simply a unique title for the report definition and is not displayed on the report itself. It is the name of the definition file, with an .lgx extension, in the file system. A report name representative of the content of the report is useful because the name is included in the report web address (URL).

The new definition is now available in Studio and, in the Definition Editor, you’ll see that it contains the default elements Style Sheet, Report Header, Body, and Report Footer. All of these elements are optional, and unused elements may be deleted from the report. In this example, we’ll leave all of these elements in the report.  

Step 3: Create the report table

The first step in any table-based report is to add a Data Table element to the Body element of the report.  

Hint: There is very little difference between the Body element and the Report Header and Report Footer elements. These three elements are provided to logically separate the sections of a report.  

Figure 2.9: Definition Editor Panel after adding Data Table element

Set the ID attribute to a meaningful descriptor, such as "dtCustomers". Note that the report will not run if this attribute is not set. 

Step 4: Define the table data layer

Every Data Table element requires a child DataLayer sub-element. For this example, we’ll add a DataLayer.SQL element. Again, set the ID attribute to a meaningful descriptor, such as "dlCustomerData". Again, the report will not run if this attribute is not set. 

Figure 2.10: Definition Editor Panel after adding DataLayer.SQL element

Hint: Although duplicate IDs are sometimes allowed, a unique ID should be used for each element. I can virtually guarantee you’ll save yourself a lot of grief by making a habit of providing unique names for all report elements.    

In addition, set the Connection ID and Source attributes. In this example, use the Northwind connection you created earlier and the following query as the Source value:

SELECT * FROM Customers

This SQL query creates a list of all of the customers in the database. 

Figure 2.11: Attributes Panel after setting DataLayer.SQL element attributes

Hint: For an excellent introduction to SQL review the free online training materials at http://www.w3schools.com/.

As an alternative, Logi Studio provides the Query Builder wizard to help generate and test SQL queries.

Figure 2.12: Suggestions Panel 

Note that the Run the Query Builder wizard is only available in the Suggestions panel when the Source attribute is selected in the Attributes panel.

Figure 2.13: Query Builder

To use the Query Builder, select and drag the Customers table from the Tables list on the left into the query work space and the column selection dialog box will appear, as shown above. Select the “(All Columns)” option and the appropriate SQL query will appear. Click the Save and Close button in the lower right-hand corner to return to the Definition Editor. 

Step 5: Define the report table columns

At this point, the report will execute, but nothing will be displayed because the columns in the table must be explicitly defined in the report definition. The Data Table Column element is used to define table columns. Add two of these elements beneath (as children of) the DataTable element (dtCustomers). 

Figure 2.14: Definition after adding Data Table Column elements

Set the new elements’ attributes as shown below:

Figure 2.15 Attribute Panel after setting Data Table Column elements attributes

In addition to the Data Table Column element, which is just a container, another element must be added to actually display the retrieved data for each column. Many different elements will do the job but the Label element is the most commonly used. So add a Label element beneath each DataTableColumn element. Again, set the ID attribute to a meaningful descriptor, such as "lblCustomerID". 

Hint: Although setting the ID attribute is occasionally optional, I suggest always setting the ID to a meaningful value for every element. 

Hopefully, by now, you’ve acquired the habit of setting the ID attribute. Therefore, I’ll mention setting it less frequently.

Figure 2.16: Definition Editor Panel after adding Label elements

To actually display the data that’s been retrieved into the data layer, you need to set each Label element’s Caption attribute, using a “token”, as shown below.  

Figure 2.17: Attributes Panel after setting Label element attributes

Initially, the token notation (i.e. @Data.CustomerID~) may seem a little strange, but there is a method to the madness. For now, though, it’s enough to understand that the token simply references data in a column in the DataLayer element. Tokens are case-sensitive and must be spelled exactly as shown. 

At this point, your first report is complete. Go ahead and preview the report by selecting the Preview tab at the bottom of the Definition Editor panel. 

Hint: Previewing a report automatically saves the report definition. Although, changes to the _settings definition are not automatically saved. 

The report should look like this:

Figure 2.18: Report Preview Panel

Hint: When the data layer is empty (i.e. the SQL query returns no results) even though there is no data, the table header row will still be displayed. To hide the header row in this situation, set the DataTable element’s Hide When Zero Rows attribute to “True”.