Home Data Oracle Hyperion Interactive Reporting 11 Expert Guide

Oracle Hyperion Interactive Reporting 11 Expert Guide

books-svg-icon Book
eBook $36.99 $24.99
Print $60.99
Subscription $15.99 $10 p/m for three months
$10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
BUY NOW $10 p/m for first 3 months. $15.99 p/m after that. Cancel Anytime!
eBook $36.99 $24.99
Print $60.99
Subscription $15.99 $10 p/m for three months
What do you get with a Packt Subscription?
This book & 7000+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook + Subscription?
Download this book in EPUB and PDF formats, plus a monthly download credit
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with a Packt Subscription?
This book & 6500+ ebooks & video courses on 1000+ technologies
60+ curated reading lists for various learning paths
50+ new titles added every month on new and emerging tech
Early Access to eBooks as they are being written
Personalised content suggestions
Customised display settings for better reading experience
50+ new titles added every month on new and emerging tech
Playlists, Notes and Bookmarks to easily manage your learning
Mobile App with offline access
What do you get with eBook?
Download this book in EPUB and PDF formats
Access this title in our online reader
DRM FREE - Read whenever, wherever and however you want
Online reader with customised display settings for better reading experience
What do you get with video?
Download this video in MP4 format
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with video?
Stream this video
Access this title in our online reader
DRM FREE - Watch whenever, wherever and however you want
Online reader with customised display settings for better learning experience
What do you get with Audiobook?
Download a zip folder consisting of audio files (in MP3 Format) along with supplementary PDF
What do you get with Exam Trainer?
Flashcards, Mock exams, Exam Tips, Practice Questions
Access these resources with our interactive certification platform
Mobile compatible-Practice whenever, wherever, however you want
  1. Free Chapter
    Advanced Hyperion Interactive Reporting Techniques
About this book
Oracle Hyperion Interactive Reporting is one of many products in the Oracle Business Intelligence Enterprise Edition Plus software suite, an industry-leading business intelligence platform. The primary focus of the Interactive Reporting product is to provide strong relational querying and data analysis capabilities, where the software provides significant flexibility for creating custom dashboards, interfaces, and data analysis routines through the use of JavaScript programming and built-in software functionality. While Interactive Reporting is extremely flexible, performing advanced operations in the software is complicated and requires basic programming knowledge and an advanced understanding of the software. This book continues from where The Business Analyst’s Guide to Oracle Hyperion Interactive Reporting 11 completed and provides the reader with the information to successfully execute the advanced features of the product along with examples and specific techniques applicable to everyday use. The Oracle Hyperion Interactive Reporting 11 Expert Guide provides software users and developers with many examples of the techniques used by software experts. The book begins with an introduction to leveraging advanced features of the project and an introduction to JavaScript. Dashboards are a major focus of the book with four chapters focused on building a simple to complex dashboard including functions, global objects, and syncing selections across dashboards. The book places an emphasis on learning methods for data analysis by using advanced programming and built-in functions, and a unique approach to using code to generate batch reports and exports is provided. The Dashboard Studio Optimize Utility and the Dashboard Studio Merge utility are explained in detail, and the approach to building and using a central code repository for use in dashboards and computations across multiple documents in an enterprise is demonstrated. This book will help the reader become an expert user of the software, providing the skills necessary to understand, communicate, and perform advanced level tasks. The authors’ experience in developing and supporting Interactive Reporting users is very apparent throughout each chapter of the book. While the book displays content and examples from version 11 of the software, the book is also applicable to previous versions of the software dating back to version 8, so readers not currently using Interactive Reporting 11 will find the book applicable to daily use with the product.
Publication date:
December 2011
Publisher
Packt
Pages
276
ISBN
9781849683142

 

Chapter 1. Advanced Hyperion Interactive Reporting Techniques

