Linking Summary Charts and Detail Reports

A common feature supported by bar charts and pie charts is the capability to drill-down to view the details behind the summary chart.

In the following example, when the user selects a customer bar in the bar chart, the orders for the selected customer are displayed in the details report.

Switch to the Chapter5Report1 definition and set the Chart.XY element’s Chart Type to “Bar”.

To link the bar chart to a report, add an Action.Report element beneath the Chart.XY element. Then add Target.Report and LinkParms child elements beneath the Action.Report element to define the target detail report and the selected customer.

Figure 5.22: Definition Editor Panel after adding the elements 

Set the Target.Report element’s ID and Report Definition File attributes as shown below:

Figure 5.23: Attributes Panel after setting the attributes

And, finally, add a link parameter to pass the selected customer’s ID to the target report. The @Chart token is used to access the data layer in the chart: 

Figure 5.24: Attributes Panel adding the parameter

Now preview the bar chart report and click one of the bars to display the detail report. The process of adding this drill-down capability to a pie chart is exactly the same.

Creating a Simple Scatter Chart

To work with the scatter chart, create a new, blank report and call it “Chapter5Report3”.

Step 1: Create a scatter chart report

Add a Chart.Scatter element to the Body element in the report, and set the Chart Title attribute to a meaningful description (i.e. “Orders by Month”).

Figure 5.16: Definition Editor Panel after adding Chart element

Step 2: Define the table data layer

Every Chart.Scatter element requires a child DataLayer element. In this example, we’ll add a DataLayer.SQL element. 

Figure 5.17: Definition Editor Panel after adding DataLayer element

In this example, set the Connection ID to “connNorthwind” and use the following SQL query as the Source:

SELECT MONTH(OrderDate) AS OrderMonth, 

       COUNT(*) AS OrderCount

FROM Orders

GROUP BY MONTH(OrderDate)

ORDER BY MONTH(OrderDate)

This SQL query creates a summary of orders by month. 

Step 3: Define the column to display on the x-axis and y-axis

Set the Y-axis Data Column attribute to display the “OrderCount” column data, and set the X-axis Data Column attribute to display the “OrderMonth” column data. Set the Chart Title attribute to an appropriate name (i.e. “Orders By Month”).

Figure 5.18: Attributes Panel after setting Chart element attributes

Step 4: Format the scatter chart report

All that remains is to format the chart. In most cases, the default formatting is not accepta-ble. In the example below, the following presentation attributes are changed:

• Set the Height and Width attributes to display a 400 X 600 chart.
• Set the Top Border, Bottom Border, Right Border, and Left Border attributes to provide room for the title and axis labels. 
• Set the XAxisTitle and YAxisTitle attributes to meaningful descriptors.
• Set the Show Data Values attribute to “True” to display the data values on the scatter chart.
• Set the Symbol attribute to a value of “Circle” to define the symbol used to draw the data points.

Figure 5.19: Attributes Panel after setting Chart element attributes

The resulting scatter chart is presented as shown below:

Figure 5.20: The Scatter Chart example

Step 4: Add a trend line to the scatter chart report

Scatter charts often include a trend line and adding one is very simple in Logi Report. Simply add a Trend Line element to the Chart.Scatter element:

Figure 5.21: Definition Editor Panel after adding Trend Line element

Now, the resulting scatter chart is presented with a trend line. You can format the trend line by setting its Color and Line Width attributes.

Creating a Simple Pie Chart

To work with the pie chart, create a new, blank report and call it “Chapter5Report2”.

Step 1: Create a pie chart report

Add a Chart.Pie element to the Body element in the report, and set the Chart Title attribute to a meaningful description (i.e. “Top 10 Customers”). An ID is not required for a chart.

Figure 5.11: Definition Editor Panel after adding Chart element

Step 2: Define the table data layer

Every Chart.Pie element requires a child DataLayer element. In this example, we’ll add a DataLayer.SQL element. 

Figure 5.12: Definition Editor Panel after adding DataLayer element

In this example, set the Connection ID to “connNorthwind” and use the following SQL query as the Source:

