Sales Territory

Corporate Reports ..

In this workshop, we're going to create a Sales Order Report. The report will display Sales revenue grouped by Territory and Country, displaying the Customer Name, City, Order Date, Order Number.

• Select the data source and report template.

• Add data columns.

• Create report groups.

• Sort, filter, prompt, and add summary totals to the report.

• Modify the format of numeric data.

• Modify the header, footer, and title.

• Save an Interactive Report to the repository.

Sales Territory Report

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.

1. In the Folders pane, expand Public > Steel Wheels, and then in the Files pane, double-click Vendor Sales Report.

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.

1. To view the available fields, on the main toolbar, click the Edit Content button.

Edit Mode

You're only able to view the report ..

but you can see in the Data tab in the Selection pane that the available fields come from the Orders data source. The Data tab also identifies grouping and sorting information.

The Selection pane also includes a tab for formatting individual report elements, and a tab for general report preferences.

In the lower right corner, an option to show tips on start-up and a button to hide tips.

1. To view the available export formats, on the interactive toolbar click the drop-down arrow for Export.

1. To display the Filter panel, on the interactive toolbar click the Filters button.

1. To display the Layout panel, on the interactive toolbar click the Layout button.

1. You can also set the Row Limit.

Sales Territory Report

One of the standard corporate reports is a breakdown of the total customer revenue by 'Sales Territory'.

Workflow - Interactive report

Data Source & 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.

1. From the User Console Home Perspective, click Create New > Interactive Report.

Orders - Data Source

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

1. In the Selection Pane, click the General tab, and then click Select.

Select - Report Template

1. Use the left and right arrow to scroll through the available templates, and then click on Left Aligned - Nickel.

Report Templates

You can define your own Report Template using Pentaho Report Designer.

Left Aligned - Grid -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.

1. In the Selection Pane, click the Data tab.

Data Fields

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

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

Delete Country

1. To turn on the Layout panel, on the Interactive Toolbar, click the Layout button.

Interactive Toolbar

1. From the Data panel, select Country and drag it to the Columns line on the Layout panel.

Layout Panel

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

2. From the Data panel, double-click Customer Name.

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

4. To rearrange columns from the Layout panel, on the Columns line, click Customer Name and drag it between Country and City.

Rearrange Fields in Layout

1. To rearrange columns from the Report Canvas, click the Order Number column header and drag it between City and Order Date.

Rearrange Fields on Canvas

1. 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 columns

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

1. To add a group for Territory, drag Territory, from the Data pane to the Groups line on the Layout panel.

Grouping

1. To add a subgroup, on the Report Canvas, click the Country column header and drag it below the Territory group.

Country sub-group

Territory and Country are added to the Group line on the Layout panel, and to the Group Sorting section at the bottom of the Data pane. By default, groups are sorted in Ascending order when they are created.

---

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.

1. To change the sort order for Territory to Descending.

Descending sort on Territory

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

Descending sort on Customer Name

Under Field Sorting in the Selection Pane, the sort order for Customer Name is Ascending. There is a red X you can click to remove the sort.

Data tab - sorting

Filters & Prompts

Filters are used to restrict or limit the data that is presented in a report. When you create the filter, you can choose to select from a list of values or match a specific value by typing the value in the text box and specifying a constraint.

There are several ways to create filters:

• Turn on the Filters panel and drag fields to the filter area

• Click the drop-down arrow next to a column header and then select Filter

• Right-click a field in the Data panel and then select Filter

Filter the report to only show results for the North American Territory; Country is the USA, since January 1, 2004.

1. Ensure the report layout is defined as illustrated.

Report layout

1. On the Interactive Toolbar, click the Filters button.

Filter

1. To filter on Territory, from the Data pane, select Territory and drag it to the Filters panel.

Filter Panel

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

Filter on Territory

You can define a filter constraint as a parameter. The parameter name will appear under the Parameters tab in Dashboard Designer when you use the report in a dashboard. The parameter(s) also appear, and can be edited when working in Report Designer.

Filter Panel

1. To filter on Country, in the report details, drag Country to the Filters panel.

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

1. To filter on Order Date, in the report details, click the Order Date column header and drag it to the Filters panel.

Filter on Order Date

1. 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:

Operator	Definition
AND	Finds records that match both values. 1 AND 2
OR	Finds records that match either value. 1 OR 2

