infor bi: creating reports with application studio...

Infor BI: Creating Reports with Application Studio Training Workbook

Infor BI February 1, 2016

Course Code: 01_0061050_IEN0077_BSA

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

Legal notice

Copyright © 2016 Infor. All rights reserved. Important Notices The material contained in this publication (including any supplementary information) constitutes and contains confidential and proprietary information of Infor. By gaining access to the attached, you acknowledge and agree that the material (including any modification, translation or adaptation of the material) and all copyright, trade secrets and all other right, title and interest therein, are the sole property of Infor and that you shall not gain right, title or interest in the material (including any modification, translation or adaptation of the material) by virtue of your review thereof other than the non-exclusive right to use the material solely in connection with and the furtherance of your license and use of software made available to your company from Infor pursuant to a separate agreement, the terms of which separate agreement shall govern your use of this material and all supplemental related materials ("Purpose"). In addition, by accessing the enclosed material, you acknowledge and agree that you are required to maintain such material in strict confidence and that your use of such material is limited to the Purpose described above. Although Infor has taken due care to ensure that the material included in this publication is accurate and complete, Infor cannot warrant that the information contained in this publication is complete, does not contain typographical or other errors, or will meet your specific requirements. As such, Infor does not assume and hereby disclaims all liability, consequential or otherwise, for any loss or damage to any person or entity which is caused by or relates to errors or omissions in this publication (including any supplementary information), whether such errors or omissions result from negligence, accident or any other cause. Without limitation, U.S. export control laws and other applicable export and import laws govern your use of this material and you will neither export or re-export, directly or indirectly, this material nor any related materials or supplemental information in violation of such laws, or use such materials for any purpose prohibited by such laws. Trademark Acknowledgements The word and design marks set forth herein are trademarks and/or registered trademarks of Infor and/or related affiliates and subsidiaries. All rights reserved. All other company, product, trade or service names referenced may be registered trademarks or trademarks of their respective owners.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

Table of Contents Table of Contents ....................................................................................................................................... i About this workbook ................................................................................................................................. iv Course Overview ....................................................................................................................................... v Course Agenda ......................................................................................................................................... vi

Lesson 1: Introduction to Application Studio .......................................................................................... 1 Introduction to Infor BI ............................................................................................................................... 2 Introduction to Application Studio .............................................................................................................. 3

Lesson 2: Getting started ........................................................................................................................... 6 Logging on to Application Studio ............................................................................................................... 7 The User Interface ..................................................................................................................................... 9 Check your understanding....................................................................................................................... 21

Lesson 3: Creating reports ...................................................................................................................... 22 Overview .................................................................................................................................................. 23 Creating folders and reports .................................................................................................................... 24 Adding hyperblocks to reports ................................................................................................................. 26 Drill down level on a hyperblock .............................................................................................................. 29 Lists ......................................................................................................................................................... 30 List objects ............................................................................................................................................... 33 Adding data cell formulas ........................................................................................................................ 37 Modifying hyperblock properties .............................................................................................................. 39 Using styles and style sheets .................................................................................................................. 41 Format your report ................................................................................................................................... 44 Inserting text and images ........................................................................................................................ 46 Check your understanding....................................................................................................................... 48

Lesson 4: Select structure data and create cross table ....................................................................... 49 Combine hyperblocks .............................................................................................................................. 50 Global and local lists ................................................................................................................................ 55 Structure Selection .................................................................................................................................. 59 Freeze areas and visible report areas ..................................................................................................... 61 Check your understanding....................................................................................................................... 62

Lesson 5: Filter and sort data .................................................................................................................. 63 Filtering report data ................................................................................................................................. 64 Sorting report data ................................................................................................................................... 68 Check your understanding....................................................................................................................... 72

Lesson 6: More interactive reports by using Actions ........................................................................... 73 Global and report variables ..................................................................................................................... 74 Actions ..................................................................................................................................................... 77 Command buttons ................................................................................................................................... 84 Managing variables ................................................................................................................................. 87 Check your understanding....................................................................................................................... 89

Lesson 7: Display additional information on your report ..................................................................... 90 Attributes.................................................................................................................................................. 91 Check your understanding..................................................................................................................... 104

Lesson 8: Combine hyperblocks in a table .......................................................................................... 105 Nesting Hyperblocks .............................................................................................................................. 106 Check your understanding..................................................................................................................... 110

Lesson 9: Enrich your reports with visual information ...................................................................... 111 Sparklines .............................................................................................................................................. 112 Charts .................................................................................................................................................... 113 Check your understanding..................................................................................................................... 122

Lesson 10: Enhance your data with qualitative information .............................................................. 123

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

ii Course Introduction © 2016 Infor Education

Open Dialog Action ................................................................................................................................ 124 Check your understanding..................................................................................................................... 130

Optional lesson 11: Build a landing page ............................................................................................. 131 Jump Action ........................................................................................................................................... 132 Check your understanding..................................................................................................................... 135

Lesson 12: Report books ....................................................................................................................... 136 Page setup ............................................................................................................................................ 137 Printing reports ...................................................................................................................................... 141 Report Books ......................................................................................................................................... 143 Exporting reports ................................................................................................................................... 147 Check your understanding..................................................................................................................... 148

Course Summary .................................................................................................................................... 149 Course Review ...................................................................................................................................... 150

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

iv Course Introduction © 2016 Infor Education

About this workbook Welcome to this Infor Education course! We hope you will find this learning experience enjoyable and instructive. This Training Workbook is designed to support the following forms of learning:

Classroom instructor-led training Virtual instructor-led training

This Training Workbook is not intended for self-study or as a product user guide.

Activity data

You will be asked to complete some practice exercises during this course. Step-by-step instructions are provided in this guide to assist you with completing the exercises. Where necessary, data columns are included for your reference.

Your instructor will provide more information on systems used in class, including server addresses, login IDs and passwords.

Reference Materials

INFOR BI Application Studio reference materials are available from the following locations:

Application Studio Online Help

Symbols Used in this Workbook

Hands-on exercise (“Exercise”)

For your reference

Instructor demonstration (“Demo”)

Your notes

Scenario

Question

Note

Answer

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

Infor BI: Creating Reports with Application Studio Training Workbook v © 2016 Infor Education

Course Overview This course provides an introduction to the basic features of Application Studio and the foundational knowledge required to build your own application.

Course Length

2 days

Course Goal

At the end of this course you will know the basic features of Application Studio and how to use them to build your own application.

Learning Objectives

Upon completion of this course, you will be able to: Identify the basic features and functionality of Application Studio. Create and format interactive reports by using styles and style sheets. Filter and sort report data. Enrich reports with visual information like charts and sparklines. Build a landing page with report navigation. Print and export reports and report books.

Audience

Infor BI Business Consultants Infor Business Partners

System Requirements

Remote access to the Infor BI Application Studio Test Environment

Prerequisite Knowledge

Basic knowledge of Microsoft Windows Basic knowledge about Microsoft Excel General knowledge of multidimensional databases

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

vi Course Introduction © 2016 Infor Education

Course Agenda

Lesson Lesson title Learning objectives Estimated time

Course Overview Review course expectations. Day 1

Lesson 1 Introduction to Application Studio

List the main components of Infor BI. Identify the features and functionality of

Application Studio.

Day 1

Lesson 2 Getting started

Describe a repository, project, report catalog, database alias, and cube.

Explain the relationship between a project and report catalog.

Day 1

Lesson 3

Creating reports

Describe how to create a report and folder. Explain how to add a hyperblock to a report. Describe how data formulas are used in a

report. Explain how to apply styles to a report. Explain how to format cells, rows, and

columns in a report.

Day 1

Lesson 4

Select structure data and create a cross table

Explain how to select elements in a report using various selection functions.

Explain how to create a global list and insert it into a report.

Describe how to change a global list to a local list.

Describe how to replace a local list with global list.

Day 1

Lesson 5

Filter and sort data

Explain how to filter data by value. Describe how to filter report data to suppress

zero and null values. Describe how to use aggregation functions. Explain how to sort report data.

Day 1

Lesson 6

More interactive reports by using Actions

Describe how to create and use Actions and variables.

Define a global variable and report variable. Explain how a button is used to generate

commands. Explain how images can be used to design

buttons Describe how to enable single filters.

Day 1

Lesson 7 Display additional information on your report

Describe how to use attributes. Explain how to display Captions and attributes

in a hyperblock.

Day 2

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

Infor BI: Creating Reports with Application Studio Training Workbook vii © 2016 Infor Education

Lesson Lesson title Learning objectives Estimated time

Describe how to use Textboxes with formulas. Describe how to add a variance calculation to

a hyperblock.

Lesson 8 Combine hyperblocks in a table

Explain how multiple hyperblocks may be used to enhance your report.

Day 2

Lesson 9 Enrich your reports with visual information

Explain how to add sparklines in reports. Describe ways to create charts in reports.

Day 2

Lesson 10 Enhance your data with qualitative information

Explain how to add a commentary function to your report.

Day 2

Optional lesson 11 Build a landing page Explain how to link reports.

Day 2

Lesson 12

Report books

Explain how to configure the page layout. Describe how to create a report book. Explain how to preview a report for printing. Explain how to use the Print Wizard to print a

report. Explain how to export a report to various file

formats.

Day 2

Course Review Debrief course. Day 2 @ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

Infor BI: Creating Reports with Application Studio Training Workbook 1 © 2016 Infor Education

Lesson 1: Introduction to Application Studio Estimated Time

30 minutes

Learning Objectives

After completing this lesson, you will be able to:

List the main components of Infor BI. Identify the features and functionality of Application Studio.

Topics

Introduction to Infor BI Introduction to Application Studio

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

2 Lesson 1: Introduction to Application Studio © 2016 Infor Education

Introduction to Infor BI Infor BI is a fully integrated solution suite that supports various types of financial, operational, and sales business intelligence requirements. It can be used for standard reporting, flexible ad-hoc reporting and analysis, dashboard creation, business planning, budgeting, forecasting, and financial consolidation.

The following table identifies the components of the Infor BI suite and provides a brief description of each:

Component Description

OLAP Server A real-time, in-memory, online, analytical processing (OLAP) database server for multidimensional analysis, planning, and modeling.

Application Studio A web-based front-end used for visualizing data through reports, analysis, dashboards, and data entry.

Office Plus A fully integrated Microsoft® Excel® add-in mainly used for ad-hoc analysis and reporting.

Designer A tool for designing and creating Infor BI OLAP Server databases.

Delta Miner A tool providing statistical analysis, data mining, and other advanced analytic capabilities.

ImportMaster An extract, transform, and load (ETL) layer that facilitates integration with both Infor and non-Infor source systems.

Planning An application for financial, operational, and overall business budgeting and forecasting.

Consolidation A statutory and management consolidation application that leverages the Infor BI OLAP Server.

Dynamic/Enterprise Performance Management (d/EPM)

An integrated suite of financial performance management applications, packaged analytics, and compliance tools that operate across the complex web of your enterprise systems to deliver a real-time, single version of the truth.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Introduction to Application Studio Infor BI Application Studio is a robust solution used to access, filter, and analyze data. The data is quickly transformed into valuable information that can be published into a report.

Application Studio accesses data from multiple sources, including leading OnLine Analytical Processing (OLAP) and relational sources. Sources might include Enterprise Resource Planning (ERP) or SAP systems, locally managed databases or remote Point of Sales (POS) systems to name a few. Information is integrated via standard interfaces, allowing you to simultaneously present information from different data sources into one report. All reports are instantly web-ready and can be deployed using standard web browsers.

Infor BI Architecture

In Application Studio you build and view reports and applications. A reporting application is a suite of linked reports which support a particular business function, such as financial planning. Reports retrieve data from a database but can also be used to write back data to the database.

Reports are spreadsheets which contain lists of elements and associated values which are retrieved from databases through database aliases. You can define lists in a variety of ways and display them in a variety of list objects.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

4 Lesson 1: Introduction to Application Studio © 2016 Infor Education

Reports can be viewed in the full client (a smart client), in Infor BI Application Studio WebServices and in Infor Dashboards and Infor Motion Dashboards. There are some differences in behavior of reports in the full client and the web client. We will discuss these as we move through the course.

Reports are stored in a Report Catalog, in a hierarchical structure of folders and subfolders. Reports can be accessed from the report catalog based on user permissions and configuration.

Reports and report books can be exported and delivered to users in many different formats like Excel or PDF, or you can schedule report delivery.

________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Sample Application Studio Report

Application Studio and other Infor products are used by customers in a variety of ways, including:

Sales planning and budgeting Management control and reporting Productivity analysis Corporate performance management Sales reporting and analysis Consolidation, reporting and budgeting Inventory analysis and management Calculation of optimal production capacities

The following example displays a finished Application Studio report:

Application Studio Report

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

6 Lesson 2: Getting started © 2016 Infor Education

Lesson 2: Getting started Estimated Time

30 minutes

Learning Objectives


Describe a repository, project, report catalog, database alias, and cube. Explain the relationship between a project and report catalog.

Topics

Logging on to Application Studio The User Interface Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Logging on to Application Studio When you log on to Application Studio you are required to connect to a repository from the Log On dialog box. You then select an existing project and report catalog or add a new project and catalog. Therefore, it is important to understand how repositories, projects, and report catalogs are used.

Logon Dialog

Repository

A repository is a store for objects that the various Infor BI products have in common held in a SQL Server or Oracle relational database. The repository is administered with Infor Repository Administration.

Each repository contains users, groups, roles, projects, and report catalogs. Users and groups can be members of several different roles. Users and groups receive access to the projects and report catalogs through their membership of roles.

In this course we will use the Tutor repository which contains one project and one report catalog for us to start with.

Project

Projects and report catalogs are created and stored in a repository. You use projects to save groups of reports on specific topics. Each project can have its own set of user permissions and administration permissions. A project contains database aliases, global lists (which can also be used in Office Plus), and user rights.

Report Catalog

A project can contain multiple report catalogs, which store and organize multiple reports, style sheets and templates.

Database Aliases

Databases are accessed through an alias, so if the database or server name changes, all reports will continue to work. The database alias contains the registration information for the data source, including the server name, database name, and connection method. The alias is stored in the repository, and is available to any user connected to that repository.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Log on to Application Studio

Your instructor will demonstrate how to log on to Application Studio.

_______________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

Exercise 2.1: Log on to Application Studio

In this exercise, you will log on to Application Studio.

Exercise steps

1. Select Application Studio from the Windows Start menu.

2. Click the Options>> button.

3. Select Tutor Repository from the Repository drop-down menu.

4. Click Connect.

5. Click OK.

Note: Basic authentication will be used for access in this course. We will use the Admin user with no password to login and access our reports as well. However, you should consider the security structure to be implemented in your environment as it relates to user login accounts, password settings, and access to report data.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


The User Interface Application Studio consists of the following main components:

Menu Bar Toolbars Repository Explorer (Left pane)

Report Catalog Pane Database Structure Pane Accessories Pane

Report Area List Designer (Right pane)

You’ll use Application Studio to design and view reports. The following graphic displays a typical Application Studio view for a report designer:

Application Studio View

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Toolbars

Application Studio includes a standard toolbar and Mode toolbar.

Standard Application Studio Toolbar

The following table provides a brief description of each button on the standard toolbar:

Button Description

Show/Hide Left-docked and Right-docked Panes

Shows or hides the Repository Explorer (left-docked) and List Designer (right-docked) panes. Use F11 and F12 to show and hide as well

Previous and Next Moves through the reports you have viewed. These buttons are not enabled until you have viewed at least two reports.

Save Saves the report. This button is enabled in View, Debug, and Design view modes. You can switch between views without saving your changes. However, by default, you are prompted to save your changes when you move between reports. Note: You can change the default setting so that reports are automatically saved.

Print Preview Previews the report for printing.

Print Prints the report.

Export Exports the report to an external file format, such as PDF, Excel, Power Point, HTML or Images.

Splasher Selects cells and changes their values in the database according to a variety of rules. This button is enabled in View mode when you click a writable cell. Whether or not a cell is writable depends on the formula used and whether the cell is protected. For example, if a report shows that sales of a product in 2012 were 598,224 units, you could use the Splasher button to increase the value in the cell by 12%. Any future report returning the sales of that product in 2012 would return a value of 670,010 units.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Repository Explorer

Repository Explorer

In Report Catalog you can manage reports: create, rename, and manage permissions. In Database Structure you can see which data are registered in database aliases. You can expand the data structures and for example, drag dimensions into the report. In the Accessories pane you can manage variables, custom hierarchies and other accessories. By default, the auto-collapse option is selected, which collapses a pane when you expand another. In the above graphic, the Report Catalog is expanded. The Repository Explorer can be docked by right-clicking the title bar and selecting Docking or undocked by right-clicking and selecting Floating.

