Adding Interactive Report Paging

Many reports have the potential of displaying an overwhelming number of rows. While viewing all these rows is sometimes necessary, in most cases, displaying only a “page” of the report is more appropriate. Logi Studio provides excellent support for displaying only one report page at a time while allowing the user to interact with the report to navigate through the its pages.    

We want to use some nice navigation buttons for this purpose and so you’ll first need to add the button images to your project. To do this, select the Support Files tab in the Application panel. Right-click the Image item and select Add Existing File… from the pop-up menu:

Figure 2.33: Support Files tab in the Application Panel

In the browser, navigate to and select:

C:\Program Files\LogiXML IES Dev\AME\LgxPackages\page_first_en.gif   

Repeat the process for all four “page_” files. When complete, the Support Files tab should look like this:

Figure 2.34: Support Files tab with all four images added

Now switch back to the Definitions tab and our report definition. To implement interactive paging to a report, add the Interactive Paging element to the Data Table element, as shown below: 

Figure 2.35: Definition Editor Panel after adding InteractivePaging element

This is another element that does not have an ID attribute. Set the other attributes as shown below. Note that when you select the First Page Caption attribute, the names of the images you just added to your project appear in the Suggestions panel. 

Figure 2.36: Attributes Panel after setting InteractivePaging element attributes

Hint: The easiest way to add interactive paging is to use the “Add interactive paging controls” wizard available in the Suggestions panel when the Data Table element is selected. 

If you use the wizard, it will automatically place the navigation button image files in the Support Files/Images folder in the Application panel for you. If desired, these images can be replaced with custom images. 

The Page Row Count attribute controls the number of rows displayed on each page. The Caption Type attribute determines whether the paging controls are text or images. If the Caption Type attribute is set to “Image”, then the First/Last/Previous/Next Page Caption attributes define the images to display for the corresponding paging controls. If the Caption Type attribute is set to “Text”, then the First/Last/Previous/Next Page Caption attributes define the text to display for the corresponding paging controls.  The Location attribute controls the location of the paging controls. Valid values are “Top”, “Bottom”, or “Both”.  And, the Hide When One Page attribute controls whether the paging controls are displayed whenever the report consists of a single page. 

Each page optionally displays a page number caption indicating the current page number, and the total number of pages (i.e. Page 2 of 45). The Show Page Number attribute controls whether the page number caption is displayed. Even the page number caption can be customized using the Page Number Caption and/or Page of Caption attributes. The Page Number Caption attribute defines the text to display in place of the word “Page” in the “Page x of y” caption. And, the Page of Caption attribute defines the text to display in place of the word “of” in the caption.    

The end result of all this looks like:

Figure 2.37: Report Preview Panel

Note that only 20 records are being displayed. Clicking the paging buttons allow you to navigate through the report and entering the page number takes you directly to the specified report page. 

Hint: We’ll discuss how to remove the border around images on the report in a later chapter. 

 

Adding a Report Summary Row

Another common feature of reports is a summarizing row calculating the sum or average of the values in a column or similar calculations. Again, Logi Studio provides this feature with minimal effort.

To add a summary row to a report, add a Summary Row element to the Data Table element (dtCustomers):

Figure 2.28 Definition Editor Panel after adding SummaryRow element

Set the element’s ID to “CustomerTotalColumn” and set its Caption to “Total:”

Figure 2.29: Attribute Panel after setting SummaryRow element attributes

Next, add a Data Column Summary element beneath the desired Data Table Column element. 

Figure 2.30: Definition Editor Panel after adding Data Column Summary element

Set the Function attribute to the desired function: Count. Then set the Data Column attribute to the desired column name.  

Figure 2.31: Attributes Panel after setting Data Column Summary element attributes

By default, the summary row displays the summary value defined by the corresponding Data Column Summary elements. 

The Summary Row element can be customized by adding Column sub-elements to it but we won’t get into that here. 

In the Preview feature, the bottom of the report should now include the summary row, as shown below:

Figure 2.32: Report Preview Panel

Adding Sorting By Report Column

One of the most desirable features of many reports is the ability to immediately sort the rows based on a specific column. Logi Studio makes the implementation of this sorting capability almost effortless.

To allow sorting by column in a report, add a Sort element to the desired Data Table Column element and set its Data Column attribute to the column used to sort the rows (i.e. CompanyName). In addition, it is sometimes necessary, based on the column data type, to set the Data Type attribute to specify the type of data being sorted.