SELECT TOP 10 CustomerID, COUNT(CustomerID) AS OrderCount

FROM Orders

GROUP BY CustomerID

ORDER BY COUNT(CustomerID) DESC

This SQL query creates a summary of the top 10 customers in the database based on number of orders. 

Step 3: Define the column to display on the x-axis and y-axis

Set the Data Column Y-axis attribute to display the “OrderCount” column data and set the Label Column X-axis attribute to display the “CustomerID” column data. 

Figure 5.13: Attributes Panel after setting initial Chart element attributes   

Step 4: Format the pie chart report

All that remains is to format the chart. In most cases, the default formatting is not accepta-ble. In the example below, the following presentation attributes are changed:

• Set the Height and Width attributes to display a 400 X 400 chart.
• Set the 3-Dimensional property to a value of “8” to control the depth of the pie.  
• Set the Top Border and Left Border attributes to provide room for the title and axis labels. 

Figure 5.14: Attributes Panel after setting Chart element attributes 

The resulting pie chart is presented as shown below.

Figure 5.15: The Pie Chart example 

Creating Multi-Layer Charts

Often a more complex chart is needed to effectively summarize information. Logi Reports supports combination charts that allow multiple layers and multiple chart types to be included on a single chart.

In this example, we’ll add another layer to the line chart developed in the previous section.

Step 1: Add a second grid layer

To add another layer to the chart, add Extra Grid Layer and Secondary Data Access child elements beneath the Chart.XY element:

Figure 5.8: Definition Editor Panel after adding elements 

Set the Secondary Data Axis element’s Data Title attribute to a meaningful description (i.e. “Freight”).

 

Step 2: Define the data to display in the new grid layer

Modify the data layer’s SQL query to return the total freight costs in addition to the number of orders, as shown below:

SELECT TOP 10 CustomerID, 

   COUNT(CustomerID)  AS OrderCount,

   SUM(Freight) AS Freight

FROM Orders

GROUP BY CustomerID

ORDER BY COUNT(CustomerID) DESC

Set the Extra Grid Layer element’s Data Column Y-axis attribute to the new total freight costs field in the data layer (i.e. Freight). Then set the presentation attributes (Chart Type and Color) to display the data as a brown line chart.

 

Figure 5.9: Attributes Panel after setting attributes

Assuming that the Chart.XY element’s Chart Type is also set to "Line", then when you preview the report, the resulting multi-line chart is presented as shown below.

Figure 5.10: Report Preview Panel showing line chart

Notice that the second y-axis scale (“Freight”) is shown on the right side of the grid chart. 

Of course, it’s not necessary that both grid layers display the same chart type. Simply change one of the Chart Type attributes on the Chart.Grid or Extra Grid Layer element to combine multiple chart types on a single chart.  

Creating a Simple Bar Chart

For the bar chart report, we’ll create a new report.

Step 1: Create a bar chart report

Using the techniques from other chapters, create a new, blank report definition named “Chapter5Report1”. In its definition, add a Chart.XY element to the Body element in the report.

Figure 5.1: Definition Editor Panel after adding Chart element

Set its Chart Type attribute to a value of “Bar” and its set the Chart Title attribute to a meaningful description (i.e. “Top 10 Customers”). An ID is not required for a chart.

Step 2: Define the table data layer

A chart, like a data table, needs a Data Layer element to retrieve the data it will display. Add a DataLayer.SQL element beneath the Chart element. 

Figure 5.2: Definition Editor Panel after adding DataLayer element 

Give the data layer an appropriate ID and set it to use the connNorthwind connection.

Use the following SQL query for its Source attribute:

SELECT TOP 10 CustomerID, COUNT(CustomerID) AS OrderCount

FROM Orders

GROUP BY CustomerID

ORDER BY COUNT(CustomerID) DESC

This SQL query returns a summary of the top 10 customers in the database by number of orders.  

Hint: The SQL “TOP” clause behaves slightly different between SQL Server and Access. SQL Server always returns the number of rows requested, but Access returns more than the number of rows requested when a “tie” is encountered.    

Step 3: Define the column to display on the x-axis and y-axis