If the Repository Explorer is not visible, click the Left toolbar button.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


The Report Catalog Pane

The Report Catalog pane is located in the Repository Explorer and displays reports, style sheets, report books, scheduled tasks and templates. If the Report Catalog pane is not visible, click the Report Catalog title bar in the Repository Explorer.

Report Catalog Pane

Repository Project

Report Catalog

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


The Database Structure Pane

The Database Structure pane displays the repository you are logged into and any database aliases registered in that repository. If the pane is not visible, click the Database Structure title bar in the Repository Explorer.

Hierarchy

Multidimensional databases are well suited to data that has a hierarchical structure. That is, data that can be ranked according to age, size, importance, etc. Common analogies for hierarchical structure are tree structures or parent-child relationships. In Application Studio, data is referred to as parent, child, or sibling data. An element with no children is a base element.

Database Alias

To report on a database, you connect to it through a database alias. A database alias defines a connection to a local or remote database. Expanding the database alias will display the cubes within the database. Expanding a cube will display all dimensions within that cube.

Database Structure Pane

Database Alias Cubes Dimensions

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Cubes

A cube is a logical association of elements within a database that displays data in a multidimensional format. For example, a cube could contain three dimensions: Region, Product, and Month. Here we see a simple example of the data that might be referenced by the cube:

We can also display this structure graphically to better understand the relationship between the elements:

Dimension Product

Dimension Month

Dimension Region

Germany

Austria

Switzerland

Product 1

Product 2

Product 3

Jan

Feb

Mar

Germany

Austria

Switzerland Jan Feb Mar

Product 1 100 800 445Product 2 750 1200 660Product 3 1235 800 950Jan Feb Mar

Product 1 650 900 40Product 2 750 950 700Produkc 3 1500 100 1200Jan Feb Mar

Product 1 450 800 560Product 2 750 950 700Product 3 2500 2800 3500

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Dimensions

Dimensions are a fundamental building block of a multidimensional database. A dimension is a group of similar items. In our example we show three dimensions: Region, Product, and Month. We can define some rules that control and support the concept of a Dimension:

• Group of measures or list of items with similar characteristics (e.g.: group of products) • Measures / Items are called elements of the dimension • Dimensions define the structure of the cube • Dimensions do not contain data - The elements of the dimension serve as indexes for the values

stored in the cubes

Cells

This structure allows us to select individual cells. For instance, we can select the data for Product 2 sold in Germany during February:

Slice Or we can select a "slice" of data which includes all of the information in one dimension. Here we see a slice for product sales in Germany:

Ja

n

Fe

b

Ma

r Germany

Austria

Switzerland

Product 1

Product 2

Product 3

Jan

Feb

Mar

Product 1

Product 2

Product 3

Germany

Austria

Switzerland @ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Levels

In addition to the logical grouping of elements we've seen so far, we can also group elements in Levels. For example, a Product hierarchy could contain levels for All Products (all elements), Product Groups (tires), Product sub-Groups (winter tires or summer tires), and Products (model SX555). This describes a four-level hierarchy. We will see later that you can specify that only elements at a particular level of the hierarchy are displayed. Levels may be referred to by name; however, levels also have numerical identities. The parent or root of a hierarchy is at Level 1 and the levels and sub-levels are Level 2, Level 3, etc.

Demo: Database Alias

Your instructor will show the information defined in a database alias.

_______________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________ ________________________________________________________________________

Exercise 2.2: Database Alias

In this exercise, you will define a database alias.

Exercise steps

1. Click the Database Structure title bar in the Repository Explorer. The Database Structure pane opens.

2. Right-click the Database Aliases header. The actions menu displays.

3. Hover over New.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


4. Click New Database Alias... The New Database Alias dialog displays.

New Database Alias Database tab

5. Type Academy + <your initials> in the Name field.

6. Select Infor BI OLAP Provider from the Provider drop-down menu.

7. Select XMLA from the Driver drop-down menu.

8. Type localhost in the Server field.

9. Select localhost/Best_Practices_Template_Olap over http from the Database drop-down menu.

10. Click Test Connection. The message “Connection test successful” will display.

11. Click OK.

12. Click Yes.

13. Click the Log On icon found under the Database Structure title bar.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


The Accessories pane

The Accessories pane displays custom hierarchies, formula lists, global and report variables as well as report objects which can be used on your report. In the image below, the user only sees:

Custom Hierarchies Olap Meta Data Variable Lists

Formal Lists Global Variables

Custom Hierarchies Lists and Variables

Objects

Accessories Pane

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Report area

The report area, located in between the Repository Explorer and List Designer, displays the report in one of the three possible modes.

The Mode toolbar

Mode Toolbar

When building reports you will switch frequently between the different report modes. The following table provides a brief description of each button on the Mode toolbar:

Button Description

View Shows the report with all data calculated and formatting applied.

Debug Displays the report as it will appear in View mode, but with a limited number of list elements and results, making it faster when you are working with large lists. This mode also displays the report formulas, making it a useful debugging tool.

Design In this mode the actual report building is done. For example, this mode allows you to insert objects from the Database Structure, configure the information that displays, and specify how the information displays. Note: Although you construct and configure reports in Design mode, you can create and manage reports in any of the modes.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


The following example displays an Application Studio report in Design mode. In this view the report looks similar to an Excel spreadsheet.

Application Studio Report in Design Mode

Select View and the report appears as the user will see it with all formatting and calculations applied:

Application Studio Report in View Mode

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Check your understanding

Which two of the following are stored in a repository?

a) Fonts

b) Imported colors

c) Report catalog

d) Project

e) Hyperblocks

A __________ organizes reports and folders.

a) Report catalog

b) Repository

c) Folder

d) Project

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

22 Lesson 3: Creating reports © 2016 Infor Education

Lesson 3: Creating reports Estimated Time

1 hour

Learning Objectives


Describe how to create a report and folder. Explain how to add a hyperblock to a report. Describe how data formulas are used in a report. Explain how to apply styles to a report. Explain how to format cells, rows, and columns in a report.

Topics

Overview Creating folders and reports Adding hyperblocks to reports Drill down level on a hyperblock Lists List objects Adding data cell formulas Modifying hyperblock properties Adding combo box objects and naming them Using styles and style sheets Format your report Inserting text and images Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Overview A report is created using hyperblocks and formulas. The hyperblock calls the structures of the database, and the formula retrieves the data for the retrieved structures.

You may also access different views of data without having to amend any report formulas. This is achieved by using hyperblocks.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Creating folders and reports You create folders and reports in the Report Catalog. You can use multiple levels of folders to organize reports.

Demo: Create your first report

Your instructor will demonstrate how to select a folder and create a new report in the Report Catalog.

_____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________

Exercise 3.1: Create your first report

In this exercise, you will select a folder and create a new report in the Report Catalog.

Exercise steps

1. Click the Report Catalog title bar in the Repository Explorer. The Report Catalog pane opens.

2. Click the + next to Reports to display the sub-groups below. In this case, there are no sub-groups to display.

3. Right-click the Reports folder.

4. Select New > Folder.

5. Type <Your name> as the New Folder name.

6. Click anywhere on the screen to save the change.

7. Right-click the <Your name> folder.

8. Select New > Report... The Create Report dialog box opens.

9. Type Report 1 - Product Sales in the Name field.

10. Type Report_1_ProductSales in the Unique name field.

11. Select the Blank report option in the Template area.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


12. Select <None> from the Style sheet drop-down menu.

Creating a Report

13. Click OK.

Working in Design view

When you construct a report, you work in Design view. In Design view, the report spreadsheet and the Objects toolbar become available.

Design View

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Adding hyperblocks to reports A hyperblock is an object which displays a list of elements.

Hyperblocks can be vertical or horizontal. By default, they consist of two cells: a key output cell and a value cell.

The key output cell, by default, shows the name of the elements. It also provides an expand and collapse option. If you view a report in View mode, the elements of the list are displayed, but the hyperblocks are not visible.

The value cell can hold any formula.

Select the cells in the report grid where you want to position the hyperblock and drag a dimension from the Database Structure pane onto the selected cells.

HINT: You can also drag hierarchies (interesting for non-BI-OLAP databases like MSAS) and you can drag levels. When dragging a level, a list with that level selected will be created.

When a Hyperblock is selected for editing it shows a hashed border. Clicking outside of a hyperblock will deselect it and it will not be editable. To select it again, click its border. You can also select the cells within the Hyperblock.

If you have trouble selecting a Hyperblock, for example, if you have many nested Hyperblocks in your report, you can right-click anywhere in your spreadsheet and use the pop-up menu to select “Select Hyperblock > the Hyperblock you want” or use the report objects section from the accessories pane.

Inserted Hyperblock

You can click the rotation arrow in the upper left corner cell of a hyperblock to alternate its orientation between vertical and horizontal.

Product hyperblock

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Add hyperblocks to a report

Your instructor will demonstrate how to add a hyperblock to the report

_____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________

Exercise 3.2: Add hyperblocks to a report

In this exercise, you will add a vertical product hyperblock to your report.

Exercise steps

1. Click Design mode toolbar button. The report view changes to design mode.

2. Click and drag to select cells C13 and D13.

3. Click the Database Structure title bar to display the Database Structure pane.

4. Expand the Database Aliases folder.

5. Expand the Academy database and the SALES cube.

6. Drag the PRODUCT dimension and drop it onto the selected cells. This creates a PRODUCT hyperblock in the vertical orientation.

7. Click the arrow if the hyperblock arrow it is pointing to the left to make it point down.

8. Click View mode toolbar button to view the report. We see the product categories, but there will not be any data.

9. Click the + next to each product level to display the sub-groups below.

10. Click Design mode toolbar button to return to the grid.

11. Select the PRODUCT hyperblock.

12. Click the ellipsis. The Object Properties dialog appears.

13. Type hb_PRODUCT in the Name field.

14. Click OK. The hyperblock is now renamed to something more meaningful to help with referencing in the future.

15. Click and drag the column separator between columns C and D to expand the width of the column. (make the column about twice as wide)

16. Click and drag the column separator between columns D and E to expand the width of the column. (make the column about twice as wide)

17. Right-click cell D13.

18. Select Format Cells... The Format Cells dialog appears.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


19. Click Number from the Category column.

20. Click OK.

21. Click View mode toolbar button to view the report. We see the product categories, but there will not be any data.

22. Click Save.

23. Click the + next to each product level to display the product group for each individual product.

24. Open a web browser.

25. Type http://localhost/webservicesBasic in the URL field to access your report.

26. Type Admin in the User name field.

27. Select Tutor_RepositoryDB from the Repository drop-down menu.

28. Click Connect.

29. Click OK. Note: if there is a warning for Academy_RDB, click Skip.

30. Select Training > Report 1 - Product Sales in the navigation panel on the left. The consumer view of your report appears.

31. Close the browser application and return to Application Studio.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Drill down level on a hyperblock We've seen how we can create a report that only lists the highest levels of data with the option to expand or collapse the tree structure to view more detail. Reports that display all their data at once may not be convenient or easy to use. On the other hand, having to open a tree structure in your report level by level can become very tedious and unproductive. We can control the level of drill down displayed initially in the report using settings on the hyperblock.

Demo: Drill down level

Your instructor will demonstrate how to update the hyperblock settings to display all levels in the report by default.

_____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________ _____________________________________________________________________

Exercise 3.3: Drill down level

In this exercise, you will update the hyperblock settings to display all levels in the report by default.

Exercise steps

1. Click Design mode.

2. Click the Product hyperblock.

3. Click the ellipsis. The Object Properties dialog appears.

4. Select 6 from the Begin expansion at level drop down list.

5. Click OK.

6. Click View mode toolbar button to view the report. We see all of the product categories and product entries expanded. Note: You can still collapse the tree structure to hide some of the entries (i.e. zero values).


8. Click cell D12.

9. Type Actual in the cell.

10. Click Save to save your report.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Lists A list is a query on a dimension, defined by various rules and criteria. A list does not contain the elements of the dimension; rather, it stores the filters and selections that were sent to the server to create the list.

Earlier, when you dragged a dimension from the Database Structure pane and dropped it into the report to create a hyperblock, Application Studio created a local list.

If you make any changes to a local list in a report, the changes will only affect the report in which you created the list.

Local Lists

Editing Local Lists

In the first table of the following example, all of the products in the dimension are displayed. However, you may not always want to report on all data. Instead, you may want to display only a subset of data.

There are various ways of editing lists so that reports based on them include only a subset of the dimension elements. For example, you may want to display sales totals for summer and winter car tires, instead of every single product.

List Data

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Using the List Designer

You use the List Designer to edit a list. The List Designer becomes available when a hyperblock is selected. If the List Designer is not visible, use one of the following methods to display it:

Press F12 Click the Right button on the toolbar Select View > Right-Docked Panes

List Designer

The icons indicated below in List Designer are the Apply changes to the list icon and the Select Children, Select Parent, and Select Sibling icons, respectively. They are used to select elements. You can also right click on the item and these same icons appear for selection.

List Designer icons

Apply changes to the list icon

Select Children, Select Parent, Select Sibling icons

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Edit a Local List

Your instructor will demonstrate how to edit a local list to display different product selections.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Optional Exercise 3.4: Edit a Local List

In this exercise, you will edit a local list to display different product selections.

Exercise steps

1. Click the PRODUCT hyperblock. Make sure the crosshatch border displays which indicates the hyperblock is selected.

2. Click the Show/Hide Right-docked Panes icon on the toolbar to open the List Designer. The Structure Selection pane contains all of the product elements that can be displayed in the report.

3. Select the Winter Tires:

a) Right-click Winter Tires. A selection menu appears.

b) Select Children.

c) Click the Preview icon on the List Designer toolbar. The Preview pane opens and lists all of the Winter Tire elements that will display in the report.

4. Select the Winter Tires heading:

a) Click Children of Winter Tires.

b) Click the X icon to remove this selection.

c) Right-click the Winter Tires entry. A selection menu appears.

d) Select Elements underneath > All, Including Parent.

e) Click the Apply changes to the list icon on the List Designer toolbar.

f) Click View mode. The report displays with the elements selected based on the edited list.

5. Remove the selections in the list:

a) Click Design mode.

b) Click the Product hyperblock. Make sure the crosshatch border displays which indicates the hyperblock is selected.

c) Click Elements underneath Winter Tires (inclusive) in the List Designer.

d) Click the X icon to remove this selection.

e) Click the Apply changes to the list icon on the List Designer toolbar.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


List objects List objects are controls, which allow selecting an element from a list. They include:

Combo boxes List boxes Option buttons

We will also look at button objects later in this lesson, which are similar, but not the same as list objects.

List Objects

Adding a List Object

The method for adding and using combo boxes, list boxes, and option buttons in reports is the same. To add a list object to a report, you select the cell in the report grid where you want the list object to appear and click the list object button in the Objects toolbar. You then drag the appropriate dimension or list from the Database Structure onto the list object. Double-clicking the list object opens the Object Properties dialog box where you can name the object and edit other properties.

Demo: Add a List Object

Your instructor will demonstrate how to add a list object.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

List Objects

Button Object

Combo Box

List Box

Option Buttons

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Exercise 3.5: Add List Objects

In this exercise, you will add a list object (combo box) to allow for dynamic selection of data.

Exercise steps


2. Click cell D5.

3. Click the combo box icon in the Objects toolbar.

4. Drag the UNIT dimension from the Database Structure pane onto the combo box.

5. Click cell C5.

6. Type Company in the cell.

7. Click cell D6.

8. Click the combo box icon in the Objects toolbar.

9. Drag the TIME dimension from the Database Structure pane onto the combo box.

10. Click cell C6.

11. Type Time in the cell.

12. Click cell D7.

13. Click the Combo Box icon in the Objects toolbar.

14. Drag the PROFIT dimension from the Database Structure pane onto the combo box. Note: You may have to expand the Measures folder.

15. Click cell C7.

16. Type Measure in the cell.

17. Click Save.

18. Click View mode.

19. Click each drop-down to display the options available. Note: Even though we can select different settings (region, time, and measure) the values in the report do not change. We'll need to associate the selection in the combo box with the data formula.


21. Click in cell D13.

22. Press Delete on the keyboard to remove the formula.

23. Click the Edit OLAP Formula icon in the Objects toolbar. The Edit OLAP Formula dialog appears. Note that TIME, UNIT, and PROFIT are now associated with ReportObjects. These are references to the combo boxes that we created.

24. Select the ellipsis for VERSION.

25. Select Actual from the list.

26. Click OK.

27. Click OK. The dialog closes and now we see the results of our selections. Note the formula displayed that represents the selections includes the ReportObjects references. Let's change these to names that are more recognizable.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


a. Double-click the first combo box which is cell D5. The Object Properties dialog displays.

b. Type lv_Company in the Name field.

c. Click OK.