Welcome to the Oracle Hyperion Interactive Reporting 11 Expert Guide! Interactive Reporting is an extremely robust and powerful business intelligence tool providing ad-hoc querying and analysis, dashboards, and reporting capabilities. This Expert Guide picks up where The Business Analyst's Guide to Oracle Hyperion Interactive Reporting 11 left off, with a focus on providing knowledge of the expert features of the product. While the Business Analyst's Guide was an introduction to using the product for the novice to intermediate user, this guide focuses on evolving software users into experts. Interactive Reporting provides many flexible and advanced features that are commonly unknown to the typical business user. One of the most important lesser known features of the software is that it exposes the developer features of the product to the everyday user. These developer features allow the user to leverage scripting in common exercises, to build custom interfaces, and to use code to drive automation. While these features may seem complicated to a user of the product, these features can easily be learned and implemented after reading this book.

This chapter will start with an introduction to the book, highlighting the different groups of content that will be discussed. After the book introduction, the following content is a brief review of some of the main features of Interactive Reporting. The purpose of this review is to baseline terminology that will be used throughout the book and to orient the user to the sections of Interactive Reporting where custom scripting and advanced features are utilized.

This chapter covers the following topics:

  • An introduction to the Expert Guide

  • A review of the EPM Workspace

  • A review of Interactive Reporting sections

  • Leveraging code throughout the software

  • An overview of the Scripting Interface

Introduction to the Oracle Hyperion Interactive Reporting 11 Expert Guide

Interactive Reporting provides users with a significant amount of flexibility in creating dashboards, reports, and analyzing data including the ability to leverage custom programming throughout the document. With this custom programming functionality comes complexity, where users need to understand the best practices in both the software and using JavaScript to create dashboards or custom calculations. This book focuses on providing the reader with an understanding and examples of where custom coding and features of Interactive Reporting can be leveraged to enhance the use of the product in daily activity. These features include introducing JavaScript programming concepts, creating simple to complex dashboards, analyzing content using built-in and JavaScript functions, creating briefing slides and batch exports, building a central code repository for use in the enterprise, and using the developer tools for optimizing and merging two or more Interactive Reporting documents together.

Review of Interactive Reporting concepts

The book begins with a review of the Interactive Reporting sections and highlights the use of Computed Items across each of the sections of the document software. The introduction provides a high-level understanding of the steps needed to perform computations in each section and provides insight into the differences in the sections. The Script Editor is also introduced, and an overview is provided on the features of the editor and the steps to add JavaScript code to dashboard objects and the overall document. This first chapter lays the framework for the book and provides a solid understanding for the content presented across the rest the book.

An Introduction to JavaScript in Interactive Reporting

The book transitions from the overview of the different sections of the product to an introduction to the syntax, methods, and features of JavaScript in Interactive Reporting. JavaScript is an object-oriented scripting language that is commonly used in web development to provide enhanced user interfaces. Within Interactive Reporting, JavaScript is used across all sections of the document, except for the Query section, to perform custom computations and to add interactivity. A solid understanding of JavaScript is needed to become an advanced user of the Interactive Reporting software. The fundamentals described in the second chapter and utilized throughout the rest of the book will provide the user with the confidence needed to build and leverage JavaScript in daily interaction with the software.

Building simple to advanced dashboards

Dashboard sections in Interactive Reporting are used for many purposes, from creating dashboard views of information, to using controls and objects on a dashboard to drive and orchestrate behaviour across multiple sections. This book places a large emphasis on building simple to complex dashboards and provides an understanding for managing code, filters, and interactivity across multiple dashboard sections in a single document. These dashboard chapters provide invaluable information for managing and reusing code inside a document, and the chapters demonstrate best practices for interacting with Interactive Reporting sections and components. Concepts learned in the dashboard chapters can be applied to any business situation where code is needed to perform an operation from processing queries to topics including creating custom programs that produce batch processing, using data from one query to filter another, and exporting to files.

Advanced computations