Figure 2.24: Definition Editor Panel after adding Sort element

Note that the Data Column value does not use a token but instead uses the actual column name from the table and that Sort is one of the few elements that doesn’t have an ID.

Figure 2.25: Attribute Panel after setting Sort element attributes

You could even sort by more than one column. Simply set the Data Column attribute value to a list of column names separated by commas.

The result should look similar to the section of the report below:

Figure 2.26: Report Preview Panel

Clicking the Customer Name header sorts the report in ascending order by company name. Each time the header is clicked, the sort order toggles between ascending and descending, as shown below:

Figure 2.27: Sort order changed after header clicked

Hint: The default sort sequence is Ascending but this can be configured using the First Sort Sequence attribute.  

Debugging Reports

Before we continue adding additional features to our report, I think it is important to discuss the debugging capabilities of Logi Studio. Logi Studio provides the following features to help in debugging your reports.

The first feature is in the Attributes panel. Red asterisks (*) indicate required attributes.

The second feature is application/report validation. Logi Studio provides three ways to perform validation:

• To validate a report, select the report in the Application panel, and click the “Validate” button in the Application panel toolbar.

Figure 2.20: Validate button in Application Panel

• To validate all reports, select the Reports folder in the Application panel, and click the “Validate” button in the Application panel toolbar.
• To validate the application, select the Definition folder in the Application panel, and click the “Validate” button in the Application panel toolbar.

The third feature is the Application Trace debugging page. To enable the debugging page, in the _settings definition set the General element’s Debugger Style attribute to “Debugger Links”.

 

Figure 2.21: Attributes Panel after setting the Debugger Style attribute

Setting this attribute causes a “Debug this page” link to be added at the bottom of all pages. To only display the debug web page when an error is encountered, set the Debugger Style attribute to “Error Detail”.

Hint: In production, set the General element’s Debugger Style attribute to “No Details” to avoid displaying sensitive information to users.  

Selecting the debug link on a page will display the Application Trace web page. This page provides wonderful insight into what’s going on behind the scenes. In particular, it includes the data layer source (SQL) and data, security information (Authenticated User, User Roles, and User Rights), request tokens, and performance metrics. 

To view the SQL and data associated with a data layer, preview the report, and select the “Debug this page.” link at the bottom of the report. 

Figure 2.22: Application Trace page data layer debug information

Click the “View Data” link to display the data associated with the data layer: 

Figure 2.23: Data associated with the data layer

Notice that the data is in XML format, where each data layer row is represented by an XML element and each column is represented by an XML attribute. 

Another debugging feature helpful in pinpointing bottlenecks in a report is the duration column in the Application Trace. The duration metric indicates the elapsed processing time for each event during the rendering of a report.   

A Word about Security

There are a few different aspects of security you need to consider when developing a Logi application, including database server security, web server (IIS) security, and ASP.NET security.

First, let's address database security when using Microsoft SQL Server. If a user ID and password are included in the Connection String (defined in the Connection.SQL element in the _settings definition), then the specified credentials are used to access the database using SQL Server Authentication.

Hint: These SQL Server credentials are for SQL Server Authentication, not Windows Authentication.    

Otherwise, if the Connection String specifies integrated security (“Integrated Security=SSPI”), then the web application security credentials are used to access the database using Windows Authentication.

Hint: The “Trusted_Connection=Yes” or “Trusted_Connection=True” or “Integrated Security=True” connection string attributes are all equivalent.  Also, integrated security only applies to Microsoft SQL Server.    

But, what are the “web application security credentials”?

By default, the web application security identity is configured to execute the web application with the default ASP.NET Windows NT user account: <user_machine>\ASPNET

So, when the connection string specifies integrated security, the default ASP.NET Windows NT user account must have access to any database accessed by a report. In most production environments, this user account doesn’t have access to the database.

Hint: In a development and test environment, the default ASP.NET Windows NT user account is often given access to the database server.    

At this point, we have the following options:

• Grant access to the database to the default ASP.NET Windows NT user account (not recommended)
• “Impersonate” a user account with access to the database (recommended). But, how do we impersonate a user account?

To execute the web application under the identity of the current authenticated user, locate the web.config file for your Logi application (it’s in the application project folder) and, using a text editor like Notepad, add the following XML element to it:

<identity impersonate="true" />