d. Repeat steps a. though c. for the Time (type lv_Time) and Measure (type lv_Measure) combo boxes.

e. Click cell D13. The formula now contains a more meaningful reference to the combo boxes (i.e. ReportObjects.Measure.Text).


29. Test the report by selecting different elements from the drop-down list. This should change the data in the report.

30. Click Save.

List Object Properties

The Appearance tab of the Object Properties dialog box includes options for controlling the size and position of a list object.

The following table identifies the Position options available and provides a brief description of each.

Position Description

Depends on the cell size and position

Allows a list object to expand and collapse as the size of surrounding cells. For example, if you have a horizontal hyperblock for the UNIT dimension in cell B4 and a list object in cell C6, the list object will expand and contract as you drill down and up through the UNIT dimension

Only depends on cell position

Allows a list object to retain its size, but it can still be moved out of position by other objects.

Independent of cells option selected

Allows a list object to retain its size and position, regardless of surrounding cells, even if this means that the list object will overlap another object in the report. For example, you can place a list object in an area of a report that is empty until you drill down in an adjacent object.

You can resize a list object without snapping it to the cell grid, by pressing the ALT key, while resizing it with the mouse.

The Properties tab of the Object Properties dialog box provides further options for changing the appearance of a list object. For example, you can specify that a list object have white text on a black background, with a yellow arrow on a red background.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Edit a List Object’s Properties

Your instructor will demonstrate how to edit the position and appearance of a list object.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 3.6: Edit a List Object’s Properties

In this exercise, you will edit the position and appearance of a list object.

Exercise steps


2. Click and drag to select rows 5-7.

3. Right-click and select Rows...

4. Click Custom under the Height row properties section.

5. Click the cm hyperlink to display the converter. Note: You can specify a value in inches, pixels, dpi, or points depending on the requirements of the report.

6. Highlight the value in the inch field.

7. Type 1 in the inch field. Notice the inch converts the value into 2.540 cm automatically.

8. Press Enter to have the system accept the change.

9. Click OK.

10. Right-click and select Rows...

11. Click Custom under the Height row properties section.

12. Type 0.6 in the cm field.

13. Press Enter to have the system accept the change.

14. Click OK.

15. Click Save.

16. Click View mode to display the report.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Adding data cell formulas The easiest way to add a cell formula and populate data from an OLAP database is to use the Edit OLAP Formula button; it is also a good way to make sure all dimensions in a cube are referenced.

Demo: Add a data cell formula

Your instructor will demonstrate how to add a formula to a data cell so the report displays budget values.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 3.7: Add a data cell formula

In this exercise, you will add a formula to a data cell so the report displays budget values.

Exercise steps



3. Drag the hyperblock to include column E.

4. Click cell E12.

5. Type Budget in the cell.

6. Click cell E13.

7. Click the Edit OLAP Formula icon. The Edit OLAP Formula dialog box displays.

8. Click the ellipsis next to VERSION.

9. Click Budget.

10. Click OK.

11. Click OK.

12. Click the column separator between columns E and F to drag to expand the column so that data displays in the cell.

13. Right-click cell E13.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


14. Select Format Cells...

15. Select Number in the Category column.

16. Click OK.

17. Click in cell B2.

18. Type Product Target Achievement as the title of this report.

19. Click Save.

20. Click View mode. The report now contains the values from the SALES cube.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Modifying hyperblock properties

Hyperblock object properties

You can improve the appearance of a report by editing the object properties of the hyperblocks in the report. The Object Properties dialog box includes the following tabs:

General Appearance

General tab

The following table identifies the options available on the General tab and provides a short description of each:

Option Description

Name Displays the name of the hyperblock. Hyperblocks are referenced in other places in the report (e.g. filters) and it is helpful to maintain a unique name.

List You can see whether a local or a global list has been used.

Begin expansion at level When a list is displayed hierarchically, this check box can be used to control which level of the hierarchy initially displays. When selected, you enter a level in the field to the right and the data will initially display in View mode with that level fully expanded. When cleared, the data will display in View mode with all levels fully expanded, without an option to collapse (the +/- controls aren’t given, then).

Indent When selected, each level will be indented as it is expanded when displaying the report in View mode:

+All years

+Year

+Quarter

Month

Note: When a hierarchy is displayed vertically, indenting the levels may make the structure of the hierarchy clearer. However, with horizontal hyperblocks, indenting will only increase the width of the column headings.

Appearance tab

The Appearance tab allows you to specify whether a hyperblock is displayed with a border around it and, if so, the color and thickness of the border.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Hyperblock output properties

You can edit output properties, such as position and value, for both output cells and the value cells of a hyperblock. Output properties can also be used to determine how elements fit within a cell by modifying column widths. For example, if a row heading is too narrow to display an entire element or a column is too narrow and displays #### in the cell, you can correct this using the Hyperblock Output Properties dialog box.

The following table identifies the options available in the Hyperblock Output Properties dialog box and provides a short description of each:

Option Description

Position By default, hyperblocks contain only two cells, an output cell and a value cell. However, they can be expanded vertically and horizontally to create additional space.

Value From this field, you can select different options for the display of elements in reports. For example, you can display their unique names, captions, attributes or create a user-defined option. This setting may be overridden by the number format applied to the key output cell.

Fill Style By default, this list only includes the Standard style. However, if you have created a level-specific format for the key output cell, it will appear in this list and can be selected. Note: This option is not available for the value cell.

Use current width Selecting this option uses the current width of a column.

Calculate automatically

Selecting this option automatically adjusts the cells to display the full value or element name, (depends whether you edit output cell option or value call option) when a report is displayed in View mode. This option is useful when values are too long to be displayed within the standard width of the key output cells. You can further define how to automatically calculate column widths using the following options:

Same width for all columns

By default, all column headings in a report have the same width. Select this check box to ensure that all column headings are wide enough to display their contents in full.

Minimum width

Select this check box to set a minimum width for a column.

Width depends on % of the elements

Select this check box to display only a percentage of the longest element. This is useful if there are a large number of elements and some are significantly longer than others.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Using styles and style sheets A style is a set of formatting characteristics, such as font type, size, color, and alignment that can be saved and applied to cells within a report. Styles allow you to quickly and easily apply formatting choices consistently throughout your report.

Applying styles

To apply a style, you select the cell(s) to which you want to apply the style, and select the style from the Styles drop-down list.

Style sheets

Styles that you create within a report are only available for that report. Style sheets; however, contain styles that can be applied to other reports.

Any report dropped into the Style Sheets folder in the Report Catalog can be used as a style sheet. However, only reports specifically designed for that purpose will make successful style sheets.

Applying a style sheet

You can apply a style sheet when you create a report or after you have created a report. Applying a style sheet copies any formatting and the style names from the style sheet to the new report. You can then use the styles by applying them to appropriate cells.

When you use styles from a style sheet, you are linking to the styles in the sheet. Formatting of a report will override formatting of the style sheet on which the report is based. Special settings override the default settings.

If you assign a style sheet to an existing report, or if you add new styles to a style sheet, you must copy the styles from the style sheet to the individual reports.

Styles Drop-down lists

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Apply a Style Sheet to a report

Your instructor will demonstrate how to apply a style sheet to a report.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 3.8: Apply a Style Sheet to a report

In this exercise, you will apply a style sheet to an existing report, copy the styles from the style sheet, and apply some of the styles.

Exercise steps


2. Click the Report Catalog title bar.

3. Click on the Report 1 - Product Sales report.

4. Click Show currently selected object properties icon on the Report Catalog toolbar. The Properties panel appears.

5. Click in the Style Sheet field under the Options section.

6. Click the drop-down arrow.

7. Select Style Sheets > Sample Solution Styles > Academy Style.

8. Click X to close the Properties dialog.

9. Right-click the Report 1 - Product Sales report.

10. Select Style Sheet > Copy Styles.

11. Click Save.

12. Click View mode to display the report in View mode. Note: The formatted title and toolbar added to the report. Note: Also, the column headers are not aligned over the data.

13. Click Design mode to return to the grid view.

14. Click and drag to select cells C12 through E12.

15. Click the Standard drop-down arrow.

16. Select Table Head - Right Aligned from the list.

17. Click in cell C13.


19. Select Text - Grid Line from the list.

20. Click and drag to select the number cells D13 through E13.

Completed Report

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



22. Select Number - Grid Line from the list.

23. Click and drag to select cells C5 through C7.


25. Select Combobox Label from the list

26. Click Save.


Switching between style sheets

You can easily switch between style sheets. If the style names in the new style sheet are identical to the style names already used in the report, the new styles will automatically be applied. If the report does not use one of the styles in the new style sheet, that style will have no effect on the report.

To remove a style sheet, select <None> from the Style sheet drop-down list in the Options section of the Properties pane.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Format your report Instead or in combination of using styles and style sheets you can change the appearance of reports by formatting cells, columns, and rows directly in every report.

Formatting cells

You use the Format Cells dialog box to format a cell’s number format, alignment, font, border, and pattern, and to protect or unprotect a cell.

To open the Format Cells dialog box, use one of the following methods:

Right-click a cell and select Format Cells. Select Format > Format Cells. Press Ctrl+1.

The following table identifies the tabs on the Format Cells dialog box and provides a brief description of each tab.

Tab Description

Number Format

Specifies how numbers in cells are displayed. Use the user-defined format for custom number formats when none of the existing formats is suitable or for displaying graphics in KPI formulas.

Alignment Specifies a cell’s text alignment and direction.

Font Specifies a cell’s font properties, such as a type, style, and size.

Border Specifies the type, color and width of borders to use around a cell.

Pattern Specifies a cell’s background pattern and color.

Protection Specifies whether cells and/or cell formulas are protected from change. Clear the Cells check box to make a cell editable. Note: By default, you cannot edit the content of a cell in View mode.

Formatting rows and columns

You use the Row Height dialog box to format rows in a report. To access the Row Height dialog box, you select Format > Rows or right-click a row heading and select Rows.

The Row Height dialog box allows you to:

Replace a row number with a title Adjust the row height for a selected row Change the default row height for all rows in a report Select the optimal row height for rows containing, for example, large font text Show and hide rows Create flexible row height (allow growth, allow shrink) Use variables to show and hide rows (used in Best Practices Templates)

You use the Column Width dialog box to format columns in a report. To access the Column Width dialog box, you select Format > Columns or right-click a row heading and select Columns. The Column Width dialog box includes formatting options similar to the Row Height dialog box. HINT: You can create reports which adjust to any window size, if you use the row and column settings wisely.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Applying report background images

You can apply a background image to a report by selecting Format > Background and selecting an image file to open. The image must be in .bmp, .jpg, .png or .gif file format. To remove a background image, simply deselect the Image checkbox.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Inserting text and images You can insert static and translatable text into a report, as well as images.

Inserting text

To insert static text in a report, you type the text into a cell in the report grid.

To insert translatable text in a report, you select the cell in which to display the text and then select Insert > Text to display the Edit Names and Texts dialog box.

From here, you can edit string, folder, and report names, if necessary. When you select a phrase and click OK, the =TRANSLATEUNIQUESTRING() function is inserted into a cell. The text string will display in the cell, and the function will display in the formula editor.

When inserting translatable text in a report, the text that displays varies depending on the report language selected. You can select a report language by selecting Extras > Language Settings.

Inserting images

To insert an image into a report, select Insert > Image, and select the image file to insert. You can also copy and paste an image into a report. After the image is inserted, you can resize and reposition it as necessary.

Optional demo: Insert images

Your instructor will demonstrate how to insert images.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Optional exercise 3.9: Insert images

In this exercise, you will insert images.

Exercise steps



3. Drag the hyperblock to include column H.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


4. Click cell F12.

5. Type Variance in the cell.

6. Click in cell F13.

7. Type =D13-E13 in the cell.

8. Click cell G12.

9. Type Variance % in the cell.

10. Click in cell G13.

11. Type =E13/F13 in the cell.

12. Right-click on cell G13.

13. Select Format Cells…

14. Select Percentage.

15. Click OK.

16. Click cell H12.

17. Type Score in the cell.

18. Click in cell H13.

19. Type =G13 in the cell.

20. Right-click on cell H13.

21. Select Format Cells…

22. Type [Graphic bpt_KPI_green_2]; [Graphic bpt_KPI_red_2]; [Graphic bpt_KPI_Neutral]; in the User-defined format instruction field.

23. Click OK.

24. Click and drag to select cells F12 through H12.


26. Select Table Head - Right Aligned from the list.


@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



You must be in View mode to insert a hyperblock into a report.

a) True

b) False

When you add hyperblocks, the dimensions you specify are included; however, you must also reference the remaining ________________.

a) Links to a report

b) Cells in the block

c) Related cubes

d) Dimensions in a cube

The Styles drop-down menu allows you to select the ____________.

a) Format for the contents of a cell

b) Report layout

c) Content used in a hyperblock

d) Position of an element in a report

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Lesson 4: Select structure data and create cross table Estimated Time

1 hour

Learning Objectives


Explain how to select elements in a report using various selection functions. Explain how to create a global list and insert it into a report. Describe how to change a global list to a local list. Describe how to replace a local list with global list.

Topics

Combine hyperblocks Global and local lists Structure Selection Freeze areas and visible report areas Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

50 Lesson 4: Select structure data and create cross table © 2016 Infor Education

Combine hyperblocks You can overlap hyperblocks so that they share value cells. Using two hyperblocks and inserting a formula in the shared value cell is a basic technique in defining reports. It creates a tabular report in which the list elements in the horizontal hyperblock form the row headings, and those in the vertical hyperblock form the column headings. The formula populates the body of the report. In this lesson we will create a report that displays product sales over time that look similar to this:

Trend Report

Demo: Combine hyperblocks

Your instructor will demonstrate how to create a report using multiple hyperblocks.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Time

Product

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Exercise 4.1: Combine hyperblocks

In this exercise, you will create a report using multiple hyperblocks to show product sales over time.

Exercise steps

1. Create a new blank report:

a. Right-click on the Training report folder.

b. Select New > Report...

c. Type Report 2 - Trend in the Name field.

d. Type Report_2_Trend in the Unique Name field.

e. Click the Style Sheet drop-down arrow.

f. Click the + sign next to Style Sheets.

g. Click the + sign next to Sample Solution Styles.

h. Select Academy Style.

i. Click OK.

j. Click Design mode.

k. Click in cell B2.

l. Type Trend in cell B2. The style sheet will format anything in cell B2 as the report title.

2. Click the Database Structure title bar to display the Database Structure pane.

3. Expand the Academy database and the Sales cube.

4. Drag the PRODUCT dimension and drop it onto cell C10. This creates a PRODUCT hyperblock in the vertical orientation.

5. Click and drag the column separator between columns C and D and drag to the right to create more space in column C.

6. Drag the TIME dimension and drop it onto cell D9. This creates a TIME hyperblock in the horizontal orientation.

7. Click and drag the column separator between columns D and E and drag to the right to create more space in column D.


9. Insert an ROC formula:

a. Click the Edit OLAP Formula icon.

b. Click OK.

10. Click Save.

11. Click View mode to display the report in View mode. The report includes all products in the first column, a total in the second column and the values for each year in additional columns moving to the right.


@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


13. Change the selection to display the months of a year instead of the whole dimension:

a. Click the TIME hyperblock. Make sure the crosshatch outline appears around the hyperblock.

b. Click the + next to All Years in the List Designer. The list expands to display all years.

c. Right-click on 2014.

d. Click Elements underneath.

e. Click Month.

f. Click Preview on the toolbar. The selections appear in the Preview window below.

g. Click Apply Changes to the list icon on the toolbar.

h. Click View mode.

14. Create a combo box to select the year to display in the report:

a. Click Design mode.

b. Click in cell D7.

c. Click the Combo Box icon on the left.

d. Click and drag the TIME dimension from the SALES cube to the selected cell.

e. Right-click cell D7.

f. Click Object Properties.

g. Type lv_Year in the Name field.

h. Click OK.

i. Right-click All Years in the List Designer.

j. Click Children.

k. Click Apply Changes to the list icon on the toolbar.

l. Click in cell C7.

m. Type Year in the cell C7.

15. Apply the year selector to the time display:

a. Click the TIME hyperblock.

b. Click Elements underneath 2014 at level Month in the Structure Selection. The Selection Settings appears below.

c. Select 2014 under the Selection Settings section.

d. Click the down arrow next to 2014.

e. Click Edit Formula...

f. Click Variables on the toolbar.

g. Click the + next to List Views.

h. Double-click lv_Year. The display changes in the results column to show a reference to Report Objects and our local list.

i. Click OK.

j. Click Apply Changes to the list icon on the toolbar.

k. Click View mode.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


l. Click the down arrow next to Year.

m. Select a different year and view the results in the report.

16. Click Save.