Set the attributes for the chart element as follows:

Attribute	Value
Chart Type	Bar
Data Column Y-axis	OrderCount
Chart Title	Top 10 Customers
Data Title	Number of Orders
Label Column X-axis	CustomerID
Label Title	Customer ID
Show Data Values	True

Hint: Use the Extra Data Column element to display multiple bars on a bar chart.    

Step 4: Format the bar chart report

All that remains is to format the chart. Generally the default formatting is not acceptable. 

• Set the Height and Width attributes to display a 400 X 600 chart.
• Set the Color attribute to a value of “Green” to control the bar color.
• Set the 3-Dimensional property to a value of “8” to control the depth of the bars.  
• Set Border Color, Grid Horizontal Color, and Grid Vertical Color to “Silver”. 
• Set Top Border, Bottom Border, Right Border, and Left Border to provide room for the title and axis labels

Set additional attributes for the chart element as follows:

Figure 5.3: Attributes Panel after setting Chart element attributes 

The resulting bar chart is presented as shown below:

Figure 5.4: The Bar Chart example 

We can easily change the chart style by changing the Chart Type attribute to “Area”:

Figure 5.5: The Area Chart example

Or to a "Line" chart:

Figure 5.6: The Line Chart example

Or to a "Spline" chart:

Figure 5.7: The Spline Chart example

Chapter V. Creating Charts and Graphs

In this chapter, we’ll explore the various charts/graphs available in Logi Report. In addition to creating basic charts, we’ll create a multi-layer chart, and add drill-down capabilities to the charts.

Controlling the Report Link Presentation

Controlling the presentation of the links on a report is slightly more complex because a link has one or more “states”. The states for a link are Link, Visited, Hover, Active, and Focus and here are their definitions:

State	Description
Link	A link that has not been visited.
Visited	A link that has been visited.
Hover	A link with the mouse pointer hovering over it.
Active	A link that is active. Normally, this is the period of time between the mouse button being clicked and released.
Focus	A link that has focus.

One method of controlling the link presentation is through CSS classes. For example, adding the two CSS classes below to the style sheet displays all links in black, except hovering over a link displays the link in red.

A
{
   color : Black;
}

A:Hover
{
   color : Red;
}

In HTML a link is represented by the anchor tag <a>. The global style associated with the HTML anchor tag applies to all states where an explicit style is not defined (in this case, Link, Visited, Active, and Focus).

Hint: A link may be in more than one state at a time (i.e. Visited and Hover). Therefore, the Hover state style should appear after the Linked and/or Visit state styles, and the Active state style should appear after the Hover state.  

Many different style attributes can be used to provide feedback to the user. The most common style attributes are: color, background-color, text-decoration, and the font attributes.

Controlling the Report Table Presentation

While controlling the report presentation is similar for all elements, there are a few unique concepts for tables.

Like many elements, the Data Table element includes the Class attribute. It also includes the Alternating Row Class attribute and a few other attributes for convenience: Cell Spacing, Caption Class, and Column Header Class.


1. Formatting the data table rows

If you’re of a certain age, you may remember “greenbar” printer paper. Its alternating row colors made it easy to read long tabular reports. We can do the same thing in Studio.

To format the table rows, add the following new classes to your style sheet and apply them to the Data Table element. Assign the .TableRowStyle class to the Class attribute and the .TableAltRowStyle class to the Alternating Row Class attribute.

.TableRowStyle
{
   background-color : #99CC99;
}

.TableAltRowStyle
{
   background-color : #CCFF99;
}


2. Formatting the table data header/total rows

To format the table header and total rows, add the following new classes to your style sheet. Assign .TableHeaderStyle class to the Data Table element’s Column Header Class attribute and the .TableTotalStyle to the Summary Row element’s Class attribute.

.TableHeaderStyle
{
   background-color : #F0FFFF;
}

.TableTotalStyle
{
   background-color : #F0FFFF;
}

Hint: To change the class of the table header in versions of Logi products earlier than version 7.x, add a class qualified by the TH tag (i.e. TH.TableRowClass {background-color : Green;}).


3. Aligning column data 

