Some software products marketed by SAP AG and its distributors contain proprietary software components of other software vendors.
Trademarks •
Microsoft®, WINDOWS®, NT®, EXCEL®, Word®, PowerPoint® and SQL Server® are registered trademarks of Microsoft Corporation.
•
IBM®, DB2®, OS/2®, DB2/6000®, Parallel Sysplex®, MVS/ESA®, RS/6000®, AIX®, S/390®, AS/400®, OS/390®, and OS/400® are registered trademarks of IBM Corporation.
•
ORACLE® is a registered trademark of ORACLE Corporation.
•
INFORMIX®-OnLine for SAP and INFORMIX® Dynamic ServerTM are registered trademarks of Informix Software Incorporated.
•
UNIX®, X/Open®, OSF/1®, and Motif® are registered trademarks of the Open Group.
•
Citrix®, the Citrix logo, ICA®, Program Neighborhood®, MetaFrame®, WinFrame®, VideoFrame®, MultiWin® and other Citrix product names referenced herein are trademarks of Citrix Systems, Inc.
•
HTML, DHTML, XML, XHTML are trademarks or registered trademarks of W3C®, World Wide Web Consortium, Massachusetts Institute of Technology.
•
JAVA® is a registered trademark of Sun Microsystems, Inc.
•
JAVASCRIPT® is a registered trademark of Sun Microsystems, Inc., used under license for technology invented and implemented by Netscape.
•
SAP, SAP Logo, R/2, RIVA, R/3, SAP ArchiveLink, SAP Business Workflow, WebFlow, SAP EarlyWatch, BAPI, SAPPHIRE, Management Cockpit, mySAP.com Logo and mySAP.com are trademarks or registered trademarks of SAP AG in Germany and in several other countries all over the world. All other products mentioned are trademarks or registered trademarks of their respective companies.
Disclaimer THESE MATERIALS ARE PROVIDED BY SAP ON AN "AS IS" BASIS, AND SAP EXPRESSLY DISCLAIMS ANY AND ALL WARRANTIES, EXPRESS OR APPLIED, INCLUDING WITHOUT LIMITATION WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE, WITH RESPECT TO THESE MATERIALS AND THE SERVICE, INFORMATION, TEXT, GRAPHICS, LINKS, OR ANY OTHER MATERIALS AND PRODUCTS CONTAINED HEREIN. IN NO EVENT SHALL SAP BE LIABLE FOR ANY DIRECT, INDIRECT, SPECIAL, INCIDENTAL, CONSEQUENTIAL, OR PUNITIVE DAMAGES OF ANY KIND WHATSOEVER, INCLUDING WITHOUT LIMITATION LOST REVENUES OR LOST PROFITS, WHICH MAY RESULT FROM THE USE OF THESE MATERIALS OR INCLUDED SOFTWARE COMPONENTS.
g201162010235
For internal use by CSC only
For internal use by CSC only
No part of this publication may be reproduced or transmitted in any form or for any purpose without the express permission of SAP AG. The information contained herein may be changed without prior notice.
About This Handbook For internal use by CSC only
Typographic Conventions American English is the standard used in this handbook. The following typographic conventions are also used. Type Style
Description
Example text
Words or characters that appear on the screen. These include field names, screen titles, pushbuttons as well as menu names, paths, and options. Also used for cross-references to other documentation both internal and external.
2011
Example text
Emphasized words or phrases in body text, titles of graphics, and tables
EXAMPLE TEXT
Names of elements in the system. These include report names, program names, transaction codes, table names, and individual key words of a programming language, when surrounded by body text, for example SELECT and INCLUDE.
Example text
Screen output. This includes file and directory names and their paths, messages, names of variables and parameters, and passages of the source text of a program.
Example text
Exact user entry. These are words and characters that you enter in the system exactly as they appear in the documentation.
Variable user entry. Pointed brackets indicate that you replace these words and characters with appropriate entries.
This handbook is intended to complement the instructor-led presentation of this course, and serve as a source of reference. It is not suitable for self-study.
About This Handbook
BOW310
Icons in Body Text The following icons are used in this handbook.
For internal use by CSC only
Meaning For more information, tips, or background
Note or further explanation of previous point Exception or caution Procedures
Indicates that the item is displayed in the instructor's presentation.
Course Overview ......................................................... vii Course Goals ...........................................................vii Course Objectives .....................................................vii
Unit 1: Introducing Interactive Analysis .............................. 1 Describing Interactive Analysis Concepts ...........................3 Explaining Interactive Analysis Core Functionality .................8 Describing Interactive Analysis and the SAP BusinessObjects Business Intelligence Platform .................................. 11
Unit 2: Creating Interactive Analysis Documents with Queries 17 Querying with Interactive Analysis.................................. 19 Creating a New Document........................................... 23 Modifying a Document's Query ..................................... 30 Working in the Query Panel ......................................... 34
Unit 3: Restricting Data Returned by a Query..................... 47 Restricting Data with Query Filters ................................. 49 Modifying a Query with a Predefined Query Filter ................ 55 Applying a Single-value Query Filter ............................... 58 Using Wildcards in Query Filters.................................... 63 Using Prompts to Restrict Data ..................................... 71 Using Complex Filters ................................................ 81
Unit 4: Designing Interactive Analysis Reports .................. 95 Working with Interactive Analysis Documents .................... 97 Displaying Data in Tables ........................................... 115 Working with Tables .................................................121 Presenting Data in Free-standing Cells ...........................127 Presenting Data in Charts ..........................................133
Unit 5: Enhancing the Presentation of Data in Reports........ 149 Using Breaks and Calculations.....................................152 Using Sorts and Hiding Data .......................................159 Formatting Breaks and Cross Tables..............................170 Filtering Report Data ................................................183 Ranking Data .........................................................192 Tracking Data Changes .............................................200 Highlighting Information with Formatting Rules ..................207
Organizing a Report into Sections .................................219 Copying and Pasting Data Blocks .................................226
Unit 6: Formatting Reports ........................................... 235 For internal use by CSC only
Unit 7: Calculating Data with Formulas and Variables ......... 267 Formulas and Variables .............................................269 Using Formulas and Variables .....................................273
Unit 8: Using Multiple Data Sources ............................... 293 Synchronizing Data from Multiple Data Sources ................295 Creating Multiple Queries in a Document.........................299 Synchronizing Data with Merged Dimensions ...................308 Using a Personal Data Provider ...................................313
Unit 9: Analyzing Data ................................................. 325 Analyzing the Data Cube ...........................................326 Drilling in an Interactive Analysis Document .....................330
Unit 10: Managing and Sharing Interactive Analysis Documents ............................................................... 343 Publishing Documents to the Corporate Repository ............345 Logging in to the BI Launch Pad ...................................349 Managing Documents in BI Launch Pad..........................353 Viewing an Interactive Analysis Document in BI Launch Pad..357 Sharing Interactive Analysis Documents..........................362 Setting BI Launch Pad Preferences ...............................367 Logging off from BI Launch Pad ...................................371
During this course, you’ll learn how to create Interactive Analysis documents for your reporting needs, to retrieve data by building queries using SAP BusinessObjects universes and to use Interactive Analysis to enhance documents for easier analysis. You’ll also learn how to organize, manage and distribute documents using BI launch pad. After taking this course, you will be able to efficiently and effectively manage personal and corporate documents to access the information you need, when you need it. You will be able to design your own reports using Interactive Analysis and share your analysis with other users.
Target Audience This course is intended for the following audiences: •
The target audience for this course is report designers who need to access and analyze information using BI launch pad and Interactive Analysis.
Course Prerequisites Required Knowledge •
Not applicable for this offering.
Course Goals This course will prepare you to: •
Access, analyze and share data using SAP BusinessObjects Interactive Analysis and BI launch pad.
Course Objectives After completing this course, you will be able to: • •
2011
Efficiently and effectively manage personal and corporate documents to access the information you and report users need. Design reports using Interactive Analysis and share your analysis with other users.
This two-day instructor-led course is designed to give you the comprehensive skills and in-depth knowledge needed to access, analyze and share data using SAP BusinessObjects™BI launch pad™ and SAP BusinessObjects™ Interactive Analysis™.
Introducing Interactive Analysis Unit Overview This unit presents key Interactive Analysis concepts and vocabulary that are important to understand before beginning this course. After completing this unit you will be able to : • • •
Describe Interactive Analysis concepts Explain Interactive Analysis core functionality Describe the relationship among SAP BusinessObjects Business Intelligence platform, BI launch pad and Interactive Analysis
Unit Objectives After completing this unit, you will be able to: • • • • • • • •
Explain how Interactive Analysis allows you to access information Describe universes, the SAP BusinessObjects semantic layer that allows you to query your corporate databases using your own everyday business terms Explain how Interactive Analysis queries retrieve data from your corporate databases Describe the different presentation styles from which you can choose to display the data in Interactive Analysis documents Understand how Interactive Analysis allows you to analyze data at different levels of detail Describe what the SAP BusinessObjects Business Intelligence platform is Explain the relationship among the SAP BusinessObjects Business Intelligence platform, Interactive Analysis and BI launch pad Describe the different methods available for sharing Interactive Analysis documents with your colleagues
Lesson: Describing Interactive Analysis and the SAP BusinessObjects Business Intelligence Platform ................................................. 11
Lesson: Describing Interactive Analysis Concepts Lesson Overview For internal use by CSC only
Lesson Objectives After completing this lesson, you will be able to: • •
Explain how Interactive Analysis allows you to access information Describe universes, the SAP BusinessObjects semantic layer that allows you to query your corporate databases using your own everyday business terms
Business Example Your organization has chosen to use Interactive Analysis to query your databases, report, analyze and share key data to make business decisions. This overview of Interactive Analysis and its semantic language supports you in your ability to use the application effectively.
Accessing Information with Interactive Analysis With Interactive Analysis, you can query your organization’s databases using your everyday business terms and an easy-to-use interface. You can create simple or very complex reports, and share the information you display with colleagues throughout the enterprise.
Getting the Right Information to Make Decisions You and your colleagues need quick, easy access to information. As part of your job, you make decisions all the time. That means you need the right information at your fingertips to help you make the right decisions. You want to access information using your everyday business vocabulary without having to understand anything about the technical way the information is stored. And once you’ve analyzed this information, you need to be able to share it with your colleagues.
Interactive Analysis is a tool for analyzing your organization's business information. It provides access to the information that you need through the SAP BusinessObjects semantic layer.
BOW310
Figure 1: Interactive Analysis Core Functions
Interactive Analysis allows business users to access and analyze corporate data from heterogeneous data sources, online or offline. They can also share the data and insights with colleagues, customers and partners over intranets and extranets. The data sources include: • • • • • • •
Relational databases (RDBMS) Online analytical processing (OLAP) server Personal data providers such as Microsoft Excel or CSV files Business Explorer (BEx) queries based on SAP Info Cubes Web services Advanced Analysis workspaces The SAP High-Performance Analytical Appliance (HANA) data source which allows you to benefit from in-memory computing
To access Interactive Analysis, you can log into the portal Business Intelligence (BI) launch pad via your internet browser. You can then create and edit Interactive Analysis documents and analyze the data displayed in them. Using BI launch pad, you can share documents with other users. You can use the Interactive Analysis Desktop, a standalone Microsoft Windows application that you can install on your computer. Interactive Analysis Desktop lets you continue to work with Interactive Analysis documents (WID) when you are unable to connect to the BI launch pad, when you want to perform calculations locally rather than on the server, or when your organization chooses to deploy Interactive Analysis without installing SAP BusinessObjects Business Intelligence platform servers or an application server. You can also use the Interactive Analysis Desktop while connected to the SAP BusinessObjects Business Intelligence platform repository. This repository is known as the Central Management Server, or "the CMS". If you use Interactive Analysis Desktop while connected to the CMS, you are working in "connected" mode. In connected mode, you can export documents that you have created locally, so that BI launch pad users can access them in the CMS repository.
Interactive Analysis provides you access to data in other data sources, such as a BEx query or an Excel file without a universe. Universes are based on business terminology that is familiar to you and shared throughout the organization so they shield users from the technical complexities of the databases where your corporate information is stored. Interactive Analysis uses universes to provide access to data. Using a universe's business-oriented view of the data, you can create queries in Interactive Analysis and get information using your own everyday terms. The terms you need to be familiar with in order to understand how this semantic layer functions for relational databases are: • • • •
Object Class Predefined query filter Universe
Object Objects are elements in a universe that map to a specific set of data in a relational database. Each object in a SAP BusinessObjects universe is defined with a business term that is commonly used in your organization, such as Country, Year, Revenue, Customer name, Customer address, and so on.
Class Classes are logical groupings of related objects. This diagram shows some of the objects used in this course to build queries and create Interactive Analysis documents, and their organization into classes. Classes are indicated here by a folder icon. The objects shown in this diagram are indicated by blue diamonds, orange rectangles, and yellow funnels. For example, the Store class includes the objects State, City, and Store name. The Store class also includes a subclass, called Store details, which contains other objects related to Stores.
Predefined Query Filter The elements identified by yellow filters in the diagram are predefined query filters. Query filters are used to restrict the data returned by an object used in a query.
Interactive Analysis allows you to access and analyze your organization's data from Relational databases and OLAP servers by means of a semantic layer in one or more SAP BusinessObjects universes. This semantic layer is a view of your organization's data, presented as one or more universes.
Unit 1: Introducing Interactive Analysis
BOW310
Later in this course, you will learn more about the different types of objects that are available in universes, as well as the role of the predefined query filter in the universe. For internal use by CSC only
An SAP BusinessObjects universe relates the objects for a business area such as finance, sales, human resources, or purchasing departments to your organization's data stored in its databases. The diagram above shows the eFashion universe which defines the objects that you use in this course. Universes are created by a universe designer who is familiar with your organization's databases using Universe design tool. The universe designer then makes the universes available to you and other users in BI launch pad, the SAP BusinessObjects portal. When you create Interactive Analysis documents, you first select a universe to query the database where the data that interests you is stored.
Quick and Easy Access to Information Business intelligence software offers tools for generating reports automatically whenever they are needed and provides easy analysis and querying to help with the decision-making business process. Many business intelligence platforms require the intervention of an information service to produce personalized OLAP cubes that are then used for reporting. Interactive Analysis users on the other hand, have access directly to the source database allowing for robust query and analysis options. This helps in responding to everyday questions and in facing problems that are present when trying to make good decisions quickly. The BusinessObjects business intelligence platform separates users from the complexity and diversity of databases and associated technologies. It uses business terminology that is shared throughout the enterprise. Because it takes less time to gather information, users can concentrate on their analysis to make the best decisions.
You should now be able to: • Explain how Interactive Analysis allows you to access information • Describe universes, the SAP BusinessObjects semantic layer that allows you to query your corporate databases using your own everyday business terms
BOW310
Lesson: Explaining Interactive Analysis Core Functionality Lesson Overview Interactive Analysis allows you to perform querying, reporting and analysis tasks, all in a single tool.
Lesson Objectives After completing this lesson, you will be able to: • • •
Explain how Interactive Analysis queries retrieve data from your corporate databases Describe the different presentation styles from which you can choose to display the data in Interactive Analysis documents Understand how Interactive Analysis allows you to analyze data at different levels of detail
Business Example This lesson provides you with an overview of core functions of Interactive Analysis so you can understand how your organization benefits from using the application.
Querying with Interactive Analysis To gather the data you require, you begin by creating an Interactive Analysis document and selecting the data source that will give you access to the appropriate data, for example a SAP BusinessObjects universe. Next, you use the Interactive Analysis Query Panel to add and organize the objects that interest you from the universe you selected. When you build a query in the Interactive Analysis Query panel, you select the universe objects and query filters that represent your business question. The query is sent to the Interactive Analysis server, if you are connected to the SAP BusinessObjects Business Intelligence platform CMS, which generates the final Structured Query Language (SQL) statements, the language used to query the database.
An Interactive Analysis document can contain multiple reports and each report may have been built using different queries. Once you run the query and retrieve the data that interests you, you can structure and organize the data as you wish in the Interactive Analysis document.
Figure 3: The Interactive Analysis Query Process
Reporting with Interactive Analysis You can use Interactive Analysis document features to create professional reports from the data you retrieve. Once you have the data you need, you can display or present it in multiple ways: • • •
As a table (horizontal, vertical, form or cross table) As a chart (bar, line, area, pie or radar) As a multiple-block report containing large amounts of data
Analyzing with Interactive Analysis You can analyze your reports and switch your business perspective by dragging and dropping objects, inserting calculations, and changing the display to make the important information easy to see at a glance. You can also perform multi-dimensional analysis by looking at results at a global level or drilling down to a more detailed level of information.
The SQL query is then sent to the database to retrieve the data mapped to the objects you selected. The database returns rows of data to the Interactive Analysis server, which populates the data as a microcube, called a data provider. This information is then formatted and displayed in an Interactive Analysis report as a simple table, or even a complex chart, ready for your analysis.
You should now be able to: • Explain how Interactive Analysis queries retrieve data from your corporate databases • Describe the different presentation styles from which you can choose to display the data in Interactive Analysis documents • Understand how Interactive Analysis allows you to analyze data at different levels of detail
Lesson: Describing Interactive Analysis and the SAP BusinessObjects Business Intelligence Platform
Lesson: Describing Interactive Analysis and the SAP BusinessObjects Business Intelligence Platform Lesson Overview Users can choose to access Interactive Analysis as a stand-alone application on their desktop or as a web application from within the SAP BusinessObjects Business Intelligence platform portal, BI launch pad. Interactive Analysis is part of the SAP BusinessObjects Business Intelligence platform so understanding the SAP BusinessObjects Business Intelligence platform and the function of Interactive Analysis within the platform is important.
Lesson Objectives After completing this lesson, you will be able to: • • •
Describe what the SAP BusinessObjects Business Intelligence platform is Explain the relationship among the SAP BusinessObjects Business Intelligence platform, Interactive Analysis and BI launch pad Describe the different methods available for sharing Interactive Analysis documents with your colleagues
Business Example You need an understanding of the SAP BusinessObjects Business Intelligence platform and how Interactive Analysis and the Business Intelligence launch pad work together.
SAP BusinessObjects Business Intelligence Platform Overview SAP BusinessObjects Business Intelligence platform is a business intelligence (BI) platform that combines end-user insight with flexible systems management for a single BI standard. This allows administrators to confidently deploy and standardize their BI implementations on a proven, scalable, and adaptive service-oriented architecture. SAP BusinessObjects Business Intelligence platform stores Interactive Analysis documents, as well as SAP Crystal reports, Desktop Intelligence documents, SAP BusinessObjects Dashboards models, Voyager workspaces, spreadsheets, and other documents. With SAP BusinessObjects Business Intelligence platform, users can access this information and organize it to suit their preferences.
BI launch pad collects and consolidates a company’s BI information and presents it in a secure, focused, and personalized view to users inside and outside an organization. BI launch pad lets users personalize how they view, manage, and distribute BI content. It is both a standalone BI portal (BIP), as well as a BI content provider for enterprise information portals (EIPs). Using BI launch pad, you can open existing Interactive Analysis documents and create new ones, using BI launch pad's Interactive Analysis report panels.
Sharing Documents with BI Launch Pad Users BI launch pad allows you to share your documents with colleagues. You can choose to save them as public documents, and if your colleagues are BI launch pad users, you can send your documents directly to them. The BusinessObjects Central Management Server (CMS) stores sent and public documents and enables others to retrieve them. If they do not use BI launch pad, you can save your reports in Microsoft Excel or Adobe PDF format, so that your colleagues can easily view and print them. Depending on how Interactive Analysis has been deployed in your organization, you can share Interactive Analysis documents in many ways: • • • • • •
Save documents as files on your workstation, in Interactive Analysis WID format, or in Adobe PDF, Microsoft Excel, or .CSV format. Send in an E-mail as an attachment, in Interactive Analysis WID, Adobe PDF, or Microsoft Excel format. Export documents to the CMS as a public or personal document. From within BI launch pad, send a document to another user or group of users. From within BI launch pad, save a document as a public or personal document in the CMS. From within BI launch pad, schedule documents to be refreshed and sent automatically by using the BI launch pad scheduler.
Depending on the rights assigned to you by your administrator you may be able to schedule and view a list of your scheduled documents. When you schedule a document you specify a time and a date to refresh it and send it to other users.
SAP BusinessObjects Business Intelligence platform comes with BI launch pad, a web desktop that acts as a window to a broad range of useful business information around your company.
BOW310
Lesson: Describing Interactive Analysis and the SAP BusinessObjects Business Intelligence Platform
You should now be able to: • Describe what the SAP BusinessObjects Business Intelligence platform is • Explain the relationship among the SAP BusinessObjects Business Intelligence platform, Interactive Analysis and BI launch pad • Describe the different methods available for sharing Interactive Analysis documents with your colleagues
You should now be able to: • Explain how Interactive Analysis allows you to access information • Describe universes, the SAP BusinessObjects semantic layer that allows you to query your corporate databases using your own everyday business terms • Explain how Interactive Analysis queries retrieve data from your corporate databases • Describe the different presentation styles from which you can choose to display the data in Interactive Analysis documents • Understand how Interactive Analysis allows you to analyze data at different levels of detail • Describe what the SAP BusinessObjects Business Intelligence platform is • Explain the relationship among the SAP BusinessObjects Business Intelligence platform, Interactive Analysis and BI launch pad • Describe the different methods available for sharing Interactive Analysis documents with your colleagues
BOW310
Test Your Knowledge
Test Your Knowledge True or False: Interactive Analysis is a single tool for understanding, controlling and sharing business information?
For internal use by CSC only
For internal use by CSC only
1.
Determine whether this statement is true or false.
□ □
2011
True False
2.
What is a universe?
3.
What is the core functionality of Interactive Analysis?
4.
Describe three ways for sharing Interactive Analysis documents with others.
Answers True or False: Interactive Analysis is a single tool for understanding, controlling and sharing business information? Answer: True True. 2.
What is a universe? Answer: A universe is the SAP BusinessObjects semantic layer that maps to data in a database and is used for creating queries in Interactive Analysis.
3.
What is the core functionality of Interactive Analysis? Answer: Querying, reporting, and analyzing data.
4.
Describe three ways for sharing Interactive Analysis documents with others. Answer: Exporting the document to the BusinessObjects CMS, sending to a user's Inbox from within BI launch pad, saving a document locally as an Interactive Analysis document (*.wid), as an Excel, or PDF file.
Creating Interactive Analysis Documents with Queries Unit Overview This unit describes how to use Interactive Analysis to create, edit and run queries. Use the Interactive Analysis Desktop application for all the exercises presented in this lesson.
Unit Objectives After completing this unit, you will be able to: • • • • • • • • • • • • • •
Describe the query process Describe universes, their role and components Describe the universes used in this course to retrieve data Log onto the Interactive Analysis Desktop application Create a new Interactive Analysis document Select the data source you want to use to retrieve data Build the query to retrieve the data that interests you Save the new Interactive Analysis document Add and remove objects from a query Project the data retrieved by an added object into the report block Describe the Query panel panes and toolbar buttons Describe the Universe outline options View and modify the query properties View, copy or edit the SQL statements that generate the query
Unit Contents Lesson: Querying with Interactive Analysis .................................. 19 Lesson: Creating a New Document ........................................... 23 Procedure: To Launch Interactive Analysis Desktop ................... 25 Procedure: To Choose a Universe for a New Query in Interactive Analysis Desktop ............................................................ 26
Procedure: To Build and Run a Query in Interactive Analysis Desktop ....................................................................... 27 Procedure: To Save a New Document Locally .......................... 28 Lesson: Modifying a Document's Query ...................................... 30 Procedure: To Edit the Query .............................................. 31 Procedure: To Project Data into a Table.................................. 32 Lesson: Working in the Query Panel .......................................... 34 Procedure: To View or Modify the Query Properties.................... 39 Procedure: To View a Query Script ....................................... 40
BOW310
Lesson: Querying with Interactive Analysis
Lesson: Querying with Interactive Analysis Lesson Overview For internal use by CSC only
Creating a document involves the following steps: • •
Choosing the appropriate data source Using the Interactive Analysis Query Panel to create a query that determines which data is extracted from the database
Lesson Objectives After completing this lesson, you will be able to: • • •
Describe the query process Describe universes, their role and components Describe the universes used in this course to retrieve data
Business Example This lesson presents an overview of the query process and key concepts to understand for using Interactive Analysis to query your organization's databases.
About the Query Process This section presents the query process from the business user's point of view. Using one of the Interactive Analysis query panels, you construct a business question, or query, that represents the information you need. The query is sent to the Interactive Analysis server. The Interactive Analysis server retrieves the data from the database and stores it in a microcube. The contents of the microcube are then formatted and displayed in an Interactive Analysis report in the form of tables, cross tables and charts for your analysis.
To retrieve new data from your corporate data sources with Interactive Analysis, you can create a new Interactive Analysis document or edit the query associated with an existing document.
BOW310
Figure 4: The Query Process from a Business User's Perspective
Universes and Objects Interactive Analysis makes it easy for you to access your corporate data by enabling you to work with familiar business terms rather than the technical SQL code required to retrieve data from a database. Interactive Analysis uses universes to achieve this. An SAP SAP BusinessObjects universe is the semantic layer that maps everyday terms that describe your business environment to data stored in the database. Using a universe, you can retrieve the data that interests you simply by dragging and dropping the desired objects. In your company or organization, universes are created by a universe designer, using Universe design tool. The designer then makes the universes available to you and other users in your organization so that you can select the appropriate business terms to create queries and retrieve data from the database. Within each universe, these business terms are listed as objects, while similar types of business terms are grouped into classes.
Objects Represent a Selection of Data Objects are elements in a SAP BusinessObjects universe that correspond to the data in the database. Ideally, the universe designer names the object with the business terms that you use in your everyday activity, such as Sales revenue, or Customer name. You use these objects to build queries and retrieve the data you want to show in your report.
Unit 2: Creating Interactive Analysis Documents with Queries
Lesson: Querying with Interactive Analysis
Figure 5: The Correspondence between Objects and Data
Universes Used in This Course The universe used in this training course, eFashion, is an example of a database for a company that runs a chain of retail stores. Universes are made up of classes and objects. Objects are elements that map to data in a relational database and are named for familiar business terms. For example, some of the objects in the eFashion Universe include State, City, and Store name. Classes are logical groupings of objects. There are three different types of objects: •
Dimension - Retrieves the data that provides the basis for analysis in a report. Dimension objects typically retrieve character-type data, for example, customer names, store names or dates. A blue, four-sided figure represents a Dimension in the Query Panel.
•
Detail - Provides descriptive data about a dimension. A detail is always attached to the dimension for which it provides additional information. For example, the Customer dimension could have Age and Address associated with it because they provide additional information about a Customer. A blue, four-sided figure with a green star represents a Detail in the Query Panel.
•
Measure - Retrieves numeric data that is the result of calculations on data in the database. For example, Revenue is the calculation of the number of items sold multiplied by the item price. Measure objects are often located in a Measures class. An orange rectangle represents a Measure in the Query Panel.
Universes can also include predefined query filters. A Query Filter restricts the information returned by objects, such as limiting data concerning revenue to a specific year. Filters can also prompt the person viewing the report to select a value, such as the query filter "Which product?" A yellow funnel represents a Query Filter in the Query Panel.
You should now be able to: • Describe the query process • Describe universes, their role and components • Describe the universes used in this course to retrieve data
2011
BOW310
Lesson: Creating a New Document
Lesson: Creating a New Document For internal use by CSC only
This lesson describes how to launch the Interactive Analysis Desktop application and how to create a new Interactive Analysis document.
Lesson Objectives After completing this lesson, you will be able to: • • • • •
Log onto the Interactive Analysis Desktop application Create a new Interactive Analysis document Select the data source you want to use to retrieve data Build the query to retrieve the data that interests you Save the new Interactive Analysis document
Business Example To design reports in Interactive Analysis, you need to know how to launch the application and build queries to retrieve the data your organization considers valuable.
Launching the Interactive Analysis Desktop Application The Interactive Analysis Desktop application is a locally installed Microsoft Windows application that lets you create and edit Interactive Analysis documents that are stored either locally on your desktop, or in the SAP BusinessObjects repository.
Unit 2: Creating Interactive Analysis Documents with Queries
BOW310
You can launch Interactive Analysis Desktop in one of three working modes: •
Connected mode:
For internal use by CSC only
In connected mode, user authentication may be handled by different security models: Enterprise, LDAP, Windows AD, Windows NT, or SAP. •
Offline mode: Interactive Analysis Desktop is not connected to the CMS repository, but applies CMS security. You can work with local documents and universes that are secured by the CMS you select at logon, or with unsecured local documents and universes. In Offline mode, you cannot import documents from or export documents to the CMS repository.
•
Standalone mode: Interactive Analysis Desktop is not connected to a CMS and no security is enforced. You can work with local, unsecured documents and universes only. You cannot import documents from or export documents to a CMS.
Interactive Analysis Desktop is connected to the SAP BusinessObjects Business Intelligence platform repository, also known as the Central Management Server, or CMS. You can work with documents from the BusinessObjects repository or with local documents.
BOW310
Lesson: Creating a New Document
1.
From the Start menu, launch Interactive Analysis Desktop, according to your instructor's directions.
2.
Click Interactive Analysis and select Login as from the drop-down menu.
3.
Select a CMS in the System list.
4.
Enter a valid user name and password.
5.
Select an authentication mode from the list. Do not choose Standalone if you want to work in Connected mode. When you choose Standalone authentication you work in Standalone mode, with no CMS connection.
6.
Make sure the Use in Offline Mode option is not selected.
7.
Click Log On. The Interactive Analysis Desktop main window displays. When Interactive Analysis Desktop is already running on your computer, launching it again opens a new instance of the application.
Creating a New Interactive Analysis Document Interactive Analysis Desktop consists of two separate panels: • •
The Query Panel. The Interactive Analysis Desktop main window.
You use these windows together to build queries and present the data returned by the query in a report. When you are ready to save, the following information is associated with the new Interactive Analysis document: • • • • •
The query definition. The data returned by the query. One or more reports. One or more blocks of data (tables, charts) presented in the report(s), and the formatting you have applied to the blocks. Local variables and conditional formatting rules defined within the document.
To create a new Interactive Analysis document, you: • • •
2011
Choose a data source for the query. Build and run a query. Save the new document.
Unit 2: Creating Interactive Analysis Documents with Queries
BOW310
To Choose a Universe for a New Query in Interactive Analysis Desktop For internal use by CSC only
In the initial window in the Interactive Analysis Desktop under Choose a data source to create a new document, click Universe. The Universe dialog box opens. If you have already created a document or are editing an existing document, you can create a new document at any time by clicking the Create a new document button from the Interactive Analysis Desktop menu bar.
2.
In the Universe dialog box, double-click the universe you want to use. The Query Panel opens, showing the universe's data in the Universe outline panel. You can now use the Query Panel to build queries in your document. To build the query, move the objects you want in your report from the Universe outline panel to the Result Objects panel.
In the Query Panel Universe outline panel, when required, click the + beside a Class to display the objects in the Class.
2.
Double-click each object you want to include in the query so they display in the Result Objects panel. To add all the objects in the class, drag the class to the Result Objects panel.
3.
Repeat the previous step until the query contains all the objects you want to include.
4.
Click Run query to run the query. The query is executed and the Query Panel closes. The data returned is displayed in a new document in the Interactive Analysis Desktop main window. The values returned by the objects you selected are presented by default in a vertical table.
Saving a New Document You can save a document that you have created with Interactive Analysis Desktop locally on your desktop or by exporting it to the CMS. By default, the Refresh on open option is not selected so that users always see the original data that was retrieved when the document was created or last refreshed, and which was stored in the document when it was saved. To see the most recent data available in the database, users can refresh the document manually when they open it. Or, you can choose to select this option to ensure that the data is updated automatically each time the document is opened. If you select the Permanent regional formatting check box, it overrides the user’s default viewing options and always display the document’s original regional setting for language and time stamping.
To Build and Run a Query in Interactive Analysis Desktop
Unit 2: Creating Interactive Analysis Documents with Queries
BOW310
1.
When you are ready to save the new document locally, click the Save button on the toolbar.
2.
Use the buttons to the right of the dialog box to navigate your file system and control display.
3.
In the Save In field, use the arrow to display and navigate to the folder where you want to save the document. The contents of the folder appear in the display box below the list.
4.
When you save the document as a Interactive Analysis document, type a description and keywords for the document as required.
5.
When you save the document as a Interactive Analysis document, choose options: Option
Description
Refresh on open
The document automatically refreshes when it is opened.
Permanent regional formatting
The current regional settings (locale) for formatting are applied regardless of what the locale is on the machine where it is opened.
Save for all users
All security information stored in the document is removed, making it accessible to all users and able to be opened in Standalone mode.
Remove document security
Default document security is removed.
6.
Type a file name.
7.
Select a file type. The file type that you select filters documents shown in the display box. You can save in three formats: • • •
Interactive Analysis document PDF document Excel document
When you save as a PDF or Excel document, you cannot enter a description or keywords and the Interactive Analysis Document options are unavailable. 8.
You should now be able to: • Log onto the Interactive Analysis Desktop application • Create a new Interactive Analysis document • Select the data source you want to use to retrieve data • Build the query to retrieve the data that interests you • Save the new Interactive Analysis document
29
Unit 2: Creating Interactive Analysis Documents with Queries
BOW310
Lesson: Modifying a Document's Query For internal use by CSC only
To change the query definition, you can edit the original query that you used to retrieve data and create the document.
Lesson Objectives After completing this lesson, you will be able to: • •
Add and remove objects from a query Project the data retrieved by an added object into the report block
Business Example To adapt to the changing needs of the report users, you need to know how to modify an existing query.
Projecting Data from an Added Object After you have created a document, you can easily change the information that is displayed in the resulting report by adding or removing objects in the underlying query. In the Data Access toolbox, click the Edit button to open the Query Panel. You can then add or remove objects and predefined query filters to change the data available in the document. Then, after you run the query and return to view the report in the Interactive Analysis Desktop main window, you can easily move the new objects from the Available Objects panel into the report block by clicking and dragging them onto the table or chart. This process is called “projecting data”.
In the Interactive Analysis Desktop main window, click the Data Access toolbox.
2.
Click the Edit button. The Query Panel displays. You can change the data retrieved by this query by adding or removing objects, changing filters, and by changing the order of the objects as they appear in the Result Objects panel.
3.
Click Run query to execute the query. The Query Panel closes. The new objects you added to the query now display in the Interactive Analysis Desktop main window in the Available Objects panel, but the values returned by those objects are not yet present in the report block.
Unit 2: Creating Interactive Analysis Documents with Queries
BOW310
1.
When the report is empty, select a single object or a class folder in the Available Objects panel, and then, drag and drop the object or class onto the report.
2.
When the report already contains tables or charts and you want the new data to display in a new table or chart, drag the new object or class to an empty area of the report. A new table header and body cell appears on the report. The table header displays the name of the object(s).
3.
To add another object to the table, drag another object from the Available Objects pane and place it to the left or right of a detail cell so that the left or right of the cell body is highlighted.
4.
Drop the object. A second column appears before or after the first column. The new column header displays the name of the object. Note: When dragging and dropping objects onto a block, do not drop the objects onto a column header or footer. The results do not display as expected. When the body of a cell displays as highlighted, then the object you drag there replaces the current column with the new object's data. Interactive Analysis displays the values in a vertical table. You can quickly turn the vertical table to a different table format, such as a cross table by using the Turn To feature.
5.
Save the document.
Understanding How the Data is Aggregated When you have a report that shows Sales revenue broken down by Year and Quarter, this is an example of a high-level aggregation. Add the Store name object to the table and Sales revenue is broken down even further. All the values in the table are re-aggregated to produce a figure that represents the sales revenue earned by each store this year. This is an example of low-level aggregation. These examples demonstrate the dynamic nature of measure objects - their values change depending on which dimension objects are used with them.
You should now be able to: • Add and remove objects from a query • Project the data retrieved by an added object into the report block
33
Unit 2: Creating Interactive Analysis Documents with Queries
BOW310
Lesson: Working in the Query Panel For internal use by CSC only
In this unit you explore the remaining features available in the Query Panel.
Lesson Objectives After completing this lesson, you will be able to: • • • •
Describe the Query panel panes and toolbar buttons Describe the Universe outline options View and modify the query properties View, copy or edit the SQL statements that generate the query
Business Example As a report designer, you need a solid understanding of the Query Panel, the information it holds and the options it allows you to define for your queries to use it efficiently in creating documents.
The Query Panel Structure The default display of the Query Panel consists of separate panels: • • • •
The Data Outline panel. The Result Objects panel. The Query Filters panel. The Data Preview panel.
The toolbar buttons for the panels are described in the following tables.
Edit Toolbar
34
Button
Description
Add Query
Allows you to add a new query to the document.
Data Outline Panel
Allows you to show or hide the data outline, which contains the Master Perspective and the Display by Navigation Path views.
Filters Panel
Allows you to show or hide the Query Filters panel.
Allows you to show or hide the Data Preview panel.
Scope of Analysis Panel
Allows you to show or hide the Scope of Analysis panel.
Add a combined query
Allows you to combine the data retrieved from more than one query using one of the following operators: • • •
minus union intersection
Query Properties
Allows you to display the query properties.
View Script
Allows you to view, copy and modify the SQL script used to generate the query.
Run query
Allows you to run the query you built and retrieve the data from the database. The results of the query are displayed in a report in the Interactive Analysis Desktop main window.
Close the query panel Allows you to close the Query Panel. The down arrow to the right of the button allows you to apply query changes and close, or to revert the query and close.
Result Objects Panel
2011
Button
Description
Add Quick Filter
Allows you to apply a filter on an object that is selected in the Result Objects panel.
Remove
Allows you to remove the selected object from the Result Objects panel.
Remove All
Allows you to remove all objects from the Result Objects panel.
Unit 2: Creating Interactive Analysis Documents with Queries
BOW310
Button
Description
Add a subquery
Allows you to run a subquery to restrict the data returned by the query.
Add a database ranking
Allows you to use the database ranking feature to retrieve only top or bottom values from the database.
Remove
Allows you to remove the selected object from the Query Filters panel.
Remove All
Allows you to remove all objects from the Query Filters panel.
Viewing the Query Properties In the Query Panel, you can view and modify the default query properties. The query properties allow you to: • • • • •
Limit the runtime for the query or the amount of data returned. Set security option. Specify the order of prompts in the report. Control potential ambiguous query results. Restrict the retrieval of duplicate data rows.
These query properties are available in the Query Properties dialog box. The sections you see in the Properties tab are described in the table below.
36
Section
Description
Name
By default, each query in the document is named successively, Query 1, Query 2, and so on. You can type a name here that describes the query.
Universe
This field shows the universe you chose to create your query. The button to the right of the Universe field allows you to select a new universe.
The Max rows retrieved setting can be overridden by the limits set by your administrator in your security profile. For example, if you set the Max rows retrieved setting to 400 rows, but your security profile limits you to 200 rows, only 200 rows of data will be retrieved when you run the query. Max retrieval time Maximum time that a query can run before the query is stopped. This can be useful when a query is taking too long due to an excess of data, or network problems. You can set a time limit so a query can stop within a reasonable time. Sample
This option allows you to retrieve a random sample of data with your query. The sample selection occurs in the database and is database-specific. In the Sample Result set field, define the number of values in the sample set. • •
Data
Select the Fixed option to retrieve the same values at each refresh. Clear the Fixed option to change the values at each refresh.
Retrieve duplicate rows This option is selected by default. In a database, the same data may be repeated over many rows. You can choose to have these repeated rows returned in a query, or to have only unique rows returned. Retrieve empty rows You can choose to include empty rows in query results.
Maximum number of rows of data that can be returned when a query is run. When you only need a certain amount of data, you can set this value to limit the number of rows of data that is returned to your document. This prevents a query from taking too much time or from returning unnecessary data to the document.
Unit 2: Creating Interactive Analysis Documents with Queries
Section
Description
Security
Allow other users to edit all queries
BOW310
For internal use by CSC only
When you clear this option, only the report creator can modify the query. Unlike the other query properties, which only apply to the selected query, this option applies to all of the data providers in the document. Prompt Order
When you have applied multiple prompted query filters, you can define the order of priority that they will appear to a user refreshing this document. Prompted query filters are presented in detail in the next lesson.
Contexts
In a few cases, your universe structure may require you to specify a context for your report. In these cases, you receive a prompt to select a context and your selection determines the dataset returned by your query. For example, a report on sales revenue for a car dealership can ask you to select a rental or purchase context. Depending on your selection, your query returns sales revenue generated by car rental or sales revenue generated by car sales. Your universe designer can advise you on how to select the appropriate context for the data that interests you. When your query requires the report user to specify a context, you can set the following parameters: • •
Select Reset contexts on refresh to prompt for a context at each refresh. Clear Reset contexts on refresh to retain the context from the previous refresh.
The Clear Contexts button allows you to remove the context set in a previous refresh.
This option is selected by default. In this case, other users who have the appropriate editing rights can edit the query and modify the data contained in the document.
BOW310
Lesson: Working in the Query Panel
1.
In the Query Panel toolbar, click the Query Properties button.
2.
Modify the query properties as required. In the query properties, you can place additional restrictions on the document relative to the restrictions that already exist on the universe, the Interactive Analysis server, and the database where the data was retrieved. You cannot override restrictions that may already be in place.
Viewing the Query's SQL When you build a query, Interactive Analysis automatically generates the appropriate SQL to retrieve the data from the database that you are accessing. You can view and edit this SQL, and even copy and paste it to another application.
You should now be able to: • Describe the Query panel panes and toolbar buttons • Describe the Universe outline options • View and modify the query properties • View, copy or edit the SQL statements that generate the query
You should now be able to: • Describe the query process • Describe universes, their role and components • Describe the universes used in this course to retrieve data • Log onto the Interactive Analysis Desktop application • Create a new Interactive Analysis document • Select the data source you want to use to retrieve data • Build the query to retrieve the data that interests you • Save the new Interactive Analysis document • Add and remove objects from a query • Project the data retrieved by an added object into the report block • Describe the Query panel panes and toolbar buttons • Describe the Universe outline options • View and modify the query properties • View, copy or edit the SQL statements that generate the query
2011
BOW310
Test Your Knowledge
1.
What is an SAP BusinessObjects universe?
2.
List the object types used in an SAP BusinessObjects universe and the kind of data returned by each object type.
3.
What are the advantages of using the Interactive Analysis Desktop application to create new documents?
4.
What is the name of the interface element in the Interactive Analysis Desktop application that allows you to build queries?
5.
What must you do before you can build a query? Choose the correct answer(s).
□ □ □ □
2011
A B C D
Select an object. Select a measure. Select a data source. Select a database.
Answers What is an SAP BusinessObjects universe? Answer: A universe is the SAP BusinessObjects semantic layer that maps objects and classes to data in a database, using everyday business terms. Interactive Analysis and other SAP BusinessObjects end-user querying tools use universes for creating queries. 2.
List the object types used in an SAP BusinessObjects universe and the kind of data returned by each object type. Answer: •
• •
3.
Dimension objects: Character-type data, dates and non-aggregated numeric data, for example, telephone numbers. Dimensions are the key elements of a query. Measure objects: Numeric- or fact-type data Detail objects: Character- or date-type data (supplementary information)
What are the advantages of using the Interactive Analysis Desktop application to create new documents? Answer: You can choose to work in three different operating modes: connected, offline, and standalone. You can choose to work with documents locally or to import and export them to and from the CMS.
4.
What is the name of the interface element in the Interactive Analysis Desktop application that allows you to build queries? Answer: The Query Panel.
5.
What must you do before you can build a query? Answer: C Select a data source on which to build your query.
6.
What determines the value displayed by a measure object in a report? Answer: The dimensions associated with the measure.
Restricting Data Returned by a Query Unit Overview This unit describes how to set up and group query filters. Query filters allow you to limit the data returned from the underlying database and displayed in your document.
Unit Objectives After completing this unit, you will be able to: • • • • • • • • • • • • • • • •
Describe the purpose of using query filters Describe the components of a query filter Describe the types of query filters Modify a query by applying a predefined query filter Create a single-value query filter Edit a single-value query filter Delete a query filter Explain how to use single- and multiple-character wildcards in query filters Create a query filter using a wildcard Describe how prompted filters allow each user to view different data every time the document is refresh Create a prompted query filter Edit a prompted query filter Describe logical operators and how you use them in filters Apply more than one filter using the AND operator Apply more than one filter using the OR operator Prioritize filters so that you are sure to retrieve the correct data
Unit Contents Lesson: Restricting Data with Query Filters .................................. 49 Lesson: Modifying a Query with a Predefined Query Filter ................ 55 Procedure: To Modify a Query with a Predefined Query Filter ........ 56 Lesson: Applying a Single-value Query Filter................................ 58
Procedure: To Create a Single-value Query Filter ...................... 59 Procedure: To edit a single-value query filter ............................ 60 Procedure: To delete a query filter ........................................ 61 Lesson: Using Wildcards in Query Filters .................................... 63 Procedure: To Apply Wildcards to Query Filters ........................ 65 Exercise 1: Restricting Data ............................................... 67 Lesson: Using Prompts to Restrict Data ...................................... 71 Procedure: To create a prompt in a report ............................... 74 Procedure: To Create a Prompt in a Report ............................. 75 Procedure: To Edit a Prompt Filter ........................................ 76 Exercise 2: Adding Prompts ............................................... 77 Lesson: Using Complex Filters ................................................ 81 Procedure: To create a report using the AND operator ................ 83 Procedure: To create a report using the OR operator .................. 84 Procedure: To define the priority between complex filters ............. 85 Exercise 3: Restricting Data with Multiple Filters........................ 87
BOW310
Lesson: Restricting Data with Query Filters
Lesson: Restricting Data with Query Filters Lesson Overview For internal use by CSC only
Lesson Objectives After completing this lesson, you will be able to: • • •
Describe the purpose of using query filters Describe the components of a query filter Describe the types of query filters
Business Example As a report designer you need to user query filters to provide report users with reports that present the specific data of value to them. Query filters help you to do this by removing extraneous data from a report.
The Purpose of Query Filters Query filters retrieve a subset of the available data, based on the definition of the filter. For example, you can apply a query filter on the Year dimension, to view only Sales revenue for a specific year. Restricting the query ensures that you retrieve only the data that interests you. This increases the usefulness of your reports. It minimizes the quantity of data returned and reduces the time required to create and refresh the document over the network. Using query filters has the following advantages: • • •
You retrieve and are able to focus on only the data you need to answer a specific business question. You hide data you do not want specific users to see when they access the document. You minimize the quantity of data returned to the document to optimize performance.
Some universes have predefined filters built into them by the universe designer. Other times, you will want to create your own query filters to limit the data. Users without rights to edit the query cannot modify the query filters you define. This ensures that the data saved with a document is appropriate for those who view or analyze the data within that document.
Restricting queries allows you to limit the amount of data that is retrieved from the data source and returned to your Interactive Analysis reports. To limit the data retrieved, you set up a query filter.
Unit 3: Restricting Data Returned by a Query
BOW310
Components of a Query Filter Filters are created in the Query Filters panel of the Interactive Analysis report panels. Query filters are made up of three parts: • •
•
Object - the object on which you want to filter data. Operator - the relationship between the object and operand. Common operators are: Equal to, Different from, Greater than, Greater than or equal to, Less than, and Less than or equal to. Operand - the object values to be used for filtering.
Every filter must include an object, an operator and an operand. These elements act together to specify what subset of the data you want to retrieve. In the example above, the filter specifies that the query must return data where the Financial Year is equal to FY03-04. Data concerning any other year will not be returned by the query. The following is a list of operators you can choose from: Operator
Retrieves Data
Equal to
Different from
Greater than
50
Example
equal to a value specified [Country] Equal to US retrieves data for the US country value only. different from a specified value
[Quarter] Different from Q4 retrieves data for all quarters except Q4.
greater than a specified value
[Customer Age] Greater than 60 retrieves data for customers over age 60.
Your administrator can prevent objects from being filtered. If you create filters on these objects, when you try to run the query a warning appears listing the filters that you cannot use. You need to remove these filters from the query in order to run the query.
BOW310
Lesson: Restricting Data with Query Filters
Operator
Retrieves Data
Less than
lower than the specified value
Less than or equal to lower or equal to specified value
Between
Not Between
In List
2011
[Revenue] Greater than or equal to 10000 retrieves data for revenue starting from $10000 and up. [Exam Grade] Less than 40 retrieves data for exam grades below 40. [Customer Age] Less than or equal to 30 retrieves data for customers age 30 or less.
between two specified values; also includes values specified
[Weeks] Between 25 and 36 retrieves data for weeks from week 25 to 36, including week 25 and week 36.
outside the range of the values specified
[Weeks] Not Between 25 and 36 retrieves data for all weeks of the year excluding week 25 through week 36. Week 25 and 36 are not included.
same as values specified
[Country] In list 'US;Japan;UK' retrieves data for countries US, Japan and UK.
Greater than or equal to greater than or equal to a specified value
Example
Unit 3: Restricting Data Returned by a Query
Operator
Is null
Is not null
Matches pattern
Retrieves Data
Example
different from multiple values specified
[Country] Not in List 'US;Japan;UK' retrieves data for all countries except US, Japan and UK.
for which there is no value entered in the database
[Children] Is null retrieves data for customers without children where Null is entered as a value for the database under the children column.
for which a value was entered in the database
[Children] Is not null retrieves data for customers with children where any value is entered into the database under the children column.
including a specific string
[Phone] Matches Pattern, '773' retrieves data for all phone numbers that have '773' in them. [Store name] Matches Pattern %Sundance retrieves data for all stores ending in Sundance.
Different from pattern doesn't include a specific [Phone] string Different from Pattern '773' retrieves all phone numbers that do not have '773' in them. [Store name] Different From Pattern
You should now be able to: • Describe the purpose of using query filters • Describe the components of a query filter • Describe the types of query filters
2011
Lesson: Modifying a Query with a Predefined Query Filter
Lesson: Modifying a Query with a Predefined Query Filter Lesson Overview A predefined query filter is an element in the universe that allows you to restrict the information returned by specific dimension, detail or measure objects. Predefined query filters are created by the universe designer and appear in the list of classes and objects in the Query Panel. A yellow funnel icon represents a predefined query filter.
Lesson Objectives After completing this lesson, you will be able to: •
Modify a query by applying a predefined query filter
Business Example Use predefined query filters to restrict the data retrieved in reports.
Using a Predefined Query Filter to Modify a Query In this section you learn to modify a query in the Query Panel by adding a predefined filter to the query. Like custom query filters that you define yourself, a predefined query filter allows you to limit the data returned by the query to specific values.
In the Interactive Analysis Desktop window, click Data Access>Data Providers>Edit. The Query Panel displays.
3.
From the Universe outline, double-click a predefined filter or drag it to the Query Filters panel.
4.
Click Run query on the Query Panel toolbar.. Interactive Analysis sends the SQL query to the database to retrieve the data you requested. When the data is returned, the Query Panel closes and the data corresponding to the query filters you selected displays in a block in the Interactive Analysis Desktop window. By default, the data displays in a table block.
Lesson: Applying a Single-value Query Filter For internal use by CSC only
Use a single-value query filter to limit the data returned by an object to one value.
Lesson Objectives After completing this lesson, you will be able to: • • •
Create a single-value query filter Edit a single-value query filter Delete a query filter
Business Example Define a single-value query filter to restrict the data in your report to only the data of value to the report users.
Creating a Single-value Query Filter When you add a single-value query filter to a query, you limit the data returned by a specific object to one single value. For example, you can find data for a certain store name by building a condition on the Store name dimension object, then selecting the name of the store you are interested in by selecting it from the dialog box that appears.
To Create a Single-value Query Filter In the Query Panel, select the object you want to filter and drag it to the Query Filters panel. The query filter appears in outline in the Query Filters panel. 2.
Use the default operator (In List), and click the arrow next to the bulleted list button to the far right of the filter definition. This is known as the Operand Type drop-down arrow. Select Value(s) from List from the list of options. The list of values for the selected object is retrieved from the database and displayed in the List of Values dialog box.
3.
In the List of Values dialog box, select the item you want to include in the filter and double-click it. An alternate way to add an item to the Select box is to select the item in the list of values and then click the>button. To remove an item from the Selected Value(s) box, select the item you want to remove and then either click the
4.
Click OK. The new filter appears in the Query Filters panel.
5.
Click Run query to generate a report based on your query.
Editing a Single-value Filter After you have run the query and reviewed the data displayed in the report, you may decide that you want to include or restrict more values for the object you used to create the query filter. You can make changes to the query filter by editing the query in the Query Panel.
From the document, click Data Access>Data Providers>Edit.
2.
In the Query Filters panel, click the Operand Type drop-down arrow and select Value(s) from List from the list of options. The List of Values dialog box displays.
3.
Optionally, click the Refresh values button in the List of Values dialog box to update the display of available values in the list.
4.
From the list of values, double-click the item you would like to add to the filter. To remove an item from the Selected Value(s) box, select the item and click the
5.
Click OK. The query filter appears in the Query Filters panel showing the filter's object, operator and operand.
You should now be able to: • Create a single-value query filter • Edit a single-value query filter • Delete a query filter
2011
BOW310
Lesson: Using Wildcards in Query Filters
Lesson: Using Wildcards in Query Filters For internal use by CSC only
You can use wildcards in query filters to search for partial values rather than the entire value. For example, you can retrieve “all product categories that begin with B.” To do this, you need to use wildcards as placeholders for partial pattern searches.
Lesson Objectives After completing this lesson, you will be able to: • •
Explain how to use single- and multiple-character wildcards in query filters Create a query filter using a wildcard
Business Example Using wildcards in query filters allows you to restrict data to values that contain a certain character-pattern. Your understanding of wildcards gives you greater flexibility in specifying the criteria for the data that displays when you use query filters.
About Wildcards Wildcards are special characters that denote any single character or any number of characters. In Interactive Analysis, use the following wildcards: • •
_ for any single character % for any number of characters
Understanding the Single-character Wildcard To look for the product categories of either SHIRTS or SKIRTS, use the pattern S_IRTS. This pattern specifies that the values retrieved must: • •
Be six characters long Have S as the first character and IRTS as the final four
The _ means that there can be a single unspecified character between the S and the IRTS.
Understanding the Multiple-character Wildcard To look for any product category that begins with the letter B, use the pattern B%. This pattern specifies that the values retrieved must have B as the first character.
You can use wildcards as placeholders for partial pattern searches. When you create the query filter using a wildcard, you must use one of the following relational operators:
BOW310
Lesson: Using Wildcards in Query Filters
1.
In the Query Panel, drag the object you want to use to build a filter into the Query Filters panel.
2.
Click the Operator Type drop-down arrow, and select Matches pattern from the drop-down menu.
3.
Click the filter definition text box and type the wildcard pattern you want to use.
4.
Click Run query. The report displays data reflecting the wildcard you entered.
Exercise 1: Restricting Data Exercise Objectives For internal use by CSC only
Business Example The eFashion store managers want to focus on the sales revenue performance of the eFashion stores that they manage personally and not be distracted by extraneous data from the stores their colleagues manage. Create a report using query filters to present only the data that interests them.
Task: Create a report to show sales revenue by year, quarter and store name and add a query filter to display the data for specific stores. 1.
Create a new document with a report showing Sales Revenue by Year, Quarter and Store name.
2.
Create a query filter to show data for the store eFashion Dallas.
3.
Run the query to view your results.
4.
Replace the filter to display figures only for eFashion New York Magnolia and eFashion New York Sundance. You need to change the filter operator. The Equal to operator can only return a single value.
2011
5.
Run the query to view your results.
6.
Save the document as Activity Filters 1 and close the document.
After completing this exercise, you will be able to: • Create a query filter to limit the data returned by objects in a query • Edit the query filter to change the data returned by objects in a query
Unit 3: Restricting Data Returned by a Query
BOW310
Solution 1: Restricting Data Task: For internal use by CSC only
1.
2.
3.
Create a new document with a report showing Sales Revenue by Year, Quarter and Store name. a)
From the initial Interactive Analysis Desktop screen, in the Choose a data source to create a new document area, under Universe, click eFashion.
b)
Under the Time period class, double-click Year and Quarter so they appear in the Result Objects panel. You can also drag the objects to the Result Objects panel.
c)
Extend the Store class to display the objects that it contains.
d)
Double-click Store name so it appears in the Result Objects panel. You can also drag the object to the Result Objects panel.
e)
Extend the Measures class to display the measures that it contains.
f)
Double-click Sales revenue so it appears in the Result Objects panel. You can also drag the measure to the Result Objects panel.
Create a query filter to show data for the store eFashion Dallas. a)
When the Query Filters panel does not display, in the Query Panel toolbar, click the Filters Panel button.
b)
From the Universe outline, drag the Store name object to the Query Filters panel. You can also drag the object from the or from the Result Objects panel.
c)
In the query filter, click the arrow to the right of the operator field and select Equal to.
d)
Click the button to the right of the operands field.
e)
In the drop-down menu, click Value(s) from list.
f)
In the List of Values dialog box, double-click eFashion Dallas.
g)
Click OK.
Run the query to view your results. a)
4.
Click Run query.
Replace the filter to display figures only for eFashion New York Magnolia and eFashion New York Sundance. Continued on next page
You should now be able to: • Explain how to use single- and multiple-character wildcards in query filters • Create a query filter using a wildcard
BOW310
Lesson: Using Prompts to Restrict Data
Lesson: Using Prompts to Restrict Data For internal use by CSC only
A prompt is a query filter that requires the report user to select the data values that the query returns at each refresh. You can design a prompt to allow users to manually enter data or to select data from a list of values.
Lesson Objectives After completing this lesson, you will be able to: • • •
Describe how prompted filters allow each user to view different data every time the document is refresh Create a prompted query filter Edit a prompted query filter
Business Example A prompted query filter empowers the report users to determine the data they want to view at each document refresh and flexibly adapt the report according to their needs at a given moment. When you create a report with a prompted query you can satisfy multiple needs in a singe report.
Prompted Filters The prompt dialog box allows you to specify the data to retrieve and display in the report. Consequently, you can focus on a specific part of the information available. Prompted filters allow multiple users viewing a single document to specify a different subset of the data and display it in the same tables and charts in the report. You can use a prompted filter to define a question that is displayed whenever the data in the document is refreshed. Users can answer the prompt either by typing or by selecting values. For example, you can use the prompted query filter to produce a report that shows sales revenue per state and prompts the user to select a product category at each refresh. This way, you do not restrict the data displayed to a single product category, such as Jewelry. Instead, the users select the product category that interests them at that moment.
Prompts can be defined on any dimension, measure or detail object listed in the Universe outline in the Query Panel.
For internal use by CSC only
Creating a Prompted Query Filter To create a prompted query filter, you use the Prompt operand. After you select the Prompt operand, click the Parameter Properties button between the Prompt text field and the Operand type button to open the Parameter Properties dialog box, where you can define query filter settings. The Parameter Properties dialog box settings are described in this table:
72
Option
Description
Prompt text
Type the text that you want to prompt the user to select a value. This text appears each time the document is refreshed.
Prompt with List of Values
Select this option to display all the values for the object and allow the user to select from those values.
Select only from list
Select this option to prevent users from typing values that do not exist in the database.
Keep last values selected
Select this option to display by default the value selected at the previous refresh. The user can change the value or refresh using the previously selected value.
Optional prompt
Select this option to allow users to refresh the document without responding to the prompt or by responding only partially to the prompt.
Note: You cannot use the operators Is Null and Not Null when creating prompted filters.
BOW310
Lesson: Using Prompts to Restrict Data
Option
Description
For internal use by CSC only
Set default values
Select this option to define the default value(s) that appear in the prompt. Define the default value(s) using one of the following methods: •
•
In the Type a value field, type the value(s). Use the arrow to move the value(s) to the field on the right. Click the Values button to choose the value(s) from the list of values present in the database.
Some important points about prompted query filters: •
•
2011
When the document contains multiple data providers, and there is already a prompt that includes (1) objects with the same data type, (2) operators of the same operator type, and (3) the same prompt text as the new prompt, Interactive Analysis displays a warning to tell you that the two prompts will be merged. This means that whenever all the data providers are refreshed, a single prompt message will appear for the two prompts. When you make a prompt optional, the prompt dialog box displays text to inform report users that when they do not provide a value for the prompt, the filter does not apply.
When the user does not respond to the prompt, the query filter is not applied to the data.
Unit 3: Restricting Data Returned by a Query
BOW310
To create a prompt in a report In the Query Panel, drag the object on which you want to apply a prompt to the Query Filters panel. The query filter appears in outline in the Query Filters panel. 2.
Click the Operand Type drop-down arrow and select Prompt from the menu.
3.
Click the Parameter Properties button between the Prompt text field and the Operand type button to open the Parameter Properties dialog box, where you can define query filter settings.
4.
Click OK to confirm the prompt.
5.
Click Run query. The Prompts dialog box opens.
6.
Select the value by which you want to filter the report, and move it to the Selected Values area.
7.
Click Run query. The document displays, reflecting the prompt value you selected.
8.
Save the document.
Prompts Dialog Box The options you define for the prompt determine how the Prompts dialog box displays to the report consumers and how they can specify the values for their reports in the Prompts dialog box. The dialog box displays all the prompts for the report in the Prompts Summary. The report consumer clicks the prompt to display the prompt and define the corresponding values. The Prompts Summary also indicates when a prompt is optional. When the Prompts dialog box displays a list of values from which the report users can select the values for the report, it also displays the last refresh date and time for the list of values. With this information, report users can determine when to refresh the list of values.
To Create a Prompt in a Report In the Query Panel, drag the object on which you want to apply a prompt to the Query Filters panel. The query filter appears in outline in the Query Filters panel. 2.
Click the Operand Type drop-down arrow and select Prompt from the menu.
3.
Click the Parameter Properties button between the Prompt text field and the Operand type button to open the Parameter Properties dialog box, where you can define query filter settings.
4.
Click OK to confirm the prompt.
5.
Click Run query. The Prompts dialog box opens.
6.
Select the value by which you want to filter the report, and move it to the Selected Values area.
7.
Click Run query. The document displays, reflecting the prompt value you selected.
8.
Save the document.
Editing a Prompted Query Filter After you have run the query and reviewed the data displayed in the report, you may decide that you want to change the prompt to include or restrict more values for the object you used to create the query filter. You can make changes to the prompted query filter by editing the query in the Query Panel.
To Edit a Prompt Filter In the Query Panel, in the Query Filters panel, click the Operator drop-down arrow to display the operators, and select the appropriate operator from the list. When you modify the value for the operator, you lose the operand for the filter. 2.
Click the Operand Type drop-down arrow and select Prompt from the menu.
3.
Optionally, to edit the prompt properties, click the Parameter Properties button next to the Prompt text field.
4.
Optionally, to edit the prompt text, type new text in the Prompt text field.
5.
Optionally, to set default prompt values, click Set default values. You can type default values into the Type a Value field and move them into the selected values box by pressing the>button, or you can click Values to open the List of Values dialog box, where you can select default values from the list of values. After selecting default values in the List of Values dialog box, click OK to close the List of Values dialog box and return to the Parameter Properties box.
6.
Optionally, select or deselect other Parameter Properties as appropriate.
7.
Click OK.
8.
Click Run query to display the new Prompts dialog box. The value you specified is presented by default in the Prompt zone. You can accept the default value or specify another value.
9.
Click Run query.
10. Save the document. The next time it is opened the report will display data concerning the value you selected as the default. When the user decides to refresh the data, the prompt dialog box allows the user to select another value.
Exercise 2: Adding Prompts Exercise Objectives For internal use by CSC only
Business Example Report consumers want to be able to specify for which store(s) a report returns sales data when they refresh the report. You create a report with a prompt and define the appropriate prompt parameters to satisfy the report consumers’ requirements.
Task: Create a report to show quantity sold by state, store name and Year with a prompt to allow the report users to specify the state. Use the parameter properties to define the prompt display and function.
2011
1.
Create a new document to retrieve Quantity sold by State, Store name, and Year.
2.
Add a prompted filter to the query to prompt report users to select the State each time they run the query or refresh the document.
3.
Run the query to view your results selecting Florida as the State.
4.
Change the prompted filter parameters so that the document does not keep the last value selected in the prompt, only allows report consumers to specify values by selecting them from the list in the prompt, allows report consumers to run the query without specifying values in the prompt
5.
Run the query without providing state values to show the results.
After completing this exercise, you will be able to: • Limit the data returned by objects in a query by applying prompts to a filter • Create a prompt filter to limit the data returned by objects in a query • Edit the prompt filter parameters
Unit 3: Restricting Data Returned by a Query
BOW310
Solution 2: Adding Prompts Task: For internal use by CSC only
1.
2.
3.
Create a new document to retrieve Quantity sold by State, Store name, and Year. a)
From the initial Interactive Analysis Desktop screen, in the Choose a data source to create a new document area, under Universe, click eFashion.
b)
Extend the Store class to display the objects that it contains.
c)
Double-click State and Store name so they appear in the Result Objects panel. You can also drag the objects to the Result Objects panel.
d)
Under the Time period class double-click Year so it appears in the Result Objects panel. You can also drag the object to the Result Objects panel.
e)
Extend the Measures class to display the measures that it contains.
f)
Double-click Quantity sold so it appears in the Result Objects panel. You can also drag the measure to the Result Objects panel.
Add a prompted filter to the query to prompt report users to select the State each time they run the query or refresh the document. a)
When the Query Filters Panel does not display, in the Query Panel toolbar, click the Filters Panel button.
b)
From the Universe outline, drag State to the Query Filters panel. You can also drag the object from the Result Objects panel.
c)
In the query filter, click the Operand type button and, from the drop-down menu, select Prompt.
Run the query to view your results selecting Florida as the State. a)
Create a report to show quantity sold by state, store name and Year with a prompt to allow the report users to specify the state. Use the parameter properties to define the prompt display and function.
BOW310
Lesson: Using Prompts to Restrict Data
5.
Change the prompted filter parameters so that the document does not keep the last value selected in the prompt, only allows report consumers to specify values by selecting them from the list in the prompt, allows report consumers to run the query without specifying values in the prompt a)
Click the Data Access toolbox.
b)
On the Data Providers tab, click Edit.
c)
In the Query Panel, in the Query Filters panel click the Parameter Properties button to the right of the prompt text.
d)
In the Parameter Properties dialog box, deselect Keep last values selected and select both Select only from list and Prompt optional.
e)
Click OK.
For internal use by CSC only
For internal use by CSC only
4.
Run the query without providing state values to show the results. a)
Click Run query.
b)
Click OK. Notice that the query retrieves the data for all states.
You should now be able to: • Describe how prompted filters allow each user to view different data every time the document is refresh • Create a prompted query filter • Edit a prompted query filter
BOW310
Lesson: Using Complex Filters
Lesson: Using Complex Filters For internal use by CSC only
To further refine the information returned to a document, you can apply more than one filter to a query. first you need to create the query filters and then determine how to apply them to the query:
Lesson Objectives After completing this lesson, you will be able to: • • • •
Describe logical operators and how you use them in filters Apply more than one filter using the AND operator Apply more than one filter using the OR operator Prioritize filters so that you are sure to retrieve the correct data
Business Example Complex query filters allow you to use multiple criteria to restrict the data that appears in your report and satisfy the specific needs of the report users.
Using Logical Operators for Multiple Conditions To produce a report that focuses more precisely on certain data, you may need to apply more than one filter. When you specify more than one filter in a query, the relationship between the filters must use either the AND or OR operator. These are known as logical operators. • •
The AND operator is used when both conditions defined in the two filters must be met for a row to be returned from the database when you run a query. The OR OR operator is used when either one or the other of the conditions defined in the filters must be met for a row to be returned from the database when you run a query.
The result of combining two or more filters can be visually represented using a Venn diagram.
The two circles each represent the number of database rows that are returned based on the conditions defined by a particular filter. Note that the two overlap. This area represents the rows that meet the conditions defined by both filters. As shown in the diagram, using the AND logical operator results in only rows represented by Area C being included in a report. On the other hand, if you use the OR logical operator, all rows represented by Circles A and B will be returned. The following sections demonstrate these points.
Using the AND Operator When you add a second filter to a query, Interactive Analysis automatically places either an AND operator between the two conditions. You saw this behavior when you added the predefined filter to your query in the last practice activity. However, when your query had a second filter that you removed from the query and you add a new second filter, by default, Interactive Analysis applies the logical operator that was last used. You can switch the operator value between AND and OR by double-clicking it. or an OR operator
Create a new query and drag the required objects to the Result Objects panel.
2.
Select an object on which you want to filter the query, and drag it to the Query Filters panel.
3.
In the operator drop-down list, select the appropriate operator for the filter.
4.
Click the Operand Type drop-down arrow, and click Value(s) from list. The values available for the object are displayed in the List of Values dialog box.
5.
Select the appropriate values from the list and move them to the Selected Value(s) zone.
6.
Click OK. The Query Filters panel displays the filter you just defined.
7.
Create a second query filter by dragging another object to the Query Filters panel.
8.
From the Operator drop-down list, select the appropriate operator.
9.
Click the Operand Type drop-down arrow, and click Value(s) from list. The values available for the object are displayed in the List of Values dialog box.
10. Select the appropriate values from the list. 11. Click OK. Interactive Analysis automatically applies the AND operator the first time you create a second filter. The OR operator appears when there was previously a second filter separated by the OR operator. Click the operator to change it to AND . 12. Click Run query. 13. Save the document.
Using the OR operator When you use the AND operator to group two query filters, your report returns data only if the conditions of both query filters are met. When you use the OR operator to group two query filters, your report returns data if the conditions of either of the query filters are met. For this reason, the OR operator returns more data than the AND operator. By double-clicking an operator, you can switch its value between AND and OR.
Create a new query and drag the required objects to the Result Objects panel.
2.
Select the objects on which you want to build the filters and drag them to the Query Filters panel. Note: The query must use at least two query filters to use the OR operator.
3.
From the Operator drop-down list, select the appropriate operators for each of the query filters.
4.
Select the appropriate Operands and values for the query filters.
5.
Group the query filters as required.
6.
When required, click the operators grouping the query filters to switch between the operator values AND and OR.
7.
Click Run query. The report displays according to the query filter grouping you created.
8.
Save the document.
Prioritizing Operators You are applying a complex filter when you combine several filters in a single query. To ensure that the filters retrieve exactly the data you want, you need to prioritize the operators. In the Query Filters panel, by positioning the query filter statements and deciding how to group them together, you define which filters will be processed first. The positioning and grouping of the filters depends on the logic of the information you are trying to retrieve.
In the Query Filters panel, drag and drop the query filters in the order you want them to display in your report. Drag a query filter on top of another one to group the filters together. Double-click the operator names to switch them between AND and OR to create the desired priority. The grouping of the query filters, represented by the brackets, determines which query filters will be processed first:
3.
Click Run query. The report displays according to the filter grouping you created.
Exercise 3: Restricting Data with Multiple Filters For internal use by CSC only
After completing this exercise, you will be able to: • Create multiple filters to limit the data returned by objects in a query • Apply the appropriate operator for the multiple filters
Business Example Report consumers want to view the sales data for the eStores where margin is low and profit is relatively high. You create a report with multiple query filters to satisfy their criteria.
Task: Use multiple filters in a query to retrieve data for stores where margin below $1,000,000 and sales revenue is above $2,000,000. 1.
Create a new document showing Store name and Sales revenue where Margin is less than $1,000,000. Note: Do not use commas in the numerical notation when creating the filter.
2.
Run the query to show the results.
3.
Edit the query and add a query filter to show only store names that also have a sales revenue of more than $2,000,000. Note: Apply the appropriate operator (AND/OR) to retrieve the data that meets your criteria.
2011
4.
Run the query to show the results.
5.
Save the report as Activity Filters 3 and close the document.
Solution 3: Restricting Data with Multiple Filters For internal use by CSC only
Use multiple filters in a query to retrieve data for stores where margin below $1,000,000 and sales revenue is above $2,000,000. 1.
Create a new document showing Store name and Sales revenue where Margin is less than $1,000,000. Note: Do not use commas in the numerical notation when creating the filter.
2.
a)
From the initial Interactive Analysis Desktop screen, in the Choose a data source to create a new document area, under Universe, click eFashion.
b)
Extend the Store class to display the objects that it contains.
c)
Double-click Store name so it appears in the Result Objects panel. You can also drag the object to the Result Objects panel.
d)
Extend the Measures class to display the measures that it contains.
e)
Double-click Quantity sold so it appears in the Result Objects panel. You can also drag the measure to the Result Objects panel.
f)
Double-click Margin so it appears in the Result Objects panel. You can also drag the measure to the Result Objects panel.
g)
From the Universe outline, drag the Margin measure to the Query Filters panel. You can also drag the measure from the Result Objects panel.
h)
In the query filter, select the operator Less than.
You should now be able to: • Describe logical operators and how you use them in filters • Apply more than one filter using the AND operator • Apply more than one filter using the OR operator • Prioritize filters so that you are sure to retrieve the correct data
You should now be able to: • Describe the purpose of using query filters • Describe the components of a query filter • Describe the types of query filters • Modify a query by applying a predefined query filter • Create a single-value query filter • Edit a single-value query filter • Delete a query filter • Explain how to use single- and multiple-character wildcards in query filters • Create a query filter using a wildcard • Describe how prompted filters allow each user to view different data every time the document is refresh • Create a prompted query filter • Edit a prompted query filter • Describe logical operators and how you use them in filters • Apply more than one filter using the AND operator • Apply more than one filter using the OR operator • Prioritize filters so that you are sure to retrieve the correct data
Answers What are some advantages of using query filters to restrict data in queries? Answer: You limit the amount of data retrieved by the database and, consequently, can reduce both processing and refresh time. You can specify precisely the data that interests you. 2.
Which of the following are components of a query filter? Answer: A, B, D Operator, Object and Operand
3.
What types of query filters can you apply to a query? Answer: A, C, E Complex, Single-value and Pre-defined are query filter types.
4.
Which logical operators must you use when you apply more than one filter? Answer: Queries that contain multiple filters use logical operators (AND/OR) to combine the filters and to create specific querying scenarios.
Designing Interactive Analysis Reports Unit Overview In this unit, you see how to present the information that you have learned to retrieve in building the queries for your reports.
Unit Objectives After completing this unit, you will be able to: • • • • • • • • • • • • • • • • • • • •
2011
Describe the components of an Interactive Analysis document Describe the different toolboxes, tabs, and toolbars available in the Interactive Analysis Desktop Customize your user settings Display the Document Properties and describe the options Insert, delete, duplicate and move reports within an Interactive Analysis document Describe the table types available in Interactive Analysis Create vertical, horizontal, cross and form tables Create a table by selecting a template Change table types Add data into an existing table Duplicate an existing table Delete tables, rows or columns Insert a blank cell in a report Display the date that the document was last refreshed Display text to help report consumers view key data Format and align the new cells in the report Describe the chart types available in Web Intelligence Create a chart Create a chart from a table Feed a chart
Lesson: Working with Interactive Analysis Documents ..................... 97 Procedure: To Change Application Modes ..............................101 Procedure: To Set User Preferences in Interactive Analysis Desktop ......................................................................105 Procedure: To Change Your Password ..................................106 Procedure: To Change the Left Panel Display..........................109 Procedure: To view and modify document properties ................. 112 Procedure: To Add a Report to a Document ............................ 113 Lesson: Displaying Data in Tables............................................ 115 Procedure: To Create a Default Vertical Table.......................... 118 Procedure: To Create a Table by Selecting a Template ............... 119 Lesson: Working with Tables ..................................................121 Procedure: To Change a Table Format ..................................122 Procedure: To Add Data to an Existing Table...........................123 Procedure: To Duplicate a Table .........................................124 Procedure: To Delete Table Rows or Columns .........................125 Lesson: Presenting Data in Free-standing Cells ...........................127 Procedure: To Insert a Free-standing Cell ..............................128 Procedure: To Change the Default Number Format ...................129 Procedure: To Insert Text to Label the New Cell .......................130 Procedure: To Modify the Default Text style in Multiple Cells Simultaneously..............................................................131 Lesson: Presenting Data in Charts ...........................................133 Procedure: To Create a Chart ............................................136 Procedure: To Create a Chart from a Table.............................137 Procedure: To Change a Chart into a Table ............................138 Exercise 4: Designing Interactive Analysis Reports ...................139
BOW310
Lesson: Working with Interactive Analysis Documents
Lesson: Working with Interactive Analysis Documents Lesson Overview For internal use by CSC only
The Interactive Analysis Desktop main window offers three different application modes which grant you access to specific features. Your license type and your security profile together determine the features to which you have access.
Lesson Objectives After completing this lesson, you will be able to: • • • • •
Describe the components of an Interactive Analysis document Describe the different toolboxes, tabs, and toolbars available in the Interactive Analysis Desktop Customize your user settings Display the Document Properties and describe the options Insert, delete, duplicate and move reports within an Interactive Analysis document
Business Example The Interactive Analysis Desktop main window allows you to analyze the data retrieved in your query and to present the data in a way that calls attention to the key information that the report users want to consult. A good understanding of the various features within the interface allows you to make the most efficient use of them.
Components of an Interactive Analysis Document A Interactive Analysis document consists of: •
One or more reports One document may contain several reports, organized as tabs along the bottom of the window. Reports contain blocks of formatted data as well as text and graphics. You specify the layout when you create a report, and you can also change the layout of existing reports.
•
One or more blocks A block is a table, cross table, form, or chart that displays information in the report.
•
2011
The microcube of data returned by the query, or other data provider
This lesson introduces you to the Interactive Analysis Desktop main window. This interface allows you to view, edit, structure and analyze the information returned by the queries that you build in the Query Panel.
Unit 4: Designing Interactive Analysis Reports
BOW310
The reports display in the Report Panel area of the Interactive Analysis Desktop application main window. The data that you can use in your reports displays in the Left Panel.
Viewing Reports in a Document Each report in a document can show different views of the same information, or it can show completely different information concerning the same subject.
About the Interactive Analysis Desktop Reporting Interface As you view an Interactive Analysis document, the Interactive Analysis Desktop main window allows you to interact with and organize the data returned by queries through: • •
The Left Panel that provides different views of the current document. The toolboxes, toolbars and buttons that allow you to control the data that displays in the Report Panel. The toolboxes, tabs and toolbars for interacting with the data in the report that are available to you depend on the application mode you use to work in the Interactive Analysis Desktop application.
•
When you right-click the report elements in the Report Panel, the contextual menu also gives you quick access to the functions available for the element in the application mode you use to work in the Interactive Analysis Desktop application.
As a report designer, you work principally in the Design mode. However, the report users may work principally in the Reading mode so you need to be familiar with the features available in the other modes. The table below provides a description of the features available in the different application modes. The description corresponds to the standard delivery of the Interactive Analysis Desktop. The security profile that your system administrator has defined for you can further restrict your access.
The microcube contains the actual data that was retrieved from your corporate database. The microcube is the structure in which the retrieved data is stored in the document. It contains all the data that you can display in the report(s) inside the document.
BOW310
Lesson: Working with Interactive Analysis Documents
Application Mode
In Data mode you can create, edit and manage queries that supply data to reports. Data mode provides access to the Data Access toolbox with features that allow you to: • • • •
Create a new document Edit, purge and refresh an existing document Create a new variable Merge objects from different data sources
Data mode also provides you access to: •
• Reading
The File tab with the main toolbar where you can open, save, print and search a document and send it as an attachment by E-mail. The Properties tab where you can set document and application properties.
In Reading mode, you can view and perform some analysis tasks on reports. When you open an existing report, it displays in Reading mode. Reading mode provides you access to the main toolbar that allows you to: • • • • • • •
Design
Create a new document Open, save, print, search a document Send a document as an E-mail attachment Edit a document with copy, cut, paste, undo and redo buttons Refresh a document Activate data change tracking, drill up or down in the data in a report designed for drilling Show or hide report filters and outlines
In Design mode, you have access to the features available in Data and Reading modes and additional analysis and formatting features. Design mode provides you access to the following toolboxes and their associated functions:
Report Element: Allows you to define the tables, charts, cells and sections in which your data displays, the colors, position and alignment of the report elements and the hyperlinks they contain. Format: Allows you to define formatting options including font, color, borders, text alignment, padding and background images. Data Access: Allows you to create a new document, edit, purge and refresh an existing document, create a new variable and merge objects from different data sources. Analysis: Allows you to perform extensive analysis tasks. You can apply report filters, ranking, input controls, data tracking, breaks, sorts, conditional formatting rules, drill up or down on data when drilling is enabled, apply calculations and show or hide outlines.
•
Page Setup: Allows you to add, delete, rename, duplicate and move reports, define page orientation, headers, footers and margins and scale a report to a number of printed pages,
Design mode also provides you access to: •
•
The File tab with the main toolbar that allows you to open, save, print and search a document and send it as an attachment by E-mail. The Properties tab that allows you to set document and application properties.
Lesson: Working with Interactive Analysis Documents
To Change Application Modes From the Interactive Analysis Desktop main window, depending on the current mode, follow the appropriate instructions to change the application mode: When you are Do the following to change to a new mode: in: Reading Click the arrow button pointing to the right >> in the mode upper right-hand corner and, from the menu, select the application mode to which you want to change. Data or Design mode
Click the application mode button in the upper right-hand corner for the mode in which you want to work.
Customizing Your User Preferences You can personalize the following settings of the Interactive Analysis Desktop application in the User Preferences dialog box. The dialog box organizes the preferences on different tabs: • • • • •
General: General preferences Viewing: Document viewing preferences Locale: Preferences for interface and formatting locales and how to use them Drill: Document drill preferences Proxy: Proxy settings for hosts and ports
Interactive Analysis Desktop General Preferences This section describes the general user preferences that you can define for your work in the Interactive Analysis Desktop application.
Select Default Universe When you define a default universe, the Universe dialog box displays with the default universe selected each time you create a new document on a universe. You can use the default universe or select another one. •
•
2011
No default universe means that no universe in the list is preselected when you open the Universe dialog box. You must select a universe from the list when creating a document. Select a universe.... option allows you browse for a default universe. After you define the default universe, its name displays here.
When Interactive Analysis Desktop is downloaded and installed from BI launch pad, help files are not installed locally. However, they are installed on the BI launch pad server. Contact your company's SAP Business Objects administrator to get the URL of the help files on the server, then enter the URL here for help to be available.
Select Microsoft Excel Format In this section you choose the format to use when you save a document in Microsoft Excel format. • •
Prioritize easy data processing in the Excel document: the Excel document will be formatted to ensure efficient data processing. Prioritize the format of reports in the Excel document: the Excel document will be formatted to ensure optimum readability.
Interactive Analysis Desktop Viewing Preferences This section describes the viewing user preferences that you can define for your work in the Interactive Analysis Desktop application.
Measurement Unit In this section, you set the unit of measurement for report display: pixel, inch, or centimeter.
Grid In this section you set grid options: • • •
Show grid: When this is selected, a grid displays to help align page elements. Snap to grid: When this is selected, page elements align to the grid to enable accurate repositioning. Grid spacing: This defines the distance between lines on the grid.
Interactive Analysis Desktop Locale Preferences This section describes the locale user preferences that you can define for your work in the Interactive Analysis Desktop application.
In this section, you choose the default folders in which to store user documents, universes, and help files. To change the default locations, click Browse, then browse to and select a folder.
BOW310
Lesson: Working with Interactive Analysis Documents
Locales A locale is a combination of language and geographical area.
•
Product locale: Select an available locale to set the application interface language. When you change this setting, you must restart Interactive Analysis Desktop for the change to be taken into account. Preferred viewing locale: The user's preferred locale for displaying document data. When a user creates a document, the preferred viewing locale is always assigned as the initial Document locale saved with the document.
Formatting Locale This option determines how Interactive Analysis formats the data in your documents. •
•
Use the document locale to format the data: When this is selected, data is formatted according to the document locale. The document locale can be saved with the document by means of the permanent regional formatting option that you can select when saving. Use the preferred viewing locale to format the data: When this is selected, data is formatted according to your preferred viewing locale preference. This overrides the document locale.
Interactive Analysis Desktop Drill Preferences Start Drill Session In this section, you choose how to start a new drill session: •
•
2011
Start drill on duplicate report: When you start a new drill session, a duplicate report is opened in the document and you drill on the duplicate. When you end drill mode, both the original report and the drilled report remain in the document. Start drill on existing report: When you start a new drill session, the current report becomes drillable. When you end drill mode, the report displays the drilled values.
Prompt when drill requires additional data: You are prompted when Interactive Analysis needs to retrieve additional data to complete the drill, and can decide whether to go ahead. If the amount of data is large, the retrieval can take time and you may decide not to drill. When this option is not selected, Interactive Analysis retrieves the additional data without prompting you. Synchronize drill on report blocks: When this is selected, drilled values are shown in all the report blocks in the report. When it is not selected, drilled values are shown only in the report block selected for the drill. Hide drill toolbar on startup: When this is selected, the drill toolbar that is normally displayed at the top of drilled reports is not shown. The drill toolbar displays the value on which you drilled. It is only useful when you want to select filters during your drill session.
Proxy Options Define the proxy settings when you use an internet proxy server to access URLs or images in your reports. You do not need to define proxy settings for images embedded in a report. Use the proxy settings defined within your internet browser to define your proxy settings in the Interactive Analysis Desktop application. • •
104
Http Host and Port Settings: Type the appropriate proxy information in each field. Https Host and Port Settings: When the https proxy settings are the same as the http proxy settings, select same as Http Proxy. When they are different, type the appropriate proxy information in the fields.
Lesson: Working with Interactive Analysis Documents
To Set User Preferences in Interactive Analysis Desktop For internal use by CSC only
Use the instructions for the application mode in which you are working: •
•
From Reading mode, click the button with two arrows pointing to the right >> in the upper right-hand corner and, in the display of buttons, click the Tools button. From Data or Design mode, click the Tools button.
A drop-down list displays. 2.
Click Options. The User Preferences dialog box displays.
3.
Click the tab that corresponds to the options you want to define: • • • • •
4.
General: to set general preferences Viewing: to set document viewing preferences Locale: to set preferences for interface and formatting locales and how to use them Drilling: to set document drill preferences Proxy: to set proxy host and port
Set preferences and click OK. The preferences are now applied. They are stored in a file in the LocData folder under the SAP BusinessObjects Business Intelligence installation folder.
To Change Your Password Use For internal use by CSC only
Procedure 1.
Use the instructions for the application mode in which you are working: •
•
From Reading mode, click the button with two arrows pointing to the right >> in the upper right-hand corner and, in the display of buttons, click the Tools button. From Data or Design mode, click the Tools button.
A drop-down list displays. 2.
Click Change Password.
4.
Type your current password in the Enter Old Password field.
5.
Type your new password in the Enter New Password field.
6.
Type your new password again in the Confirm New Password field.
7.
Click OK.
Result Your password is changed to the new password.
You can only change your password from Interactive Analysis Desktop if you are in client-server connection mode: you must have launched Interactive Analysis Desktop locally rather than from BI launch pad.
BOW310
Lesson: Working with Interactive Analysis Documents
Interactive Analysis Application Structure The Interactive Analysis application has the following components in Design mode: For internal use by CSC only
The File and Properties tabs The File tab contains the toolbar that allows you to open, save and print documents. The Properties tab allows you to set document and application properties and to determine the display of the different components and panels in the application.
•
Toolboxes Toolboxes appear at the top of the application and provide access to tabs and toolbars that allow you to perform related tasks. The toolboxes include: Report Element, Format, Data Access, Analysis, Page Setup.
•
The Left Panel The Left Panel contains several panes that provide different views of the current document.
•
The Status Bar The Status Bar appears beneath the report and allows you to activate data tracking or change the way the report displays between Quick Display and Page modes
Working with the Left Panel Views The Left Panel in the Design mode of the Interactive Analysis Desktop main window groups several panes that provide different views of the current document. For internal use by CSC only
•
• • • •
The Document Summary provides an overview of the document properties. You can print or edit the properties using the buttons at top of the Document Summary. The Report Map lists all the reports in the document displayed in the Report Panel. You can navigate among the reports and report sections via the Report Map. The Input Controls pane allows you to add and edit input controls applied to the document. The Web Services Publisher pane lists the BI Services published from the document. The Available Objects pane lists the data providers and objects available for building your reports. The Document Structure and Filters pane displays the structure of the document in a tree view and displays the filters applied to the different report elements.
In Data mode, only the Data pane appears in the Left Panel. The Data pane allows you to navigate the data providers in a document. In Reading mode, only the Document Summary, the Report Map and the Input Controls pane appear in the Left Panel, but the report user cannot modify the information in the panes. By default, when the results of a query display in the Report Panel the Left Panel displays the Available Objects view. You can change the view by clicking the buttons to the left of the view or by clicking the drop-down arrow next to the name of the active view to display the views available. For a better view of the Report Panel, you can close the Left Panel by clicking the arrow <