Aligning report headings above the data in each column depends on the data to be displayed. If the column contains a hierarchy (i.e. year, quarter, month) then by default the heading will be aligned left to support the expand/collapse option. If the columns do not contain expansion levels (i.e. only months are included) then you can disable the expansion option. (Step 17 below reviews this process).

17. Apply report formatting to align the column headings:


b. Right-click the TIME hyperblock.

c. Select Object Properties.

d. Click to deselect the check box next to Begin expansion at level.

e. Click OK.

f. Click in cell D9.

g. Click the Standard drop-down arrow.

h. Select Table Head - Right Aligned in the drop-down list.

i. Click in cell C10.

j. Click the Standard drop-down arrow.

k. Select Text - Grid Line in the drop-down list.

l. Click in cell D10.

m. Click the Standard drop-down arrow.

n. Select Number - Grid Line in the drop-down list.

o. Click in cell C7.

p. Click the Standard drop-down arrow.

q. Select Combobox Label in the drop-down list.

r. Click Save.


19. Add the Version to display how the data is calculated:


b. Right-click on row 6.

c. Click Insert Cells.

d. Click in cell C7.

e. Type Version in the cell C7.

f. Select cell C8.

g. Click the Format Painter icon on the toolbar on the top to apply the format from cell C8 to C7.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


h. Click in cell D7.

i. Click the Combo box icon on the toolbar on the left.

j. Click and drag Version from the database structure and drop it on cell D7.

k. Right-click on cell D7.

l. Select Object Properties…

m. Type lv_Version in the Name field.

n. Click OK.

o. Click in cell D11.

p. Press Delete.

q. Click Edit OLAP Formula icon on the left toolbar.

r. Click OK.

s. Click Save.

20. Click View mode. Your report now has the option to select Budget or Actual values.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Global and local lists Instead of working with local lists in several reports and building the same selections and filter again each time, you can use global lists. For example, you can create a global server list for one specific year or for the months in each quarter. A local list is available only in a single report. Global lists are available throughout all reports, so changing the definition of a global list will affect all the reports using this global list. In practice, global lists are often used on common filter objects of reports; for example, the list for the Time Dimension.

Creating a global list

Global lists are created in the Database Structure pane. You then use the List Designer to define the contents of the global list just as you did when you edited a local list.

You can create a new global list in View, Debug, or Design mode.

Inserting a global list into a report

As you saw previously in this lesson, when you drag a list from the Database Structure into a report, it is copied into a local list. To insert a global list into a report, hold down the Ctrl key while dragging and dropping the list. You create local lists in reports by dragging dimensions, hierarchies or lists from the Database Structure. However, in order to insert a global list into a report, you can only drag a list.

Demo: Create a global list and insert it into a report

Your instructor will demonstrate how to create a global list and insert it into a report..

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 4.2: Create a global list and insert it into a report

In this exercise, you will create a global list for the companies and insert it into our Trend report.

Exercise steps


2. Create a new global list:

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


a. Click the Database Structure title bar to open the list.

b. Click the + next to Academy + <your initials> and Sales.

c. Right-click UNIT.

d. Select New > Server List. Note: You can create global and local lists as server or client lists. We will focus on server lists in this course as these are standard.

e. Type Companies in the name field.

f. Right-click Total Group in the List Designer.

g. Click Children.

h. Click Apply Changes to the list icon on the toolbar.

3. Insert the global list in the report:

a. Right-click on row 6.

b. Click Insert Cells.

c. Click in cell C7.

d. Type Company in the selected cell.

e. Apply the style Combobox Label for cell C7.

f. Click in cell D7.

g. Click the Combo box icon on the toolbar on the left.

h. Ctrl-click and drag Companes from the database structure and drop it on cell D7.

i. Right-click on cell D7.

j. Select Object Properties…

k. Type lv_Company in the Name field.

l. Click OK.

4. Associate the list with the data in the report:

a. Click in cell D12.

b. Press Delete to remove the formula.

c. Click the Edit OLAP Formula icon. The UNIT selection already recognizes the global list placed into the report.

d. Click OK.

5. Click Save.

6. Click View mode.

7. Select another option in the Companies drop down list. The report updates automatically based on your selection.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Changing a global list to a local list

If you are using a global list in a report and decide that you should really be using a local list, you can copy the global list to the hyperblock by editing the object properties of the hyperblock. Open Hyperblock Properties and click Copy to local.

Change a list

Replacing a local list with a global list

If you are using a local list and want to use a global list instead, select the global list from the List field in the Object Properties dialog box.

Optional Demo: Change a global list and replace a local list

Your instructor will demonstrate how to change a global list and replace a local list with a global list.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Optional exercise 4.3: Change a global list and replace a local list

In this exercise, you will change a global list (Companies) and replace a local list with a global list.

Exercise steps


2. Change the settings for the global list:

a. Click the global list Companies in the Database Structure.

b. Click the check box next to Total Group in the List Designer.

c. Click Apply Changes to the list icon on the toolbar.

d. Click Save.

3. Click View mode.

4. Click the down arrow next to Company.

5. Select Total Group. The report updates the totals for all companies.

6. Replace a local list with a global list:

a. Click the Report Catalog title bar.

b. Click Report 1 - Product Sales. The report opens.

c. Click Design mode.

d. Right-click the Company combo box in cell D5.

e. Click Object Properties...

f. Click the down arrow next to (local list).

g. Select the + next to Academy + <your initials> > SALES > UNIT > UNIT > Companies.

h. Click OK.

i. Click Save.

j. Click View mode. The global list is now used in multiple reports. Any future changes applied to the global list will appear in all reports where it is selected.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Structure Selection The Structure Selection pane in the List Designer provides various ways for you to choose what data is visible in a report. Items you want to display can be selected as individual elements or with reference to their position in a hierarchy.

Structure Selection Pane

Selecting all base elements

Selecting each element individually can be tedious, especially if it is a large dimension. The All Base Elements option allows you to easily select all elements that do not have children – ones that do not have elements underneath them in the hierarchy.

Selecting Children, Parent, and Siblings

When using the Children, Parent and Sibling functions, the following will be selected:

Children: Elements under a parent

Parent: Element above a selected element

Siblings: Elements at the same level with a common parent

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Selecting Ranges

With the Range function, you can select items from one point in the hierarchy to another point and include all the items in between. For example, selecting the range: FROM Car Tires Summer TO Commercial (Van Tires) displays: Car Tires Summer, Car Tires Winter, and Commercial (Van Tires) in a report.

Selecting Descendants

With the Descendants function, you select a specific element as the parent and return its descendants. You can also select the level of the descendants to be displayed.

Selecting Periods to Date

When you select an element, the Periods to Date function returns all of the other elements within hierarchy that are at the same level in the hierarchy, up to, and including, the selected element. Elements that come after the selected element are not returned. As the name suggests, the Periods to Date function is intended for use with time-based lists.

Selecting Previous Periods

The Previous Periods function allows you to select an element and specify how many elements before it you want to return. For example, if you select May, and specify 3 as the index, March and April are returned.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Freeze areas and visible report areas The display of objects in reports can be affected by adjacent objects. For example, the space between a chart and its legend may widen and narrow when you drill up and down through the data displayed in an adjacent hyperblock. To prevent this you can define a freeze area. Objects within a freeze area do not expand. You can also specify that only part of a report is visible. This can be used, for example, to hide data and calculations on which the report is based. The visible area starts always in cell A1 and ends in the bottom right cell you select. Use the Visible Report Area icon from the toolbar .

Visible report area

Creating a freeze area

To specify a freeze area: 1. Highlight the cell or cells to freeze.

2. Click Horizontal Freeze Area or Vertical Freeze Area on the Objects toolbar.

3. To create a horizontal and vertical freeze area, click Freeze Area.

4. To remove a freeze area, select it and click the appropriate freeze area button.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



Global lists are created in the ____________.

a) Report Catalog.

b) Database structure.

c) Accessories panel.

d) List designer.

Open Hyperblock Properties and click Copy to local is one approach used to ____________.

a) Copy a global list to a hyperblock.

b) Change a local list into a global list.

c) Make a cell non-writable.

d) Convert an embedded formula.

If you are using a local list and want to use a global list instead, select the global list from the List field in the Object Properties dialog box.

a) True

b) False

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Lesson 5: Filter and sort data Estimated Time

1 hour

Learning Objectives


Explain how to filter data by value. Describe how to filter report data to suppress zero and null values. Describe how to use aggregation functions. Explain how to sort report data.

Topics

Filtering report data Sorting Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

64 Lesson 5: Filter and sort data © 2016 Infor Education

Filtering report data Filtering and sorting are extremely powerful and versatile features used to display data in Application Studio. While filtering extracts elements from a list; sorting changes the order of elements in a list. For example, one common use of filters is to suppress zero values. A frequently used sorting is showing the top 10 elements in a list.

Filter options

Filtering by value

Filtering by value allows you to extract data to display based on a specific value. For example, when you filter to suppress zero values, you set the Filter By field to Value, select Does not Equal in the Condition field, and type 0 in the Value field. This indicates that the filter should extract and display all values that are not zero.

Demo: Suppress zero values

Your instructor will demonstrate how to suppress zero values in a report.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Exercise 5.1: Suppress zero values

In this exercise, you will suppress zero values in a report.

Exercise steps

1. Duplicate a report and rename it for this exercise:

a. Right-click on Report 1 - Product Sales in the Report Catalog.

b. Click Duplicate.

c. Right-click Copy of Report 1 - Product Sales.

d. Click Rename.

e. Double-click on the Name.

f. Type Report 3 - Zero Suppression in the Name field.

g. Type Report_3_ZeroSuppression in the Unique name field.

h. Click OK.

i. Click Design mode.

j. Type Zero Suppression in cell B2.

2. Select Total Group in the Company drop-down list.

3. Select the lv_Time combo box

4. Select the + next to All Years in the List Designer.

5. Right-click on 2014.

6. Highlight 2014.

7. Select Children from the menu.

8. Click View mode.

9. Select 2014 Quarter 4 from the Time drop-down list to see that there are values where 0 are in place for both budget and actual.

10. Select 2014 under All Years in the Time drop-down list. Notice that some of the report fields have zeros in one or more columns. We will suppress these from the display using a filter.



13. Click the Filter title bar in the List Designer.

14. Click the New Filter icon.

15. Click the Caption drop-down list under Filter By in the Filter Settings area.

16. Select Value.

17. Double-click on the Value field.

18. Select Does not equal from the Condition drop-down list.

19. Type 0 in the Value field.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


20. Press Enter on the keyboard.

21. Click the Fill button under Data Area. The report fields are automatically listed showing where the products will be filtered on. We want to make sure to make the same selections as in the data formula on the spreadsheet.

22. Select CURRTYPE under Hierarchy. Note: You may have to scroll in the list to find CURRTYPE.

23. Select Current element to the right of CURRTYPE.

24. Click the ellipsis for the Current element.

25. Double-click Local Currency. The element appears in the right-hand column.

26. Click OK.

27. Select VERSION under Hierarchy.

28. Select the ellipsis for the Current element.

29. Double-click Actual. The element appears in the right-hand column.

30. Double-click Budget. The element appears in the right-hand column.

31. Click OK.

32. Click Apply changes to the list.

33. Click Save.

34. Click View mode. There are several rows of data that have been removed from the report where a zero value appeared in both the Actual and Budget column.

35. Select OVERSEA Holdings from the Company drop-down list. Notice that all of the rows have been suppressed since we did not have data for this set of data.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Filtering to suppress null values

By typing null in the Value field of the Filter Settings, instead of 0, the method for suppressing zero values can be used to suppress empty cells.

It is important to understand the difference between null and zero values. A field or cell that contains a null value is completely empty. In contrast, a cell with a zero value contains the value 0. This is significant because zero can be used in calculations, but null cannot. Therefore, if the data on which you are reporting contains both null and zero values:

A filter that looks for all values equal to null will not return any zero values. A filter that looks for all values that are not null will return zero (and other) values.

To suppress null or empty values, specify NULL in the filter value field as shown above.

Using aggregation functions

Aggregation functions provide further ways of extracting specific data. With these functions, you can create filters for any, average, maximum, minimum, and sum.

For example, to use an aggregation function to display PRODUCT elements whose average of Q1 and Q2 values are greater than 180,000:

1. Create a filter for values greater than 180,000.

2. Select Average as the Aggregation function.

3. Select both 2014_Q1 and 2014_Q2 for the TIME dimension.

Aggregation functions will only work if you have selected at least two elements in at least one of the dimensions.

Suppress Null Values

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Sorting report data As you now know, filtering extracts elements from a list, while sorting changes the order of elements in a list.

Sorting options

Sorting by Caption/Unique Name

Typically, sorting by Caption or Unique Name will produce the same result. By default, the captions of list elements, not their unique names, display in a report. Unique names are different, but are usually structured in such a way that when you sort them, they will be listed in the same order as the captions.

You can view the order by creating a second key output cell in the hyperblock and using it to display the unique names of the elements next to their captions.

Sorting by Value

To sort report data by value, you specify Value as the Type and Ascending or Descending as the Order in the Sorting Criteria Settings of the Sorting pane of the List Designer.

Break Hierarchy

By default, when you sort a list, its hierarchical structure is maintained. This means that elements are sorted within their levels according to the criteria you choose, but the overall list is not. You can break the hierarchy by clicking Break Hierarchy.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Sorting by Value with Cut-Off Type

A common use of sorting with a cut off is to report on the top values in a list; for example, the top five selling products.

This example uses the Top Count cut-off type to report on the top five performing products. The Top Sum cut-off type displays the smallest list of values, which, when added together, are greater than or equal to an amount you specify. So, you could use this to list all products whose total turnover is greater than $n.

The Top Percent cut-off type works in a similar manner. It provides the fewest values, which, individually or combined, form a specified percentage of the total.

The following table lists the available cut-off types and provides a brief description of each.

Position Description

Top Sum Specify the sum of the elements that are displayed. (Example Show products, whose total turnover is at least 50.000)

Top Count Specify how many elements with top values are displayed. (Example: Show the top 5 products)

Top Percent Specify the % of the total sum of the elements that are displayed.

Bottom Sum Specify the sum of the elements that are displayed.

Bottom Count Specify how many elements with bottom values are displayed. (Example: Show the bottom 5 products)

Bottom Percent Specify the % of the total sum of the elements that are displayed.

Enabling single sorts and filters

You can easily enable or disable a single sort or filter by selecting it from the list and then selecting True or False from the Enabled field in the Settings section of the Sorting pane.

This is different from selecting the Enabled check box in the Filter or Sort pane’s title bar. When you select the Enabled check box, all of the filters or sorts will be enabled.

You can use variables to enable and disable single sorting. Create a simple hyperlink action to set a variable to TRUE or FALSE and use it in the filter. Or use an action to show top 5, 10 or 25.

You can also enable single sorts or filters using a variable, triggered by an action on the report, which allows a user to interactively select the type of sort or filter they want to perform.

Demo: Sort by value with cut-off type

Your instructor will demonstrate how to create sorting criteria that returns the top 10 products.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Exercise 5.2: Sort by Value with Cut-Off Type

In this exercise, you will sort products by value in descending order and view the sorted report.

Exercise steps

1. Right-click Report 1 - Product Sales in the Report Catalog.

2. Click Duplicate.

3. Right-click Copy of Report 1 - Product Sales.

4. Click Rename.

5. Double-click the Name field.

6. Type Report 4 – Sorting in the Name field.

7. Type Report_4_Sorting in the Unique name field.

8. Click OK.


10. Type Top 10 Report in cell B2.

11. Click the Product hyperblock for editing. Make sure the crosshatch border is displayed.

12. Click the drop-down arrow next to Select Level under Structure Selection.

13. Click Size.

14. Click the Sorting title bar to open the selection panel.

15. Click the New Sorting Criterion button.

16. Click Caption under the Type column.

17. Select Value from the drop-down list.

18. Select Descending in the Order drop-down list.

19. Click the Fill button under Data Area.

20. Click the CURRTYPE under the Hierarchy column.

21. Click Current element for CURRTYPE.

22. Click the ellipsis for Current element.

23. Double-click Local Currency.

24. Click OK.

25. Click VERSION under the Hierarchy column.

26. Click Current element for VERSION.

27. Click the ellipsis for Current element.

28. Double-click Actual.

29. Click OK.

30. Click Break Hierarchy on the Sorting toolbar to sort the products regardless of their membership to product groups.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



32. Click Save.

33. Click View mode. The report is sorted by the Actual values for each product with the highest selling products displayed at the top of the list.


35. Click the Product hyperblock for editing. Make sure the crosshatch border is displayed.

36. Click the Value of data area descending sorting criteria you just created in the Sorting pane.

37. Click None under Cut-off Type. We want to display only the top 10 records in the report.

38. Select Top Count from the drop-down list.