To align column data, add this new class to your style sheet and apply the class to the Data Table Column showing numeric data (colOrderCount) elements’ Class attribute.

.ColAlignRightStyle
{
   text-align : right;
}


4. Removing the border around the page control images 

To remove the annoying border around the page controls, add the following style to the style sheet. Note that this is a class that affects the HTML <img> tag so the class name does not start with a period.

IMG
{
   border : 0;
}

Note: This style will remove the border from around all images in a report.


5. Formatting Column Data

The format of the data in a table column is controlled by the Format attribute of the element displaying the data. The Format attribute supports all the capability built into the Microsoft Visual Basic Format function.

The Format function supports predefined formats and or user-defined formats.

Hint: See MSDN® at http://msdn.microsoft.com/, for complete description of the syntax of user-defined formats.

For example, to format the Order Date in the sub-report, set the Label element’s Format attribute to a value of “Short Date”. For convenience, the Suggestions panel provides a list of the predefined formats.


6. Stretching a sub-table to fill the table area

Set the Sub-Report element Width and WidthScale attribute to 100% to cause the sub-report to match the width of the table.


7. Controlling the space between table cells

Set the Data Table and/or Sub-Data Table element(s) Cell Spacing attribute to adjust the space between table cells.

8. Controlling the table border

Set the Data Table and/or Sub-Data Table element(s) Border attribute to draw a border, of the specified width, around the table.

At this point, the report should look something like this (image was clipped for space):

Figure 4.9: Report Preview

Controlling the Report Presentation - Formatting the report header/footer

The reason styles are said to be “cascading” in CSS classes is that they apply in a hierarchical manner. A class applied to the Report (top) element in a definition applies to the whole report but style properties of that class may be overridden by other classes applied to elements lower down within the report.
To format the header and footer for a report, add the following new classes to your style sheet and apply the classes to the corresponding header and footer Label elements’ Class attributes.

.HeaderStyle
{
   background-color : Green;   
   font-size : large;
   color : White;
   text-align : center;
   width : 100%;
   padding : 5px 5px 5px 5px;
}

.FooterStyle
{
   background-color : Green;
   font-size : x-small;
   color : White;
   width : 100%;
   padding : 3px 3px 3px 3px;
}

A few new style properties are introduced in this example. The text-align style property is used to center the header, the font-size style property is used to control the relative size of the fonts, the width style property is used to stretch the header/footer the width of the browser window, and the padding style property controls the padding around the header/footer.

At this point, the report is should look like that shown below. Note that the sample below is being shown in a browser window, not the Preview window in Studio. This was done to allow the centering to look better by making the browser window smaller.

Also, remember that when you browse from Studio, you’re browsing the default report. You may need to right-click the Chapter4Report1 definition in the Applications panel and set it as the default in order to browse it.

Figure 4.8: Report Preview

Controlling the Report Presentation - Changing the font color on a report

Changing the font color on a report is accomplished by specifying the color style property. Add this class to your style sheet:

.ReportStyle
{
   background-color : OldLace;
   font-family : Verdana, Geneva, Arial, Helvetica, sans-serif;
   color : #663300;
}