One of the key aspects of business intelligence is the ability for the user to analyze and manipulate content to answer a set of business questions. Many business users typically prefer to use Microsoft Excel to perform data analysis rather than Interactive Reporting due to their comfort with the software. While Microsoft Excel provides many excellent tools for performing data analysis, Interactive Reporting combines data analysis capabilities with the ability to filter, add computations, leverage data sets, and manipulate data in the millions of records.

Briefing slides and batch exports

Interactive Reporting provides many options for exporting information and formatted reports to different file formats. Users commonly struggle with the best and most appropriate method for creating data or formatted exports, with most users overlooking some of the most effective and efficient exporting methods. In addition to searching for the best export format, many users express interest in exporting information from Interactive Reporting into a Microsoft PowerPoint presentation. While the Hyperion Smart View product can be used to refresh objects in Microsoft documents, another option is available using a few tricks in Interactive Reporting and does not require the use of an additional piece of software.

Another less commonly known feature in Interactive Reporting is the ability to add custom code to generate batch exports of deliverables. Leveraging a few simple programming statements allows the user to save significant time and effort when exporting multiple slices of information from the same document.

The Central Code Repository

One challenge that advanced users encounter with Interactive Reporting's report-centric model is the tracking and maintenance of heavily customized dashboard reports, especially in enterprise-level implementations where the code is used repetitively and transparently across multiple documents. Given the common and straightforward practice of storing similar JavaScript code within each document in an enterprise, it is incredibly difficult and infeasible to individually identify, track, and edit changes across documents.

One simple and invaluable methodology is to store report customization scripts in an accessible database table within the enterprise environment. The chapter on the Central Code Repository (CCR) describes building an external code library that allows code to be quickly pushed into some or all documents in an enterprise. This centralized repository provides the capability for agile responses to ongoing business changes and code maintenance without modifying the consuming reporting documents.

Optimizing and Merging

The Oracle Hyperion Interactive Reporting developer tool installation is packaged with two developer tools that are extremely beneficial to users of the software. The products are the Dashboard Studio and the Dashboard Studio Optimize Utility, which allow developers to merge, modify, and fix Interactive Reporting documents in addition to many other operations not included in the Studio developer utility or Interactive Reporting Web Client.

 

Introduction to the Oracle Hyperion Interactive Reporting 11 Expert Guide


Interactive Reporting provides users with a significant amount of flexibility in creating dashboards, reports, and analyzing data including the ability to leverage custom programming throughout the document. With this custom programming functionality comes complexity, where users need to understand the best practices in both the software and using JavaScript to create dashboards or custom calculations. This book focuses on providing the reader with an understanding and examples of where custom coding and features of Interactive Reporting can be leveraged to enhance the use of the product in daily activity. These features include introducing JavaScript programming concepts, creating simple to complex dashboards, analyzing content using built-in and JavaScript functions, creating briefing slides and batch exports, building a central code repository for use in the enterprise, and using the developer tools for optimizing and merging two or more Interactive Reporting documents together.

Review of Interactive Reporting concepts

The book begins with a review of the Interactive Reporting sections and highlights the use of Computed Items across each of the sections of the document software. The introduction provides a high-level understanding of the steps needed to perform computations in each section and provides insight into the differences in the sections. The Script Editor is also introduced, and an overview is provided on the features of the editor and the steps to add JavaScript code to dashboard objects and the overall document. This first chapter lays the framework for the book and provides a solid understanding for the content presented across the rest the book.

An Introduction to JavaScript in Interactive Reporting

The book transitions from the overview of the different sections of the product to an introduction to the syntax, methods, and features of JavaScript in Interactive Reporting. JavaScript is an object-oriented scripting language that is commonly used in web development to provide enhanced user interfaces. Within Interactive Reporting, JavaScript is used across all sections of the document, except for the Query section, to perform custom computations and to add interactivity. A solid understanding of JavaScript is needed to become an advanced user of the Interactive Reporting software. The fundamentals described in the second chapter and utilized throughout the rest of the book will provide the user with the confidence needed to build and leverage JavaScript in daily interaction with the software.

