Sales Territory
Corporate Reports ..
Workshop - Sales Territory
Understanding sales performance across geographic territories is a fundamental business intelligence requirement. In this comprehensive workshop, you'll build a professional Sales Order Report from the ground up using Pentaho's Interactive Reporting tool, learning how to transform raw sales data into an organized, actionable business report that reveals revenue patterns across territories, countries, and customers.
In this hands-on workshop, you'll experience the complete lifecycle of creating an Interactive Report, from selecting your data source and template through to publishing the finished report to the repository. You'll master essential reporting techniques including data grouping, sorting, and filtering, while learning how to enhance report usability with prompts that let users customize their view. You'll also develop the formatting skills needed to present numeric data professionally and create polished headers, footers, and titles that give your reports a finished, business-ready appearance.
What You'll Accomplish:
Select an appropriate data source and report template to begin your report
Add relevant data columns (Customer Name, City, Order Date, Order Number, Sales Revenue)
Create logical report groups to organize data by Territory and Country
Apply sorting and filtering to control data presentation
Implement user prompts to make your report interactive and flexible
Add summary totals to calculate revenue aggregations at group levels
Format numeric data (currency, decimals) for professional presentation
Customize report headers, footers, and titles to create polished, branded output
Save and publish your completed Interactive Report to the Pentaho repository
By the end of this workshop, you'll have created a fully functional sales territory report that demonstrates best practices in business reporting. You'll understand how to structure data hierarchically, provide user interactivity through prompts, and format output for executive consumption. These skills form the foundation for creating any business report in the Pentaho Interactive Reporting environment.
Prerequisites: Pentaho Business Analytics Server with sample sales data source configured Estimated Time: 20 minutes
Vendor Sales Report
The Vendor Sales Report consists of Product Name, Scale, Items Sold, and Sales grouped by Territory and Product Vendor.
It includes subtotals for each Product Vendor, and the Sales column has been formatted as currency with no decimal places.
In the Folders pane, expand Public > Steel Wheels, and then in the Files pane, double-click Vendor Sales Report.
Toolbar
The Interactive Toolbar includes buttons to undo or redo changes, export the report, display the Filters or Layout panels, create prompts, and to navigate through the report pages.
To view the available fields, on the main toolbar, click the Edit Content button.
To view the available export formats, on the interactive toolbar click the drop-down arrow for Export.
To display the Filter panel, on the interactive toolbar click the Filters button.
To display the Layout panel, on the interactive toolbar click the Layout button.
You can also set the Row Limit.
Sales Territory
One of the standard corporate reports is a breakdown of the total customer revenue by 'Sales Territory'.
Data Sources & Templates
The first step to creating an Interactive Report is selecting a data source. Data sources are provided to you by an administrator or authorized user.
From the User Console Home Perspective, click Create New > Interactive Report.
In the Select Data Source window, click Orders, and then click OK.
Report Template
You can select a different template for your report using the General tab. You can define your own Template using Report Designer.
In the Selection Pane, click the General tab, and then click Select.
Use the left and right arrow to scroll through the available templates, and then click on Left Aligned - Nickel.
Freeze the report top row
Select the Sticky HTML headers rows option on the General tab to freeze the output header row when viewing reports in HTML (Single Page) output. This keeps the data correlation with the header when moving within a page of a report, similar to the Freeze Top Row function in Excel.
Adding Data
When you first open an Interactive Report, the categories and fields associated with the data source you selected are displayed on the Data panel in the Selection Pane.
• There are several methods to add data columns to the report:
• Select a field and drag it to the Report Canvas
• Turn on the Layout panel and drag fields to the Columns line
• Right-click a field in the Data panel and select Add to Columns
• Double-click a field in the Data panel
You can select more than one field by holding the Shift or Control key before adding them to the report. As you add fields, a blue vertical or horizontal line indicates where the column will be placed.
In the Selection Pane, click the Data tab.
From the Data panel, select Country and drag it to the Report Canvas. A blue vertical line appears, indicating where the column will be placed.
To remove Country from the Report Canvas:
• Click the Country column header.
• Drag it to the lower right corner of the canvas.
• Drop it in the trashcan.
To turn on the Layout panel, on the Interactive Toolbar, click the Layout button.
From the Data panel, select Country and drag it to the Columns line on the Layout panel.
From the Data panel, select City and drag it to the Report Canvas. The vertical line indicates the option of either displaying the data as a column or row. Drop City to the right of Country.
From the Data panel, double-click Customer Name.
Add the following additional fields, from the Data panel:
• Select Order Date.
• Hold the Ctrl key and select Order Number and Total.
• Right-click and select Add to Columns.
To rearrange columns from the Layout panel, on the Columns line, click Customer Name and drag it between Country and City.
To rearrange columns from the Report Canvas, click the Order Number column header and drag it between City and Order Date.
To resize the Customer Name column, click the resize bar between the Customer Name and City column headers and drag it to the right.
Resize the Order Number and Order Date columns to make them smaller.
Grouping & Sorting
The grouping feature in Interactive Reporting allows you to group the data in your report by one or more fields. To create a group, drag a field from the Data pane and place it above the column headers on the Report Canvas. The blue horizontal line indicates the field will be used for grouping.
If the field you want to group by is already a column in your report, click the column header and drag it up above the other headers in the report. Alternatively, you can drag a field to the Groups line on the Layout panel. You can create nested groups by “stacking” the fields on the Report Canvas or adding additional fields to the Groups line on the Layout panel.
To add a group for Territory, drag Territory, from the Data pane to the Groups line on the Layout panel.
To add a subgroup, on the Report Canvas, click the Country column header and drag it below the Territory group.
Sorting
You can sort the report data by group and/or by individual column. By default, groups are sorted in ascending order. Change the sort order using the Group Sorting drop-down in the Selection Pane.
To change the sort order for an individual column, click the drop-down arrow next to the column heading, and then select Sort > Ascending, Sort > Descending, or Sort > None from the context menu.
To change the sort order for Territory to Descending.
To sort the Customer Name column, on the Report Canvas, click the drop-down arrow next to the Customer Name column header, and then select Sort > Ascending.
Ensure the report layout is defined as illustrated.
On the Interactive Toolbar, click the Filters button.
To filter on Territory, from the Data pane, select Territory and drag it to the Filters panel.
In the Filter on Territory dialog box:
• Select the option: Select from a list.
• From the list of values, click NA.
• Click the right arrow to move NA to the Currently Included list.
• Click OK.
To filter on Country, in the report details, drag Country to the Filters panel.
In the Filter on Country dialog box, select Specify a Condition, and from the available constraints drop-down list, select Begins with, then in the text box, type US, and then click OK.
To filter on Order Date, in the report details, click the Order Date column header and drag it to the Filters panel.
In the Filter on Order Date dialog box:
• From the available constraints, drop-down list, select On or after.
• Click the next drop-down arrow, then navigate to January 2004.
• Select January 1, 2004 (2004-01-01).
• Click OK.
The filters applied are illustrated below:
AND
Finds records that match both values.
1 AND 2
OR
Finds records that match either value.
1 OR 2
Prompts
Let's create a prompt for Territory.
Ensure the report layout is defined as illustrated.
You will need to remove ‘Country begins with US’ filter and Territory.
Click on the dropdown arrow and select ‘Delete’.
To display the Prompts panel, on the Interactive Toolbar, click the Prompts button.
From the Data panel, select Territory and drag it to the Prompts panel.
The default prompt is a drop-down list.
From the Territory Parameter drop-down list, select NA.
Remove all the filters / Prompts.
Group the report by Territory and Country.
Uncheck the Filters option in Interactive Toolbar.
To add totals for the Total column, in the report details:
• Click the drop-down arrow next to the Total column header.
• Click Summary.
• Click Sum.
To edit the label for the Country subtotals:
• Point to the cell just to the left of the Australia subtotal.
• Double-click.
• In the text box, type Country Subtotal.
• Press Enter.
Repeat the workflow for Territory and Grand Total.
Calculated Measures
Select the Data tab in the Interactive Report in which you want to add a calculated field.
Navigate to the bottom of the Data tab, locate the Calculated Fields entry.
Click the + sign on the Calculated Fields.
To calculate the Tax: =[BC_ORDERDETAILS_TOTAL]*0.15
Add to the Report Drag & Drop.
To add a report title, in the title area:
• Double-click on Untitled.
• In the text box type Sales Territory Report.
• Press Enter.
To centre the report title, in the Selection Pane, click the Formatting tab, and then click the Align Center icon.
To add text to the report header, in the header area:
• Point to the left side.
• Double-click.
• In the text box, type Steel Wheels, Inc.
• Press Enter.
Select the City column header in the report details, and in the Formatting panel, click the Bold icon, and then click the Align Center icon.
Click within the City data column, and in the Formatting panel, click the Align Center icon.
To copy the formatting of the City column header and apply it to the Order Number column header, in the report details:
• Click the City column header.
• On the Formatting panel, click the Copy formatting icon.
• In the report details, click the Order Number column header.
• On the Formatting panel, click the Paste formatting icon.
To remove the decimal places from the Total column, in the report details:
• Click within Total data column.
• On the Formatting panel, click the drop-down arrow for Numeric Format.
• Select $#,###.
To change the column header for the Total column:
• Double-click the Total column header.
• In the text box, type Revenue.
• Press Enter.
To change the page format to landscape, in the Selection Pane, click the General panel, and then click the Page Setup button.
To save the report, on the toolbar click the Save icon.
To save the report:
• In the Filename field, type Sales Territory Report - Demo.
• For the Location, click the Up One Level icon twice.
• In the list of folders, double-click Public.
• In the list of folders, double-click Training.
• Click Save.
Enabling Row Limit and Query Timeout
You can limit the number of rows that are displayed in your report. You can also limit the number of seconds a query runs before a timeout occurs. Imposing row limits and timeouts on queries is important to avoid out of memory errors or processes that consume too many resources on the database server.
In the Data tab, click the small icon on the upper right corner to open the Query Setup dialog box. Make your changes as needed and close the dialog box when you are done.
Auto Refresh
When you disable the Auto Refresh mode in Interactive Report you can design your report layout first, including calculations and filtering, without querying the database until you are done. Once the report layout is complete, you can re-enable Auto Refresh mode. Data retrieval will occur once and your report will display the requested data. Disable auto refresh if you want to reduce the number of queries executed against the data source or if you know that the data source returns data slowly.
To disable Auto Refresh, click the small icon in the upper right corner of the Data tab to open the Query Setup dialog box, then disable the Auto Refresh option.
Last updated
Was this helpful?