39. Type 10 in the Cut-off Value field.


41. Click Save.

42. Click View mode. The report displays the top 10 products sorted by the Actual values for each product.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



By default, when you sort a list, its ____________ is maintained.

a) Current order

b) List of Top 10 elements

c) Sequence

d) Hierarchical structure

When you filter to suppress zero values, you ____________. Select all that apply.

a) Select Zero Value filter on the tab

b) Set the Filter By field to Value

c) Select Does not Equal in the Condition field

d) Type 0 in the Value field

e) Type NULL in the Value field

By typing null in the Value field of the Filter Settings, instead of 0, the method for suppressing zero values can be used to suppress ____________.

a) Hidden elements

b) Empty cells

c) Zero values

d) A Variance

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Lesson 6: More interactive reports by using Actions Estimated Time

1 hour

Learning Objectives


Describe how to create and use Actions and variables. Define a global variable and report variable Explain how a button is used to generate commands. Explain how images can be used to design buttons Describe how to enable single filters.

Topics

Global and Report Variables Actions Managing variables Command Buttons Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

74 Lesson 6: More interactive reports by using Actions © 2016 Infor Education

Global and report variables

Variables

Variables are used to represent types of data (numbers, characters or character strings) instead of specific items. When a report is calculated or refreshed, or an action is triggered in it, variables can be filled with actual data of the specified type. Variables can be used to pass values from one list object to another and can be used in formulas and actions. Variables can be global or local. Global variables are available throughout a report catalog. Report variables are available only in the report in which they are created.

Combo boxes do not automatically remember selections you make. However, you can use variables to remember the selection and to pass a value selected in one combo box to another combo box in another report. This feature is useful for example, in linked reports such as summary and detail reports. Such as, if you select a particular year, product, or region in a summary report, the same criteria will be selected when you open the related detail report.

Variables are the means to add interactivity and dynamic behavior to reports. When using variables in actions, a report can do nearly everything:

Change conditional formatting Change lists Change the data you are looking at (change filters) Hide and show rows and columns

Demo: Create a variable

Your instructor will demonstrate how to create a variable.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 6.1: Create a variable

In this exercise, you will create a variable.

Exercise steps

1. Select Report 3 – Zero Suppression from the Report Catalog.


3. Open the Accessories pane from the Repository Explorer.

4. Right-click the Report Variables folder.

5. Select New > Report Variable.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


6. Type rv_ZeroSuppression_OnOff for the Report Variable name.

7. Select the blank area to the right of the rv_ZeroSuppression_OnOff variable.

8. Click the ellipsis.

9. Type True.

10. Click OK.

11. Click the Product hyperblock. Make sure the crosshatch border is displayed.

12. Select Filter from List Designer.

13. Select the Enabled drop-down list under Filter Settings.

14. Select Edit Formula...

15. Expand Report Variables.

16. Double-click rv_ZeroSuppression_OnOff.

17. Click OK.

18. Click View mode. Based on the value of the variable (TRUE) the report displays with zero suppression enabled.

19. Select Accessories in the Repository Explorer (Left pane).

20. Select Report Variables.

21. Double-click rv_ZeroSuppresion_OnOff.

22. Change the value of the variable to FALSE. The report automatically updates to show all products.

23. Press Enter.

Input and output variables

You can use an action, for example, to link two reports. When a user clicks a link in one report, the second report opens. If the linked reports both contain combo boxes, you can use input and output variables to ensure that a selection made in one report is automatically selected in the other. Note: If you are designing reports for use in dashboards, you will need to use report parameters in addition to input and output variables. To use input and output variables to pass values between combo boxes:

1. Create two reports, each with a combo box containing, for example, a Time list.

2. In Design mode, double-click the combo box in one of the reports. The Object Properties dialog is displayed.

3. Select Output Variable and select the appropriate global variable from the drop-down list.

4. If no suitable variable exists, create one. For example, Time.

5. In the second report, select Input Variable in the Object Properties dialog. Select the same global variable as in the first report.

6. The date selected in the combo box of the first report is automatically selected if you open or jump to the second report. To pass values from and to both reports, specify both Input and Output variables in both reports. If the reports will be used in a dashboard, add report parameters.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Creating global variables

You will need to use a global variable whenever the value will be passed between reports. Global variables are accessible to all of the reports in a report catalog. Report variables are only available in the report for which they are created.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Actions Actions can be defined on reports, and on the cells and objects within them. Actions can be triggered by various events. For example, an action may be a jump to another report, which is triggered by clicking on a cell. Or, the action may set the value of a variable, and be triggered by a drag and drop event or by editing a cell. An action definition consists of one or more actions. Each has a mode, a condition and a type. The mode describes the event which triggers the action (for example, right-click or drag). The condition specifies the condition that must apply for the action to be triggered. The type describes the type of event that is triggered (for example, a jump to another report or setting a parameter). You can copy and paste an action definition (which may contain several individual actions). Or, you can copy a single action within an action definition. Actions also can have inputs and parameters.

Action modes

The action mode describes the event which triggers the action. This could be a right-click or a drag event. As an example, if you are using a button to create action on a report the button click is the event that would trigger the action.

Action mode Description

Hyperlink Direct action

Hyperlink and shortcut menu on right-click

Direct action on left-click; Right-click opens the shortcut/context menu.

Shortcut menu on left-click Opens the context menu with additional options on left-click.

Shortcut menu on right-click Opens the context menu with additional options on right-click.

Edited cell Action occurs if user enters data in the cell where the action resides.

Drag Action that occurs when user does the dragging.

Drop Action is performed after drag and drop.

Chart click Action is performed when user clicks on chart/diagram.

Dialog OK Action is performed if dialog has been closed with OK.

Dialog cancel Action is performed if dialog has been closed with Cancel.

Conditions

You can specify the circumstances in which an action is triggered. In many cases the default condition =TRUE can be used. For example, this action passes the value held by report variable rv_One to report variable rv_Two, when a user clicks a hyperlink:

Type: Set parameters Condition: =TRUE

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Mode: Hyperlink Parameter name: rv_Two Parameter value:=ReportVariables.rv_One.text

Action types

The action type describes the type of event that is triggered (for example, a jump to another report or setting a parameter). Select the action type from the type list.

Action type Description

Jump Defines a jump to a target report

Recalculate report Recalculates the report

Select element Opens the Find Element dialog.

Set parameters Sets the parameters that are assigned to the report.

Export report Exports the report in one of these formats: Export formats: HTML, PDF, Excel

Print report book Prints a report book.

Open dialog Opens a specified target report in a new window.

Close dialog Closes a dialog which has been opened by an Open dialog action and, optionally, sets parameters.

Application Engine process Runs a process created in the Infor ION BI Application Engine.

Drillback in Ming.le If Application Studio is running in Infor Ming.le, you can define an action which drills back to the application on which Application Studio is reporting.

Jump to dashboard Enables jumps from one dashboard displayed in Infor ION Dashboards or Infor Motion Dashboards to another.

Demo: Create a jump action

Your instructor will demonstrate how to create a simple jump action.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Exercise 6.2: Create a jump action

In this exercise, you will create a jump action to move from Report 1 to Report 3.

Exercise steps

1. Click the title bar for the Report Catalog.

2. Click Report 1 - Product Sales to open the report.


4. Select cell C9.

5. Type Go to Report 3 in the selected cell.

6. Right-click cell C9

7. Click Define Action. The Define Action dialog appears.

8. Click Set Parameters under Actions.

9. Select Jump from the drop-down list.

10. Double-click the value field for Target report under Inputs.

11. Select Report 3 - Zero Suppression.

12. Click OK.

13. Click OK to close the Define Action dialog.

14. Click Save.


16. Click Go to report 3. Report 3 appears. This was a simple example of how we can use actions in a report to create interactivity. We'll return to this approach later in the course.

Optional demo: Create an action with a multiselection element

Your instructor will demonstrate how to create an action to implement multiselection on an element.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Optional exercise 6.3: Create an action with a multiselection element

In this exercise, you will create an action to implement multiselection on an element.

Exercise steps


2. Right-click Report 4 - Sorting.

3. Select Duplicate.

4. Right-click Copy of Report 4 – Sorting.

5. Click Rename…

6. Double-click the Name field.

7. Type Report 5 – Multiselection in the Name field.

8. Type Report_5_Multiselection in the Unique name field.

9. Click OK.


11. Create two report variables to use for this report:

a. Click the Accessories title bar under Repository Explorer.

b. Right-click Report Variables.

c. Select New > Report Variable.

d. Type rv_SelectCompany as the name.

e. Press Enter on the keyboard.

f. Right-click Report Variables.

g. Select New > Report Variable.

h. Type dummy as the name.

i. Press Enter on the keyboard.

12. Update an element to use the multi-selection option:

a. Click the Company combo box in cell D5.

b. Press Delete to remove the object.

c. Click cell D5.

d. Type Select Element in cell D5.

e. Click the Define Action icon in the toolbar on the left.

f. Click Set parameters under Actions.

g. Select Select Element from the drop-down list.

h. Click the value field next to Global List under Inputs.

i. Click the down arrow.

j. Click the + next to Global lists.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


k. Select Companies.

l. Double-click in the value field for Select Element under Inputs. The Edit Value dialog appears.

m. Click the + next to Report Variables.

n. Double-click rv_SelectCompany. A formula appears in the selected list.

o. Click OK.

p. Double-click the value field next to Action result.

q. Click the down arrow.

r. Click the + next to the Report variables.

s. Select dummy.

t. Click the check box next to Enable multiselection.

u. Double-click <New parameters> under Parameters.

v. Click the + next to Report variables.

w. Select rv_SelectCompany from the drop-down list.

x. Double-click =TRUE under Value.

y. Click the + next to Report variables.

z. Double-click dummy. A formula is added to the selected list.

aa. Click OK.

bb. Click OK.

cc. Click Save.


14. Click the Select Element link. A pop-up window appears with options to select one or more elements from the database.

15. Select multiple check boxes.

16. Click OK. While we are able to select the elements, notice that the report is displaying errors under Actual and Budget. Let's correct these errors.


18. Click cell D13. Notice that the formula has a reference to the combobox that was removed and replaced with the action. The old reference was "ReportObjects.lv_Company.Text".

19. Click and drag to highlight ReportObjects.lv_Company.Text in the formula.

20. Type rv_SelectCompany.text to replace the highlighted text.

21. Click cell D13.

22. Repeat steps 19 and 20 to update the formula for Budget in cell E13.

23. Click Save.

24. Click View mode. Now the data appears but our sorting function does not work. The sort selection was also based on the value in the combobox. Let's go fix that reference as well.


26. Click the PRODUCT hyperblock.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


27. Click the Sorting title bar in the List Designer.

28. Click Top count 10 of data area.

29. Click the selection value next to UNIT.

30. Click the ellipsis. The Select Elements dialog appears.

31. Click the Variables tab.

32. Click the + next to Report Variables.

33. Select the formula listed on the right.

34. Press Delete on the keyboard to remove the formula.

35. Double-click rv_SelectCompany.

36. Click OK.

37. Click Apply changes to list.

38. Click Save.

39. Click View mode. Everything seems to work fine in our report but we still see Select Element in our action. We would prefer to see the current selection(s) listed here.



42. Click the Insert Function icon on the toolbar on the left.

43. Scroll down the list of Functions and find MEMBERGET.

44. Select MEMBERGET.

45. Click OK. The formula is inserted into the cell with placeholders for the references it needs. We'll replace each one with the correct value. Replace the elements as follows:

alias Academy + <your initials>

cube SALES

hierarchy [UNIT]

element rv_SelectCompany.text

param1 self

46. Click and drag to highlight ,[param2].

47. Press Delete on the keyboard to remove this parameter. Your formula should look like this: =MEMBERGET( "Academy + <your initials>", "SALES", "[UNIT]", rv_SelectCompany.text, "self")

48. Press Enter on the keyboard. Notice that the value displayed in cell D5 is a technical name not the value of the selection. This is a formatting issue that can be easily fixed.


50. Click Format on the main menu bar.

51. Select Format Cells...

52. Select Text from the Category list.

53. Click the tilde (~) in the Number Formats column.

54. Click OK.

55. Select Hyperlink from the Standard drop-down list. This will display the result as a link and be a bit more intuitive for the user.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


56. Click Save.


@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Command buttons Command buttons perform specific actions. For example, you can make the process of navigating between reports more seamless by adding a command button on one report to take the user to a second report.

Command buttons can be used to:

Refresh report data Jump to another report Open a pick list Execute an action Write back values in WebServices

Adding a command button

To add a command button to a report, you select the cell in the report grid and click the Command button in the Objects toolbar. Double-clicking the Command button opens the Object Properties dialog box where you can enter a caption and a tooltip (shown to the user on mouse over in web) and select an action for the button.

The Appearance and Properties tabs in the Object Properties dialog box for a button object provide the same positioning and formatting options as for list objects.

Demo: Create an action using a button

Your instructor will demonstrate how to create an action using a button.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 6.4: Create an action using a button

In this exercise, you will create an action using a button to enable and disable zero suppression in the report.

Exercise steps


2. Click Report 3 - Zero Suppression to open the report.

3. Create a variable to control zero suppression on our report:

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



b. Click the title bar for Accessories.

c. Right-click the Report Variables folder

d. Select New > Report Variable.

e. Rename the variable rv_ZeroSuppressionOnOff_Button

f. Type TRUE in the value field.


5. Select the Edit Formula... drop-down list next to Enabled in the Filter Settings.

6. Click Edit Formula.

7. Click the + for Report Variables.

8. Double-click rv_ZeroSuppressionOnOff_Button. The variable is added to the selected list.

9. Click OK.

10. Click the Apply changes to the list icon.


12. Click the Command Button icon in the toolbar on the left.

13. Double-click the button. The Object Properties dialog appears.

14. Type btn_OnOff in the Name field.

15. Type Filter in the Caption Text field.

16. Type Switch zero suppression on or off in the Tooltip Text field.

17. Click the radio button next to Click to execute an action under Click Action.

18. Click the ellipsis next to Click to execute an action. The Define Action dialog appears.

19. Double-click <New parameter> under Parameters.

20. Select Report Variables > rv_ZeroSuppressionOnOff_Button from the drop-down list.

21. Click the ellipsis next to the value =TRUE. The Edit Value dialog appears.

22. Click the + next to Report Variables.

23. Double-click rv_ZeroSuppressionOnOff_Button. A formula is added to the selected list.

24. Change the expression to read =NOT(ReportVariables. rv_ZeroSuppressionOnOff_Button.Text). We added the NOT value and enclosed the original formula in parenthesis to indicate that when we click the button we want to change the value of the variable to whatever it is not. I.e. TRUE to FALSE or FALSE to TRUE.

25. Click OK.

26. Click OK to close the Define Action dialog.

27. Click OK to close the Object Properties dialog.

28. Click Save.

29. Click View mode. Based on the value of the variable (TRUE) the report displays with zero suppression enabled.

30. Click the Filter button to see the data displayed in the report change depending on whether zero suppression is enabled or not.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


31. Enhance the display so we know what the current state of the filter is:


b. Drag the rv_ZeroSuppressionOnOff_Button variable to cell D9. The current value of the variable is displayed in the cell. The formula appears above.

c. Edit the formula to read ="Zero suppression is "&ReportVariables.rv_ZeroSuppressionOnOff_Button.Text

d. Click Save.

e. Click View mode.

f. Click the Filter button to change the setting on and off.


33. Change the filter button to an image button:

a. Double-click the Filter button. The Object Properties dialog appears.

b. Delete the text in the Caption Text field.

c. Click the Properties tab.

d. Click the value in the Style in Full Client field.

e. Select Custom from the drop-down list.

f. Click in the value field next to Image.

g. Click the ellipsis. The Image Source dialog appears.

h. Click the radio button next to Shared Image.

i. Select filter from the drop-down list.

j. Click OK.

k. Click OK to close the Object Properties dialog.

l. Click the Filter button.

m. Alt-click and drag the handles to adjust the size and shape of the button.

n. Click Save.

o. Click View mode. The report now displays a recognizable button and helpful information that is updated when the button is pressed.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Managing variables Global and report variables can have:

A value for the current user. A default value. A value for a specified user or role.

All users see the default value of a variable unless a value has been specified for them or their role. This can be used, for example, where a variable identifies an object to which not all users have access. You can specify the default and current values for a variable in the Properties pane when you create a global or report variable. Or, you can specify the values in these dialogs:

Manage Values for Users: Select a user or role and see the current value of every variable. Manage Values for Variables: Select a variable and see the current value for every user or role.

Note: These options are disabled if the variable folder contains only system variables (indicated by a red icon). Create one or more global or report variables to enable the options. For report variables, the options are disabled until the report is saved. If required, you can use the options Clear Values and Clear All Values to return all variable values to their defaults.