Building simple to advanced dashboards

Dashboard sections in Interactive Reporting are used for many purposes, from creating dashboard views of information, to using controls and objects on a dashboard to drive and orchestrate behaviour across multiple sections. This book places a large emphasis on building simple to complex dashboards and provides an understanding for managing code, filters, and interactivity across multiple dashboard sections in a single document. These dashboard chapters provide invaluable information for managing and reusing code inside a document, and the chapters demonstrate best practices for interacting with Interactive Reporting sections and components. Concepts learned in the dashboard chapters can be applied to any business situation where code is needed to perform an operation from processing queries to topics including creating custom programs that produce batch processing, using data from one query to filter another, and exporting to files.

Advanced computations

One of the key aspects of business intelligence is the ability for the user to analyze and manipulate content to answer a set of business questions. Many business users typically prefer to use Microsoft Excel to perform data analysis rather than Interactive Reporting due to their comfort with the software. While Microsoft Excel provides many excellent tools for performing data analysis, Interactive Reporting combines data analysis capabilities with the ability to filter, add computations, leverage data sets, and manipulate data in the millions of records.

Briefing slides and batch exports

Interactive Reporting provides many options for exporting information and formatted reports to different file formats. Users commonly struggle with the best and most appropriate method for creating data or formatted exports, with most users overlooking some of the most effective and efficient exporting methods. In addition to searching for the best export format, many users express interest in exporting information from Interactive Reporting into a Microsoft PowerPoint presentation. While the Hyperion Smart View product can be used to refresh objects in Microsoft documents, another option is available using a few tricks in Interactive Reporting and does not require the use of an additional piece of software.

Another less commonly known feature in Interactive Reporting is the ability to add custom code to generate batch exports of deliverables. Leveraging a few simple programming statements allows the user to save significant time and effort when exporting multiple slices of information from the same document.

The Central Code Repository

One challenge that advanced users encounter with Interactive Reporting's report-centric model is the tracking and maintenance of heavily customized dashboard reports, especially in enterprise-level implementations where the code is used repetitively and transparently across multiple documents. Given the common and straightforward practice of storing similar JavaScript code within each document in an enterprise, it is incredibly difficult and infeasible to individually identify, track, and edit changes across documents.

One simple and invaluable methodology is to store report customization scripts in an accessible database table within the enterprise environment. The chapter on the Central Code Repository (CCR) describes building an external code library that allows code to be quickly pushed into some or all documents in an enterprise. This centralized repository provides the capability for agile responses to ongoing business changes and code maintenance without modifying the consuming reporting documents.

Optimizing and Merging

The Oracle Hyperion Interactive Reporting developer tool installation is packaged with two developer tools that are extremely beneficial to users of the software. The products are the Dashboard Studio and the Dashboard Studio Optimize Utility, which allow developers to merge, modify, and fix Interactive Reporting documents in addition to many other operations not included in the Studio developer utility or Interactive Reporting Web Client.

 

The EPM Workspace


The EPM Workspace, similar to a portal, is where all Oracle Hyperion objects, reports, and files can be accessed and integrated using a shared security model. The Workspace is accessible through the web browser and contains a file system and other document management gadgets, including personal pages, job scheduling, and content subscription.

 

Interactive Reporting interface and components


Understanding the Interactive Reporting interface is crucial to being proficient in the software. The different sections of the software contain a variety of different options, but the location of where to find and utilize these options is the same across the tool. Knowledge of the interface and how to leverage the features of each section is essential to unlocking the full potential of the product. The terms displayed in the next screenshot will be referenced throughout the book:

Nearly all features of the Interactive Reporting user interface can be controlled or modified by report authors. Later chapters of the book will describe the methods for controlling the display of the different Web Client Interface menus, toolbars and sections.

 