Filter icons appear on the dimension(s) / measure(s) in the Data Panel

---

Prompts

Prompts to provide an easy way to interactively filter a report.

Let's create a prompt for Territory.

1. Ensure the report layout is defined as illustrated.

Report Layout

You will need to remove ‘Country begins with US’ filter and Territory.

1. Click on the dropdown arrow and select ‘Delete’.

Delete Filters

1. To display the Prompts panel, on the Interactive Toolbar, click the Prompts button.

Prompts

1. From the Data panel, select Territory and drag it to the Prompts panel.

Prompts Panel

The default prompt is a drop-down list.

1. From the Territory Parameter drop-down list, select NA.

Select Territory = NA

We'll be revisiting Prompts ..!

Totals

You can apply a summary function to columns containing numeric values to add subtotals and grand totals to your report.

To apply a summary function to a numeric value, click the drop-down arrow next to the column header, and then select Summary from the context menu.

You can then specify to summarize the data using one of the following functions:

• None

• Average

• Count

• Count Distinct

• Maximum / Minimum

• Sum

After the total is added to the report, you can customize the label in the cell next to the total.

1. Remove all the filters / Prompts.

Remove all Filters

1. Group the report by Territory and Country.

Report layout

1. Uncheck the Filters option in Interactive Toolbar.

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

Add Totals

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

Add caption

1. Repeat the workflow for Territory and Grand Total.

---

Calculated Measures

You can create calculated fields from fields that are available in the data source and from other calculated fields. When you create a calculated field, a new field is generated in the Calculated Fields list. The values are determined by the kind of calculation the function performs. You can add these fields to the columns or groups in the layout to create more robust reports.

Generic functions like now() or 2+5 cannot be added to an empty layout. These generic functions can be added after the layout has at least one column or group from a data source field.

The Filter, Prompt, Sort, and Aggregation options are not supported for calculated fields.

1. Select the Data tab in the Interactive Report in which you want to add a calculated field.

2. Navigate to the bottom of the Data tab, locate the Calculated Fields entry.

Calculated Fields

1. Click the + sign on the Calculated Fields.

2. To calculate the Tax: =[BC_ORDERDETAILS_TOTAL]*0.15

Calculated Measure - Tax

1. Add to the Report Drag & Drop.

If you're creating alot of Calculated Measures, then BP is to add them to the Schema.

Format & Save

You can add a report title or text to a report header and footer by double- clicking the label in the appropriate section on the first page of the report.

Formatting options such as font type, font size, colour, background colour, and text alignment are available under the Formatting panel in the Selection Pane. Buttons are also available to copy, paste, and remove formatting. To apply formatting, first select the object in the report. The appropriate formatting buttons activate based on the type of object selected.

By default, Interactive Reporting presents you with a page in Letter format (8.5” x 11”) in portrait mode. You can change the page format, orientation, and margin sizes by clicking the Page Setup button found on the General panel in the Selection Pane.

If a report contains numeric values, you may need to change the formatting of those values (for example, to include a currency symbol). Select the column that contains the numeric values, and then specify the numeric format on the Formatting panel. In some instances, formatting is applied based on the metadata associated with the data source. This formatting can be overridden.

To finish off the report we're going to:

• Add a title for the report and centre it

• Add text to the report header

• Format column headers and data

• Change the column header for the Total column

• Change the page layout to landscape.

1. To add a report title, in the title area:

   • Double-click on Untitled.

   • In the text box type Sales Territory Report.

   • Press Enter.

Add a Report Title

1. To centre the report title, in the Selection Pane, click the Formatting tab, and then click the Align Center icon.

Fomatting options

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

Report Title

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

2. Click within the City data column, and in the Formatting panel, click the Align Center icon.

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

2. 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 $#,###.

3. To change the column header for the Total column:

   • Double-click the Total column header.

   • In the text box, type Revenue.

   • Press Enter.

4. To change the page format to landscape, in the Selection Pane, click the General panel, and then click the Page Setup button.

Page setup

---

You can save your report using the Save or Save As toolbar buttons, or by selecting File > Save or File > Save As from the menu. When you save the report, you must specify both a filename and a repository location.

1. To save the report, on the toolbar click the Save icon.

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

Save Report

Query Settings

Query Settings

The Query Settings dialog box is found on the Data panel.

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.