For anonymous access, the user account credentials configured in the IIS management console are used to access the database. Otherwise, the credentials of the currently authenticated user account are used to access the database.

Web server security is configured using the IIS Manager tool. Select the Logi application’s virtual directory in the console. By default, the virtual directory is located in the IIS tree view panel under the Default Web Site node (i.e. Internet Information Services -> (local computer) -> Web Sites -> Default Web Site -> MyFirstApplication).

Right-click the application's virtual directory and select the Properties menu item. Select the Directory Security tab. Select the Edit command in the Anonymous access and authentication control panel on the Directory Security tab. Confirm Anonymous access is enabled. The web security identity is configured in the Anonymous access panel.

Figure 2.19:  Anonymous Access and Authentication Control dialog box for IIS 6.0 

Note that the dialog box shown above may be arranged slightly differently depending on which version of IIS you’re using.

By default, the account used for anonymous access to the web site is IUSR_<user_machine>. Like the default ASP.NET Windows NT user account, in most production environments, this user account doesn’t have access to the database.

At this point, we have the following options:

• Grant access to the database to the account used for anonymous access (not recommended)
• Change the account used for anonymous (not recommended)
• Impersonate a user account with access to the database (recommended). But, how do we impersonate a specific user account?

To execute the web application under the identity of a specific user, locate the web.config file for your Logi application (it’s in the application project folder) and, using a text editor like Notepad, add the following XML element to it:

<identity impersonate="true" 
          userName="<user_name>" 
          password="<user_password>" />


There is no single security approach that is perfect for all environments. But, in most production environments, I recommend using integrated security and impersonating a specific “application” user with access to the database server and any other necessary resources.

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”.       

Creating Your First Logi Application Project

Every Logi report is contained within a Logi application project. Of course, an application project can, and often does, contain many reports. In this section, you’ll be creating an application project that will ultimately contain all the reports created throughout the book.

Step 1: Create a new Logi application project folder

In Logi Studio, a new Logi Report application is created by clicking the New button:

or by selecting the File-New Application item from the main menu. 

Figure 2.1: File menu

You’ll be asked to designate an application project folder. Let’s decide to call our project folder MyFirstApplication and place it in C:\Inetpub\wwwroot\. Create the new folder, in that location, with that name, and select it. Click OK. 

Hint:  In the future, it may be more useful to create your application project folders in a development folder instead of the root folder of IIS. 

The wizard will now create appropriate folders and files in your project folder and open the project for you in Studio. 

By default, the Prepare a New Application wizard is launched. However, for this example, you’ll manually prepare the new application so select Cancel to exit the wizard. 

Step 2: Register the new application

Every Logi application is a web application and must be registered with the web server. Logi Report provides a wizard to register applications. 

Warning: The Register this application in IIS wizard only works when the web server is IIS. 

Select the Register this application in IIS wizard in the Suggestions panel (lower right-hand corner). 

Figure 2.2: Suggestions Panel

Note: This option is only available whenever the _settings definition is selected in the Application panel (upper left-hand corner).

The wizard will prompt you for the Application Server Name. Click Next to accept the default value (localhost). If Logi Server was not installed on the same machine as Logi Studio, then enter the name of the machine with Logi Server installed.

If the application is registered successfully, you’ll be prompted to select the Finish command.

Step 3: Set the Logi application path

Select the Path element in the Definition Editor panel (center) and enter a value for the Application Path attribute in the Attributes panel (upper right-hand corner). If you selected localhost for the Application Server Name earlier, the value will be:  http://localhost/MyFirstApplication. Otherwise, substitute the appropriate machine name for “localhost”.

Figure 2.4: Attributes Panel – Application Path attribute

Step 4: Set the browser caption

The HTML page caption displayed at the top of the browser windows can be set by selecting the Application element in the Definition Editor panel and entering the application title in the Caption attribute in the Attributes panel. The application caption is rendered as the value for the HTML <title> tag.

Figure 2.6: Attributes Panel - Caption attribute

The default report to display whenever a user navigates to an application without specifying a report is controlled by the Default Report attribute in the Attributes panel. By default, the report with the name Default is displayed. 

Hint: A quick way to set the default report is to right-click the report in the Application panel, and select the Set as Default menu item. 

For example, if the user navigates to http://localhost/MyFirstApplication, then the report dis-played is the Default report. 

When you browse the report, the address displayed by the web browser in its address bar will be:

http://localhost/MyFirstApplication/rdPage.aspx. 

This is because the ASP.NET web page “rdPage.aspx” handles the request all reports. For reports other than the default report, the “rdReport” parameter is required, and indicates the report definition. So the address

http://localhost/MyFirstApplication/rdPage.aspx?rdReport=Default 

is equivalent to 

http://localhost/MyFirstApplication/rdPage.aspx.

Chapter II. Creating Basic Reports

In this chapter, you’ll create your first Logi application project and report using Logi Studio. Your report will implement sorting by column, summary row, and interactive paging. While this may seem like much more than a basic report, with Logi Studio it’s a snap.

The purpose of the report is basic (“display a list of customers in the database”) but the concepts you’ll learn apply to even the most complex report.

Upgrading

Previous versions of Logi reporting products were based on the .NET 1.x framework. Starting with version 8, the products use .NET 2.0. Therefore, when upgrading existing reporting applications to the latest version of Logi reporting products, the existing applications must be upgraded to .Net 2.0.

Figure 1.2: Logi Report Server Manager

To upgrade an existing reporting application, first use the Logi Server Manager. In it, simply select the Logi application(s), select the latest version from the Available Versions dropdown list, and click the Update Version… button.

You’ll also need to change the properties of your application’s virtual directory on the IIS server, using the IIS Manager utility (see next page).

Figure 1.3: IIS 6.0 web application Properties dialog box

To upgrade the web application in the IIS Manager, select and right-click its virtual directory and select Properties in the pop-up menu. Select the ASP.NET tab, and select the installed .NET 2.0 version as shown above. Click the Apply button.

If you plan to use both .NET 1.x and .NET 2.0 applications simultaneously, depending on the version of IIS you’re using (later than IIS 5.x), you may need to create Applications Pools to isolate them from one another. The LogiXML DevNet web site has information about how to implement Application Pools.

About the Examples in this Book

All of the examples used in this book are available for download on the LogiXML DevNet website. Instructions for downloading the example reports are provided with the electronic version of the book. The example application includes a menu to aid in the navigation of the example reports.

Figure 1.1: Example Main Menu

Installation

Since Logi Report is built on top of the Microsoft .NET 2.0 framework, the framework must be installed before installing this product. In addition, Microsoft Internet Information Server (IIS), or another web server that supports .NET, must be installed. For the purposes of this book, we’ll assume the web server is IIS.

IIS is supported on Microsoft Windows XP Pro, Windows 2000 Server, Windows 2003 Server, and Windows Vista (Business, Enterprise, and Ultimate).

Note: Microsoft Windows® XP Home Edition does not support IIS.

The order of installation of this supporting software is important! The .NET 2.0 framework needs to be installed after IIS.

In addition, all the samples in this book are based on the Northwind database included with Microsoft SQL Server or the Northwind Traders database for Microsoft Access 2000.  Therefore, Microsoft SQL Server (or the MSDE) or the Microsoft Access Northwind Traders sample database must be installed before running the examples from the book. The Microsoft Access 2000 Northwind Traders sample database is available for download from the Microsoft website (www.microsoft.com).

Hint: In a hosted environment, the Logi Server part of Logi reporting products must be installed on the host server.

The LogiXML DevNet web site has several excellent documents and a Flash-based demonstration that provide step-by-step guidance through the installation process.

Chapter I - Introduction to Logi Reporting Products

The purpose of this chapter is to introduce you to the different versions of Logi reporting products and help you with an installation. I believe the best way to learn is through examples. However, if you’ve already installed your Logi reporting product and you're ready to start building reports, feel free to skip to the next chapter.

Versions

There are three different versions of the LogiXML reporting products. The most obvious difference between the versions is the cost.

Logi Report, which is available for free, is not a stripped-down or “crippled” version of the reporting product. This version contains all of the functionality required by many web reporting applications.

Hint: The latest version of Logi Report is available for free at http://www.freereporting.com/.

For example, the following popular features are now included in the free version of the product:

• Multi-processor servers
• PDF exporting
• Plug-ins

Logi Info Lite adds report scheduling and delivery, process tasks, and integrated element-level security capabilities.

Logi Info adds dashboards, portal capabilities, and additional Chart and Graph elements such as Heatmaps, GoogleMaps, and animated gauges.

All of the elements appear in the each version of the LogiXML reporting products but the elements not supported by a particular version appear as “grayed-out” (inactive).