Review of Interactive Reporting sections


Before identifying where the advanced features of the product can be leveraged in the document, it is important to review the different sections of the software. The following list provides an overview of each section of the Interactive Reporting software:

  • The Data Model and Query sections are used to model database objects and build queries from a data source.

  • Each Query section is accompanied by a Results section where data returned from a query is displayed, filtered, and computed. There is only one Results section per Query section, and each Results section displays data in a row by column format.

  • Table sections are similar to Results sections and are used to manipulate and split a set of results into different subsets for analysis. Each table section is specific to a set of results and multiple table sections can be created.

  • A Pivot section is a common presentation section in Interactive Reporting that aggregates data in a row by column format and the pivot functionality is similar to Microsoft Excel pivots. The Pivot section can use any column of data from the parent Results or Table section.

  • A Chart section is another presentation section of the document. The Chart section is commonly used to display data in a graphical format. Different chart formats exist, ranging from bar and pie charts to scatter and bubble charts.

  • The Report section provides the ability to present charts and tables of data in a printable report format. The Report section is the only section of the document that allows for the addition of data from multiple queries in the document into a single reporting object, and reports provide an additional method of splitting out data by Report Groups.

  • Dashboards are used to create custom interfaces or interactive displays of key metrics. The dashboard section provides the flexibility to leverage custom scripting and interactivity to automate manual features of the product.

 

Leveraging code


Many methods exist for adding custom code throughout the document. Interactive Reporting provides the flexibility to manipulate data and build custom applications through using a JavaScript programming language throughout all sections of the document except the Query section, where custom fields are defined using database specific SQL.

JavaScript is commonly used in two ways within the document, including - building computations in presentation sections and building logic to force specific behaviours across the document or on a dashboard. When building computations in sections, JavaScript and native functions are used in Computed Items to modify and enhance a set of results. When driving behaviour across the documents or adding interactivity, JavaScript code is used in the Document Scripts or on a Dashboard section. In either approach, Interactive Reporting provides significant flexibility and interactivity to model and solve complex business problems.

Query section Computed Items

Many users require the ability to provide further analysis on data returned from a query. Computed Items can be added to a query to create custom Request line items, which allow the user to modify and enhance the data elements in the data model.

Building Computed Items in the Query section is very different than building Computed Items throughout the rest of the document. The syntax for building Computed Items in the Query section is database specific SQL, while Computed Items throughout the rest of Interactive Reporting are created using JavaScript. The differentiation in syntax is based on the design of the product, where the Query section is the only section that builds a query string that is passed to the database. The rest of the sections in an Interactive Reporting document contain and display data that has already been returned from a data source.

The syntax of the Computed Item in a query is included as a column in the query string that is passed to the database. The following steps are methods to add Computed Items to the Request line:

Right-click method

Query menu method

Right-click inside the Request line

Select Add Computed Item

Open the Query menu

Select Add Computed Item

Upon adding the Computed Item to a query, a window opens as shown in the following screenshot:

The logic for the blank Computed Item can be typed directly into the Computed Item Definition box of the Properties window that appears. The Functions and Reference buttons are included to aid the user in creating the Computed Item definition by providing a selection option for referencing column names and a list of predefined functions. The Options button is used to set the Datatype of the column, which is important in order to accurately represent the data returned from the query for the column.

Note

In each Query Element, the Table or Topic name must be added as a qualifier in front of the column name, that is, Table_Name.Column_Name. This syntax is similar to qualifying a database table with the table owner, where Interactive Reporting treats the Table or Topic as the owner of the table.

Since the table needs to be qualified (referenced by table name), users commonly add items to the Request line and then edit the item properties by double-clicking on the item to view the Properties of the item. By adding an item to the Request line before editing, the item definition is populated in the Definition section of the Properties window. Upon the completion of editing the Computed Item, the item is added to the Request line with the Name specified after pressing OK.