Colors can be specified using actual color names (“Red”, “Green”, “OldLace”) or using HTML hexadecimal color codes (“#663300”). Edit your style sheet class now to contain this information.

The TopStyle Lite style sheet editor includes a useful Color Picker tool that will display hexadecimal color codes.

Figure 4.7: The TopStyle Lite Color Picker Tool

You can invoke the Color Picker in TopStyle Lite by selecting the Color … menu option for a style color property. 

Accuracy of color rendition is frequently dependent on factors such as the browser and video hardware being used to view a report. Accurate translation to a printed page can also be highly variable.

Controlling the Report Presentation - Changing the font on a report

Changing the font on a report is accomplished by specifying the font-family style property.

.ReportStyle
{
   background-color : OldLace;
   font-family : Verdana, Geneva, Arial, Helvetica, sans-serif;
}

Fonts are often specified in a prioritized list, arranged from highest to lowest priority, to indicate alternate fonts to be used in case a particular font is not installed on the machine.

Controlling the Report Presentation - Changing the background color of a report

There are a number of different ways to specify CSS classes and I’ll discuss two different methods of modifying the report background color. Specifying the background on a report is accomplished by specifying the background-color style property.

The first method is to define a style for the HTML <body> tag. This will affect everything beneath the Body element without further ado.

BODY
{
   background-color : OldLace;
}

The second approach is to define a class (which always starts with a “.”)

.ReportStyle
{
   background-color : OldLace;
}

Add this class to your style sheet and specifically apply that class to the appropriate element in the report:

Figure 4.6: Attributes Panel after setting Report element Class attribute

Go ahead and make this assignment in your report definition. Note that the same style sheet and style class should be applied to any sub-report (i.e. the Chapter3SubReport).

Controlling the Report Presentation - Adding space between elements

Adding space between elements in a report is accomplished using the New Line and Space elements.

Hint: A New Line element can represent more than one line by adjusting its Line Count attribute. Similarly, the Space element can represent more than one space by adjusting its Size attribute.

For example, to add a report header title to the report created on the previous page, add a Label and a Newline element to the Report Header element as shown below. Then add the same again in reverse order to the Report Footer element.

Figure 4.5: Definition Editor Panel after adding New Line and Label elements

Set appropriate IDs for the Label elements. Set “Customer Order Report” for the header Label element’s Caption and “Copyright 2014 – Randy Bourgeois” for the footer Label’s Caption.

The Horizontal Line and Vertical Line elements are used to separate sections of a report with a delineating line. The width (or height) and thickness of the line are defined in terms of pixels.  The Horizontal Rule element is similar to the Horizontal Line element, and can be used interchangeably.

Hint: A useful feature of the Horizontal Rule element is its ability to automatically adjust its width to the width of the browser window.

Creating a Style Sheet

In this section we’ll create a style sheet to use throughout the application and then apply the style sheet to a report.


Step 1: Create a new style sheet and a new report definition

Select the Support Files tab at the bottom of the Application panel. Right-click the Style Sheet support files folder and select the Add New File … menu option.

Give your style sheet a name like “MyFirstStyleSheet.css”. Notice that, unlike report definitions, support files require you to supply a file extension when naming them.

Figure 4.1: Application Panel after adding new StyleSheet file

In the Application panel, switch back to the Definitions tab and make a copy of the "Chapter3Report2" report definition. Rename it as “Chapter4Report1”. 

Step 2: Apply the style sheet to the report

In the new Chapter4Report1 definition, select the default Style Sheet element.

Figure 4.2: Definition Editor Panel after adding Style Sheet element

Set its Style Sheet attribute to your new style sheet. Look in the Suggestions panel for a list of style sheets. 

Figure 4.3: Attributes Panel after setting Style Sheet element attribute 

The classes in the style sheet will now be available to elements in the report. 

In addition to applying a style sheet to an individual report, a global style sheet can be applied to all reports by adding a Global CSS element within the _settings definition. If desired, a style sheet can be applied to a report and will override the global style sheet. 

Step 3: Editing style sheets 

Editing a style sheet can be accomplished within Logi Studio in two ways. First, if you select a style sheet file in the Support Files list in the Application panel, its contents will open in a simple editor that takes the place of the Definition Editor. Any changes made there are saved when you navigate to another part of Studio. 

The second method of editing is to use the TopStyle Lite style sheet editor that’s installed with Logi Studio. This is a more robust editor with a number of nice features. To open your style sheet in it, first open the style sheet in the simple editor mentioned above and then click the “Edit with Style Sheet Editor” button in the upper right-hand corner of the editor’s frame. TopStyle Lite will launch and open your style sheet file independently. 

Figure 4.4: A style sheet in the Studio simple editor 

In order to see changes made and saved in TopStyle Lite reflected in the simple editor in Studio, you will need to right-click the style sheet file in the Support Files list and select the “Refresh from File” option. This is also required in order to be able to edit the file in the simple editor after editing it in TopStyle Lite.

You can, of course, open and edit style sheet files outside of Studio with any editing tool ranging from the highly complex down to Notepad. 

Chapter IV. Reporting Style Cookbook

So far, we’ve explored the mechanics of generating both simple and hierarchical reports. Now, let’s improve the presentation of these reports.

The visual characteristics of a report are controlled primarily through the use of CSS (Cascading Style Sheet) classes. Many of the elements in Logi Report support one or more class attributes to control the presentation of the element.

Hint: When possible use CSS classes to control the presentation of the report, instead of attributes. This allows a more consistent, maintainable presentation across all reports.

It’s not practical to cover all the aspects of cascading style sheets in this book. Instead, we’ll learn how to control the presentation through practical examples (ala cookbook style).

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

Creating a Hierarchical Report by Linking Reports

The third approach to building hierarchical reports is implemented using two independent reports that are linked together. In fact, this approach can be used effectively to create a “web” of reports. Instead of embedding the details in the master report, they’re contained in a separate “stand-alone” report.

Step 1: Copy the previous hierarchical report 

We’ll base this hierarchical report on the previous report developed in Chapter 3. So, as before, copy the “Chapter3Report2” report and rename the copy to “Chapter3Report3”.


Step 2: Provide a link from the summary report to the detail report. 

Replace the Action.ShowElement (“actShowOrderDetails”) element beneath the customer ID column (“colCustomerID”) with an Action.Report element and, beneath it, add a Target.Report element. By selecting and dragging, move the Link Params element from beneath the Sub-Report (“sbrOrderDetails”) to it as well.

Figure 3.30: Definition Editor Panel after modifying Action elements

Set the Target.Report element’s Report Definition File attribute to reference the sub-report developed in the previous section. Notice that, when the attribute is selected, you can select from a list of reports in the project that appears in the Suggestions panel.

Figure 3.31: Definition Editor Panel after setting Target element attributes

Step 3: Remove the sub-report.

Remove the More Info Row (“mirOrderDetails”) element and all its child elements. 

Figure 3.32: Definition Editor Panel after removing elements

At this point, the hierarchical report is fully functional, and the summary report should look similar to the original report. When the user selects a Customer ID, the order details for the selected user are displayed in a separate detail report as shown below.

Figure 3.33: Report Preview Panel showing detail report

Of course, these are not the only options for building your reporting application, and these approaches are not mutually exclusive. Often, a combination of two or more of these approaches is used to provide an effective, intuitive reporting application. 

Adding Sub-Report Sorting By Column

To allow sorting by column in a sub-report, add a Sort element to the desired Data Table Column element:

Figure 3.28: Definition Editor Panel after adding Sort element

Set the Data Column attribute to the column to sort by (i.e. OrderDate). In addition, when sorting non-character data, you should set the Data Type attribute to specify the type of data being sorted, in this case Dates.

Figure 3.29: Attributes Panel after setting Sort element attributes

In addition, in the Chapter3Report2 report definition, you need to change the Sub-Report  (sbrOrderDetails) element’s Sub-Report Mode attribute to “IncludeFrame”. 

Hint: The columns in a sub-report only support sorting whenever the Sub-Report element Sub-Report Mode attribute is set to a value of ‘IncludeFrame’. If the Sub-Report Mode attribute is set to a value of ‘Embedded’, then the sort elements are ignored. 

Creating a Hierarchical Report with Sub-Reports

The second approach to building hierarchical reports involves implementing the detail report using a sub-report instead of a sub-table.

Step 1: Copy the current hierarchical report definition

We’ll base this hierarchical report on the previous report developed in Chapter 3. So, as before, copy the “Chapter3Report1” report and rename the copy to “Chapter3Report2”.


Step 2: Create a new, blank report definition that will become our sub-report

With the same controls you used to copy the previous report, use Add New File… to create a new, empty report definition. This will be our sub-report so name it “Chapter3SubReport” and it should open in the Definition Editor.

Figure 3.21: Definition Editor Panel after adding the new report

The Report Header and Report Footer elements are not needed and can be deleted. We’ll finished defining the sub-report in a later step, so right now, in the Applications panel, select the Chapter3Report2 definition. 

Step 3: Replace the sub-table with a sub-report 

In the Chapter3Report2 definition, delete the Sub-Data Table (“sdtOrderTable”) element – this will also delete all of its child elements – and replace it with a Sub-Report element. 

Figure 3.22: Definition Editor Panel after adding Sub-Report element

Set the Sub-Report element’s attributes as shown below. Note that the Attributes panel also identifies this element as an “IncludeFrame”. This is an old name carried forward for the sake of compatibility.

Figure 3.23: Attributes Panel after setting Sub-Report element attributes

Step 4: Link the report and sub-report

Add a Target.Report element beneath the Sub-Report element. Target elements are used in a variety of situations to redirect the user to a different report definition, URL, or process. Then add a Link Params element beneath the Sub-Report element as well. 

Figure 3.24: Definition Editor Panel after adding Target.Report and Link Params elements

And set their attributes as shown below. Note something interesting: the Link Params element has no pre-determined attributes! Instead you click Add… and provide both an attribute name and a value.

Figure 3.25: Attributes Panel after setting element attributes

The Link Parms attributes define a name-value pair that will be passed to the sub-report. In this way, the sub-report will be told which Customer ID to report on. 

Step 5: Build the sub-report definition

In the Application panel, select the definition for our sub-report: Chapter3SubReport. In the Definition Editor, add a Data Table element and DataLayer.SQL element to the sub-report. 

Figure 3.26: Definition Editor Panel after adding Data Table and DataLayer elements

Provide appropriate ID attributes for the elements. In this example, in the data layer, we’ll use the connNorthwind connection and the following SQL query as the source:

SELECT * FROM Orders

WHERE CustomerID = '@Request.CustomerID~'

Notice that the SQL query is using a special token, @Request, to reference the information passed from the Link Parms element in the main report. 

Step 6: Create the sub-report table columns

As with any data table, we’ll need to add Data Table Columns in order to display data. By design, these columns are exactly the same as the ones used in the sub-table in the previous report. This means you can go back to the Chapter3Report1 definition, locate the elements, and simply copy ‘n’ paste them into this sub-report definition. When you copy an element, all of its child elements get copied too, simplifying the process.

Figure 3.27: Copying  Data Table Columns and Label elements from a previous report definition

At this point, your second hierarchical report is fully functional and, when you preview it, it should look similar to the previous report. 

Reminder: the report that gets previewed is the one that’s open in the Definition Editor, so be sure you’ve opened Chapter3Report2 before previewing.

Adding a Report Summary Row

When you made a copy of the Chapter 2 report to use in this chapter, you “remarked” the summary row. This summary row displayed the number of customers in the report. Now, let’s modify the summary-related elements to display the number of orders in the report.

First, right-click and unremark the green Summary Row (“sumCustomerCount”) and Data Column Summary (“csmCustomerCount”) elements.

Next, move the Data Column Summary element from beneath the colCustomerName element to beneath the colOrderCount element. You can do this using the Cut and Paste items in the right-click popup menu or by selecting the element to move and then dragging it to and dropping it on its destination parent element (“colOrderCount”).

Figure 3.18: Definition Editor Panel after moving Data Column Summary element

Now set the Data Column Summary element’s attributes as shown below:

Figure 3.19: Attributes Panel after setting Data Column Summary element settings

At this point, the report should appear in the Preview panel as shown below:

Figure 3.20: Report Preview Panel

Note that the final row of the report now contains the total number of orders.

Adding Report Sorting By Column

Sorting by the customer name was already supported by the report, but let’s add sorting by the order count, too. Add a Sort element to the desired Data Table Column element (“colOrderCount”), as shown below:

Figure 3.16: Definition Editor Panel after adding Sort element

Set its Data Column attribute to the column to sort by (i.e. OrderCount) and its Data Type attribute to specify numerical sorting, instead of alphabetic sorting (i.e. Number), as shown below:

Figure 3.17: Attributes Panel after setting the Data Column Sort element attributes

Hint: The columns in a sub-table don’t support sorting. Use a sub-report instead to allow sorting. 

Preview the report and you’ll see that you can now also sort it by the number of orders.