Layout Input Elements with an Input Grid Element

Often multiple input values are collected from the user and it can be difficult to get the input elements aligned neatly in a two-column format. The Input Grid element, which is a container for input elements, provides a method for achieving such an alignment.

The example below is a report definition that collects the date range criteria for a report, using Input Date elements organized within an Input Grid element, and executes a report based on the criteria.

Figure 6.15: Definition Editor Panel after adding Input Grid and Input Date elements

The Input Date elements’ Caption attributes have been used to provide captions and, when the report is previewed, the inputs look like this:

Figure 6.16: Report Preview Panel   

Adding Report Criteria

While generating static reports is useful, many reports must support the selection of report criteria by the user to narrow the scope of the report.

For this example, we’ll start with the simple Customer List report created in Chapter 2, and add the capability to limit the customer list by country.

Step 1: Copy the basic report.

First, make a copy of the “Chapter2Report” report, and rename the report “Chapter6Report1”.


Step 2: Add the criteria selection to the report

There are a number of input elements available to collect input from the user. For visual clarity, you may care to collapse the dtCustomers element, hiding all of its child elements.

 In this example, add the following beneath the Body element and move them above the dtCustomer element: a Label element to prompt the user for the criteria, a Space element, an InputText element to accept input from the user, and a New Line element. Give the Label and InputText elements appropriate IDs.

Figure 6.1: Definition Editor Panel after adding input elements

Set the Label element’s Caption attribute to prompt the user for input (i.e. “Enter the Country:”).

Figure 6.2: Attributes Panel after setting Label element attributes

Hint: An alternative to using a Label element to prompt the user for input is to set the InputText element’s Caption attribute.   

Step 3: Add a button to execute the report

Add New Line and Button elements below dtCustomers to execute the report.  

Figure 6.3: Definition Editor Panel after adding Button element 

Set the Button element’s Caption attribute to provide a description of the button action (i.e. “Run Report”).

Figure 6.4: Attributes Panel after setting Button element attributes

Beneath btnRunReport add Action.Report and Target.Report elements to specify the report to run when the button is clicked.

Figure 6.5: Definition Editor Panel after adding Action and Target elements 

In this example, the report executes itself with the desired criteria. Therefore, set the Target element’s Report Definition File attribute to this report (i.e. “Chapter6Report1”).

Figure 6.6: Attributes Panel after setting Target element attributes 

Expand dtCustomers and modify the dlCustomerData data layer element’s Source attribute to use the criteria:

SELECT * FROM Customers

WHERE Country LIKE '@Request.txtCountry~%'

When the button is clicked, the HTML form in the report that contains the text input element will be submitted. Form fields submitted in this manner are “sent” to the target report definition as values identified by @Request tokens, followed by the input element ID. So @Request.txtCountry~ represents the country value entered by the user. The percent symbol (%) in the query is SQL syntax is a “wildcard” that enhances matching and is not part of the token.

Now, preview the report. Because the report expects an @Request token, you’ll be prompted to enter one. This is a handy Studio feature that lets you feed request variables to reports when testing. For now, however, just click Continue.

Figure 6.7: The Test Parameters Input dialog box 

Your report should appear in the Preview window and will contain all customers. Remember, we haven’t provided any criteria yet to limit the report to specific countries.

Enter some country criteria (the names of most countries or abbreviations, such as USA or UK, for some) and click the button. Your report should be limited to the correct entries. 

To see the effect of that percent sign (%) in the SQL query, enter just the letter “F” in your criteria and click the button. The report will include customers from Finland and France. 

Figure 6.8: Report Preview Panel after criteria “UK” entered

Step 5: Remembering the selection criteria

Notice, however, that our criteria textbox is cleared each time the report runs so we’re not really sure what criteria we’re viewing. To allow the report to “remember” the value entered by the user, set the Input Text (txtCountry) element’s Default Value attribute to the value “@Request.txtCountry~”.

Figure 6.9: Attributes Panel after setting Input Text element attribute 

The value entered will now appear in the Input Text element each time the report runs.

Step 6: Using a drop-down list

Instead of requiring the user to type in the country, a more user-friendly approach is to allow the user to select the country from a list of valid values. Remark the Input Text  (txtCountry) element, and add just below it an Input Select List element. 

Figure 6.10: Definition Editor Panel after adding the Input Select List element

To supply valid values for the Input Select List, add a DataLayer.SQL element beneath it and give it an appropriate ID.

Figure 6.11: Definition Editor Panel after adding the DataLayer element

Set the data layer’s Connection ID attribute to “connNorthwind” and, to retrieve a list of unique country names from the database, use the DISTINCT keyword in the SQL query in the Source attribute. The attributes should be as shown below:

Figure 6.12: Attributes Panel after setting the DataLayer element attributes

Set the Input Select List element’s Caption Column and Value Column attributes to the name of the appropriate column in the Data Layer (i.e. “Country”). In this example, these attributes are both set to the same column. But often the Caption Column is set to a user-friendly value and the Value Column is set to a key. 

In addition, set the Input Select List element’s Default Value attribute to the request token that will contain the value selected by the user. 

Figure 6.13: Attributes Panel after setting the InputSelectList element attributes 

Hint: The Input Select List element’s Include Blank attribute is used to add an empty item to the list and the Include Blank Caption attribute is used to specify an item to add to the list (i.e. “All” or “None”). When the user selects the “blank” item, the @Request token returns an empty string.   

Finally, modify the Data Layer (“dlCustomerData”) element’s Source property to use the country name selected from the list:

SELECT * FROM Customers

WHERE Country = '@Request.selCountry~'

Notice that the percent sign (%) is no longer used in the query.

Now, preview the report and select different countries from the list.

Figure 6.14: Report Preview Panel

To allow the user to select multiple items from the list, set the Input Select List element’s Multiple Selections attribute to “True”. In addition, modify the Data Layer (“dlCustomerData”) element’s Source property to retrieve all the selected items. 

SELECT * FROM Customers

WHERE Country IN (@SingleQuote.Request.selCountry~)

When an input select list allows multiple items to be selected, the @Request token returns a comma-separated list of items. The @SingleQuote token inserts single quotes around each item selected in the input select list. These two tokens used together allow the selected items to be easily used with the SQL statement IN clause. 

Chapter VI. Creating Interactive Reports

The purpose of this chapter is to build more dynamic, flexible reports using the family of input elements provided by Logi Report. In particular, we’ll demonstrate the collection and use of report criteria, the different methods of laying-out input elements, and input validation.

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.