Note

Before completing the custom column definition, it is important to review and adjust the Datatype of the column using the Options of the item. For example, converting a Date field to a string requires the Data type of the column to be set to a string to avoid incorrectly formatted data after processing.

The following are examples of simple Query section Computed Items with Oracle as a data source:

  • Concatenation: Products.Prod_Name||' '||Products.Prod_Desc

  • String Manipulation: SUBSTR(Customers.Cust_Postal_Code,1,5)

  • Value Replacement: NVL(Promotions.Promo_Name, 'No Product Name').

  • Date/Time Functions: TO_DATE('2009-02-01','YYYY-MM-DD')

When Computed Items are added to the Request line, the Computed Item element will display at the end of the list of all of the elements in the Request line. Upon processing, the computed column will appear in the data results in the order of the columns in the Request line (if this is the first time the query is processed) or the field will be added at the end of the data results (if the query was previously processed). Computed Items are not differentiated in appearance from any other element in the query, where the Computed Item elements appear similar to the elements mapped from topics or tables. However, any Computed Item or data element containing a function will display with the function notation surrounding the data element name in the Request line. For example, adding a SUM function to a data element in the Query section will display the function name, as shown in the following screenshot:

Results section Computed Items

Computed Items are added to the Results section to modify contents and add calculations to data returned from a query. Since the results set is data that has already been processed, the Computed Items in the Results section are based on data stored in the document. While the syntax used in Computed Items in the Query section is the SQL syntax of the database, the syntax used in the Results section to create custom computations is JavaScript. The following steps are methods to add a Computed Item to the Results section:

Right-click method

Results menu method

Right-click inside the Results section

Select Add Computed Item

Open the Results menu

Select Add Computed Item

Once the Add Computed Item menu item has been selected, the Computed Item window appears, as shown in the following figure:

The Name box is for specifying the name of the Computed Item. The Definition text box is used for entering the logic for the Computed Item.

Note

It is important to note that the column names are case sensitive. It is also important to note that columns with a space in the column name must contain underscores instead of spaces when referenced in Computed Items (that is, Day of Week is referenced as Day_of_Week in the definition window) and special characters are also replaced with underscores. If adding computations to a column with a special character, it is helpful to use the Reference feature to select the column.

The buttons below the Definition text box are the different logic conditions and operators that can be used in the Computed Item definition. These different conditions and operators can be typed into the Definition box by the user or the buttons on the screen can be used to populate the definition window with the desired content. The buttons to the right of the text box are similar to the buttons in the Query section, where predefined Functions can be used to transform data elements in the section. These predefined functions are specific to Interactive Reporting and provide the ability to manipulate string, date, and text data. The Reference button provides the ability to accurately reference data elements in the section without the need to type in the name of the column. The Options button is used to set the Datatype of the column, which is important in order to accurately represent the column of data after the computation.

Upon the completion of editing the Computed Item, the item is added to the Results section with the Name specified after pressing OK. The column is added to the end of the Results section and is displayed in the Data Layout window in blue text and in italics. The column can be moved around to the desired location in the Results section, and the column can be easily modified by following one of the two steps:

Right-click method

Results menu method

Highlight the column of data in the Results section

Right-click and select Modify Column

Highlight the column of data in the Results section

Open the Results menu and select Modify Column

The following are a few simple examples of Results section computed items:

  • Concatenation: Products.Prod_Name+" "+Products.Prod_Desc

  • String Manipulation: Substr(Customers.Cust_Postal_Code,1,5)

  • Conditional Functions: Nvl(Promotions.Promo_Name, 'No Product TTarName')

The examples provided are used to demonstrate simple programming operations that can be performed in the Results section. Future chapters of this book will discuss the different programming functionality and advanced operations that can be added to Computed Items.

Table section Computed Items