Demo: Managing variables

Your instructor will demonstrate how to make a variable available to other users.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 6.5: Managing variable

In this exercise, you will make a variable available to other users.

Exercise steps


2. Right-click the rv_ZeroSuppressionOnOff_Button variable.

3. Select Manage values for variables.

4. Click in the Value field next to Default values.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


5. Type TRUE in the value field. This sets the starting value for the variable for other users running the report.

6. Click OK.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



When creating an action, the __________ describes the event which triggers the action.

a) Mode

b) Condition

c) Location

d) Type

__________ can be used to jump to another report.

a) Command buttons

b) Cell formats

c) Settings in a style sheet

d) Filters

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

90 Lesson 7: Display additional information on your report © 2016 Infor Education

Lesson 7: Display additional information on your report Estimated Time

1 hour

Learning Objectives


Describe how to use attributes. Explain how to display Captions and attributes in a hyperblock. Describe how to use Textboxes with formulas. Describe how to add a variance calculation to a hyperblock.

Topics

Attributes Functions Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Attributes Attributes are created and saved in the OLAP Server database and can be used to add information to a list. For example, for each item in a list of products, you might store the product manager's name as an attribute. You can display attributes in List objects. All dimensions have intrinsic attributes, in addition to any that are created in the database. The Attributes pane of the List Designer displays all attributes. There are different ways in which to reference attributes. For example, in a list object, you can change the output format of the list to 'Attribute', having first selected the attribute in the List Designer. Or you can use formulas such as ROA and RWA to reference attributes in the spreadsheet for example. Custom hierarchies and lists also have attributes but there are some differences in the way in which they are referenced.

Attributes Pane

Each object in the report catalog has a number of attributes. To display the unique name we can use a function like:

=ReportObjects.<Name>.text

The .text segment indicates that we want to display the technical name for the element. The unique name of the element All Tires for example would be [PRODUCT].[All Tires]. In this case [PRODUCT] is the dimension that we embedded in the in the report object and [All Tires] is the element that was selected.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


We would see [PRODUCT].[All Tires] in the report. While this would not be useful for an end user to view, it is quite useful for the developer to understand the database structure and selected elements used in the report. It will be seen in functions and actions so an understanding of the structure will save time as you design interactive reports.

As an example, in most cases we want to view the display name for the element that we have selected. In this case we use =ReportObjects.<Name>.alias and the result would display as All Tires. This is the most common display for our report and what the user will see when they run the report.

In addition, we can select other information depending on the list view. To display the first selected attribute you would use: =ReportObjects.<Name>.attribute. The result would be the value associated with the first attribute configured in the list. If none, then the display would be blank.

You can also display the values of other attributes with .attribute1, attribute2, and attribute3.

Tilde

When you select a dimension and display it in a hyperblock, the first cell contains a special character (~) which indicates that the Alias should be displayed whenever this element appears in a report. In most cases you will not need to do anything to support this functionality. However, you can break it. This would cause the report to display the technical name for the element instead. This happens most often when you apply a style to the hyperblock.

The correct format for the first cell in a hyperblock is Number with a special character assigned - the tilde.

Tilde

Adding attributes to hyperblocks

Attributes add further information to elements. For example, you can add the name of a product manager as an attribute to the Products dimension, so you can easily see who is responsible for which products.

If you want, you can even change the output of a hyperblock so that it displays attributes rather than element names (captions). When doing this, you will need to specify which attribute you want the hyperblock to display because a list can have several attributes.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Select and display attributes

Your instructor will demonstrate how to select and display attributes in a report.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 7.1: Select and display attributes

In this exercise, you will select and display attributes in a report.

Exercise steps

1. Select Report Catalog under Repository Explorer.

2. Create a new report:

a. Right-click the Training folder under Reports.


c. Type Report 6 – Attributes in the Name field.

d. Type Report_6_Attributes in the Unique name field.

e. Click the Style Sheet drop-down arrow.

f. Select Style Sheets > Sample Solution Styles > Academy Style from the list.

g. Click OK.


4. Type Product Details in the selected cell.

5. Click and drag to select cells C11 and D11.

6. Select Database Structure from Repository Explorer.

7. Click and drag the PRODUCT dimension onto the C11 and D11 cells.

8. Select the attributes to display in the report:

a. Click the title bar for Attributes in the List Designer.

b. Click the check box next to aProductionManagerEmailAddress.

c. Click the check box next to bProductManagerEmailAddress.

d. Click Apply changes to the list on the toolbar.

9. Add the attributes to the report:

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


a. Click and drag the sizing handles on the hyperblock to expand it to column H.

b. Click the Accessories title bar from Repository Explorer.

c. Click the + next to Hyperblock1. This is the active hyperblock in our report.

d. Click and drag aProductionManagerEmailAddress to cell G11.

e. Click and drag bProductManagerEmailAddress to cell E11.

f. Click Save.


11. Display the attributes available to the hyperblock:


b. Click the Product hyperblock.

c. Click and drag the sizing handle down to row 16 to expand the size of the hyperblock.

d. Right-click the Product hyperblock.

e. Click Object Properties. Note: The list of elements displayed under Hyperblock cells. In addition to the option to move elements using drag and drop you can specify the precise location for each element.

f. Double-click E11 in the Position column.

g. Type D13 in the Position field.

h. Click OK.

i. Double-click G11 in the Position column.

j. Type D14 in the Position field.

k. Click OK.

l. Click OK. The Object Properties is closed. Note the location of the two attributes that were modified in the previous steps.

m. Click Save.

12. Click View mode. The additional information is now displayed in a vertical format.


14. Insert captions to describe the additional elements:

a. Click in cell C13.

b. Type Product Manager in cell C13.

c. Click in cell C14.

d. Type Production Manager in cell C14.

e. Click in cell C16.

f. Type UN in cell C16.

g. Click to select the hyperblock. Make sure the crosshatch border is visible.

h. Right-click the hyperblock.

i. Click Object Properties.

j. Click the New button under Hyperblock cells to add a new cell.

k. Click the new Caption.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


l. Select Unique Name from the Caption drop-down list.

m. Double-click D11 in the Position column.

n. Type D16 in the Position field.

o. Click OK.

p. Click OK to close the Object Properties dialog.

q. Click and drag cells C13 through C14 to highlight the cells.

r. Select Combobox Label from the Standard drop-down list.

s. Click and drag cells D13 through D14 to highlight the cells.

t. Select Text from the Standard drop-down list.

u. Click and drag the column separator between columns C and D to expand the width to fit the text in cell C14.

v. Click and drag the column separator between columns D and E to expand the width to fit the text in column D14. Make the width twice as wide as the original column width.

w. Click cell C16.

x. Click the Align Right icon on the toolbar.

y. Click Save.

15. Click View mode. The additional information is now displayed in a vertical format.

Children Cardinality

This attribute contains a value for the number of children an element has. If an element has three children then the Children Cardinality attribute will have a value of 3. This sounds like a simple concept but it has many uses. In the next exercise, we will look at one use of the Children Cardinality attribute to apply conditional formatting.

Optional demo: Leaf cell formatting

Your instructor will demonstrate how to use the attribute for children cardinality to apply conditional formatting to a report.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Optional exercise 7.2: Leaf cell formatting

In this exercise, you will use the attribute for children cardinality to apply conditional

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


formatting to a report.

Exercise steps


a. Select Report Catalog from Repository Explorer.

b. Right-click the Training folder under Reports.

c. Select New > Report...

d. Type Report 7 - Conditional Formatting in the Name field.

e. Type Report_7_CondFormat in the Unique name field.

f. Select Style Sheet > Sample Solution Styles > Academy Style from the Style Sheet drop-down list.

g. Click OK.

2. Add content to the report:

a. Type Budget Planning in cell B2.

b. Click the Database Structure title bar under Repository Explorer

c. Click and drag the PRODUCT dimension to cell C9.

d. Drag the sizing handle to the right to span columns C through G.

e. Click in cell D7.

f. Type Actual in cell D7.

g. Click in cell D8.

h. Type 2014 in cell D8.

i. Click in cell E7.

j. Type Budget in cell E7.

k. Click in cell E8.

l. Type 2014 in cell E8.

m. Click in cell D9.

n. Click Edit OLAP Formula on the toolbar to the left.

o. Click the ellipsis next to TIME.

p. Select All Years > 2014 from the list.

q. Click the ellipsis next to PROFIT.

r. Select Revenue.

s. Click the ellipsis next to VERSION.

t. Select Actual from the list.

u. Click OK.

v. Click in cell E9.

w. Click Edit OLAP Formula on the toolbar to the left.

x. Click the ellipsis next to TIME.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


y. Select All Years > 2014 from the list.

z. Click the ellipsis next to PROFIT.

aa. Select Revenue.

bb. Click the ellipsis next to VERSION.

cc. Select Budget from the list.

dd. Click OK.

ee. Click OK.

ff. Click Save.

3. Click View mode.

4. Format the report:


b. Click and drag to select cells C7 through E7.

c. Select Table Head – Background Only - Right Aligned from the Standard drop-down list.

d. Click and drag to select cells C8 through E8.

e. Select Table Head - Right Aligned from the Standard drop-down list.

f. Click and drag to select cells D9 and E9.

g. Select Number - Grid Line from the Standard drop-down list.

h. Click cell C9.

i. Select Text - Grid Line from the Styles drop-down list.

j. Click and drag the column separator between columns C and D to expand the width to fit the text in the cells.

5. Click View mode. The report appears and is easy to read.


7. Apply conditional formatting to write into the lowest level budget cells:

a. Click the Product hyperblock. Make sure the crosshatch border is displayed.

b. Click the Attributes title bar in List Designer.

c. Click the check box next to Children Cardinality.

d. Click Apply changes to list on the toolbar.

e. Click the Accessories title bar in the Repository Explorer.

f. Click the + next to Report Objects.

g. Click the + next to Hyperblock1.

h. Click and drag the Children Cardinality element to cell G9.

i. Click in cell E9.

j. Click Conditional Formatting from the toolbar on the left. The Conditional Formatting dialog appears.

k. Select Formula is from the Condition drop-down list.

l. Type G9=0 in the formula field to the right of the Formula is selection.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


m. Click Add. The formula appears in the List of Conditions.

n. Select Number - Grid Line - Writeback from the - Format - drop-down list.

o. Click OK.

p. Click cell E9.

q. Click in the formula bar.

r. Select to highlight the ROC at the beginning of the formula.

s. Type RWC to overwrite the ROC. This changes the formula from ROC (Read Only Cell) to RWC (Read Write Cell).

t. Press Enter on the keyboard.

u. Click OK.

v. Click Save.

8. Click View mode.

9. Click the + next to All Season Tires to drill down. The report shows a blue background and allows editing for any field under Budget when the value for Children Cardinality is 0.

10. Click the + next to AS600 Aquachrono to drill down. Now we see the product codes for three tire types and the budget fields become writeable.


12. Hide the column containing the Children Cardinality values:

a. Click in cell F11.

b. Click Visible Report Area in the toolbar on the left. The Children Cardinality values are still in the report but will not display for the user.

c. Click Save.

d. Click View mode.

Cell Notes

Cell Notes are an attribute with a specific purpose - to record notes related to an individual value in the database. This can be useful when reviewing information in a report or sharing a report with other team members. Each person can add notes and see what others have written.

In the example that follows we will add cell notes to a report to be used for budget planning. The notes will assist us in tracking and recording the decision making process.

Optional demo: Cell notes

Your instructor will demonstrate how to create a writeable text area for notes.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Optional exercise 7.3: Cell notes

In this exercise, you will create a writeable text area for notes.

Exercise steps


a. Click Report Catalog from Repository Explorer.

b. Right-click Report 7 - Conditional Formatting.

c. Select Duplicate

d. Right-click Copy of Report 7 - Conditional Formatting.

e. Click Rename.

f. Type Report 8 - Cell Notes in the Name field.

g. Type Report_8_CellNotes in the Unique name field.

h. Click OK.

i. Click Save.

2. Add a comments field to the report:


b. Right-click column F.

c. Select Insert Cells.

d. Click in cell F8.

e. Type Comments in the selected cell.

f. Right-click cell E9.

g. Select Copy.

h. Right-click in cell F9.

i. Select Paste Special...

j. Click Formulas.

k. Click OK. The formula has been copied but it has an error because the references are no longer correct. Review the formula and locate the reference to cell D9.

l. Type C9 as a replacement for D9 in the formula.

m. Type NOTEGET as a replacement for ROC at the beginning of the formula. This changes the function to get the cell note.

n. Click in cell F9.

o. Select Cell Note Write from the Standard drop-down list.

p. Click Save.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


3. Click View mode. The report now contains a place to write notes related to the budget planning for each element in the list of products. However, the amount of space available is limited to the width of the column.

4. Add a text box to display the current note:


b. Right-click row 5.


d. Repeat steps b and c three more times.

e. Click Insert on the main menu bar.

f. Select Objects > Text.

g. Click and drag to select cells B5 through F8.

h. Click in cell F13.

i. Click and drag to highlight the formula in the Formula area. Make sure you select the entire formula for this cell.

j. Press Ctrl-C. This copies the selected formula to the clipboard.

k. Double-click in cell B5.

l. Type @{ in the text box.

m. Press Ctrl-V. This pastes the data from the clipboard into the text box.

n. Type } in the text box.

o. Delete the = at the beginning of the formula.

p. Click any cell.

5. Create a Report Variable to hold the reference to the product for the cell note:

a. Click the Accessories title bar in Repository Explorer.

b. Right-click Report Variables.

c. Select New > Report Variable.

d. Type rv_SelectedProduct in the name field.

e. Press Enter on the keyboard.

f. Click in cell C13.

g. Click Define Action on the toolbar on the left.

h. Double-click <New parameter> under Parameters.

i. Select Report Variables > rv_SelectedProduct from the drop-down list.

j. Double-click =TRUE in the Value column.

k. Type =$C13 in the Value field.

l. Click OK.

m. Click OK.

n. Double-click the text box created in cell B5 to view the formula in the text box.

o. Click and drag to highlight C13 in the formula. Note: You may have to arrow down in the formula to find C13.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


p. Type rv_SelectedProduct.text to replace C13 with the variable.

q. Click Save.

6. Click View mode.

7. Test the new functionality for cell notes:

a. Type Test Comment in the first comment cell.

b. Press Enter on the keyboard.

c. Click the product name on the first line. The comment will appear in the text box above the report.

d. Repeat steps a through c to enter additional comments and view the results.

8. Add a button to act as a display link for the comment:


b. Right-click column G.


d. Click in cell G13.

e. Type ... in cell G13.

f. Right-click on cell C13.

g. Select Copy.

h. Right-click on cell G13.

i. Select Paste Special...

j. Select Actions.

k. Click OK.

l. Click and drag the column separator between columns G and H to reduce the width of the column.

m. Click in cell G13.

n. Click Define Actions on the toolbar on the left.

o. Click OK.

p. Click in cell G16.

q. Click Visible Report Area twice.

r. Click Save.

9. Click View mode.

10. Click the ellipsis next to a cell note that has text. The comment appears in the larger text box above.

Functions

OLAP Server formulas perform the key functions of Application Studio. Most importantly, they retrieve data and let you manipulate it at different levels of the database hierarchy, format it and write it back to the database.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


With Excel formulas and functions like SUM, AVERAGE, COUNT, etc. you perform calculations on data.

Demo: Formulas and functions

Your instructor will demonstrate how to add a variance calculation to a hyperblock.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 7.4: Formulas and functions

In this exercise, you will add a variance calculation to a hyperblock.

Exercise steps


a. Select Report Catalog from the Repository Explorer.

b. Right-click Report 1 – Product Sales.

c. Select Duplicate

d. Right-click Copy of Report 1 – Product Sales.

e. Click Rename…

f. Type Report 1a – Product Sales with Variance in the Name field.

g. Type Report_1a_ProductSalesVariance in the Unique name field.

h. Click OK.


j. Click and drag the sizing handles on the hyperblock Product to expand it to column F.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


k. Click cell F12.

l. Type Variance in cell F12.

m. Click cell F13.

n. Type =D13-E13 in cell F13.

o. Use the format painter to copy the format of cell D12 to D13 and apply it to cells F12 to F13.

2. Click Save.

3. Click View mode.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



The Attributes pane of the __________ displays all attributes.

.

a) List Designer

b) Report Catalog

c) Database Structure

d) Style Sheet

Attributes can be added to a hyperblock by first ____________.

a) Dragging the attribute onto the report

b) Defining the content of the attribute

c) Selecting the attributes in the List Designer

d) Selecting the attributes in the Report catalog

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Lesson 8: Combine hyperblocks in a table Estimated Time

45 minutes

Learning Objectives


Explain how multiple hyperblocks may be used to enhance your report.