Computed Items in a Table section are very similar to Computed Items in the Results section. However, the main difference between the Table section and the Results section is the ability to add multiple Table sections to a set of results. The ability to add multiple Table sections to a set of results allows the user to split Computed Items between the Tables and allows the user to filter on a Computed Item that is created in a parent Results or Table section leveraging the Prior, Next, Cume or any other aggregating function.

Note

Filters cannot be placed on an aggregating function in the section it was created.

The following steps are methods to add a Computed Item to the Table section:

Right-click method

Table menu method

Right-click inside the Table section

Select Add Computed Item

Open the Table menu

Select Add Computed Item

Once the Add Computed Item menu item has been selected, the Computed Item window appears, as shown in the following figure:

The functionality of the Computed Item box is identical to the features provided in the Results Computed Item section and no additional functionality exists that is different from the Results section.

Pivot section Computed Items

Computed Items are common operations performed in the Pivot section, where calculations are easily performed on aggregated sets of data. While items are added to the Row and Column Labels, the Pivot section only provides the ability to add a Computed Item to the pivot Facts. While computations can be completed in the Results section, it is more effective to compute percentages and other division based calculations in the Pivot than the Results or Table sections to accurately calculate totals. A Computed Item is added to a Pivot section by following one of the two steps:

Right-click method

Pivot menu method

Right-click in the main Pivot window.

Select Add Computed Item

Open the Pivot menu

Select Add Computed Item

The syntax for a Computed Item is the same as that found in the Results and Table sections and the Computed Item is highlighted in italics in the Facts section of the Data Layout. In the next example, a Computed Item has been created to show Total Sales over Quantity Sold:

Notice the computation of the values in the Total lines. Currently the Total lines show the sum of the values displayed in the pivot and not the division of the Total Sales value by the Quantity Sold total value.

Pivot Settings: True Totals and Surface Values

The Pivot section provides the flexibility of calculating the Computed Items and total lines of the document by either the computation of the values shown on the screen or by the values contained in the data results. The settings can be easily configured in the Pivot Options menu, where the Use Surface Values or True Computed Item Totals options checkboxes can be toggled.

When the True Computed Item Totals option is selected, the pivot total lines will be calculated by totaling the individual values of the columns that make up the total instead of performing the computation operation on the total column.

When the Surface Values option is selected, the pivot calculates the Computed Item from values displayed on the pivot instead of the values in the parent Results or Table section for all columns. The Surface Values setting is necessary when adding one or more Computed Items together in the pivot where the values in the pivot must be used in the calculation.

Editing Pivot Computed Items

Similar to the Results and Table sections, the Computed Item can be modified by one of the following two steps:

Right-click method

Pivot menu method

Highlight the column of data in the Pivot section

Right-click and select Modify

Highlight the column of data in the Pivot

Open the Pivot menu and select Modify

Chart section Computed Items

Computed Items can be leveraged in a Chart to display constants or other variations of data elements not contained in the data results. Computed Items are added to a Chart by one of the following two steps:

Right-click method

Chart menu method

Right-click in the main Chart window.

Select Add Computed Item

Open the Chart menu

Select Add Computed Item

The syntax for Computed Items in a Chart section is the same as found in the Results and Table sections, where the Computed Item is highlighted in italics in the Facts section of the Data Layout.

Note

Computed Items cannot be used in the Scatter or Bubble charts.

The chart in the following screenshot is a Bar Line chart, with a Computed Item set to a line and configured to use the scale on the left axis:

In this example, the Computed Item, Budget, is set to a constant value to display a threshold. The following screenshot displays the syntax used to complete the Computed Item. More advanced logic can be used as desired:

Computed Items in the Chart section are easily modified by one of the two following steps:

Right-click method

Chart menu method

Highlight the data element in the Data Layout window of the Chart.

Right-click and select Modify Computed Item

Highlight the data element in the Data Layout window of the Chart.

Open the Chart menu and select Modify Computed Item

Report section Computed Items

The Report section provides the ability to add two types of computations in a report, where computations can be performed in Field expressions as well as in the Facts of Report Tables. Computed Items are added to Report Tables through one of the two following methods:

Report menu method

Right-click method

Highlight a column of data inside the table.

Open the Report menu and select Add Computed Item.

Highlight a column of data inside the table.

Right-click and select Add Computed Item.

When a Computed Item is added, the Computed Item window appears as shown in the following screenshot:

The text box at the top of the window is for adding the desired name for the column. Below the Column Name field is the configuration for selecting a field from a table. At the bottom of the window is the Formula section for adding the logic to the Computed Item. The following screenshot shows an example of a Computed Item leveraging fields from two Table sections:

Notice the difference in logic between the Report section Computed Items and the logic for the other Computed Items in the document. Since the Report section Computed Items can reference data from more than one section in the document, Report section Computed Items require a reference back to the section where the data element is contained. Additionally, a data function is referenced at the end of each Computed Item. The function and the term currBreak are added because the Report sections contain grouping that can enforce different levels of aggregation. Each item in a Report section is aggregated based on the area that is placed in a report, whether it is added to the Body, Header, Footer, or one of the Report Groups. The currBreak defines the aggregation to apply, where variations of the currBreak logic can be utilized to modify the aggregation performed.

The Expression line

In addition to editing Computed Items using the Computed Item window, the Expression line in the Report section is also used to edit Computed Items as well as Field items. The Expression line is a feature that is specific to the Report section and logic is displayed in the Expression line when an item is clicked by the user in a report.

Document Scripts

Each Interactive Reporting document contains the ability to have a predefined set of steps occur when the document opens, before the document closes, before processing, after processing, and upon update of session values. These document level actions are referred to as Document Scripts in Interactive Reporting.

Document Scripts are added to the document by clicking on the File menu and selecting the Document Scripts menu item. Upon selecting the Document Scripts item, the scripting interface window called the Script Editor is displayed for adding the logic for the Document Scripts. Chapter 5, Building the Dashboard Framework section, provides details on configuring Document Scripts to support dashboards and global operations.

Dashboards

The term dashboard is typically used to describe an executive-level report displaying key business indicators in an effort to capture the health of a business at a point in time. The Dashboard section in Interactive Reporting provides users with the ability to create simple to complex dashboards, as well as the flexibility to add controls and custom JavaScript programming to add interactivity or to create a custom interfaces.

 

The Scripting Interface


The Script Editor is the interface where code is written to perform an operation through Document Scripts or the Dashboard section. The Script Editor is opened in one of several ways including: selecting the Document Scripts menu item from the File menu, selecting the Scripts menu item from the Dashboard menu, or by right-clicking an object on a dashboard and selecting Scripts. The following image is an example of the Script Editor open to an item on the Dashboard section:

Chapter 2, Introduction to JavaScript and the Interactive Reporting API, provides a detailed introduction to JavaScript and provides a detailed explanation of the Script Editor.

 

Summary


The goal of this chapter was to provide an introduction to the book and a review of Interactive Reporting terminology, adding Computed Items to sections, and the Scripting Interface. The chapter began with an introduction to the topics that are discussed in future chapters, including JavaScript, Dashboards, Advanced Computations, the Central Code Repository, Batching, and the Dashboard Studio and Optimize Utility. The chapter transitions from an introduction to a review of Interactive Reporting topics and terminology used throughout the book, including the Interactive Reporting Interface and a review of Interactive Reporting sections. The methods to create, build, and modify Computed Items in each section are introduced, and an introduction to Document Scripts is presented. The chapter concluded with a brief overview the Scripting Interface, setting the stage for the following chapter on logical programming in JavaScript.

Oracle Hyperion Interactive Reporting 11 Expert Guide
Unlock this book and the full library FREE for 7 days
Start now