Topics

Nesting hyperblocks Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

106 Lesson 8: Combine hyperblocks in a table © 2016 Infor Education

Nesting Hyperblocks Creating more meaningful reports requires two or more hyperblocks to be nested, with their formula cells overlapping. You can then enter formulas in the overlapping cells to retrieve values or other information for the database.

You can think of this in terms of the vertical hyperblock forming the X axis of a simple report or graph and the horizontal hyperblock forming the Y axis. The overlapping data cells form the body of the report where the data is displayed.

We can also use a horizontal hyperblock forming the X axis of a simple report and then apply another horizontal hyperblock to add more detail. In the example below we will create a Top 10 report sorting the data by sales group. It will look similar to this:

Nested hyperblocks

Hyperblock 1 - source of the product and sales values.

Hyperblock 2 - source of the region grouping.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Nest hyperblocks

Your instructor will demonstrate how to nest two hyperblocks in a report and will then show how the nested hyperblocks appear in View mode.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 8.1: Nest hyperblocks

In this exercise, you will nest two hyperblocks to show results within an out grouping.

Exercise steps

1. Right-click Report 4 - Sorting

2. Select Duplicate.

3. Right-click Copy of Report 4 - Sorting

4. Click Rename…

5. Type Report 9 - Sorting by Company in the Name field.

6. Type Report_9_SortingbyCompany in the Unique Name field

7. Click OK.

8. Click Save.



11. Press Delete to remove this label.


13. Press Delete to remove this combo box. The Actual and Budget totals will display 0, we'll correct this in a moment.

14. Add the outer hyperblock to display the Companies:

a. Click cell C13 and drag to cell E13 to select this whole area.

b. Move the selected area one column to the right.

c. Click cell C13.

d. Click the Database Structure title bar under Repository Explorer.

e. Click and drag UNIT to cell C13. The default is for a new hyperblock to appear in the opposite axis of the first one. Thus, this hyperblock has an arrow going to the right (horizontal).

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


f. Click the arrow to change it to a vertical axis.

g. Click Structure Selection in the List Designer.

h. Click to select the European Holding check box.

i. Right-click on European Holding in the Structure Selection.

j. Select Elements Underneath > All, Including Parent.

k. Click Apply changes to list.

15. Select cell C13.

16. Select Text – Grid Line from the Standard drop-down list.

17. Select cells E12 and F12.

18. Select Table Head from the Standard drop-down list.

19. Click cell E13.

20. Press Delete to remove the formula.

21. Click the Edit OLAP Formula button. The Edit OLAP Formula dialog box displays.

22. Select Actual as the VERSION.

23. Click OK.

24. Click cell F13.

25. Press Delete to remove the formula.

26. Click the Edit OLAP Formula button. The Edit OLAP Formula dialog box displays.

27. Select Budget as VERSION.

28. Click OK.

29. Click Save.

30. Click View mode. Notice that the report displays the companies and the top 10 products within each group. However, the top 10 products in the second and subsequent groups are not sorted properly. We'll need to update the sorting selection to include our outer hyperblock.

31. Update the sort sequence for the Top 10 products:


b. Click the PRODUCT hyperblock.

c. Click the Sorting title bar in the List Designer.

d. Click Top count 10 of data area. Notice that the Selection value for UNIT in the Data area contains an invalid reference.

e. Double-click the UNIT Selection value under the Data Area. The Select Elements dialog appears.

f. Click the value on the right.

g. Press Delete on the keyboard to remove the value.

h. Click the Variables tab

i. Click + next to Hyperblocks.

j. Double-click Hyperblock[x].

k. Click OK.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


l. Click Apply changes to list.

32. Click Save.

33. Click View mode. The top 10 products in each group are now sorted properly.

34. Click the - next to European Holding to see only the region totals.

35. Click the + to drill down.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



When using more than one hyperblock in a report, one hyperblock is always the X-axis and the other is always the Y-axis.

a) True

b) False

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Lesson 9: Enrich your reports with visual information Estimated Time

1 hour

Learning Objectives


Explain how to add sparklines in reports. Describe ways to create charts in reports.

Topics

Sparklines Charts Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

112 Lesson 9: Enrich your reports with visual information © 2016 Infor Education

Sparklines Sparklines are an alternate way to display information using the minimum amount of space. They can be incorporated into a hyperblock to display on each line of a report.

There are several types of sparklines available, including:

Line Column Benchmark Win/Loss Bar

This example shows a column-type sparkline displaying data for the previous 12 months. The first and the last value from the previous 12 months is highlighted in a different color for each row.

Example sparkline

A key feature of sparklines is that the data on which they are based does not need to exist in the report. The data source for the sparkline can be specified within the data area of the sparkline itself. You create sparklines with the Sparkline Wizard. There are three different ways to retain data for the sparklines:

Children Descendants From spreadsheet

Children sparkline

The sparkline shows information about the children of a selected parent element. You can display the parent element in, for example, a combo box. The sparkline changes when you select a different parent element from the combo box.

Descendant sparkline

The sparkline shows information about the descendant of a selected parent element, at a selected level of the hierarchy. You can display the parent element in, for example, a combo box. The sparkline changes when you select a different parent element from the combo box.

From spreadsheet sparkline

This is the only type of sparkline which requires the data on which it is based to exist in the report, either in visible or hidden cells. For example, a table of values can be the basis of a spreadsheet sparkline.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Charts Application Studio includes a number of charting options, such as Column, Bar, Pie, and Area charts.

Creating a chart

Charts graphically represent data in a spreadsheet. There are several types of charts, each with one or more subtypes.

Chart Wizard

To create a chart, you can highlight the data and click Chart Wizard in the Objects toolbar. Or, you can highlight the data and select Insert > Chart. To base a chart on one or more hyperblocks, highlight the hyperblocks and click Chart Wizard.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Integrating charts

To display the data of a chart as a graphic only, create a chart and drag it so that its top left corner covers the top left corner of the value cell of the hyperblock. Or create a visible report area in which to display the chart. The data is hidden in the non-visible area. You can also incorporate the chart data in a data table within the chart. We'll explore the options found in both the Sparkline Wizard and Chart Wizard in this next exercise.

Demo: Create a chart in a report

Your instructor will demonstrate how to create a chart in a report.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 9.1: Create a chart in a report

In this exercise, you will create a chart in a report that shows the relationship between the Top selling products and the total sales.

Exercise steps

1. Create a copy of the Top 10 report:

a. Select Report Catalog from Repository Explorer.

b. Right-click Report 4 - Sorting.

c. Select Duplicate.

d. Right-click Copy of Report 4 - Sorting.

e. Click Rename…

f. Type Report 10 - Charts in the Name field.

g. Type Report_10_Charts in the Unique name field.

h. Click OK.


2. Right-click column E.

3. Select Insert Cells.

4. Add a chart to show the contribution of the Top 10 products to total sales:

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



b. Click in cell C15.

c. Type Sum of Top 10 Products in the selected cell.

d. Click in cell D15.

e. Type =sum(D13) in the selected cell.

f. Click in cell C16.

g. Type Total of All Products in the selected cell.

h. Click in cell D16.

i. Click Edit OLAP Formula on the toolbar on the left.

j. Select SALES from the Cube drop-down list.

k. Click OK.

l. Click and drag over cells C15 and C16.

m. Select Text - Grid Line from the Standard drop-down list.

n. Click and drag over cells D15 and D16.

o. Select Number - Grid Line from the Standard drop-down list.

p. Click in cell D18.

q. Type =D15/D16 in the selected cell.

r. Click in cell D19.

s. Type =1-D18 in the selected cell.

t. Click and drag on cells D18 and D19.

u. Click the Chart Wizard icon on the toolbar on the left.

v. Click Doughnut.

w. Click Next.

x. Type 1 in the Number of series field.

y. Type 2 in the Number of categories field.

z. Click Next.

aa. Type Percentage of Top 10 Products on All Products as chart title.

bb. Click Finish.

cc. Click Save.

dd. Click View mode. The chart already works fine. Now we want to make some formatting adjustments. To make our color selections easier we will make an extra step:

5. Import the color settings from the style sheet:


b. Click the + next to Style Sheets in the Report Catalog.

c. Click Academy Style.

d. Click Extras on the main menu bar.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


e. Click User-defined Colors.

f. Click Export.

g. Type Academy Colors in the File name field.

h. Click Save.

i. Click OK. The User-defined Colors dialog closes.

j. Click Report 10 - Charts in the Training folder.

k. Click No when prompted to save changes to Academy Style.

l. Click Extras on the menu bar.

m. Click User-defined Colors.

n. Notice the color palette.

o. Click Import.

p. Click the Academy Colors.xml file in the list.

q. Click Open.

r. Click OK. Now we have the colors from the style sheet also available in the design view of our report which makes chart formatting much easier.

6. Adjust the display of the chart:

a. Click and drag the chart to display on cell C18. This will cover the calculations for the data and display only the chart

b. Click the Legend.

c. Press Delete to remove the legend.

d. Double-click the orange slice of the chart. The Format Data Point dialog appears.

e. Click Solid Fill under Fill.

f. Click the down arrow next to Color.

g. Click the top Blue color in the first column.

h. Click User-defined under Border.

i. Click the down arrow next to Color.

j. Select White.

k. Click the Data Labels tab.

l. Select the Show value radio button.

m. Click OK.

n. Double-click the value displayed on the chart.

o. Click the Number tab.

p. Select Percent from the Category column.

q. Click the Font tab.

r. Click Bold in the Font Style list.

s. Click the down arrow next to Color.

t. Select White.

u. Click OK.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


v. Double-click the green slice of the chart. The Format Data Point dialog appears.

w. Click Solid Fill in the Fill panel.

x. Click the down arrow next to Color.

y. Click the last Grey color in the first column.

z. Click User-defined in the Border panel.

aa. Click the down arrow next to Color.

bb. Select White.

cc. Click OK.

dd. Click Save.

7. Click Save.

8. Click View mode. The report uses the charts to graphically display the relationships between the data elements in the report.

Optional demo: Create a sparkline in a report

Your instructor will demonstrate how to create a sprakline in a report.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Optional exercise 9.2: Create a sparkline in a report

In this exercise, you will create a sparkline in a report that shows the relationship between the Top selling products and the total sales.

Exercise steps

1. Open the Top 10 report.

2. Right-click column E.

3. Select Insert Cells.

4. Add a sparkline chart to display the relationship between the Top 10 products:

a. Click and drag over cells D12 and D13.

b. Select the Format Painter icon.

c. Select cells E12 and E13 to paste the format to cells E12 and E13.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


d. Click the Sparkline Wizard icon on the toolbar on the left.

e. Click the Bar type.

f. Click Next.

g. Type =D13 in the Data source field.

h. Type =E13 in the Target cell field.

i. Click Finish.

j. Click in cell E13. The Sparkline Properties dialog appears. Now we can configure the display of the sparkline.

k. Click Axis…

l. Click to deselect the check box next to Normalize X-Axis.

m. Select the For whole group radio button under X-Axis Minimum.

n. Select the For whole group radio button under X-Axis Maximum.

o. Click View mode. The report shows a good distribution of the values as a chart.

p. Click Save.

5. Import the color settings from the style sheet:


b. Click the + next to Style Sheets in the Report Catalog.

c. Click Academy Style.

d. Click Extras on the main menu bar.

e. Click User-defined Colors.

f. Click Export.

g. Type Academy Colors in the File name field.

h. Click Save.

i. Click OK. The User-defined Colors dialog closes.

j. Click Report 10 - Charts in the Training folder.

k. Click No when prompted to save changes to Academy Style.

l. Click Extras on the menu bar.

m. Click User-defined Colors.

n. Notice the color palette.

o. Click Import.

p. Click the Academy Colors.xml file in the list.

q. Click Open.

r. Click OK. Now we have the colors from the style sheet also available in the design view of our report which makes chart formatting much easier.

6. Update the color in the Sparkline chart to match the Doughnut chart:

a. Click the Sparkline chart. The Sparkline Properties dialog appears.

b. Click the color picker to the right of Color.

c. Click the top Blue color in the first column.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


d. Click the X to close the properties dialog.

7. Click Save.

8. Click View mode. The report uses the charts to graphically display the relationships between the data elements in the report.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Customizing a chart

To customize a chart, double-click to open the chart for editing then right-click in the chart area and select an option from the drop-down list. For example, you can select Chart Options to modify the title, legend, data labels, and other options.

Chart Elements

Understanding cell references

There are three types of cell references:

Relative Absolute Mixed

Relative references

With relative references, the relative position of the formula and cells remains constant, regardless of where the formula is copied. For example, if you use the formula =D8-D9 to return a value to cell D11 and then copy that value to cell E11, the formula in E11 will refer to cells E8 and E9.

Absolute references

There may be instances when you want to prevent the references to specific cells from changing when you copy a formula. To do this, use absolute references. To create a formula with absolute references, you must type a dollar sign ($) before the part of the reference that you do not want to change.

For example, the formula =$D$8-$D$9 produces the same value as =D8-D9, but the value will always refer to cells D8 and D9, regardless of where the formula is copied.

Mixed references

The dollar sign ($) can also be used to fix only a row reference or a column reference. For example, the formula =$D8-$D9 in cell D11 will produce the same value as =D8-D9. If that value is copied to cell E11, the formula on which it is based will still refer to cells D8 and D9. This is because the column reference

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


has been fixed, but the row reference is relative. When copying the value to cell E12, the formula will refer to D9 and D10.

Similarly, values produced by the formula =E$8-E$9 will always refer to rows 8 and 9, but the column reference will change if the value is copied to another column. For example, if you copy the value to cell D12, the formula will refer to cells D8 and D9.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



One way to create a chart in an Application Studio report is to you can highlight the data and____________________.

a) Insert a sparkline

b) Drag and drop onto a cell

c) Click Chart Wizard in the Objects toolbar

d) Apply a style from the drop-down menu

When using a __________ reference in a report, the dollar sign is used to fix only a row reference or a column reference.

a) Relative

b) Minimal

c) Mixed

d) Absolute

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Lesson 10: Enhance your data with qualitative information Estimated Time

45 minutes

Learning Objectives


Explain how to add a commentary function to your report.

Topics

Open Dialog Action Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

124 Lesson 10: Enhance your data with qualitative information © 2016 Infor Education

Open Dialog Action The Open Dialog Action is an easy way to enhance an existing report with more information like notes or any other details to analyze in a given context.

Cell notes provide a read/write capability for notes associated with Infor OLAP data.

Demo: Create a dialog window

Your instructor will demonstrate how to create a dialog window.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 10.1: Create a dialog window

In this exercise, you will create a dialog window to display and edit cell notes.

Exercise steps

1. Create 2 copies of the Top 10 report:

a. Right-click Report 4 - Sorting.

b. Select Duplicate.

c. Right-click the Copy of Report 4 – Sorting.

d. Click Rename...

e. Type Report 11 - Top 10 with Cell Notes in the Name field.

f. Type Report_11_Top10WithCellNotes in the Unique name field.

g. Click OK.

h. Right-click Report 4 - Sorting.

i. Select Duplicate.

j. Right-click the Copy of Report 4 – Sorting.

k. Click Rename...

l. Type Report 12 - Edit Cell Note in the Name field.

m. Type Report_12_EditCellNote in the Unique name field.

n. Click OK.

2. Create a report variable:

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


a. Select Report 12 – Edit Cell Note from the Report Catalog under Repository Explorer.

b. Click Design mode.

c. Click the Accessories title bar under Repository Explorer.

d. Right-click Report Variables

e. Select New > Report Variable.

f. Type rv_Product in the Name field.

g. Press Enter on the keyboard.

3. Format the Edit Cell Note report:

a. Click the Report Catalog title bar.

b. Right-click the Report 12 - Edit Cell Note.

c. Select Properties.

d. Click in the Options > Style Sheet field.

e. Select Style Sheets > Dialog from the Style Sheet drop-down list.

f. Click in the Options > Immediate Writeback field.

g. Select Enable from the Immediate Writeback drop-down list.

h. Click in the Options > Writeback behavior field.

i. Select Form from the Writeback behavior drop-down list.

j. Click X to close the dialog.

k. Click in cell B2.

l. Press Delete on the keyboard.

m. Click Format from the top menu bar.

n. Click Format Cells...

o. Click the Border tab.

p. Select these items:

q. Click the Protection tab.

r. Click to deselect the check box next to Cells to remove protection (make it writeable).

Border Style

Border Color

Line Style

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


s. Click the Alignment tab.

t. Select the radio button next to Top under Vertical.

u. Select the radio button next to Left under Horizontal.

v. Select the check box next to Wrap Text.

w. Click OK.

x. Click and drag the column separator between columns B and C separator to increase the width of the column about 5 times.

y. Click the row separator between rows 2 and 3 to increase the height of the column about 5 times. Your report should look similar to this:

z. Right-click the column header for column C.

aa. Select Insert Cells.

bb. Click and drag the separators between A and B, then C and D to adjust the size of columns A and C to look similar to this:

Edit Cell Note Report

Edit Cell Note Report

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



5. Click the Visible Report Area icon on the toolbar on the left.

6. Create a formula for the cell note:

a. Click in cell E13.

b. Press Ctrl-C.

c. Right-click in cell B2.

d. Select Paste Special...

e. Click Formulas.

f. Click OK.

g. Click and drag in the formula to select "=ROC".

h. Type =NOTEGET to replace the highlighted text.

i. Click and drag in the formula to select "A2".

j. Type rv_Product.text to replace the highlighted text.

k. Press Enter on the keyboard. The display in cell B2 will change to #NA! We will fix this later.

7. Click the PRODUCT hyperblock.

8. Press Delete on the keyboard to remove the hyperblock.

9. Click and drag to select cells E12 through F14.

10. Right-click and select Delete Cell Content to remove all formulas and formatting in these cells. The Delete Cell Content dialog box appears.

11. Select the All radio button.

12. Click OK.

13. Click Save.

14. Click Report 11 - Top 10 with Cell Notes under Report Catalog.

15. Click the PRODUCT hyperblock. Make sure the crosshatch border is visible.

16. Drag the sizing handle to the right to include column F.

17. Right-click in cell F13.

18. Select Format Cells....

19. Click the Number Format tab.

20. Type [Graphic bpt_Edit] in the User-defined format instruction field.

21. Click OK.

22. Type 1 in the selected cell.

23. Click Save.

24. Add an action to display the pop-up window:

a. Click Define Action in the toolbar on the left.

b. Double-click Set parameters in the Actions panel.

c. Select Open dialog from the drop-down list.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


d. Click Report 11 - Top 10 with Cell Notes under Inputs Value section. The report name is highlighted.

e. Click the ellipsis to the right of the report name.

f. Select Report 12 - Edit Cell Note from the list.

g. Click OK.

h. Double-click <New parameter> in the Parameters panel.

i. Click the drop-down arrow.

j. Select Report Variables > rv_Product from the list.

k. Double-click <New parameter> in the Parameters panel.

l. Click the drop-down arrow.

m. Select List Views > lv_Measure from the list.

n. Repeat steps h through l and select Company and Time.

o. Double-click the value to the right of Product. The Edit Value dialog box appears.

p. Type =C13 in the column on the right.

q. Click OK.

r. Double-click the value to the right of Measures. The Edit Value dialog box appears.

s. Click the + next to List Views.

t. Double-click lv_Measure. A formula representing the combo box for Measures appears on the right.

u. Click OK.

v. Repeat steps r through u to select the values for Company and Time.

w. Click OK.

25. Click Save.


27. Test your cell notes function:

a. Click the top red X in the far right column of the report.

b. Type a note in the Edit Cell Note dialog.

c. Press Enter on the keyboard.

d. Click X to close the note.

e. Click the Edit icon again. The note should display in the edit dialog.

28. Modify the edit column to display the pencil when a note exists and a - when one does not:


b. Click cell F13.

c. Type =IF (G13="","-",1) in the Formula field. This will display a - sign when there is no note and a pencil (the 1 indicates display the image) when there is.

d. Press Enter on the keyboard.

e. Click the Product hyperblock.

f. Click and drag the sizing handle to the right to encompass cell G13.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


g. Right-click cell D13.

h. Select Copy.

i. Right-click cell G13.

j. Select Paste Special...

k. Select Formulas.

l. Click OK.

m. Click and drag to select ROC in the formula at the top.

n. Type NOTEGET in the highlighted part of the formula.

o. Click and drag to select F13 in the formula.

p. Type C13 in the highlighted part of the formula.

q. Press Enter on the keyboard.

29. Click in cell F14.

30. Click Visible Report Area in the toolbar on the left.

31. Click Save.


33. Test your cell notes function:

a. Click the - icon.

b. Type a note in the Edit Cell Note dialog.

c. Click X to close the note.

d. The icon should change from - to the pencil icon.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



Use the ____________ action to create a pop-up window to edit and store notes.

a) Jump

b) Hyperlink

c) Open Dialog

d) Refresh

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Optional lesson 11: Build a landing page Estimated Time

30 minutes

Learning Objectives


Explain how to link reports.

Topics

Jump Action Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

132 Optional lesson 11: Build a landing page © 2016 Infor Education

Jump Action When you have two or more reports you want users to be able to navigate to the reports from a single selection list. The Jump action is used to jump from one point in a report to somewhere else. As an example, we've created a number of sales related reports in this course. It would be helpful to have a single home page where you can select a report and jump to it. The process to create this home report is fairly simple. Let's look at an example.

Demo: Build a landing page

Your instructor will demonstrate how to create a landing page report and link reports.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 11.1: Build a landing page

In this exercise, you will create a landing page report and link reports.

Exercise steps


a. Right-click the Training report folder under Report Catalog in Repository Explorer.


c. Type Sales Reports in the Name field.

d. Type SalesReports_Home in the Unique name field.

e. Select Style Sheet > Sample Solution Styles > Academy Style from the Style Sheets drop-down list.

f. Click OK.

g. Click in cell B2.

h. Type Sales Reports in the selected cell.

i. Click Save.

2. Add a report to the list:

a. Click in cell C7.

b. Type Product Target Achievement. This is the name of the first report that we created.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


c. Click Define Action in the toolbar on the left.

d. Double-click Set Parameters under Actions.

e. Click Jump.

f. Double-click Sales Reports in the Value column under Inputs.

g. Click Report 1 - Product Sales in the list. This is the report name that we want to jump.

h. Click OK.

i. Click OK.

j. Click Save.

3. Click View mode.

4. Click Product Target Achievement. The sales report opens. The jump does what we want; now how do we get back?

5. Click the Previous icon from the top toolbar. The Sales Reports report is displayed again.

6. Click Style Sheets > Academy Style from the Report Catalog under Repository Explorer.


8. Right-click the Home icon on the report.

9. Select Object Properties.

10. Click the ellipsis next to Click to execute an action.

11. Double-click in the blinking red exclamation icon in the Value column under Inputs.

12. Select Reports > Training >Sales Reports under the Choose the target report list.

13. Click OK.

14. Click OK.

15. Click Save.

16. Click Sales Reports from the Report Catalog under Repository Explorer.


18. Click Product Target Achievement. The sales report opens.

19. Click the Home icon.

20. Select Sales Reports. The home page is displayed.


22. Format the report:

a. Click cell C7.

b. Select 12 from the font size selector on the toolbar.

c. Click Save.

23. Click View mode. Test the link and make sure you can open the report then return using the icon on the report toolbar.


25. Right-click cell C7.

26. Select Copy.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

134 Optional lesson 11: Build a landing page © 2016 Infor Education

27. Select cell C9.

28. Press Ctrl-V to paste.

29. Type Trend in the selected cell.

30. Click Define Action.

31. Double-click Report 1 - Product Sales under the Input Value section.

32. Select Report 2 - Trend.

33. Click OK.

34. Click OK.

35. Click Save.


37. Test the links:

a. Click Trend. The Trend report opens.

b. Click Home.

c. Click Sales Reports. The Sales Reports home page opens.

38. Repeat steps 25 through 34 to add other reports:

Report 3 - Zero Suppression Report 4 - Sorting Report 5 - Multiselection Report 6 - Attributes Report 7 - Conditional Formatting Report 8 - Cell Notes Report 9 - Sorting by Company Report 10 - Charts Report 11 - Top 10 with Cell Notes

39. Click Save.

40. Click View mode. Test the links and make sure all reports open as expected.

@

Cop

yrigh

t 201

6 Info

r.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



Use the ____________ action to open another report.

a) Open dialog

b) Refresh

c) Link

d) Jump

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

136 Lesson 12: Report books © 2016 Infor Education

Lesson 12: Report books Estimated Time

45 minutes

Learning Objectives


Explain how to configure the page layout. Describe how to create a report book. Explain how to preview a report for printing. Explain how to use the Print Wizard to print a report. Explain how to export a report to various file formats.

Topics

Page setup Report Books Printing Reports Exporting Reports Check your understanding

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Page setup Application Studio provides the controls options necessary to format your report for printing. As with most other applications you can select settings for paper size, orientation, margins, etc.

Page Setup

In the Print area section:

Define the print area by specifying the cells in the range to print, for example: C3:H12 If some rows contain content which should be printed on each page, enter the line numbers,

separated by colons (for example 1:2:3) If some columns contain content which should be printed on each page, enter the column

names, separated by colons (for example A:B:C)

Do not print objects

The print function will print the report exactly as it appears on the screen. However, in most cases the report has been built for interactivity with drop-down selection menus. These serve no purpose in a printed report and should be removed before printing. The best approach is to specify the print option in the element properties dialog.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Object Properties

Deselect the Print Object check box so the printed report will not display the combo box.

Alias function

The other aspect of this approach is, "What do we put in place of the object?" We really want to display the current selected value of the object and display that in the report. As we described earlier, every object has multiple views including .text, .alias, and .attribute. We can specify the .alias of an object to display the current value for the report.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Display the alias for a combo box

Your instructor will demonstrate how to update a report to display the alias for an object.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Exercise 12.1: Display the alias for a combo box

In this exercise, you will update a report to display the alias for an object.

Exercise steps

1. Select Report 1 - Product Sales from Report Catalog under Repository Explorer.

2. Right-click cell D5.

3. Select Object Properties. Note the name.

4. Click the Appearance tab.

5. Deselect the Print Object check box.

6. Click OK.


8. Press the down arrow on the keyboard to move into cell D5.

9. Type =lv_Company.alias in the formula.

10. Select Filter List from the Standard drop-down list to format this cell.


12. Click Print Preview. The report shows the value of the current selection and the combo box is no longer displayed.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Header and Footer

Header and footer options are similar to most other applications with printing capability. Specify the text to display in each field or select several pre-defined options from the object list.

Header and footer settings

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Printing reports The standard Application Studio toolbar includes buttons that allow you to quickly and easily preview and print reports.

Infor ION BI Application Studio Help Printing

Using Print Preview

Reports do not always print as expected. For example, a report that looks as if it will print on one page may actually print on four pages. The Print Preview feature allows you to view a report before printing it and change settings to ensure that it prints correctly.

The Print Preview button is enabled in View and Debug modes.

Using the Print Wizard

Application Studio provides a Print Wizard that allows you to easily print reports and report books. When printing static reports, ones with no range selected, the Print Wizard offers standard print options, such as choice of printer, print range, and number of copies. If you are printing a report from which you can select other elements, for example, from combo boxes for slice dimensions, you can use the Print Wizard to print multiple reports.

Most of the selections in the Print Wizard are optional. You can click the Finish button on any page of the wizard when you are ready to print.

Optional demo: Use the Print Wizard

Your instructor will demonstrate how to use the print wizard to print the current report.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Optional exercise 12.2: Use the Print Wizard

In this exercise, you will use the print wizard to print the current report.

Exercise steps

1. Click the Print drop-down arrow icon from the toolbar.

2. Select Wizard… The Print Wizard displays.

3. Select Current Report radio button.

4. Click Next. The Print Ranges page displays.

5. Select the Elements drop-down list to the right of lv_Company. The Select Elements dialog box displays.

6. Click Select Element…

7. Select the European Holding check box.

8. Click OK.

9. Repeat steps 4-5 for each element you want to include in the printed report.

10. Select the Split By check box to begin a new report for each change in element.

11. Click Next. The Page Setup page displays.

12. Select the Landscape radio button under Orientation.

13. Click Next. The Header and Footer page displays.

14. Select Report Name from the footer drop-down list icon.

15. Click Next. The Printer Settings page displays.

16. Select the Microsoft XPS Document Writer to use in the Name field.

17. Click Finish.

18. Type Report1 in the File name field.

19. Click Save.

20. Click OK when prompted. The report displays in the Print Preview window. @

Cop

yrigh

t 201

6 Info

r.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Report Books You can use Report Books when you will be printing or exporting a group of reports, and you do not want to specify print settings each time you print the reports. When you create a report book, you define the content and structure of the book and specify the report layout settings for the book.

To create a report book, right-click Report Book in the Report Catalog and select New Report Book. You can then specify settings for the report book on the General, Reports, Page Setup, and Header and Footer tabs.

The following table displays the available tabs when printing report books and a description of each:

Dimension Description

General Specify general settings, such as the name and description of the report book

Reports Define the report structure and specify parameters for individual dimensions.

Page Setup Specify print settings, such as paper size, orientation, and margins.

Headers and Footers Specify header and footer print settings, such as printing the date and time in the footer of a report.

You must be in Design mode to specify report settings.

Structuring the Report Book

When creating a report book, the Reports tab allows you to structure the report book.

If reports are all listed at the same level, all views (selected elements) of one report will print, or export, followed by all views of the next report.

Report Book Structure

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


In this example, the Summary report prints for all three selected Regions. Then, the Product Details report will print for all three selected Products, using the last Region setting, Asia.

Report Structure: Reports at Same Level

If you want to repeat subsequent reports for each instance of the selected elements in the first report, you can click the right arrow to indent the subsequent reports under the initial report.

In the following example, the two indented reports (Product Details, Region Details) are repeated for each selected Region in the initial report.

Report Structure: Reports with Sub Books

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Specifying Report Parameters

The Reports tab also allows you to specify report parameters for individual dimensions. Parameters are the combo boxes or other list objects of the report, for which you can select, which elements to include.

The following table displays the available report settings for parameters:

Dimension Description

Show Select this check box to display the dimension in the Print Wizard, Export Wizard, and when scheduling tasks.

Editable Select this check box to allow editing in the Print Wizard, Export Wizard, and when scheduling tasks.

Elements Select specific element(s) of that dimension to be included. Options include:

All elements – includes all elements Automatic – includes the current

element, so the element currently selected in the combobox

<selection> – allows you to select the elements to include from the Select Elements dialog box

Split By Select this check box to create separate files, identified in the File Name field, for each element specified for that dimension.

Clear this check box to write all reports to a single file, specified in the File Name field.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Demo: Create a Report Book

Your instructor will demonstrate how to create a report book that includes three reports.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

Printing a Report Book

To print a report book, you open the Print Wizard and select a report book. The same options are available in the Print Wizard for printing report books as printing individual reports.

Print Wizard

Depending on your specifications in the General tab of the Report Book, some print options may not be available.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Exporting reports Click the Export button on the toolbar to display a drop-down list of export options.

The following table displays the available export options and a description of each:

Export Description

PDF Exports the report to a Portable Document File.

MS Excel Exports the report to a Microsoft Excel file.

HTML Exports the report to a HyperText Markup Language file.

MS Power Point Exports the report to a Microsoft Power Point file

images Exports a report as a .PNG or .JPG image. The image is exported in a .zip file.

Other Exports the report itself as .package file, which can be used to hand the report itself to someone else. The XML export option is available only on context menu on single reports

Wizard Displays the Export Wizard and allows you to export a report book or a report with a range selection.

Demo: Export a Report Book

Your instructor will demonstrate how to use the Export Wizard to export a report book to a PDF file.

______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________ ______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.



When you create a report book, you define the __________of the book. Select all that apply.

a) Reports

b) Page setup

c) Headers and footers

d) Content and structure

e) Owner

Click the ______________ button to make sure the report will print as expected before sending it to the printer.

a) Print

b) Print Wizard

c) Print Preview

d) Validate

Most of the selections in the Print Wizard are optional. You can click the Finish button on any page of the wizard when you are ready to print.

a) True

b) False

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


Course Summary Estimated Time

30 minutes

Course Objectives

Now that you have completed this course, you should be able to:

Identify the basic features and functionality of Application Studio. Create and format interactive reports by using styles and Style sheets. Filter and sort report data. Enrich reports with visual information like charts and sparklines. Build a landing page with report navigation. Print and export reports and report books.

Topics

Course Review

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

150 Course Summary © 2016 Infor Education

Course Review

What are three items you can add to a report to make it dynamic?

_______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________

What does selecting all base elements allow you to do?

_______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________

Which of the following can affect others users reports in Application Studio? Select all that apply.

a) Making changes to a local list b) Making changes to a global server list c) Editing a report style d) Editing a style sheet

Which of the following is not considered a list object?

a) A combo box. b) A list box. c) A command button. d) An option button.

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.


What does filtering by value do? Provide an example of filtering by value.

_______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________

What is a common use for sorting with a cut-off?

_______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________ _______________________________________________________________________

@ C

opyri

ght 2

016 I

nfor.

All righ

ts res

erved

. Do n

ot dis

tribute

and r

eprod

uce w

ithou

t auth

oriza

tion.

infor bi: creating reports with application studio...

Documents