HP Storage Essentials Storage Resource Management Report Optimizer Software 6.
Legal and notice information © Copyright 2002-2008 Hewlett-Packard Development Company, L.P. Hewlett-Packard Company makes no warranty of any kind with regard to this material, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. Hewlett-Packard shall not be liable for errors contained herein or for incidental or consequential damages in connection with the furnishing, performance, or use of this material.
Contents 1 About Web Intelligence . . . . . . . . Contents . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15 How Web Intelligence performs business intelligence over the web . Interacting with Web Intelligence reports . . . . . . . . . . . . . . . . . . . . Viewing and printing reports . . . . . . . . . . . . . . . . . . . . . Drilling results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Performing on-report analysis . . . . . . . . . . . . . . . . . . . .
Building a simple query. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Using the Data tab. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Opening and closing a class folder. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Merging dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Automatic merging . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Working with merged dimensions in reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Understanding the effects of merged dimensions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
How do subqueries work? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Building a subquery . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Subquery parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . How the subquery components combine. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . What is a database ranking?. . . . .
Formatting values on table cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Sizing cells . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Setting properties for table headers and footers . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Setting page layout for tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Setting the chart size . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Inserting and formatting titles for charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Displaying charts with a 2D or 3D look . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding background colors . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Formatting chart borders . . . . .
Available sort orders . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Applying ascending or descending sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Applying custom sorts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Prioritizing the order of multiple sorts on the same table . . . . . . . . . . . . . . . . . . . . . . . . . . . 227 227 228 228 16Ranking Data . . . .
How are alerters applied to tables with breaks? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Creating alerters. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Formatting alerters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Applying multiple conditions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
The Section keyword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Break keyword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Block keyword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Body keyword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Using keywords to make reports generic .
Query drill . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Setting your drill options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Making documents drillable . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Specifying the scope of analysis for a document . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Saving documents as Excel or PDF files . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 398 Saving Web Intelligence documents as Excel Spreadsheets. . . . . . . . . . . . . . . . . . . . . . . . . 399 Generating Web Intelligence documents to PDF . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 399 26Linking documents with OpenDocument . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 401 Overview . . . . . . . . . . . . . . . . . . . . . . . . . . . .
1 About Web Intelligence How Web Intelligence performs business intelligence over the web Web Intelligence provides business users an easy to use interactive and flexible user interface for building and analyzing reports on corporate data over the web, on secured intranets and extranets. The Web Intelligence software is installed by your administrator on a web server on your corporate network.
viewing into a drillable report or drill on a duplicate of the original report to retain a version of the results before your drill analysis. Once you have found the information you need, you can save a snapshot of the drilled report to share the results of your analysis with other Web Intelligence users, or save the document in Excel or Portable Document (PDF) format to print or email to other business contacts.
Note: On-report analysis of Web Intelligence reports in Interactive view format is only available, if your administrator has deployed Web Intelligence in JSP mode. Creating and editing Web Intelligence documents You can create or edit Web Intelligence documents using one of several tools: • Java Report Panel • Query – HTML • HTML Report Panel This section explains the differences between each tool.
or edit the data providers in documents created using any of the other Web Intelligence tools. Used together with On-Report Analysis, Query – HTML provides a complete solution for building data providers and designing powerful reports in a pure HTML environment. Once you have run the data providers to generate a standard report, you can leverage Web Intelligence On-Report Analysis features to format multiple reports, add formulas, and create variables.
Note: Web Intelligence Query – HTML and On-Report Analysis in Interactive view format are only available, if your administrator has deployed Web Intelligence in JSP mode. Web Intelligence HTML Report Panel Designed for users who need to build basic reports, the HTML Report Panel provides query and report features in a simple wizard-like interface. Each document is based on a single data source and can contain multiple reports, displaying different subsets of information.
About Web Intelligence
2 Getting up and running Overview You access the Web Intelligence Java Report Panel via InfoView, the corporate business intelligence portal. This chapter tells you how to: • log in and out of InfoView • select the Java Report Panel as your Web Intelligence document editor Logging in and out of InfoView You access Web Intelligence by using your web browser to log into InfoView, the corporate business intelligence portal.
For example, if the name of the InfoView server is corpbusintell and the port number is 4200, then type: corpbusintell:4200 Your administrator can provide you with this system information. 4. In the Username box, type your user name. 5. In the Password box, type your password. 6. In the Authentication box, select the authentication provided to you by your administrator. 7. Click Log On. The InfoView home page appears.
Note: If you are using Web Intelligence deployed in ASP mode, only the Java Report Panel is available. It is therefore not necessary to select the Java Report Panel on the Web Intelligence Document Preferences page. Note: Information on setting your options for viewing, analyzing, and interacting with reports is provided in the Performing On-Report Analysis on Web Intelligence Reports guide, since these options do not affect how you use the Web Intelligence Java Report Panel.
Getting up and running
3 Creating and editing Web Intelligence documents Overview You create and edit reports in Web Intelligence documents by launching a Web Intelligence report panel from InfoView. This guide tells you how to create and edit documents using the Java Report Panel.
2. Click the title of the universe on which you want to create a document. This icon indicates a universe The Web Intelligence report panel opens. NOTE: The first time you use the Java Report Panel, Web Intelligence automatically downloads a Java applet to your computer. If your computer is using Microsoft Windows 2000 as its operating system, you must be logged into your computer as Administrator to download the applet.
The list of documents appears. 3. Click the document title. 4. If the document contains any prompts, you need to select the value(s) you want returned to the document, and then click Run Query. The Java Report Panel launches and displays the document. Editing a Web Intelligence document directly To edit a Web Intelligence document directly: 1. Make sure you are logged into InfoView. For full information, see ”Logging into InfoView” on page 21. 2.
The Java Report Panel launches and displays the reports in the document. NOTE: To edit Web Intelligence documents using the Java Report Panel, the Java Report Panel needs to be selected on the Web Intelligence Document Preferences page in InfoView. To find out how to do this, see ”Setting your Create/Edit options” on page 23.
2. Specify measurement settings for the document properties and define grid settings here. 3. Click OK. Web Intelligence saves your new settings. The new settings are applied the next time you launch the Java Report Panel. Java Report Panel interface tour The Java Report Panel provides a rich interface for working with Web Intelligence documents.
Toolbars The following table describes the Java Report Panel toolbars: Toolbar name Description Main Used to save and print reports, to switch between Query and Report view, to show/hide the other toolbars and to select default interface configurations. Note: The Main toolbar is always visible in Report View and Query View (when you are working in the Query Panel). Formatting Used to set font styles and backgound colors.
Name Description Template Used to add tables, charts and free-standing cells to a report. (See, for example, ”Creating a table by selecting a template” on page 131.) Map Describes the report structure graphically. Properties Used to set the properties associated with the currently-selected report element. The property list changes depending on the selected element. For example, if you select a cell, the tab lists the properties associated with cells.
The following table describes the default configurations: Name Description Data • All tabs appear on the left with the Data tab at the front. Data/Properties • All tabs appear on the left. • The Properties tab appears below the other three. Data/Properties on Right • The Data, Templates and Map tabs appear on the left. • The Properties tab appears on the right. Collapsed Data/Properties • The Data, Templates and Map tabs appear collapsed in toolbar form down the left side of the screen.
4 Building basic data providers Overview This chapter describes how you can build a basic report by creating and running a data provider using objects in a universe. It explains the fundamentals of universes and describes the objects that you use to construct a query.
information to the database. Web Intelligence can generate SQL data providers of unlimited length. When the data is returned to the Web Intelligence report panel, it is presented in a table form, with columns that have the same names as the objects that you used in the query. The data is arranged in rows. Data providers and queries are based on universes You build data providers and queries in the Java panel using objects in a universe.
Universe component Description Class Logical grouping of objects. Each class has a meaningful name, for example; the class Store contains the objects State, City, and Store name. What types of objects can you use in a query? Objects can represent different types of information: Table 0.1: Object Examples Description Dimension Retrieves the data that will provide the basis for analysis in a report.
Classes and Objects are presented in a tree structure as follows: Each folder represents a class Each icon within a class represents an object Details are under dimensions Store details is a subclass of Store You can search for classes and objects in the tree by selecting an item in the tree, then typing a letter. You can refine the search by typing further letters.
What happens when you run a query? When you run a query, you retrieve the information from the database represented by the objects that you have added to the Results Objects pane. The request for information is processed in the database, and the results are sent back to the Web Intelligence Java Report Panel in the form of a document. The document contains all the information that you asked for in the query, arranged in table columns and rows.
Overview An overview of the query building process appears below: What you do to build a query See the section Navigate the Query Manager to find the objects that you want to use in the query.
The class closes. The objects contained in the class are no longer displayed. Building the query You build a query by selecting objects in the Data tab and dragging them onto the Result Objects pane. You can add objects one-by-one or add all the objects in a class folder at once. You can also remove objects from the Result Objects pane at any time.
2. Select a class and drag it over to the Result Objects pane. All of the objects in the class appear in the Result Objects pane. Removing an object from a query To remove an object from a query: 1. Make sure you are in Query View. The Edit Query button is pressed in when you are working in Query View. 2. Select an object in the Result Objects pane. 3. Drag the object over to the Data tab. Or Click Delete. The object is removed from the Result Objects pane.
you can pull this data in to the report to allow you to access more detail at any time. This process of refining the data to lower levels of detail is called drilling down on an object. In the universe, the scope of analysis corresponds to the hierachical levels below the object selected for a query. For example, a scope of analysis of one level down for the object Year, would include the object Quarter, which appears immediately under Year. You can set this level when you build a query.
Note: Including a scope of analysis in a document increases the document size significantly. This is because the data necessary for the scope you specify is saved with the document, even though it is not visible in the reports unless you start drill mode and drill down to the data to display the corresponding values. In order to minimize the size of documents and optimize performance, we recommend that you only include a scope of analysis in documents where you are certain that users will need to drill.
The level appears in the list box and the dimensions that are hierarchically below each dimension in the Result Objects pane appear in the Scope of Analysis pane. [Quarter] and [Month] appear under [Year] [SKU desc] and [Color] appear under [Category] 5. If you want to add selected dimensions to the scope of analysis or create a custom scope of analysis, select dimensions in the Query Manager and drag them across to the Scope of Analysis panel.
• You have used a combined query and the database does not support the combination operator you used. (For more information on combined queries, see ”Working with combined queries” on page 55 on page 55.) In each case you see a hierarchical view of the SQL queries on the left of the dialog box. The root item of the hierarchy is labelled according to the reason why the SQL was generated in multiple queries.
Query property options You can set the following query properties: Query Property Available Options and Description Name • The name you specify for the selected query is displayed on the corresponding query tab. Data • Retrieve duplicate rows 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.
Query Property Available Options and Description Security • Allow other users to edit all data providers When selected, other users who have the appropriate editing rights can access Query View and modify the data providers in the document. When cleared, only the report creator can modify the data providers. This option is selected by default. Unlike the other query properties, which only apply to the selected query, this option applies to all of the data providers in the document.
The Query Properties options appear. 3. Click the Fold/Unfold arrows at the top right of each section of the options to expand or close the property groups. 4. Select or type query property options. For a full explanation of each option, see ”Query property options” on page 45. The modifications you make to the query properties are applied immediately.
and ”Selecting a universe and launching a report panel” on page 25. Once the universe is available in the Java Report Panel, you can start building a query. Building and running a simple query To build and run a simple query: 1. Verify that you are in Query View. The Edit Query button is pressed in when you are in Query View. 2. Click the + sign next to the class folder containing the object you want to include in the query. The class expands to show objects. 3.
Working with query contexts When you create a query, or refresh a report, you may be asked to choose a context before the query can run. In a universe, contexts are implemented to avoid ambiguous data providers. This section describes what is an ambiguous query, how contexts are used, and explains how you can choose a context to ensure that your query returns relevant information.
asking you to choose whether you want the country information to be sales or reservations information. Defining how contexts are used in a query In Web Intelligence you can customize how contexts are used in a report. You can set the following options to determine how contexts are used when you refresh a report: Option Description Reset contexts on refresh • When selected, you are prompted to choose a context each time a query requiring a context is run.
3. In the Context section of the Query Properties, select or clear the Reset contexts on refresh check box. Or If you want to remove all existing contexts saved in the report, click the Clear contexts button. This does not remove the context from the universe. Selecting a context To select a context: 1. From the Select a Context box, click one of the listed contexts. This is the context that contains the data that is relevant to your query. 2. Click OK. The query is run for the selected context.
1. On the Retrieving Data dialog box, click Cancel. NOTE: The Retrieving Data dialog box appears when you click Run Query or Refresh Data to retrieve the latest values from the database to a document. The Interrupt Data Retrieval dialog box appears. 2. Select one of the following options: • Restore the results from the previous data retrieval – Web Intelligence restores the values to the document that were retrieved the last time the query was run.
Incompatible objects and SQL When you build a query, Web Intelligence generates SQL behind the scenes. This SQL is run against the database to produce a result that Web Intelligence displays in a report. For a query to be free of incompatible objects, Web Intelligence must be able to generate a single SQL query to retrieve the data. If this is not possible, the query contains incompatible objects.
Building basic data providers
5 Working with combined queries Overview ”Building basic data providers” on page 33 on page 55 describes how to create basic data providers. The data providers described in that chapter contain one query only. You can create much more powerful data providers by including combined queries. Combined queries are multiple queries on the same universe that return a single set of data. Note: You cannot create combined queries across multiple universes.
Depending on the type of combined query, Web Intelligence returns the following values: Combination type Values UNION US; UK; Germany; France; Spain INTERSECTION US MINUS UK; Germany; France What can you do with combined queries? Combined queries allow you to answer questions that are otherwise difficult or impossible to frame in a single Web Intelligence query.
Note: SQL is the standard query language of relational databases, although each database has its own dialect. If your database does not support the type of combination in your query, Web Intelligence performs the query at the report level by generating multiple SQL queries whose data it resolves after retrieval from the database. For more information on the SQL generated by Web Intelligence, see ”Viewing and editing the SQL behind the query definition” on page 43.
The individual queries in the combined queries are named Combined Query n. Combined query button Combined queries and type of combination 3. To switch to a query, click Combined Query n. 4. To change the combination type, double-click on the operator. The operator moves through the sequence UNION, INTERSECTION, MINUS. 5. Build each query within the combined query as you build any normal Web Intelligence query. 6. Click Run Query. To edit a combined query 1. Click on the Combined Query n you want to edit.
returns Year and Revenue, and you cannot combine a query that returns Year with a query that returns Revenue. You must also pay attention to the semantics of your combined queries. While it is possible to combine a query that returns Year with a query that returns Region if both dimensions are of the same data type, the result - a mixed list of years and regions - is unlikely to be meaningful.
finds the union/intersection/minus between that data set and the data returned by Combined Query n + 2. Web Intelligence continues in this way through all the queries in the relationship.
Query Result (Query 1 MINUS Query 2) US; Spain INTERSECTION Query 3 Note: If your database directly supports the type of combined query you wish to execute, Web Intelligence generates SQL containing combination operators. (For more information see ”How does Web Intelligence generate combined queries?” on page 56.) In this case the order of precedence depends on the order of precedence defined in the database. See your Web Intelligence administrator for more details. Setting the order of precedence 1.
Working with combined queries
6 Including multiple data providers Overview This chapter tells you how you can include multiple data providers in a single document. It includes the following information: • using multiple data providers • defining multiple data providers in a document • purging data from multiple data providers Note: This chapter tells you how to add data providers to documents that already include a first data provider.
Note: You cannot include objects from different universes in the same report block (that is, in the same table or chart). For full information, see ”For more information on merged dimensions, see ”Merging dimensions from multiple data providers” on page 73 on page 63.” on page 71.
(If you require information on creating a new document and defining the first query, refer to ”Building basic data providers” on page 33.) Add Query button The name of each query displays on a Query tab 2. Click the Add Query button on the Query toolbar. The Universe dialog box appears. You can create a new query on a universe already used in the document or select a different universe.
3. In either of the two lists, select the universe on which you want to define a query. 4. Click OK. The new query pane appears. The query tab for the new query displays the default name for data providers: Query followed by a number that indicates the total number of data providers in the document; for example Allow other users to edit all data providers. You can rename the query with a more meaningful name now or later. (See ”Renaming a query” on page 68.) 5.
The New Query dialog box appears. You need to choose how you want to include the data from the new query into the document. 7. Select the appropriate option: If you want to... Then select...
Duplicating a query To duplicate a query: 1. Make sure you are in Query View. The Edit Query button is pressed in when you are in Query View. 2. Select a the query you want to duplicate by right-clicking the appropriate query tab at the bottom of the report panel. 3. Right-click the query tab and then select Duplicate Query from the drop-down menu. Web Intelligence creates a duplicate of the selected query and adds it to the report panel. You can edit the data definition of the duplicate query.
Either right-click the tab of the query you want to rename and then select Rename Query from the shortcut menu. Or With the query tab you want to rename selected, click the Properties tab. The Query Properties sub-tab appears. 2. In the Name box, type the name for the query. 3. Press the Enter key. The new name appears on the Query tab.
A sub-menu appears. 4. Depending on where you want to move the query, select Left or select Right. The selected query moves accordingly. Purging data from multiple data providers When you purge data from a document you remove all data from the document, while leaving the document structure intact. If the document contains multiple data providers, you can purge specific data providers within the document. Note: If you view purged reports in Results View, charts are not visible.
For more information on merged dimensions, see ”Merging dimensions from multiple data providers” on page 73 on page 63.
Including multiple data providers
7 Merging dimensions from multiple data providers Merging dimensions from multiple data providers Overview This chapter describes how to synchronize multiple data providers in a Web Intelligence report by merging them on common dimensions. After merging common dimensions you can place dimensions from different data providers in the same block. Merged dimensions and data synchronization are new in Web Intelligence XI R2.
all the data providers synchronized through the merged dimension in the same block as the merged dimension. Which dimensions do you merge? The only restriction that Web Intelligence imposes on merged dimensions is that they must be of the same data type: for example, character data. But it does not make sense to merge unrelated dimensions even when their data types are the same. For example, it does not make sense to merge a dimension containing customer names with a dimension containing sales regions.
NOTE: When you select a dimension, all dimensions of different data types are disabled because you cannot merge dimensions of different data types. 3. Click Values to view the values associated with the dimensions. 4. Click Merge. The Create Merged Dimension dialog box appears. You use this dialog box to specify the properties of the merged dimension. 5. Select the dimension in the Source Dimension drop-down list.
• The dimensions are in the same universe. To do this: 1. Right-click outside any block or chart and click Document Properties on the popup menu. 2. Click Auto-merge dimensions. Working with merged dimensions in reports Once you have created a merged dimension you can include it in a report as you include any other report object. Merged dimensions appear under the Merged Dimensions folder in the Data tab. You can expand each merged dimension to see the dimensions that are merged in it.
Understanding the effects of merged dimensions Data synchronization through merged dimensions adds enormous power to Web Intelligence. Merged dimensions also have implications for the results that Web Intelligence displays in certain situations. You need to understand these implications to work effectively with merged dimensions and synchronized data. The following sections describe these situations.
Web Intelligence cannot determine the number of sales per year for customer Jones because the data provider that stores the number of sales does not break them down by year. Web Intelligence therefore reproduces the total number of sales on each row. Note: Although the Number of Sales values are duplicated, if you add a standard calculation to the bottom of the column (for example a Sum or Average calculation), the result is correct.
The block is different in Desktop Intelligence/BusinessObjects because BusinessObjects extends the values of the Country of Origin dimension through the values returned by the Revenue measure. You can alter the Web Intelligence default behavior so that it corresponds with Desktop Intelligence/BusinessObjects behavior in this situation. Extending dimension values in Web Intelligence To extend dimension values in Web Intelligence: 1.
For example, Paul might also have an address in London, which means that there is no unique ‘Paul’ row with which WebIntelligence can synchronize Paul’s age: Customer Address John London Paul Paris Paul London Age John 25 Paul 28 If the relationship between Customer and Address is one-to-one, WebIntelligence can ignore Address in the synchronization.
Example: Filtering a merged dimension In this example you have a report with the following data providers, which are merged on the Country dimension: Country France US US Resort French Riviera Bahamas Beach Hawaiian Club Country France US Future Guests 46 56 Revenue 835,420 971,444 1,479,660 If you apply the filter Country=”US” to the first block, Web Intelligence also filters the second block to give the following result: Country US Future Guests 56 If the Country dimensions are not merged the seco
Merging dimensions from multiple data providers
8 Filtering data retrieval using query filters Overview You limit the data returned to a Web Intelligence document by applying filters when you define the query. Using query filters enables you to secure the data you don’t want specific user groups to see and limits the size of the documents stored on your network. When you run the query or refresh the document data, Web Intelligence returns only the values that meet the query filter definitions.
$130K. To create a document with only the information you need, you apply the following query filters: Year = 200 Quarter = Q$ State = Texas Margin >= 130,000 To avoid displaying the filtered values Texas, 2002, and Q4 in the table columns Year, Quarter, and State, you exclude the [Year], [Quarter], and [State] objects from the Result Objects pane.
report to display a different subset of the same data. The data hidden by the report filters remains saved with the Web Intelligence document. You can modify the report filters to display different data in the report or remove the report filters altogether to display all the data defined in the query. You define report filters in Report View. For information on using report filters, see ”How report filters work” on page 239.
For example, you can filter the [Year] dimension to return values for a specific year, filter the [Revenue] measure to return values for a range of revenue figures, or filter the [Postal Code] detail to return values for a specific postal area. For an illustrated description and examples of dimensions, measures, and details, see ”What types of objects can you use in a query?” on page 35. Note: Your administrator can prevent objects from being filtered.
To obtain data... for example... select... to create the filter...
To obtain data... for example... select... to create the filter...
The following table gives examples of when it is more efficient to type a constant or select value(s) from list: If... for example... then... the list of values on the dimension or detail you are filtering is long and you are sure of how to spell the value you want to filter; names of months or numbers for specific years, type a constant. you are not sure how to spell the value(s) you want to filter; select value(s) from customer names or product lines, which can the List of Values.
This chapter explains how to use the following two filter types: • predefined filters • custom filters For information on creating prompts, see ”Building prompt filters on data providers” on page 113. For information on creating advanced filters, see ”Filtering data using subqueries and database ranking” on page 105.
Or Drag and drop the objects onto the Result Objects pane. The objects appear on the Result Objects pane. For step-by-step instructions on selecting objects to build a query, see ”Adding an object to a query” on page 39. 3. Double-click the predefined filter. Or Drag-and-drop the predefined filter to the Query Filters pane. The predefined filter appears on the Query Filters pane. When you run the query, the data corresponding to the query filter(s) you selected is returned to the report.
• Using the Quick Filter option – allows you to quickly select one or multiple values from a list of value(s) • Using the Filter Editor – allows you to select from many operators and to either type or select the value(s) Using the quick filter option to create filters Quick filters allow you to quickly define the value(s) you want to retrieve for a specific object without launching the Filter Editor.
4. Reselect the object on the Result Objects panel and click the Add Quick Filter button on the Report toolbar. The List of Values dialog box appears. The values for the selected object are listed. 5. Select the value(s) you want to retrieve from the database. For example, to filter the query for values in Q1, select the [Quarter] dimension, then select Q1 from the list of values. 6. Click OK. The new filter appears on the Query Filters pane.
The Query Filters pane is where Web Intelligence displays all the filters defined on the query. Query Filters pane For information on how to define the result objects you want Web Intelligence to retrieve to the document, see ”Building and running a simple query” on page 48. 3. Select the object you want to filter and drag it to the Query Filters pane. The query filter appears in outline in the Query Filters pane. 4.
Combining multiple filters on a query Typical business questions require to retrieve information that matches more than one criteria. For example, if you are analyzing customer services data, you will most likely want to focus on customers for a specific time period and also for a specific region, and probably also for a specific level of customer service contract. With Web Intelligence, you retrieve data that answers several criteria like this by combining filters in the same query.
How you combine query filters You combine filters to retrieve data that corresponds to multiple business criteria. For example, to retrieve data for customers who live in California and who don’t have a premium service contract, you combine the following two filters: Filter 1: [State] Equal to California And Filter 2: [Service Contract] Not equal to Premium Combining query filters To combine query filters: 1. Create each filter.
1. If necessary, change the operator to Or, by double-clicking the And operator once: Or now displays as the operator. Combining simple filters with combined filters You can combine filters with other groups of combine filters to retrieve data that corresponds to one or multiple business criteria. You combine filters with And or Or operator. You can combine groups of filters with single filters to answer complex criteria.
Using Or Using Or to combine all three filters returns data that matches any one of the filters: In this case, Web Intelligence returns values for Texas for all quarters and data for all states in Q4, and data for all states where sales revenue was greater than $2M. Using And and Or You can retrieve data that answers either the criteria specified in one filter or the criteria specified in the other filter(s).
or that corresponds to • both the second filter – [Quarter] Equal to Q4 and the third filter – [Sales Revenue] Greater than $2M Applying query filters to business questions This section provides examples of how you can apply query filters to Web Intelligence documents to answer typical business questions.
2. Enter the start date and end date for the month of December 2002: If you selected Constant... if you selected Value(s) from list... • type the date for December 1, 2002 into the From: box, • select the date for December 1, 2002 and click the From>> button • type the date for December 31, 2002 in the To: box • select the date for December 31, 2002 and click the To>> button 3. Click OK to confirm the filter. The new filter appears in the Query Filters pane.
The Filter Editor dialog box appears. 3. Select the In list operator. NOTE: : If you want to... for example... select... include data that is listed in a list of values that you specify, retrieve values for three cities: London, Paris, Tokyo, In list exclude data that is listed in a list of values that you specify, exclude values for three cities: London, Paris, Tokyo, Not in list 1. Select the Operand type, Value(s) in list. The values related to the [City] object appear. 2.
Example: Analyzing data for customers in a specific postal area by filtering the first two letters of their post code In this example, you want to analyze data for customers living in the CA postal area. You can do this by retrieving only the data for customers whose post code includes CA. To do this: 1. Click the Edit Query button on the Web Intelligence toolbar. Query View appears. 2. Drag and drop the [Post Code] object into the Query Filters pane. The Filter Editor dialog box displays. 3.
The new filter appears in the Query Filters pane. When you run the query, only data for customers whose post code includes the string you specified, will be returned to the document.
Filtering data retrieval using query filters
9 Filtering data using subqueries and database ranking Overview ”Filtering data retrieval using query filters” on page 83 explains how you restrict the data returned by a query by adding query filters.This chapter explains how to work with more advanced types of query filter. This chapter covers the following advanced query filters: • subqueries • database rankings Subqueries and database rankings allow you to create filters that are much more powerful than standard query filters.
For more information on viewing the SQL generated by Web Intelligence, see ”Viewing and editing the SQL behind the query definition” on page 43.) Note: SQL is the query language supported by all relational databases (RDBMS), although each database has its own syntax. Building a subquery You build a subquery in the Query Filter pane of the Query Panel. Note: You can mix subqueries and other types of query filter in the Query Filters pane. To build a subquery 1.
this case the second subquery remains at the same level as the first, and becomes part of the WHERE clause of the first. Filter object(s) Filter By object(s) WHERE conditions Outline of second subquery joined to first subquery in an AND relationship. Click the arrow to hide/display the WHERE condition. Subquery parameters A subquery or set of subqueries contains the following parameters: Parameter Description Filter Object(s) The object whose values are used to filter the result objects.
Parameter Description Operator The operator that specifies the relationship between the Filter object and the Filter By object. Because of database restrictions you cannot use certain combinations of operators and Filter By objects together. For example, if you use the Equal To operator with a Filter By object that returns multiple values, the database rejects the SQL because this type of subquery requires the Filter By object to return one value only.
NOTE: The selected object appears in both boxes in the subquery outline. You often use the same object in both boxes, although this is not required. If the objects do not return any common values, the subquery returns no values, and the query therefore returns no values. 4. Drag the Reservation Year object to the area of the subquery outline beneath the Service objects. Web Intelligence adds a WHERE condition on the Reservation Year object. 5. Set the Reservation Year condition operator to Equal To. 6.
• Pre-ranking data reduces the amount of data retrieved across the network and stored in Web Intelligence. Note: You can perform a database ranking only if your database supports it. If this is not the case, the Add a database ranking button is disabled on the Query Panel toolbar. Databases that support ranking are Oracle, DB2, Terradata and Redbrick.
Parameter Description Number of records The number of records to return in the ranking. For example, the top 10. Ranking dimension The dimension used in the ranking. For example, if the dimension is Region and the ranking is Top 10, the ranking returns the top 10 regions. Based on The measure by which the ranking dimension is ranked.
NOTE: The Add a database rankingbutton is disabled if your database does not support ranking. 4. Select the ranking direction (Top or bottom.) 5. Type the number of records you want the ranking to return in the box next to Top/Bottom. NOTE: You can specify a prompt instead of a constant by clicking on the arrow next to the number. When you select a prompt the user must enter the ranking number when the query is run.
10 Building prompt filters on data providers Overview This chapter tells you how to build prompts to filter Web Intelligence documents. Each time you refresh the document data, the prompts appear for you to specify the filter values you want Web Intelligence to return to the document. Prompts enable multiple users to return different data to the same document every time they refresh the document data.
When you refresh the data in the report, the prompt displays to request you to specify the start date and end date of the period, for which you want to return data to the document. Note: When a document contains multiple data providers, any prompts that include (1) objects with the same data type, (2) operators of the same operator type, and that (3) use the same prompt text are merged. When all the data providers are refreshed, a single prompt message appears for such prompts.
When you run the query or refresh the document data, Web Intelligence displays the prompts. What objects can I filter with a prompt? You can define prompts on any dimension, measure, or detail object listed on the Data tab in Edit Query View. For example, you can filter the [Year] dimension to return values for a specific year, filter the [Revenue] measure to return values for a range of revenue figures, or filter the [Postal Code] detail to return values for a specific postal area.
To obtain data... for example... select... to create the filter... not equal to a value you specify, retrieve data for all quarters except Q4, Not Equal to [Quarter] Not Equal to Q4 greater than a value you specify, retrieve data for customers aged over 60, Greater than [Customer Age] Greater than 60 greater than or equal to a value you specify, retrieve data for revenue starting from $1.
To obtain data... for example... select... to create the filter...
Note: You can’t use the following operators for prompts: Is null and Is not null. You can define query filters using these operators (see ”Which operator should I choose?” on page 115) Choosing how prompts display By default, Web Intelligence prompts display a box and a list of values. You answer the prompt by either typing the value(s) in the box or by selecting value(s) from the list.
Note: If the prompt is for a date and you want users to see the popup calendar in order to select the date(s) then do not do not select Prompt with List of Values.
The display properties of the merged prompt When you create a new prompt that has the same prompt text as another prompt, the display properties specified for the two prompts are applied to the merged prompt according to the following rules: • Select only from list takes priority over a prompt message that does not include this constraint • Prompt with List of values takes priority over a prompt message that does not display the List of values • Keep last values selected takes priority over a prompt message
3. From the Data tab, drag the object on which you want to apply a prompt and drop it onto the Query Filters pane. The query filter appears in outline in the Query Filters pane. 4. Click the arrow at the right of the Query Filter and select Prompt from the menu. 5. Type the prompt text in the text box. 6. Click the icon next to the text box and use the dialog box that appears to set the prompt properties.
• Which account? • Which calendar period: from? to? this enables each accounts manager viewing the document to view report values for a specific customer account during a specific period. Combining prompts To combine prompts: 1. Create each prompt. For step-by-step information on how to create a prompt, see ”Creating a prompt” on page 120. 2. Make sure that the Show/Hide Filters pane button on the Query toolbar is pressed in. You can view the prompts on the query in the Query Filters pane.
Or now displays as the operator. For information on changing the order of prompts, see ”You can also combine prompts with other types of query filter in the same way.” on page 123. NOTE: way. You can also combine prompts with other types of query filter in the same Prioritizing the order of prompts To prioritize the order of prompts: 1. Make sure you are in Query View. The Edit Query button is pressed in when you are working in Query View. 2. Click the Properties tab.
For information on how to answer prompts when you open Web Intelligence documents, see page 27.
11 Inserting and formatting tables Overview When you create a new document, Web Intelligence generates a new report with a vertical table and standard formatting. This initial table includes all of the data in the query behind the document. You can edit the table contents, personalize the formatting, or turn the table into a different table type or chart. You can also insert more tables onto the same report or onto new reports within the same document.
The following section describes each template. Vertical tables Vertical tables display header cells at the top of the table and the corresponding data in columns. By default, the header cells display the names of the dimensions, details, and measures included in the table. The body cells display the corresponding values. Horizontal tables Horizontal tables display header cells at the left of the table and the corresponding data in rows.
values for [State] on the left axis. The body displays values that [Sales Revenue] for each quarter in each state. You can include multiple dimensions in crosstabs. For example, this crosstab displays two dimensions. The values for the [Sales Revenue] measure are values each state by quarter for each line. Crosstabs display values of the dimensions and measures they contain. You can include additional headers that display the names of the dimensions and measures names.
body are calculated according to all of the coordinates on the table axes, whether or not there is a row for the specific coordinate in the SQL result. The current value of the dimension on the axis provides the value to be displayed in the body. The value for that dimension will be the same for the whole column, if the dimension is in the on top axis or for the whole line in other cases. Forms Forms are useful in your report if you want to display detailed information per customer, product, or partner.
preview them. When you return to Results View, Web Intelligence requests the server to apply the changes and returns the modified format in a single operation. Results View displays the results retrieved from the data source and how the report will print Structure View displays the structure of the report, and the definition of the data, filters, sorts and calculations You can create and format tables in either Structure View or Results View.
Note: When you create a new document, Web Intelligence generates a default report with a vertical table that contains all the objects in the query. You can modify or remove this table. See ”Applying a different template to existing tables” on page 135 or ”Clearing cells and removing tables” on page 146. For information on creating new documents, see “Template ook” on page Chapter. For information on inserting additional blank reports into a document, see ”” on page 353.
4. When the “Drop here to insert a cell” ToolTip appears, drop the object onto the left or right of the table header. A second column appears before or after the first column. The new column header displays the name of the object. 5. To add more objects to the table, repeat step 3. Web Intelligence displays the values in a vertical table. You can quickly turn the vertical table to a different table format, such as a crosstab by using the Turn To feature.
The available Templates appear. 5. Click the + sign next to Tables. The table templates are listed. 6. Drag a template from the Template tab onto a blank area of the report. The template appears on the report. 7. Click the Data tab. The objects and variables that the document contains appear here. You can add any of these objects or variables to the table. 8. Drag an object or variable onto an empty header or body cell of the table. 9.
If you are working in Results View, the values of the objects display on the table. If you are working in Structure View, the name of the object displays on the table. 10.To add more objects to the template, drag an object from the Data tab and drop it onto the template. Make sure that the ToolTip “Drop here to replace a cell” appears, and then drop the object onto the template. 11.If appropriate, repeat step 10. to allocate more objects to the table columns, rows and body.
Make sure that the ToolTip “Drop here to insert a cell” appears before you drop the object. Structure View The object appears in a new table cell. In Structure View, the object names display on the template. In Results View, the values for each object display on the table. 13.If you are working in Structure View, click View Results to display the values in the new table. Duplicating tables You can include multiple tables on a single report.
3. With your pointer and the Ctrl key pressed in, drag the duplicate table to an empty area of the report. The duplicate table appears in Structure View until you drop it onto an empty report area 1. Release the pointer, then release the Ctrl key. The duplicate table appears on the report. For information on positioning the duplicate table on the report page, see ”Aligning tables, charts, and free-standing cells on reports” on page 371.
You work in the California branch of a retail clothing chain, and have been sent a report showing revenue by quarter for the western region. The data is presented in a vertical table, which presents the results by quarter for each of the three western states, including California. However, it is not easy to compare the results in a glance. You turn the chart to a crosstab to get a comparative view of the sales results quickly. You can change the format of existing tables on reports.
If the Templates tab is hidden, click the Show/Hide Manager button on the Report toolbar. The Templates appear. 4. Click the + next to Tables. The table templates are listed. 5. Drag a template from the Template tab onto a table. You must drop the template directly on the existing report block. Web Intelligence applies the new template to the table values. Applying a different template to a table using the Turn To dialog box To apply a different template to a table using the Turn To dialog box: 1.
3. Right-click the report block. 4. On the shortcut menu, click Turn To. The Turn To dialog box appears. 5. On the Tables tab or on one of the Chart tabs, select the table or chart template you want to apply to the table. 6. Click OK. Note: If you turned a table to a chart, the unicode font is not retained in the chart if the font for the text on the table was unicode, and if unicode is not defined as your default font for charts.
The objects and variables included in the document are listed. 4. Drag the object you want to add to the table from the Data tab, and drop the object where you want to add it: To add the object into a new... column to the left of an existing column, column to the right of an existing column, row before an existing row, row after an existing row, drag the object onto... the left edge of a column header. the right edge of a column header. the top edge of a row header. the bottom edge of a row header.
Adding table rows or columns using the Insert row or column toolbar menu To add a table row or column using the Insert row or column toolbar menu: 1. Make sure you are working in Report View. The Edit Report button is pressed in when you are in Report View. 2. Select the table you want to reformat. A gray border appears around the table. 3. Select the column or row next to which you want to insert the new column or row.
2. Drag an object from the Data tab, and drop the object onto the blank column or row. If the Data tab is hidden, click the Show/Hide Manager button on the Report toolbar. Web Intelligence allocates the selected object to the new column or row. The name of the object displays in the column or row header, and the values for the object display on the body cells. Removing table rows or columns To remove a table row or column: 1. Make sure you are working in Report View.
To move a row or column on a table: 1. Make sure you are working in Report View. The Edit Report button is pressed in when you are in Report View and the report tabs of each report in the document are visible. 2. Select the table column or row that you want to move. 3. Drag the selected column or row and drop it before or after another column or row on the table. When you drag a row or column, the column or row header displays next to your pointer The Lines column header displays next to the pointer.
3. Drag the selected column or row onto the column or row with which you want to make the swap. For example, to swap the Lines and State columns, drag the Lines column onto the State column. Web Intelligence swaps the two columns or rows. Replacing table columns and rows You can replace table columns and rows with different data. Replacing a table column or row To replace a table column or row: 1. Make sure you are working in Report View. The Edit Report button is pressed in when you are in Report View.
4. Drag the object you want to add to the table from the Data tab, and then drop the object onto the row, column, or body you want to replace. For example, to replace the values for quarter by values for state, drag and drop the [State] object onto the table column headed “Quarter.” The values for the new object display on the table.
Note: If the document doesn’t include the data you want to add to the crosstab, you can edit the query. For more information, see ”You can search for classes and objects in the tree by selecting an item in the tree, then typing a letter. You can refine the search by typing further letters. For example, if you type “y“ then “e”, Web Intelligence first searches for objects beginning with “T, then “searches for objects begninning with “ye“.” on page 36.
3. Drag the selected column or row onto the top edge of the table or the left side of the table where you want to create the new axis and form the crosstab. For example, you move the Quarter column above t Sales column to create a crosstab that displays Yea values down the left axis, Quarter values across the top axis, and Sales revenue values in the body. 4. Drop the column or row onto the header.
The following illustration shows the cells you can select. Clearing a header cell, clears the contents of the individual cell Clearing body cells, clears the contents of all the body cells Clearing a footer cell, clears the contents of individual footer cell Clearing cell contents from a table To clear cell contents from a table: 1. Make sure you are working in Report View. The Edit Report button is pressed in when you are in Report View and the report tabs of each report in the document are visible. 2.
Note: You cannot select and remove multiple tables at one time. Removing a table using the shortcut menu To remove a table using the shortcut menu: 1. Make sure you are working in Report View. The Edit Report button is pressed in when you are in Report View and the report tabs of each report in the document are visible. 2. To select the table you want to remove, click the top edge of the table. A gray border appears around the table. 3. Right-click the selected table. The shortcut menu appears. 4.
• table or cell borders – select line styles and colors • cell text – define fonts, font sizes, and font styles • cell size – specify the height and width of cells • spacing within and between table cells – padding and spacing Note: The colors available in Web Intelligence are the colors set up for your desktop. Formatting table or cell backgrounds By default, tables have the standard Web Intelligence format.
Or Click Custom, and then create a custom color, using the Swatches, HSB (Hue, Saturation, Brightness), or RGB (Red, Green, Blue) tab, and click OK. If you know the RGB hexadecimal color reference, you can type the reference into the combo boxes next to the + and - buttons on the RGB tab. Web Intelligence applies the background color to the selected table or cell. Defining alternate row and column colors for a table To define alternate row and column colors for a table: 1.
If you know the RGB hexadecimal color reference, you can type the reference into the combo boxes next to the + and - buttons on the RGB tab. Web Intelligence applies the alternate row color to the table. In this example, the row color frequency is set to 2 Selecting and removing skins To select or remove a skin: 1. Make sure you are working in Report View. The Edit Report button is pressed in when you are in Report View and the report tabs of each report in the document are visible.
2. Select the table or table cell for which you want to format a background. 3. Right-click the table, then select Edit Format from the shortcut menu. The Properties tab displays the table or cell formatting options depending on which element you selected. (For more information on the Properties tab, see ”Java Report Panel interface tour” on page 29.) 4. Click the ... button next to Background image in the Appearance property sub-group. 5. Select Skin in the Background Image dialog box. 6.
The Properties tab displays the table or cell formatting options depending on which element you selected. (For more information on the Properties tab, see ”Java Report Panel interface tour” on page 29.) 4. Click the ... button next the the Borders property. 5. Use the buttons in the Border Editor dialog box to set the border style. Note: You can also set the borders of header cells, body cells and footer cells in the table properties. To do so, click the ...
• using drag and drop • specifying the size of cells on the Properties tab Note: If you want to hide cell contents on reports, you can set the cell width to 0.1 cm so that the cell width can be modified to display the cell contents later. Note: When you upgrade documents created with an earlier version of the product, Web Intelligence does not display cells whose width was originally set to 0.0 cm. (The cell contents remain hidden and the cell width cannot be modified.
The Properties tab displays the cell formatting options. (For more information on the Properties tab, see ”Java Report Panel interface tour” on page 29.) 3. Set the Width and Height properties in the Display properties sub-group. 4. If you want to set the cell to autofit, select Autofit Width and/or Autofit Height. NOTE: Some Web Intelligence functions are incompatible with AutoFit cells.
• add table headers or footers Showing or hiding table headers and footers To show or hide a table header or footer: 1. Make sure you are working in Report View. The Edit Report button is pressed in when you are in Report View and the report tabs of each report in the document are visible. 2. Select the table, right-click and select Edit Format from the menu. The Properties tab displays the table formatting options.
To set Web Intelligence to avoid page breaks in tables: 1. Select the table, right-click and select Edit Format on the menu. The Properties tab displays the crosstab formatting options. (For more information on the Properties tab, see ”Java Report Panel interface tour” on page 29.) 2. Select Position > Avoid page breaks in table. NOTE: You can include multiple tables and charts on a single report, and then align them neatly on a report using the relative alignment feature.
When you or other users view the report in Results View, the tables, columns, and rows appear or not as you specified. Avoiding duplicate row aggregation Web Intelligence automatically aggregates any rows that display the same set of dimensions. If you select the Avoid duplicate row aggregation option, each duplicate value is displayed in a separate table column or row. Avoiding duplicate row aggregation To avoid duplicate row aggregation: 1.
Note: Web Intelligence copies the text to the clipboard as tab-separated text. To copy and paste a table to another application as text 1. Select the table, right-click and select Copy as text on the menu. 2. Paste the contents of the clipboard into the target application. NOTE: If you choose Copy as text, then paste the table into the current Web Intelligence report, the table appears as a normal Web Intelligence table.
Inserting and formatting tables
12 Displaying information in free-standing cells Overview This chapter explains how to insert and format free-standing cells in a Web Intelligence document using the Java Report Panel.
includes sales results and margins for City lines, and ask managers to advise if the sales of City clothing lines should be discontinued. You include free-standing cells that display the date the results were last refreshed with the data on the database and a comment from you asking the regional managers for their advice. Inserting text, images, and formulas You can insert blank cells on reports and then insert text, an image, a hyperlink, or a formula into the blank cell.
5. Right-click the cell, then select Edit Format from the shortcut menu to display the Properties tab. The Properties tab lists the properties (arranged into groups) relevant to free-standing cells. 6. Do one of the following: If you want to display... Then... Text – a title or comment, • type the text into the General > Text property.
Web Intelligence displays the information you specified in the new cell. Inserting information about the report data You can insert special cells to show the following information about report data: • Last refresh date – the date that the results displayed in the report were updated with the data on the database. For more information about setting refresh options, see page 27. • Drill filters – the names of the objects that filter the results displayed on a drilled report.
The Drill Filter cell remains blank until you start Drill mode and perform a drill action on the drillable report. The Last Refresh Date cell displays the date the document was last refreshed immediately. Adding a text label before a free-standing cell To add a text label before a free-standing cell: 1. In Report View, click the Templates tab. The Template categories appear. 2. Click the + sign next to Free-Standing Cells. 3.
• Page Number/Total Pages • Total Number of Pages Page Number When you insert the Page Number cell, Web Intelligence displays the page number of the report page. Page Number/Total Pages The Page Number/Total Pages cell displays the page number of the report page followed by the total number of pages in the document. Total Number of Pages The Total Number of Pages cell displays the total number of pages in the document.
If you want to... then... position the page numbers in the page header or footer, • click the View Page Layout button • click the View Structure button • select the page header or page footer on the report • drag the page number cell template from the Template onto the header or footer cell See page 166 in this section for full information about each template. If you are working in Results View, the page numbers appear in the cell.
To set the formatting options for free-standing cells: 1. Make sure you are in Report View. 2. Right-click the cell and select Edit Format to display the Properties tab. 3. Set the cell formatting properties in the Properties tab. Web Intelligence applies the options to the selected cell. For more information about formatting cells, see ”Formatting tables and table cells” on page 148.
13 Inserting and formatting charts Overview This chapter explains how to create, edit, and format charts in Web Intelligence documents using the Java Report Panel.
Line charts Line charts connect specific data values with lines, either horizontally or vertically. Line charts are useful if you want to show trends or changes in data over time. There are five types of line charts: mixed, stacked, percent, 3D, and 3D surface. Area charts Area charts are line charts in which the area between the lines and axis are filled in. Area charts are useful if you want to emphasize the size of the total data in a report, as opposed to the changes in the data.
Once you have defined the query you go to Report View to define the chart(s) on report(s). The Edit Report button takes you to Report View. Edit Report is always visible on the Web Intelligence toolbar.
6. Select the chart template drag it onto an empty area of the report. In this example, the 3D Bar template is being dropped to the right of an existing table on a report. The empty template appears on the report. 7. Click the Data tab. 8. Drag a dimension or measure object onto the chart axis, where you want the results for that dimension or measure to appear on the chart. The template indicates whether you can drop dimension or measures on each axis.
9. Drop the dimension or measure onto the axis when the “Place dimension objects here” or the “Place measure objects here” ToolTip appears. Here, the [Year] dimension is being dropped onto the X-axis of a 3D Bar chart 10.To allocate more dimension and measure objects to each chart axis, repeat step 8. You must allocate objects to the X-axis and Y-axis for charts to display correctly. Allocating objects to the Z-axis is optional. 11.
12.Resize the chart by selecting the chart and then dragging the borders outwards. Drag the vertical borders to the right or the left to change the chart width. Drag the horizontal borders up or down to change the chart height. The finished chart appears.
For information on formatting charts and positioning charts on the report page, see ”Formatting charts” on page 180 and ”Positioning charts on the report page” on page 179. Changing existing tables and charts to different chart types You can change the way information is presented on reports by turning the tables and charts into different chart types. This enables you to apply different formats and decide which format communicates the information most clearly.
Dragging a different chart template onto a table or chart To drag a different chart template onto a table or chart: 1. Make sure you are in Report View. The Edit Report button is pressed in when you are working in Report View. 2. Click the Templates tab. If the Templates tab is not visible, click the Show/Hide Manager button on the Report toolbar to display the Templates tab. 3. Click the + sign next to Charts. 4. Click the + sign next to the template type you want to apply. 5.
onto the Data tab, and then drag and drop the objects you want to display on the chart from the Data tab onto each chart axis. Applying a different chart template to a table or chart using Turn To To apply a different chart template to a table or chart using Turn To: 1. Place your pointer on the border of the table or chart you want to modify. A gray border appears around the selected report block. 2. Right-click the selected table or chart. A shortcut menu appears. 3. Click Turn To.
NOTE: If the new chart type does not have data allocated to both the X-axis and the Y-axis, the chart will not appear when you view the report in Results View or in Drill mode. In this case, click the View Structure button on the report panel toolbar, and then click the Data tab and drag the appropriate dimensions or measures onto the axes on the new chart template. When you click View Results, the chart appears.
The chart is removed from the report. Working with charts You work with charts by setting their properties on the Properties tab. The Properties tab displays the properties of the selected report element. When you select a chart, the Properties tab displays the properties that are relevant to charts. For more information on the Properties tab, see ”Java Report Panel interface tour” on page 29. Setting page layout for charts You can set the page layout for each page in a Web Intelligence report.
2. Open the Page layout > Position property group on the Properties tab. 3. Click ... next to the Position property to open the Relative Position dialog box. 4. Select the distance of the upper left point of the chart in relation to another report element by entering • the number of pixels • the part of the other report element from which you want to measure the pixels (in the first drop-down list) • the report element from which you want to measure the pixels (in the second drop-down list) 5.
About 2D charts For example, 2D charts include an optional axis legend that lists the values displayed on the bars or lines. Chart title Y-Axis Label Data Legend title Y-Axis Values Legend values Floor X-Axis Label X-Axis Values In this example, a 2D bar chart includes the optional Z-axis. Including data on the Z-axis enables you to show an additional break down of the results displayed on the chart bars.
The following example shows a 3D bar chart. Chart title Y-axis label Chart data Right wall Y-axis values Floor X-axis label X-axis values Z-axis values Z-axis label In this example, the chart bars display sales revenue per quarter, per year. The [Sales revenue] measure is on the Y-axis, the [Quarter] dimension is on the X-axis, and the [Year] dimension is on the Z-axis. About pie charts Pie charts have a single axis displayed on the body of the pie. This is the Y-Axis.
Chart formatting options You can personalize the formatting properties of the following areas on a chart: • size – the width and height of the chart • title – insert, edit, and format a title for the chart • apply a 3D look to 2D charts • apply a background color • chart borders – specify border style and color • walls and floors – show/hide and specify formatting • axis legend – show/hide and specify formatting • axis labels – specify formatting (all charts), rename labels (all charts), show/hide the X-Axi
3. Press your pointer again, and then drag the border to resize the chart to the width you want. 4. To modify the height of the chart, place your pointer on the bottom border. 5. When your pointer turns to a double-arrow, press your pointer again and then drag the border to resize the chart to the height you want. Resizing a chart by specifying a measurement To resize a chart by specifying a measurement: 1. Right-click the chart and click Edit Format. 2.
information on creating custom colors, see ”Creating custom colors for chart elements” on page 197.) 13.Select the border style by clicking ... next to the Border style property and setting the border properties in the Define Border dialog box. Displaying charts with a 2D or 3D look You can format 2D charts with a 3D look. A 3D look shades the chart bars or pie segments, giving the 2D chart the appearance of being in relief.
Adding background colors You can add a background color to charts in Web Intelligence reports. The background is applied to the entire block. The background color fills the block behind the chart and the legend Adding background colors To add a background color to a chart: 1. Right-click the chart and click Edit Format. 2. Open the Appearance property group on the Properties tab. 3. Click the arrow next to Background color to display the color palette. 4.
Displaying chart floors and walls Floors and walls give depth to charts and help to set off the data displayed on the chart bars or lines. 3D charts have a floor, a left wall, and a right wall. 2D charts just have a floor. You can specify a color for the floor and walls. A light gray wall on a 2D bar chart Gray walls and floor on a 3D bar chart Selecting and formatting chart floors and walls To select and format the chart floor and walls: 1. Right-click the chart and click Edit Format. 2.
Note: Axis legends are not available for 3D Bar charts. You can set Web Intelligence to display the axis legend to either the left, right, or bottom of the chart. You can also hide the axis legend. Axis legend title Axis legend Values You can format the text, background, and borders of the: • axis legend title • axis legend values The following section explains how to show and format axis legends on charts. Showing or hiding axis legends To show or hide the axis legend: 1.
Formatting axis labels You can personalize the formatting of axis labels text, borders, and background colors. By default, the axis labels display the object name(s) of the object(s) on each axis of all chart types except pie charts and polar charts. Y-axis label X-axis label Z-axis label For example, the object allocated to the X-axis on this 3D bar chart is [Year] so, by default, the axis label is Year. You can replace the name of the object by a label you type.
2. Open the Appearance > X/Y/Z Axis > Label property group. 3. Select Show object name to show the object name on the label. 4. Type the text into Other label if you want to assign custom text to the label in place of the object name. 5. To format the text, Click ... next to Text Format to open the Text Format dialog box. 6. Select the font in the Font list. 7. Select the font style in the Font style list. 8. Select the font size in the Size list. 9.
• grid – opt to show or hide a grid on an axis Grid (the grid shows on both axes) Axis legend Axis scale (this axis scale is set to start at zero) Axis label Axis values Formatting axis value numbers and text To format axis value numbers and text: 1. Select the chart and click Edit Format. 2. Open the Appearance > X/Y/Z Axis > Values property group in the Properties tab. 3. Click ... next to the Number format property to display the Number Format dialog box. 4.
11.Select Underline to set the text style to underline. 12.Select Strikethrough to set the text style to strikethrough. 13.Select Wrap Text to wrap the text. 14.You can set many of the properties in the Text Format dialog box directly from the property list, where they appear beneath Text Format. Showing a specific range of axis values To show a specific range of axis values: 1. Right-click the report and click Edit Format. 2. Open the Appearance > X/Y/Z Axis > Scale property group on the Properties tab. 3.
NOTE: Automatic frequency must be unselected before you can type a custom frequency. Showing/hiding and formatting the grid for chart axes To show/hide and format the grid for a chart axis: 1. Right-click the chart and select Edit Format. 2. Open the Appearance > X/Y/Z Axis > Values property group in the Properties tab. 3. Select Show grid to show the grid. 4. Select Show markers to show the grid markers. 5. Click Grid color and select the grid color using the Color Palette or the Choose Color dialog box.
1, 3, 5, 7, 9 To get the next number in the sequence, you add 2 to the previous number. Logarithmic scales Logarithmic scales are based on multiplication rather than addition. In a logarithmic scale, the steps increase or decrease in size. Logarithmic scales are based on multiplication (or division). Consider, for example, the logarithmic sequence: 2, 4, 8, 16, 32 To get the next number in the sequence, you multiply the previous number by 2. We can say that this sequence represents ``base 2.
Showing/hiding data values To show/hide data values: 1. Right-click the chart and select Edit Format. 2. Open the Appearance > Data group in the Properties tab. 3. Select Show data to show the data. The data values are shown or hidden on the chart, according to your selection. The data values are hidden on this chart The data values are shown on this chart Specifying a color palette for the chart data To specify a color palette for the chart data: 1. Right-click the report and select Edit Format. 2.
7. To set all the colors in the palette to the same color, click Set All Colors. Varying the data markers for each result To vary the data markers for each result: 1. Right-click the chart and select Edit Format. 2. Open the Appearance > Data property group in the Properties tab. 3. Select Vary data markers. Note: You can only vary the data markers for each result on the following chart types: 2D Line charts, 2D Bar and Line charts, and Radar charts.
The following sections describe how you change the display of charts by setting chart properties. To access all chart properties 1. Select the chart. 2. Select the property groupthat contains the property you want to set. You access the properties in a sub-group by clicking the + next to the group. Creating custom colors for chart elements To create a custom color 1. Select a base color in the Swatches tab. 2.
2. To paste the chart to another part of the report, right-click where you want the chart to appear then click Paste on the menu. 3. To paste the chart into another application, paste the contents of the clipboard from within the other application. The chart appears as a picture in the open document if pasted to another application. NOTE: You can also copy a chart into another application by dragging and dropping it directly into the open document in the target application.
14 Formatting numbers and dates Overview This chapter describes the formats that you can apply to values displayed in cells and on chart axes. You can select from a range of predefined formats or create your own custom formats. This chapter tells provides information on: • default formats • applying predefined formats • defining custom formats Default formats You can change how values display in specific cells or on chart axes.
Applying a predefined format using the shortcut menu To apply a predefined format using the shortcut menu: 1. Make sure you are in Report View. The Edit Report button is pressed in when you are working in Report View. 2. Right-click a cell. For example, right-click a cell in a section, table, or chart. If you select a cell in the body of a table, Web Intelligence automatically selects all the body cells that display values for the same object.
5. Click OK. Web Intelligence applies the new format to the cell. Applying a predefined cell format using the Properties tab To apply a predefined cell format using the Properties tab: 1. Make sure you are in Report View. The Edit Report button is pressed in when you are working in Report View. 2. Click a cell. For example, click a cell in a section, table, or chart.
5. Click the Number Format button. The Number Format dialog box appears. 6. Click a format in the Format Type list. 7. The available formats for the format type you selected appear in the Properties pane. 8. Select the specific format you want to apply to the contents of the selected cell(s). 9. Click OK. Web Intelligence applies the new format to the cell(s). Defining custom formats You can use the Custom format type to define a customized number format for any cell.
You create a report which contains information on the sales revenue and margin for certain eFashion clothing lines. Some of the results are negative values. The margin for City Trousers line is a negative result You want to be able to spot any negative results quickly, so you create a custom number format that displays any negative values in red. You apply the custom format to the Sales Revenue column and Margin column of the table.
Formatting cells displaying number and currency data Character(s) Display(s) Example # The corresponding digit. If the number has less digits than the number of # characters used to specify the format, no leading zeros are inserted. ‘12345’ with the format: The corresponding digit. If the number has less digits than the number of 0 characters used to specify the format, a leading zero(s) is inserted before the number. ‘123’ with the format The grouping separator as defined by your locale.
Character(s) Display(s) Example [%]% Displays a percentage sign (%) after the result ‘0.5’ with the format: and multiplies the result by 100. % #,##0[%]% gives ‘50%’ The % sign after the result, but does not multiply the result by 100. ‘1’ with the format: A non-breaking space () ‘1234567’ with the format: #,##0% gives ‘1%’ # ##0 gives ‘1234 567’ 1, 2, 3, a, b, c, $, £, (and so on) The alphanumeric character. ‘705.15’ with the format: $#.#0 gives ‘$705.
Characters for formatting cells displaying date and time data Data Type Character Displays Example The number of the day in the month with no leading zeros. If the date for day is less than two characters, the date displays without a zero before it. The first day of a month with the format: The number of the day with leading zeros. If the date for day is less than two characters, the date displays with a zero before it. The first day of a month with the format: The name of the day abbreviated.
Data Type Character MM mmm mmmm Displays Example The number of the month with leading zeros. If the number for month is less than two characters, the number displays with a zero before it. ‘January’ with the format: The name of the month abbreviated. The first letter is capitalized. ‘January’ with the format: The name of the month in full. The first letter is capitalized. ‘January’ with the format: The last two digits for year.
Data Type Character HH:mm Displays Example The hour and minutes with leading zeros. ‘7.15 am’ with the format: HH:mm gives ‘07:15’ HH:mm:ss The hour, minutes, and seconds with leading zeros. ‘7.15 am’ with the format: HH:mm:ss gives ‘07:15:00’ mm:ss The minutes, and seconds with leading zeros. ‘07:15:03’ with the format: mm:ss gives ‘15:03’ Defining a new custom format To define a new custom format: 1. Make sure you are in Report View.
Text boxes appear for you to type your custom format(s). 6. Select a format listed in the Properties pane, and then edit the selected format by typing additional characters in one or more text boxes. Or Type a custom format in one or more text boxes. For example, if you want to create a custom format for Number values, type the custom format you want in the Positive, Negative, and Equal to Zero boxes.
NOTE: You cannot delete or edit custom formats. To change a custom format, you need to create a new custom format and apply the new format to the selected cell(s). Any custom formats not applied to cells in a document are deleted automatically when you close the Web Intelligence Java Report Panel. Applying a custom format already defined in a document To apply a custom format already included in a document: 1. Make sure you are in Report View.
15 Using sections, breaks, and sorts Overview This chapter describes how you can organize the information displayed in reports so that reports are easy to read and navigate. It provides information on the following: • using sections to group data on reports • using breaks to group data in tables • using sorts to organize sections and data on tables Using sections to group data on reports To make reports easy to navigate, you can divide the report into sections that group related data together.
You can include sections in the report map. This enables you to quickly navigate long reports section-by-section or to go directly to the section that has the data you want to analyze. The map displays a link to each section of the report The report is divided into sections by year. Each section displays values for a specific year. You can include subsections below sections. For example, you can divide a report into sections by year and then add a subsection by state.
What makes up a section? A section consists of a section cell and the section itself. The section cell is a free-standing cell that displays a value for a selected dimension (for example, state, customer, year, and so on). For example, if a report has a section on the year dimension and the database has data for the years 2001-2003, then there will be one section for year 2001 data, a second section for year 2002 data, and a third section for year 2003 data.
The following illustration shows a report with sections in Structure View. When you view a report in Structure View, each section is clearly indicated by a section divider.It is easier to format sections when you work in Structure View. The section divider is labelled, indicating which object is selected for the section. In this example the section is based on the [Year] dimension so, when the report is viewed in Results View, each section displays values for a specific year.
2. Select the header cell on the table that displays the name of the dimension. Or Select a body cell on the table that displays a value for the dimension. For example, if you want a section for each state, either select the header cell labelled “State” or select one of the cells that displays the name of a state. The name of the dimension is selected when you select the header cell A value for the dimension is selected when you select a body cell 3.
• If you are in Results View, a cell containing one value for each unique value for the added object appears above the table or crosstab. The data in the table or crosstab is automatically sorted according to the cell value. • If you are in Structure View, a cell is added above the table or crosstab. 4. If you are in Structure View, click View Results to see the results. Web Intelligence applies the section to the report and displays the results.
4. Set the section properties. If you want to... then... Name of the section with a different name from the name of the dimension selected for the section, type a name for the section in the General > Name property. Specify the component(s) of the section that will qualify the entire section as an “empty section,” if there is no data on the database for that component(s), Check the components in the drop-down list next to the Display > Hide section when empty property.
If you drop the cell outside the section dividers, a warning appears asking you to confirm that you want to place the cell outside the section. Or Right-click the section cell and click Edit Format on the menu, then set the Position > Left edge and Position > Top edge properties on the Properties tab. The section cell appears at the position you specified. Controlling page breaks for sections To control page breaks for sections: 1. Make sure you are in Report View.
To work in Results View, verify that the View Results button is pressed in. To work in Structure View, verify that the View Structure button is pressed in. 2. Select the section cell. 3. Select the Page Layout > Repeat on every new page property in the Properties tab. 4. If you are working in Structure View, click the View Results button to view the option applied to the results on the report. The section cell appears on each report page where part of the section is displayed.
You can format the text, background, borders, and page layout position of section cells. For information on how to do this, see ”Formatting free-standing cells” on page 167. Using breaks to group data on tables This section explains how to break up data in tables and crosstabs and describes the different options available to format and manage these breaks. What is a break? A break is a division in a result table in which data is grouped according to a selected value into self-contained parts.
• When you create a break on Year, the data remains as one block. The year values are repeated for each corresponding value of State and Sales Revenue. • When you create a section on Year, the data is no longer contained in one block, but divided into sections. One section for each value of Year. Each section is a separate block.
Inserting a break To insert a break: 1. Verify you are working in Report View. The Edit Report button is pressed in when you are working in Report View. 2. Click a cell in the column or row where you want to insert a break. The cell is highlighted. 3. Click the Insert/Remove Break button on the Reporting toolbar. Web Intelligence divides the table up into as many mini tables as there are unique values for the selected cell. Web Intelligence inserts a footer at the end of each break.
Using multiple breaks You can insert multiple breaks on a table or crosstab. You can also set a priority on the order of the breaks. The following example shows two tables that show sales revenue results for Q3 and Q4 in year 2002 and 2003. Both tables have breaks on [Year] and [Quarter]. The table on the left has a break on [Year] first and then a second break on [Quarter]. The table is broken into two mini tables for year 2002 and year 2003. The results for Q3 and Q4 display for each year.
Prioritizing multiple breaks To set a priority on a break: 1. Verify you are working in Report View. The Edit Report button is pressed in when you are working in Report View. 2. Verify that the View Structure button is pressed in. 3. Right-click the table that contains multiple breaks and select Edit Format on the menu. The Properties tab shows the properties relevant to a table. 4. Click the ... button next to the Breaks > Break priority property to display the Breaks dilaog box. 5.
• Page layout properties – how the breaks display on the report page Break display properties You can set the following display properties for a break: Format option When selected... Show break header A header is displayed for each part of the table, crosstab, or form when you insert a break. For example: break header Show break footer A footer is added after the last row for a table or column for a crosstab when you insert a break.
Page layout property Description Avoid page breaks in blocks Where possible, keeps each break section on the same page. This option is not taken into account when a block is larger than one page. Repeat header Repeats the header at the top of the table on every new page when a table goes over onto a new page. Repeat footer Repeats the footer at the bottom of the table on every new page when a table goes over onto a new page.
Available sort orders The following sort orders are available: Sort order Description Default This is sometimes referred to as the “natural” order.
Choose Ascending or Descending from the drop-down list next to the Sorts > Sort property in the Properties panel. To apply multiple sorts to the same table, repeat step 1. to step 2. For information about prioritizing the order of multiple sorts, see ”Prioritizing the order of multiple sorts on the same table” on page 228 below. Applying custom sorts To apply a custom sort 1. Select the section cell or table cells you want to sort. 2.
16 Ranking Data Overview This chapter describes ranking data in Web Intelligence. Ranking allows you to isolate the top and bottom records in a set based on a variety of criteria. For example, if you have a block showing countries and associated revenues, ranking allows you to rank the data in the block to show the top 3 countries only, based on the revenue they generate.
Note: When you create a ranking using the interface, Web Intelligence uses the Rank() function behind the scenes to assign ranking values. Creating a ranking using the Web Intelligence interface When you rank the data in a block by using the Web Intelligence interface, you tell Web Intelligence to sort and filter the data in the block according to the rank.
Ranking parameters The following table describes the parameters that you set in a ranking and how they affect the rank: Parameter Description Top/bottom When the calculation mode is Count, the ranking returns the top/bottom n records based on the measure specified in the Based on parameter. For example, the top 3 countries by revenue generated, the bottom 3 year/quarter combinations by revenue generated.
Parameter Description For Each The dimension on which the ranking is based (for example, country). If you do not specify this parameter the ranking is based on all the dimensions in the block. For example, if you have a block with Year and Quarter and you do not specify a dimension, Web Intelligence uses the values of the Based on measure generated by the year/quarter combination. Calculation mode The type of calculation used to create the ranking.
Parameter Value Based on Revenue Calculation mode Count This ranking gives the following result: Region Revenue 2005 5,000,000 2004 4,000,000 2003 3,000,000 To calculate this ranking, Web Intelligence: • sorts the records in descending order • displays the top 3 records Example: Rank the bottom 40% of regions by revenue To perform this ranking you set the following parameters: Parameter Value Top/Bottom Bottom n/n% 40% For Each Region Based on Revenue Calculation mode Percentage
• sorts the records in ascending order • works through the records until 40% of the total number of records are displayed Example: Rank the top regions whose cumulative revenue is less than or equal to 10,000,000 To perform this ranking you set the following parameters: Parameter Value Top/Bottom Top n/n% 10,000,000 For Each Region Based on Revenue Calculation mode Cumulative sum This ranking gives the following result: Region Revenue Central 5,000,000 North West 4,000,000 To calculate
Parameter Value Based on Revenue Calculation mode Cumulative percentage This ranking gives the following result: Region Revenue South East 1,000,000 South West 2,000,000 To calculate this ranking, Web Intelligence: • sorts the records in ascending order • works through the records until it reaches the record that causes the cumulative total of the measure, expressed as a percentage of the overall total of the measure, to exceed 30% • displays those records that do not cause the cumulative perce
The following table shows a tied ranking. The Top 3 ranking column shows how Web Intelligence ranks these records in a top 3 ranking; the Bottom 3 Ranking column shows how Web Intelligence ranks these records in a bottom 3 ranking. Dimensio n Measure Top 3 Ranking Bottom 3 Ranking A 10 4 1 B 20 3 2 C 30 1 3 D 30 1 3 In each case Web Intelligence includes records up to and including rank 3.
If you create a rank on a measure whose values depend on the data order, for example a measure that that uses the Previous() function or any of the running aggregate functions such as RunningSum(), Web Intelligence displays the #RANK error message in all cells in the block.
Ranking Data
17 Filtering the values displayed in reports Overview This chapter tells you how you can filter the data displayed in Web Intelligence reports to focus on the information that interests you for a specific business purpose. You do this by applying report filters.
the Accessories product line are displayed, and you filter the chart for state, so that only results for California are displayed.
You can filter multiple dimensions and measures in a report. For example, you can create another filter on the [Sales revenue] measure to focus the results displayed on the report to sales revenue totals that are higher than a specific company target: When you refresh the data again, Web Intelligence displays the information that corresponds to the filter(s). This report shows sales results greater than or equal to $300,000 during the year 2003. The Report Filters pane is shown.
What objects can I filter? You can define filters on any of the dimension, measure, details, and variables listed on the Data tab of the document. dimension detail measure variable For example, you can filter the [Year] dimension to return values for a specific year, filter the [Revenue] measure to return values for a range of revenue figures, or filter the [Postal Code] detail to return values for a specific postal area.
To obtain data that... for example to... select this operator... to create this filter...
To obtain data that... for example to... select this operator... for which a value was entered on the database, Is not Null customers with children (the children column on the database has a data entry), to create this filter... [Children] Is not Null Note: If you are building filters on the query, more operators are available. See ”Which operator should I choose?” on page 242.
How many filters can I include in a report? You can include one or multiple filters in a single Web Intelligence report. Using the Java Report Panel, you can apply filters at three levels within a report: • the entire report • sections and subsections • tables, charts, and forms Example: Using filters to compare sales revenue for Colorado and average sales revenue across all US states In this example, you are a sales manager for Colorado.
The difference between query filters and report filters Web Intelligence allows you to apply filters at two levels within a single document: • the query definition – this is the data defined in Query View, retrieved from the data source, and returned to the Web Intelligence document • the data displayed in the report – this is a subset of the data in the query definition that is displayed on a specific report within the document Filters you apply to the query definition are called query filters.
3. If you want to filter a block (that is, a table, chart, or form), then click the top edge of the block to select it. Or If you want to filter a section, and not just the section cell, click View Structure and then select either the start section or end section divider. Selecting a section for Quarter 4. Click the Add Quick Filter button on the Report toolbar. The List of Values dialog box appears. The values for the selected section or block are listed. 5.
You can apply multiple filters to the same report. See ”Applying multiple filters to a report” on page 251. For information about editing and removing report filters, see ”Editing and removing report filters” on page 254. Creating a custom filter using the Filter Editor To create a custom filter using the Filter Editor: 1. Make sure you are in Report View. The Edit Report button is pressed in when you are working in Report View. 2. Click the report tab of the report you want to filter.
The Report Filters pane displays the name of the selected part of the report. For example, if you select a table, the Report Filter pane displays the name of the selected table. By default, Web Intelligence gives each block the following name: block followed by a number (for example, Block1, Block2, Block3, and so on). To make it easier to select blocks for positioning and formatting you can give blocks meaningful names (for example, Crosstab: Monthly Sales, Pie Chart: Annual Hires, and so on).
Refer to ”Which operator should I choose?” on page 242. 4. Select Constant or Value(s) from list. The following table will help you decide which to choose: If... for example... then... the values on the dimension, measure, or detail you are filtering do not change often and you are sure of how to spell the value you want to filter; names of months or numbers for specific years, type a constant.
1. The next step depends on whether you selected Constant or Value: If you selected Constant... If you selected Value... Type the value(s), you want to retrieve, in the Constant box. Select the value(s), you want to retrieve, from the displayed List of Values and add them to the Values Selected box, by clicking the >> button. Depending on the operator you selected, type a single value: Depending on the operator you selected, select one value: ...or select multiple values: ...
Combining multiple filters on a report To combine filters on a report: 1. Create each filter. See ”Creating custom report filters” on page 246. By default Web Intelligence combines all the filters with the And operator. 2. You can leave the And operator or change the operator to Or. This table explains the difference between the And and the Or operators: You want to retrieve... for example... select...
To view all the filters on a report: 1. Make sure you are in Report View. The Edit Report button is pressed in when you are working in Report View. 2. Click on an the area of the report for which you want to see filters. For example, if you want to see how the report is filtered, click an empty area of the report background. If you want to see how a section, table, or chart is filtered, click the section, table, or chart. 3. Click the Properties tab, then click the Filters sub-tab.
If any filters are applied to the select area the filters icon appears. In this example, the report is displayed in Structure View. A section is selected. The selected section divider is highlighted. Filter icon 3. Place your pointer on the filter icon. The pointer becomes a hand. 4. Click the Filter icon. The Filter sub-tab appears in the left frame of the report panel. The filters are listed.
4. Double-click the filter you want to edit. The Filter Editor appears. The name of the filtered object is listed under Filtered Object. You edit the operator and operand type here. For full information on how to define the filter properties see ”Creating a custom filter using the Filter Editor” on page 248. Removing report filters To remove a report filter: 1. Make sure you are working in Report View. The Edit Report button is pressed in when you are in Report View. 2.
5. Select the filter you want to remove and drag it onto the Data tab. 6. If you are working in Structure View, click the View Results button. Web Intelligence removes the filter from the report and recalculates the results.
18 Using standard calculations Overview This chapter describes how you can insert standard business calculations in a Web Intelligence report.
Calculation type Icon Use to... Maximum Display the maximum value of the selected data. Percentage Display the selected data as a percentage of the total. The results of the percentage are displayed in an additional column or row of the table. Note: Percentages are calculated for the selected measure compared to the total results for that measure on the table or break. To calculate the percentage of one measure compared to another measure, you need to build a custom calculation.
The calculation drop-down list appears. Drop-down list showing available calculations 4. Select the calculation that you want to apply to the data in the column. If you insert the calculation into a vertical table, the calculation is applied to the column values. A new row is added at the bottom of the table. This row is called a footer. The aggregated value appears in the new row cell.
NOTE: If you insert a percentage calculation, the results of the percentage are displayed in an additional column or row of the table. If you are Structure View, the calculation applies to the column definition. The definition for the calculation appears in the new cell. NOTE: You can create a break on a dimension to better organize the data. When you split up the table by separating out a dimension, for example Year, the data is reorganized based on the new grouping.
The table below shows both a sum and average on Sales revenue. Sum of Sales revenue values Average of Sales revenue values Removing calculations You can remove a calculation from a table or cross tab at any time. Removing a calculation from a table or crosstab To remove a calculation from a table or crosstab: 1. Make sure you are in Report View. The Edit Report button is pressed in when you are working in Report View. 2. Right click the table or crosstab footer that contains the calculation.
Using standard calculations
19 Using alerters to highlight key results Overview Alerters enable you to highlight specific ranges of results in a format that makes those results stand out on reports.
For example, you can create an alerter to highlight any sales revenue results that exceeded your quarterly sales revenue target of $900K. Each time you refresh the report data, the sales revenue results equal to or greater than $900K will be highlighted in yellow.
The object (or cell contents), the operator, and the operand make up the condition that determines whether the formatting will be applied to the report cells. When you apply the alerter to a table column/row or cell, Web Intelligence compares the condition you defined to the results on the selected column/row or cell, and displays any results that meet that condition in the format you specified. How many conditions can you include in an alerter? An alerter can contain multiple conditions.
For example, in a single alerter defined for [Sales revenue], one sub-alerter can highlight high results in green and a second sub-alerter can highlight low results in red. You can include a maximum of eight sub-alerters in an alerter. sub-alerter 1 sub-alerter 2 Note: You can include a maximum of eight sub-alerters in an alerter. For full information, see ”Adding sub-alerters” on page 282.
You can also define alerters that display a text or formula, an image, or a hyperlink. In this case, the results that meet the condition defined in the alerter will be replaced by the formatting. If you want the results to be visible, you can apply the alerter to a blank table row or column next to the cells that display the corresponding values.
Creating alerters You create alerters by defining the conditions and formatting you want to highlight report results that meet specific business criteria. For example, you can highlight results that fall below a business target of $10K in red. Using alerters makes important results stand out on reports. You can also define alerters that will insert text comments or hyperlinks to related information into report cells automatically when results hit or miss your business targets.
The Alerter Editor appears. 5. In the Alerter name text box, type a meaningful name for the alerter. For example, if you are creating an alerter to highlight high revenue results for product lines, you could call the new alerter High Earners. 6. If wanted, you can type a description of the alerter into the Description text box. This will help other users who modify the document, understand what the alerter signifies. You are ready to specify the condition for the alerter.
If the cell(s) on which you want to create an alerter, contains date or numeric type data (for example, a date or a calculation) you need to select an object or variable instead of Cell contents. This is because, Web Intelligence considers any value you type into the Operand(s) text box as a character string. (For information on how to specify the operand, see step 2.) 1. Click the arrow to the right of the Operator drop-down list box and select an operator from the list.
If the “Filtered object or cell” text box displays... Then... a measure object, • Either type a value into the Operand(s) text box. Or Click the ... button to the right of the Operand(s) text box and select an object or variable from the list. a dimension object, • Either type a value into the Operand(s) text box. Or Click the ... button to the right of the Operand(s) text box, and then... • To select a value on the same dimension, click Select Values, then select the value(s) and click OK.
If the “Filtered object or cell” text box displays... Then... a dimension object, • Either type a value into the Operand(s) text box. Or Click the ... button to the right of the Operand(s) text box, and then... • To select a value on the same dimension, click Select Values, then select the value(s) and click OK. Or To select a different object, Select an object or variable, then select an object or variable from the list and click OK. • Click OK again.
1. If you want to add another condition to the alerter, click the + button to the right of the Format button and specify the second condition in the new row that appears beneath the first condition. (For full information, see ”Applying multiple conditions” on page 280.) Click here to add a condition Step 3: Personalizing the formatting of cells with alerters If you want to modify the formatting properties, click Format then follow the information in ”Formatting alerters” on page 275.
1. To add a sub-alerter, click Add Sub-Alerter. Add sub-alerter 2. Define the condition(s) of the new sub-alerter in the same way as for the first sub-alerter. For full information, see ”Step 2: defining conditions” on page 269, above. You can leave the current format defined by Web Intelligence for the new sub-alerter or define your own formatting properties 3. If you want to modify the formatting properties, click Format then follow the information in ”Formatting alerters” on page 275.
2. The new alerter is added to the list of alerters contained in the document. 3. To apply the alerter, verify that a table column, table row, free-standing cell, or section cell is selected on a report, and that the check box next to the alerter is checked. 4. To close the Alerters dialog box, click OK. The alerter is applied to the report results, when you view the report in Results View. If you are working in Structure View, click the View Results button on the report panel toolbar.
Selecting cell format properties for an alerter To select cell format properties for an alerter: 1. Verify that an alerter is selected and that the Alerter Editor is open. If the Alerter Editor is not currently open, click the Alerters button on the report panel toolbar, and then select the alerter you want to edit from the list of alerters. 2. Click Format. The Alerter Display dialog box appears. 3.
To include an image, check Image URL, type the file name or URL for the image file in the URL text box, and then select the appropriate image display format and position options. (For full information about how to specify file names and URLs for images, see ”How you display images in reports” on page 311). Or To include a skin, select Skin and then click the arrow next to the drop-down list box and select one of the listed skins. 5. To close the Alerter Display dialog box, click OK.
1. Verify that an alerter is selected and that the Alerter Editor is open. 2. Click Format. The Alerter Display dialog box appears. 3. Click the button to the right of Number format.
The Number Format dialog box appears. 4. Click the appropriate format type in the Format Type list. (See ”Applying predefined formats” on page 199 for information about each format.) Or Click Custom, and then define your own custom format. For full information about custom formats, see the ”Quick reference to characters for creating custom cell formats” on page 203. 5. Click OK. The Alerter Display dialog box reappears. 6. To save your modifications, click OK. The Alerter Editor appears. 7.
The modifications to the alerter are applied when you view the report in Results View. If you are working in Structure View, click the View Results button on the report panel toolbar. For more information about activating and deactivating alerters, see ”Switching alerters on or off” on page 287. Applying multiple conditions You can apply multiple conditions in a single alerter.
3. Define the additional condition by specifying the appropriate Filtered object or cell, the Operator, and the Operand(s) as you did for the first condition. The new condition for the alerter appears here For step-by-step instructions for building conditions, see ”Step 2: defining conditions” on page 269. 4. If you want to add another condition, click the + button below the new condition you just added, and define the additional condition in the new row. 5. To save the alerter, click OK.
2. Click the - button on the right of the condition you want to remove. Click here to remove a condition Web Intelligence removes the condition. 3. If necessary, change the name of the alerter in the Alerter name text box, to reflect your modification. Alerter name text box 4. To save the alerter, click OK. The Alerters dialog box appears again. The alerter is listed. 5. To apply the modified alerter, verify that the check box before the alerter is checked.
You want to see clearly which accessories have generated sales revenue that falls below, within, or above your financial target of between $1 - 5K.
The Alerter Editor appears. 2. Click Add Sub-Alerter. A new sub-alerter section appears on the Alerter Editor. New sub-alerter section 3. Specify the condition and formatting for the sub-alerter in the usual way. For step-by-step instructions on specifying conditions and formatting for alerters, see ”Step 2: defining conditions” on page 269 and ”Formatting alerters” on page 275. 4. If you want to add another sub-alerter, click Add Sub-Alerter, and then repeat step 3. above. 5.
The Alerters dialog box appears again. Apply alerter check box 6. To apply the alerter, verify that the check box before the alerter is checked. The active alerter will only be applied to the parts of the report that are selected. You can also apply the alerter to other cells on the report or to other reports in the document. See ”Switching alerters on or off” on page 287. 7. To close the Alerters dialog box, click OK. The alerter is applied when you view the report in Results View.
The Alerter Editor appears. 2. Click Remove Sub-Alerter. The sub-alerter pane is removed. 3. Click OK. The Alerters dialog box appears again. 4. To apply the alerter, verify that the check box before the alerter is checked.
The active alerter will only be applied to the parts of the report that are selected. You can also apply the alerter to other cells on the report or to other reports in the document. See ”Switching alerters on or off” on page 287. 5. To close the Alerters dialog box, click OK. The alerter is applied when you view the report in Results View. If you are working in Structure View, click the View Results button on the report panel toolbar.
4. Check the check box next to the alerter you want to apply to the selected cell(s). 5. Click OK. The alerter is applied to the selected cell(s), when you view the report in Results View. If you are working in Structure View, click the View Results button on the report panel toolbar. Deactivating an alerter To deactivate an alerter: 1. With a document open in the Java Report Panel, verify that you are in Report View. The Edit Report button is pressed in when you are in Report View. 2.
4. Uncheck the check box next to the alerter you want to deactivate on the selected cell(s). 5. Click OK. The alerter is removed from the selected cell(s), when you view the report in Results View. If you are working in Structure View, click the View Results button on the report panel toolbar.
There are two ways to access the list of alerters and re-order them: Via the Alerters button Via the Properties tab • Click the Alerters button on the report panel toolbar. • Click the Properties tab, then click the Alerters sub-tab. The Alerters dialog box appears. • Verify that one or more alerters are activated for the selected cell(s) The alerter check box(es) is checked when an alerter is activated. • Select the activated alerter you want to move.
4. Select the alerter you want to move. 5. Click the Up arrow to move the alerter before the preceding alerter. Or Click the Down arrow to move the alerter after the following alerter. 6. To close the Alerters dialog box, click OK. Web Intelligence applies the new order for the alerters to the selected cell(s), when you view the report in Results View. If you are working in Structure View, click the View Results button on the report panel toolbar.
If any alerter(s) are applied to the selected report cell(s) then those alerters are checked. 3. Select the alerter you want to edit, and then click Edit. The Alerter Editor appears. You can edit the condition and formatting properties of the alerter here. For full information about defining the condition(s) and formatting for alerters, see ”Step 2: defining conditions” on page 269 and ”Selecting cell format properties for an alerter” on page 276. Duplicating an alerter To duplicate an alerter: 1.
3. Select the alerter you want to duplicate, and then click Duplicate. The duplicate alerter appears in the list of alerters. By default, the duplicate alerter has the name of the original alerter followed by a number (n). The duplicate of the “Champions” alerters appears at the end of the list of alerters To rename the alerter and modify its condition(s) and formatting, you need to use the Alerter Editor. 4. Click Edit.
The Alerter Editor appears. 5. In the Alerter name text box, type a meaningful name for the alerter. You can edit the condition and formatting properties of the alerter now. To see how to do this, see ”Step 2: defining conditions” on page 269 or ”Formatting alerters” on page 275. Removing an alerter To remove an alerter: 1. Verify that you are in Report View. The Edit Report button is pressed in when you are in Report View. 2. Click the Alerters button on the report panel toolbar.
4. To close the Alerters dialog box, click OK. The alerter is removed from the document. Using formulas to create advanced alerters You can build advanced alerters using the Web Intelligence formula language. This is useful if you want to include formulas within the definition of the alerter.
• Report cells containing sales revenue results that are less then 20% above the average revenue, display in blue. Note that this alerter covers values also covered by the first alerter. For example, if the average is 100, then 79 is both > 80% below the average and < 20% above the average. In this case, the first alerter takes precedence. • Report cells containing sales revenue results that are greater than 20% above the average revenue, display in green.
Building the condition for the alerter using the formula language To build the formula for an alerter, you need to include the following elements: []"" Notice that the object name separators before and after the object name are [ ] and that there are quote marks before and after the value.
In this example, you want to flag products that have generated sales revenue of over 20% of the annual sales revenue average, in just one quarter. First, using the Formula option in the Alerter Editor, you build the following alerter: Then, using the formula pane in the Alerter Display dialog box you specify the character string “High performer” for the cell format: This is the format that Web Intelligence will apply to the report cells where results meet the condition defined in the alerter.
When the alerter is applied to the quarterly sales revenue results per line, the report looks like this: Using the formula language, you can create alerters that display a text string, objects or variables, formulas, or URLs that include the OpenDocument function to link to other documents on the repository. For step-by-step instructions on how to do this, see ”Using the formula language to display a text or formula” on page 302.
The Alerter Editor appears. 2. In the Alerter name text box, type a meaningful name for the alerter. For example, if you are creating an alerter to highlight high revenue results for product lines, you could call the new alerter High Earners. 3. If wanted, you can type a description of the alerter into the Description text box. This will help other users understand what the alerter signifies. You are ready to specify the condition for the alerter using the formula language. 4. Check the Formula check box.
6. Specify the condition(s) for the alerter. (How you do this depends on whether you are using the Formula Editor or the Formula Pane: If you are using... then... the Formula Editor, • Drag-and-drop or double-click the object(s) and operator(s) you want in the condition, from the Available Objects and Available Operators lists. • Type the value(s) you want in the condition. Ensure you insert a double quote before the value and another double quote after the value.
3. If you want to modify the formatting properties already selected for the alerter, click Format, then follow the information in ”Formatting alerters” on page 275. 4. Click OK. The Alerter Editor closes and the Alerters dialog box reappears. 5. Click OK. 6. To close the Alerters dialog box, click OK. Web Intelligence applies the new order for the alerters to the selected cell(s), when you view the report in Results View.
The Alerter Display dialog box appears. 3. The next step depends on what you want Web Intelligence to display on the report cells where the results meet the condition(s) in the alerter: If you want to display... then... text, • Type the text into the Formula Pane, and then click the Validate button. NOTE: Do not include quote marks before or after the text.
If you want to display... then... a formula, • If you want to use the Formula Editor, click the Formula button, then build the formula by selecting objects, operators, and functions, and click OK. Or If you want to use the Formula Pane, type the Equals sign (=) into the Formula Pane, then type the formula.
The Alerter Editor reappears. This formula displays the text message “Below $100K” in the report cells that meet the condition specified in the alerter 2. Click OK. The Alerters dialog box reappears. 3. To close the Alerters dialog box, click OK. The modifications to the alerter are applied when you view the report in Results View. If you are working in Structure View, click the View Results button on the report panel toolbar.
Using alerters to highlight key results
20 Creating custom calculations Overview Custom calculations allow you to add additional calculations to your report beyond its base objects and the standard calculations provided by Web Intelligence. You add a custom calculation by writing a formula that Web Intelligence evaluates when you run the report. A formula can consist of base report variables, functions, operators and calculation contexts.
The first way is more suitable for experienced users. If you are not familiar with formulas, you should use the Formula Editor to build them. The Formula Editor is an interface that allows you to select the different components of your formula (that is, operators, objects, and functions) and add them to it. Using the Formula toolbar You display the Formula toolbar by clicking the Show/Hide Formula Toolbar button. Now, when you select a cell, its formula appears in the Formula toolbar. a b c d a.
1. Add an additional column to the right of the Quantity Sold column by selecting this column on the table, and then clicking Insert Column After on the toolbar. Click this arrow to display the insert column and insert row menu options 2. Click Show/Hide Formula Toolbar to display the Formula toolbar. To enter a formula by typing you type the formula directly into the Formula toolbar. 3. In the Formula toolbar, type the formula.
• Drag report objects onto the Formula toolbar. When you release an object, it appears in the Formula toolbar as if you had typed it directly. NOTE: When you drag a formula component to the Formula toolbar, it appears at the current cursor position. Using the Formula Editor You can use the Formula Editor to enter formulas in the Formula toolbar. The Formula Editor is a graphical interface that you use to build formulas. It contains all report objects, functions and operators that you can use in a formula.
The Formula Editor shows the report objects, functions and operators that you can use to build a formula. NOTE: To make your formula easier to read, you can use the Ctrl+Enter or Alt+Enter keys to insert line breaks. Building a formula using the Formula Editor To build a formula, using the Formula Editor: 1. Select the cell on the report where you want to include the formula. You can select a table cell or a free-standing cell. 2. Display the Formula toolbar and the Formula Editor.
About functions A custom calculation might consist of report objects only, for example [Sales Revenue]/[Number of Sales] However, calculations often include functions as well. A function is an operation that receives zero or more values as input and returns output based on those values. For example, the Sum function totals all the values in a measure and outputs the result. The formula Sum([Sales Revenue]) outputs a total of sales revenues.
Conditional operators Conditional operators determine the type of comparison to be made between values. The following table describes them: Operator Description = Equal to > Greater than < Less than >= Greater than or equal to <= Less than or equal to != Not equal to You use conditional operators in conjunction with If, as in: If ([Revenue] >= 10000;‘High’;‘Low’) which returns “High” for all rows where the revenue is greater than or equal to 10000 and “Low” for all other rows.
Defining the calculation context Before you can define a specific calculation context, you need to understand how calculation contexts work. What are calculation contexts? Calculation contexts give you more control over how a formula or measure is evaluated. To understand them, you need to be familiar with basic report concepts.
Note: For more information on the terms input context and output context used in this section, see ”Modifying the default calculation context using extended syntax” on page 318 Default contexts in a vertical table A vertical table is a standard report table with headers at the top, data going from top to bottom and footers at the bottom. The default contexts in a down table are: When the calculation is in the...
Default contexts in a crosstab A crosstab displays data in a matrix with measures appearing at the intersections of dimensions. The default contexts in a crosstab are: The calculation is in the... The input context is... The output context is... Header The dimensions and measures used to generate the body of the block. All the data is aggregated, then the calculation function returns a single value. Body of the block The dimensions and measures used to generate the body of the block.
The report below shows the default contexts in a crosstab: Default contexts in a section A section consists of a header, body and footer. The default contexts in a section are: The calculation is in the... The input context is... The output context is... Body All the data is aggregated, then the calculation function returns a single value. The dimensions and measures in the report, filtered to restrict the data to the section data.
Default contexts in a break A break consists of a header, body and footer. The default contexts in a break are: The calculation is in the... The input context is... The output context is... Header Current instance of the break. All the data is aggregated, then the calculation function returns a single value. Footer Current instance of the break. All the data is aggregated, then the calculation function returns a single value.
Specifying dimensions in input and output contexts Input and output contexts consist of lists of dimensions. These lists must always be enclosed in parentheses, even if the list contains only one dimension. For example, a context must be specified as ([Year];Quarter]) or ([Year]). Example: Specifying an input context In the examples given in ”What are calculation contexts?” on page 314, the input contexts consist of (Year) and (Year, Quarter) respectively.
What if you want to show the minimum revenue by year in a block with no break? You can do this by specifying the output context in a formula. In this case, the formula looks like this: Min ([Revenue]) In ([Year]) That is, the output context appears after the parentheses of the function whose output context you are specifying. In this case, the output context tells Web Intelligence to calculate minimum revenue by year.
Look at the following table: Notice that the figures in the Min by Year column are the same as those in the block footers in the example ”Specifying an output context” on page 319, even though this block contains an additional dimension, Month. This is because the formula for Min by Year explicitly excludes Month from the input context.
output context). If you had not explicitly specified the input context, the table would look like this: In this case, the input context is now (Year, Quarter, Month), so Web Intelligence calculates revenues by year, quarter and month before outputting the smallest revenue that occurs in the Sales Revenue column for each year. For example, 173,756.40 is the smallest value that appears in the Sales Revenue column in 2001, so this value appears in the Min by Year column in all rows where the year is 2001.
formula’s “break” occurs at each year/quarter change, rather than each year change. As a result, the block is as follows: In words, this formula tells Web Intelligence to calculate revenues by year, quarter and month, then output the smallest of these revenues that occurs in each year/quarter combination”. Extended syntax context operators In the examples so far, you specified input and output contexts by using the word In, then listing the dimensions to be included in the context.
In this example you have a report showing Year and Sales Revenue. Your data provider also contains the Quarter object but you do not include this dimension in the block. Instead, you want to include an additional column to show the maximum revenue by quarter in each year.
The ForEach context operator The ForEach operator adds dimensions to a context.
(Year; Quarter). Therefore, you can remove Quarter from the input context by specifying ForAll ([Quarter]) in the formula, which looks like this: Sum([Sales Revenue] ForAll ([Quarter])) Note that you can use the In operator to achieve the same thing; in this case the formula is: Sum([Sales Revenue] In ([Year])) This version of the formula explicitly specifies Year as the context, rather than removing Quarter to leave Year.
You have a report showing Year, Quarter and Sales revenue. The report has a column, Report Total, that shows the total of all revenue in the report. The formula for the Report Total column is Sum([Sales revenue]) In Report. Without the Report keyword, this column would duplicate the figures in the Sales Revenue column because it would use the default output context ([Year];[Quarter]). For more information on default contexts, see ”Default calculation contexts” on page 314.
Sum ([Sales Revenue]) In Section The figure in the Section Total column is the total revenue for 2001, because the section break occurs on the Year object. Without the Section keyword this column would duplicate the figures in the Sales revenue column, because it would use the default output context ([Year];[Quarter]). For more information on default contexts, see ”Default calculation contexts” on page 314.
Without the Break keyword this column would duplicate the figures in the Sales revenue column, because it would use the default output context ([Year];[Quarter]). For more information on default contexts, see ”Default calculation contexts” on page 314. The Block keyword The following table describes the dimensions referenced by the Block keyword depending on where it is placed in a report: The Block keyword often encompasses the same data as the Section keyword.
and the First Half Average column has the formula Average ([Sales revenue]) In Block. You can see how the Block keyword takes account of the filter on the block. The Body keyword The following table describes the dimensions referenced by the Body keyword depending on where it is placed in a report: When placed in... References this data...
You have a report showing Year, Quarter and Sales revenue, with a break on Year. The report has a section based on Year and a break on Quarter. The Body column has the formula Sum ([Sales Revenue]) In Body. The Body keyword gives a different result depending on where it is placed The totals in the Body column are the same as those in the Sales revenue column because the Body keyword refers to the data in the block.
tells Web Intelligence to remove Year and Quarter from the output context; in other words, to calculate a grand total, because there are no other dimensions in the report. (See ”The ForAll context operator” on page 325 for an explanation of ForAll.) The formula then divides each revenue by the grand total to give its percentage of the total. Although you can use ForAll in this situation, it is much better to use the Report keyword.
is 2, it means “the total revenue of all month 2s”. As a result, the percentages are not the percentages you expect. Examples of useful formulas Example: Calculating a percentage using the Percentage function Web Intelligence has the Percentage function for calculating percentages. This function calculates the percentage of a number in relation to its surrounding context. For example, the following table shows revenues by year and quarter.
If the Percentage cell is placed outside the table but still inside the section, the surrounding context becomes the total revenue. In this case the Percentage function calculates the total revenue for the section as a percentage of the total overall revenue. Example: Calculating a percentage using the Sum function You can gain more control over the context in which a percentage is calculated by using the Sum function.
You have a report showing Year, Quarter and Sales revenue. The State object also appears in the report data, although it is not displayed. When the user runs the report they are presented with a prompt and they must choose a state. You want to show the state that they have chosen in the report title.
Creating custom calculations
21 Web Intelligence functions Overview This chapter looks at the functions available in Web Intelligence. You often use functions when creating Web Intelligence custom calculations. For more information on custom calculations, see ”Creating custom calculations” on page 307. This chapter covers the following topics: • what is a function? • using functions • function examples and descriptions What is a function? In mathematics, a function is a rule relating different values or sets of values.
This description of a function’s inputs and outputs it known as its prototype. You see a brief description of a function prototype when you select the function in the Formula Editor. For example, if you select the Abs function, you see the following display: You can get a much more detailed description of the function, including its prototype and examples of its use, by clicking the More on this function after you have selected the function in the Variable Editor.
For more information on using functions as part of custom calculations, see ”Creating custom calculations” on page 307. You can see a full list of the functions available in Web Intelligence in the Formula Editor, or by referring to the Index of Web Intelligence Functions in the online help. For more detail on the Variable Editor, see ”Creating, editing, and deleting variables” on page 343.
If the Percentage cell is placed outside the table but still inside the section, the surrounding context becomes the total revenue. In this case the Percentage function calculates the total revenue for the section as a percentage of the total overall revenue. Example: Calculating a percentage using the Sum function You can gain more control over the context in which a percentage is calculated by using the Sum function.
The If function receives three inputs: a condition that returns true or false, a value to output if the condition is true and a value to output if the condition is false. For example the formula If([Sales Revenue]>2000000;"High Revenue";"Low Revenue") returns “High Revenue” if the value of [Sales Revenue] is greater than 2000000, “Low Revenue” otherwise. The condition [Sales Revenue]>2000000 is true in the first case, false in the second.
Web Intelligence functions
22 Saving formulas as variables Overview ”Creating custom calculations” on page 307 describes Web Intelligence custom calculations and using formulas as part of calculations. You can create formulas directly in a report cell, but it is often better to save a formula as a variable. By saving a formula as a variable you can reuse it throughout the reports in a document without the need to continually retype it. This chapter shows you how to save formulas as variables and use them in reports.
• using the Variable Editor – you create the formula and save it as a variable at the same time NOTE: You can use the Create Variable dialog box to change an existing report formula to a variable. Creating a variable using the Create Variable dialog box To create a variable using the Create Variable dialog box: 1. Create your formula in the Formula toolbar (see ”Creating formulas” on page 307) or select an existing formula in the report. Create Variable 2. Click Create Variable.
The variable appears in the list of report variables. You can now drag the variable into your report just as you do with objects. Saving variables using the Variable Editor To save a formula as a variable using the Variable Editor: 1. Click Show/Hide Variable Editor on the report panel toolbar. The Variable Editor appears. 2. Type your formula in the Formula pane or use the functions and operators on the Functions and Operators tab to build the formula.
5. If you select Detail, an Associated Dimension box appears. Click ... next to the box to open the Objects and Variables dialog box and select the dimension you want to associate with the detail. (For more information on associating details with dimensions, see ”Associating details with dimensions” on page 346.) 6. Click OK. The variable appears inside the Variables folder on the Data tab.
You can type this variable name into a formula or drag the variable to the Formula toolbar as you would for any report object. Example: Create a formula to return a statistical variance Variance is a statistical term. The variance of a set of values measures the spread of those values around their average. Web Intelligence has the function Var() that calculates the variance in one step, but manual calculation of variance provides a good example of how to simplify a complex formula using variables.
In this example, your initial report looks like this: Creating an Average Sold variable To create an Average Sold variable: 1. Click Show/Hide Variable Editor. The Variable Editor appears. 2. In the Formula pane, type the formula: Average([Quantity Sold] In ([Quarter])) In Report Or Double-click items on the Data, Functions, and Operators tabs to the Formula pane to build the formula. 3. Set the formula qualification to Measure. 4. In the Name text box, type: Average Sold 5. Click OK.
or double-click items on the Data, Functions, and Operators tabs to the Formula pane to build the formula. 3. Set the formula qualification to Measure. 4. In the Name text box, type: Difference Squared 5. Click OK. Creating the Variance variable To create a Variance variable: 1. Click Show/Hide Variable Editor. The Variable Editor appears. 2.
Saving formulas as variables
23 Troubleshooting formulas Overview This chapter describes the errors that Web Intelligence generates when you create a formula that contains an error. All formula error messages begin with ‘#’ and appear in the cell in which the formula is placed. The errors are: • #DIV/0 • #MULTIVALUE • #OVERFLOW • #SYNTAX • #INCOMPATIBLE • #RANK • #DATASYNC • #CONTEXT • #SECURITY • #RECURSIVE • #ERROR #DIV/0 #DIV/0 occurs when a formula tries to divide a number by zero, which is mathematically impossible.
You have a report showing Country, Resort and Revenue and you add a cell to the report containing the formula [Revenue] ForEach ([Country]). This cell returns #MULTIVALUE because Country has two values in the report: ‘US’ and ‘France’. One cell cannot display the revenues for both the US and France. Placed outside the table, a cell containing revenue can only aggregate the revenues in the table in some way (for example by summing or averaging them).
occur either when you use the Rank dialog box to create a ranking, or when you use the Rank() function. Example: Ranking on running average or previous values If you attempt to rank a block on a column that contains the Previous() function or any running aggregate function, the entire block returns #RANK.
#RECURSIVE #RECURSIVE occurs when Web Intelligence cannot make a calculation due to a circular dependency. Example: Using the NumberOfPages() function If you place the NumberOfPages() function in a cell whose Autofit Height or Autofit Width properties are set, Web Intelligence returns #RECURSIVE because the placing of this formula in an autofit cell creates a circular dependency.
24 Setting up documents for drill analysis Overview This chapter explains how you can make reports in Web Intelligence documents drillable, so that you and other users can analyze the detailed data that makes up the results displayed on tables and charts.
You work for a US national fashion retail chain, and are in charge of sales of accessories, outerwear and overcoat clothing lines in western states. You see that revenue is much higher for Q3 than the other quarters. To understand why, you drill down to look at the factors behind the result. You see that jewelry sales escalated in July (month #7). Setting your drill options Before you begin a drill session, you specify how reports will change each time you drill by setting your drill options in InfoView.
• Start drill on the existing report – the current report becomes drillable and when you end drill mode, the report displays the drilled values. • Start drill on a duplicate report – InfoView creates a duplicate of the current report and you drill on the duplicate. When you end drill mode, both the original report and the drilled report remain in the document.
The following example shows how each option affects a report as you drill down on Q4 results in a table to analyze detailed results per month in Q4. In this example, Synchronize drill on report blocks is selected in your view options, so In this example, Synchronize drill on report blocks is not selected in your view options so only the table displays the drilled For more information on how drilling affects each report block, see ”Synchronizing drill across multiple tables and charts” on page 378.
For example if you drill on the 2003 date for year, the values for Q1, Q2, Q3, and Q4 are the quarters for year 2003. This means that the quarterly values you drilled to are filtered by 2003. The Drill toolbar displays the value that filters the drilled results The Drill toolbar allows you to select other values in order to filter the results differently. For example, if you use the Drill toolbar to select 2002, the Q1, Q2, Q3, and Q4 results displayed on the drilled block would be quarters for year 2002.
Setting your drill options To set your drill options: 1. On the InfoView toolbar, click Preferences. 2. Click the Web Intelligence Document Preferences tab. The Web Intelligence Document Preferences page appears. 3. In the Select a view format section, select the view format that corresponds to the tasks you want to perform on reports: If you want to... perform drill analysis only, perform drill analysis and interactive reporting, then select...
1. In the General drill options section, select the options that you would like to apply during your drill sessions: If you want Web Intelligence to... prompt you whenever a drill action requires a new query, so that Web Intelligence can extend the scope of analysis and add more data to reports, then select... Prompt if drill requires additional data For more information, see ”Being prompted if a drill action requires additional data” on page 357.
You can select a predefined level for the scope of analysis or define your own custom level. • Predefined – the scope of analysis is the same for each dimension in the report. For example, if you set the scope of analysis at two levels, each dimension in the report will contain additional data two levels down. • Customized – you specify the dimensions in each class to which you want to be able to drill.
annual results to analyze details for quarter, month, and so on. However, the universe designer can also define custom hierarchies. The Time hierarchy The Time class For information on designing universes, see the Designer’s Guide. Viewing drill hierarchies on the universe on which a document is created To view the drill hierarchies on the universe on which a document is created: 1. Open a document in Edit mode using the Java Report Panel. 2. Verify you are in Query View.
The hierarchies organize the dimensions with the most summary data at the top and the most detailed data at the bottom. Note: A dimension can belong to several hierarchies. When you drill a result on a dimension that belongs to more than one hierarchy, Web Intelligence prompts you to select the drill path. See ”Choosing a drill path when more than one is possible” on page 373.
Note: Before you begin a drill session in Web Intelligence, you need to set your drill options in InfoView. For full information about each option and to find out how to select the appropriate options for your analysis, see ”Beginning your drill session” on page 364, above. Switching to drill mode To switch to drill mode: 1. With a document open in the Web Intelligence Java Report Panel, verify you are in Report View. The Edit Report button is pressed in when you are working in Report View. 2.
• If you do not have permission to drill, the report displays in Results View. In view mode, the report tab displays the Results icon Drilling on dimensions in tables and sections Dimensions typically represent character-type data, such as customer or business names, and dates. Web Intelligence makes calculations based on the dimensions in a report.
When you drill on a dimension to see the more data behind the displayed result, the sales revenue is calculated according to the values to which you drill. On this report, when you drill down on year 2003 to view the detailed data for each quarter in year 2003, the sales revenue, sum, and average measures are calculated for each quarter. Note: You cannot drill on detail objects. For a full description and examples of detail objects, see ”What types of objects can you use in a query?” on page 35.
Drilling down You drill down to see the lower-level data that makes up the summary results displayed on reports. This helps explain why high or low results occurred. Example: Using drill analysis to find out why sales decreased dramatically in 2003 In this example, you receive a report that shows sales revenue results for the accessories line at the eFashion retail store. The following crosstab shows that the Accessories line decreased in 2003.
Note: If you try to drill to a dimension that is already displayed in another column or row of the same table, Web Intelligence automatically displays the next available dimension in the drill path. Drilling down on a dimension value in a table or section cell To drill down on a dimension value in a table or section cell: 1. Verify you are in Drill mode. The Drill button is pressed in when you are working in Drill mode and the drillable values display on the report as hyperlinks. 2.
You can only drill up on a dimension value if: • you have previously drilled down to that dimension or • you have defined the appropriate drill path in the scope of analysis. For more information on setting the scope of analysis, see ”Setting levels of analysis” on page 41. Drilling up on a dimension value To drill up on a dimension value: 1. Verify you are in Drill mode. The Drill button is pressed in when you are working in Drill mode and the drillable values display on the report as hyperlinks. 2.
You work as regional manager for California in a retail clothing store, and have been sent the following report that shows quarterly sales revenue by state: You are only interested in analyzing the results in the state of California. In addition, you want to analyze the sales revenue broken down by each product line you sell. To drill on California data, you place your pointer on the table cell that says California.
The drilled report displays the detailed sales revenue results for each product line sold in California. Drilling by a dimension value To drill by a dimension value: 1. Verify you are in Drill mode. The Drill button is pressed in when you are working in Drill mode and the drillable values display on the report as hyperlinks. 2. On a table or section cell, right-click the dimension value you want to drill by. A shortcut menu appears, displaying the available drill paths. 3.
The report now displays data for the dimension to which you drilled. The Drill toolbar displays the valu that filter the results displayed on drilled table. This drilled table displays results for 2001 Q1. NOTE: You can only Drill by to a dimension that is included in the scope of analysis of the document. For more information, see ”Setting levels of analysis” on page 41. Choosing a drill path when more than one is possible A dimension can belong to several classes.
The Select Drill Path dialog box appears. 3. Select the path you want to drill. 4. Click OK. Note: If the dimension value you choose to drill on is the result of a previous drill, the drill path is already known. Therefore you do not need to select a drill path. Retrieving more levels of data to the report When you are drilling a Web Intelligence report, you may want to drill up or down to a dimension that lies outside the scope of analysis defined for the document.
To drill out of the scope of analysis: 1. Verify you are in Drill mode. The Drill button is pressed in when you are working in Drill mode and the drillable values display on the report as hyperlinks. 2. Drill on a dimension value. A ToolTip informs you that a new query is necessary to return the additional data to the document. If your Drill options are set for Web Intelligence to prompt you when a drill action requires a new query, the Extend the Scope of Analysis dialog box appears.
Note: You need permission from your administrator to drill out of the scope of analysis during a drill session. Drilling on measures in tables and sections When you drill on a measure value, Web Intelligence performs the drill action one level down for each related dimension in the block and displays the new measure calculation for the displayed dimensions.
The Drill button is pressed in when you are working in Drill mode and the drillable values display on the report as hyperlinks. 2. Place your pointer over the measure value on which you want to drill. Only values with hyperlinks are drillable. A ToolTip appears, displaying the next dimension(s) in each related drill path. 3. Click the measure value. Your report now displays data one dimension level down.
2. Right-click the measure value on which you want to drill up, then click the Drill up option on the shortcut menu. Or Click the Drill Up icon next to the measure value you want to analyze. The drilled table now displays data one dimension level up. Synchronizing drill across multiple tables and charts A Web Intelligence report can contain several tables or charts. The generic term used to refer to tables and charts in this guide is a block.
For example, if you drill on the Year 2003 in the crosstab below to view results for each quarter in year 2003, both the crosstab and the chart show the drilled results for quarter.
The entire report, however, is filtered for 2003, which means that only results for year 2003 are displayed in blocks that include the [Year] dimension. Drilling on charts Drilling down, up, or by on a chart, provides you with a graphical explanation for why summary results are particularly high or low.
In the 2D bar chart illustrated below, the X-axis includes the [Year] and [Quarter] dimensions. Each bar on the chart shows the values for one combination of year and quarter; for example, 2001/Q1, 2001/Q2, and so on. X-Axis When you drill on an axis value with multiple dimensions, the drilled results are filtered by both dimensions.
3. The next step depends on whether you want to drill down or drill up on the dimension value: If you want to... drill down on the dimension values, drill up on the dimension values, NOTE: then... click the values. right-click the values, then click Drill up. You cannot Drill by on a chart axis with multiple dimensions. The chart displays the value(s) for the dimension to which you drilled.
The drilled chart displays sales revenue per city per category for the “City Trousers” clothing line. Drill The Drill toolbar displays the values that filter the dimensions displayed on the chart. By looking at the filters in this example, you can quickly see that the chart only displays sales revenue information for City Trousers sold in California during Q1 in 2001. Drilling on a measure in a chart To drill on a measure in a chart: 1. Verify you are in Drill mode.
• radar and scatter charts – all types For more information, see ”Drilling on axis legends” on page 384. Note: You cannot drill on measures in 3D surface charts. Drilling on axis legends You can drill on charts via the chart legend whenever the legend lists the dimensions displayed on the chart. When the chart legend lists the measures displayed on the chart, drilling on the legend is not possible.
The chart displays the value(s) for the dimension to which you drilled. Using filters when you drill When you drill on a dimension or measure value in a table or chart, Web Intelligence filters the drilled results by the dimension or measure you drilled on. The filter is applied to all of the results displayed on the drilled report.
Showing or hiding the Drill toolbar To show or hide the Drill toolbar: 1. Verify you are in Drill mode. The Drill button is pressed in when you are working in Drill mode. The Show/Hide Drill Toolbar button is activated on the report panel toolbar. 2. The next step depends whether the Drill toolbar is displayed or not at the top of the report: If... the Show/Hide Drill Toolbar button is pressed in, the Show/Hide Drill Toolbar button is not pressed in, then... click the button to hide the Drill toolbar.
The Drill button is pressed in when you are working in Drill mode. The filters display as list boxes on the Drill toolbar. 2. In the Drill toolbar, click the drop-down arrow in the appropriate list box. Each dimension value associated with that filter appears. 3. Click the value you want. The report displays data based on the new dimension value. Adding and removing filters You can add and remove filters to the Drill toolbar.
2. From the Data tab, drag the dimension containing the values around which you want to filter your report and drop it onto the Drill toolbar. Here, the [State] dimension is being added to the Drill toolbar. A list box for the new filter appears on the Drill toolbar. You can select a value from the list of values to filter the results displayed on the drilled table, chart, or report. Removing a filter from a drilled report To remove a filter from a drilled report: 1. Verify you are in Drill mode.
4. Press your pointer, and then, with the pointer still pressed in, drag the list box onto the Data tab to the left of the report. Here, the filter for State is being dragged onto the Data tab to remove the filter from the drilled report Web Intelligence removes the filter and the report values change accordingly.
mode, Web Intelligence displays the Drill toolbar on the drilled reports showing the filters generated during the last drill session. For more information, see ”Saving your drilled results” on page 394. Note: Documents saved in drill mode take longer to open than documents saved in results mode. To find out how to return to results mode, see ”Ending drill mode” on page 394. Refreshing data in a report with prompts Some Web Intelligence reports contain prompts.
If the drilled report is filtered for Year 2003, and you then refresh the document and select year 2002 to answer the prompt, Web Intelligence retrieves results for 2002 instead of 2003. The drilled report then displays values for 2002. Using query drill You can set Web Intelligence to drill in query drill mode, which behaves differently from the standard drill mode described so far in this chapter.
Note: Drill filters are not strictly necessary in query drill mode. Web Intelligence applies them for consistency with standard drill mode. For example, the DrillFilters() function returns the correct value in query drill mode because Web Intelligence applies drill filters to match the query filters. For information on how to activate query drill, see ”Activating query drill” on page 394.
Drilling up When you drill up, query drill removes dimensions from the query. For example, if you drill up from Month to Quarter, Web Intelligence removes Month from the query. This has two consequences: • Query drill is incompatible with drill snapshots. For more information, see ”Query drill and drill snapshots” on page 393. • Web Intelligence does not allow you to drill up beyond any dimension that appears as one of the report objects.
In query drill mode, snapshots change when you drill up beyond a dimension that you included in a snapshot. Because the drill up removes the dimension from the underlying query, it also removes the dimension from the snapshot. Activating query drill You activate query drill at the document level. To activate query drill 1. Right-click on the document anywhere outside a table or chart and click Document Properties to display the Document Properties pane. 2. Open the Document Options group within the tab. 3.
A new report appears after your last report in the document. Saving a report in Drill mode To save a report in Drill mode: 1. Click the Save button to save a document you have saved previously. Or Click the arrow next to the Save button, and then save the document as either a Corporate Document or as a Personal Document. For full information on saving Web Intelligence documents using the Java Report Panel, see ”Saving and deleting Web Intelligence documents” on page 397.
Note: Filters in the Drill toolbar do not appear when you print a Web Intelligence report. You can insert a special field to display the active filters the Drill toolbar. For full information on the DrillFilter function, see ”Inserting the DrillFilters cell into your report” on page 389.
25 Saving and deleting Web Intelligence documents Overview You can save Web Intelligence documents exclusively for your personal reference or share your documents with other users. You save Web Intelligence documents to InfoView, the corporate business intelligence portal. You can also save Web Intelligence documents as Microsoft Excel spreadsheets and Adobe Acrobat PDF files. The layout and formatting in the original Web Intelligence document is closely retained in the new file format.
3. In the Title box, type the title of the document. 4. In the Description box, type a meaningful description of the document (optional). 5. In the Keywords box, type keywords that you or other users can use to search for the document in the future. By default, the document is saved to the current location displayed in the Location box. 6. Select the folder where you want to save the document in the Location box. 7. Select the categories that you want to associate with the document in the Categories box. 8.
• Microsoft Excel spreadsheets • Adobe Acrobat PDF files Saving Web Intelligence documents as Excel Spreadsheets Creating a copy of your documents as a Microsoft Excel file is especially useful if you want to combine the information in your Web Intelligence document with other data in an Excel spreadsheet. Unlike Web Intelligence documents, the Excel files are not connected to the database. This means you cannot refresh the data in the Excel file.
Saving a Web Intelligence document as a PDF To save a Web Intelligence document as a PDF file: 1. Click the arrow next to the Save button on the Web Intelligence toolbar. 2. From the Save menu, select Save to computer as... The Excel and PDF format options appear. 3. Select As PDF. Web Intelligence generates a copy of your document in Adobe Acrobat PDF Format. You can save the document using the Acrobat Save menu. The file has the standard Microsoft Excel file extension: pdf.
26 Linking documents with OpenDocument Overview OpenDocument is a function that enables you to open another document from within a Web Intelligence document using a URL. You can use OpenDocument to create a hyperlink in one document that opens another document containing related information. Both documents must be stored in the corporate repository and accessible through InfoView.
revenue values and specify that the target report is filtered by whichever state is displayed on the same table row as the clicked hyperlink. You can filter the target document by as many prompts as you like. For example, the same hyperlink can also filter the target document by whichever state, product line, and quarter is displayed on the same table row. You can also filter the target document by multiple values, even though the document contains only a single prompt.
The parameters are listed in the following tables. Parameter Description Mandatory? Values accepted sPath The name of the Crystal folder and subfolder containing the target document. Yes if sDocName is specified and is not unique. Crystal folder and/or subfolder: [folder], [subfolder] Example: Example of sPath http:///businessobjects/enterprise115/?sPath=Sales+Reports,Sub+Fold er.
Note: Do not specify both the sDocID and sDocName parameters in the same parameter list. Specify one or the other. Parameter Description iDocID The document identifier. Mandatory? Values accepted One of The document identifier. sDocName or iDocID is mandatory.
Note: To obtain the document ID, navigate to the document in InfoView, hover your mouse over the document name hyperlink, and look for the ID number in the browser's status bar. You can also obtain the document ID from the Central Management Console. Note: Do not specify both the sDocID and sDocName parameters in the same parameter list. Specify one or the other. Parameter Description Mandatory? Values accepted sIDType The Crystal object type.
Example: Example of sReportName http:///businessobjects/enterprise115/?sType=wid&sDocName=Sales+ in+2003&sReportName=First+Report+Tab Parameter Descriptio n Mandatory? Values accepted sOutputFormat Format in which the target document is opened. No. (Default is HTML .) • H (HTML) • P (PDF) • E (Excel) • W (Word) Note: W is supported by Crystal Reports only.
Example: Example of sPartContext http:///businessobjects/enterprise115/? sPath=Sales+Reports&sDocName=Sales+in+2005&sReportPart=Part1&sPartCo ntext=0-4-0 Parameter Description Mandatory ? Values accepted sInstance The instance of the target report to open. No.
Example: Example of sReportMode http:///businessobjects/enterprise115/? sPath=Sales+Reports&sDocName=Sales+in+2003&sReportPart=Part1&sReport Mode=Part Parameter Description Mandatory? Values accepted sRefresh Indicates whether a refresh should be forced when the target document or report is opened. No.
Example: Example of lsS[NAME] http:///businessobjects/enterprise115/? sType=wid&sDocName=SalesReport&iDocID=2010&lsSSelect+a+City=Paris Parameter Description Mandatory? Values accepted lsM[NAME] Specifies multiple values for a prompt. [NAME] is the prompt text. No. Multiple prompt values, separated by a comma. If the target is a Crystal report, each value must be enclosed in square brackets. If the target is a OLAP Intelligence report, use the MDX WITH clause.
Example: Example of lsR[NAME] http:///businessobjects/enterprise115/? sType=rpt&sDocName=SalesReport&lsRTime+Period:=[2000..2004) Parameter Description Mandatory? Values accepted lsU URL to which the user is redirected. No. A URL. Note: Not supported by OLAP Intelligence. Example: Example of lsU http:///businessobjects/enterprise115/? sType=wid&sDocName=SalesReport&iDocID=2010&lsU=http://www.site.
Further information on syntax and parameters URL encoding Parameters cannot contain spaces or other special characters that require URL encoding. Use the WebIntelligence URLEncode() function to ensure correct URL encoding. Example: Encoding the sDocName parameter To pass the string “World Sales Report” in the sDocName parameter, use sDocName=URLEncode(“World Sales Report”).
If 23CAA3C1-8DBB-4CF3-BA%2CB8%2CD7%2CF0%2C68%2CEF%2C9C%2C6F is the URL-encoded unique name for the page parameter in the OLAP Intelligence report, you use the following URL to open the OLAP Intelligence report to page 2: http:///businessobjects/enterprise115/?sType=car&sIDType=InfoObje ct&iDocID=440&lsS23CAA3C1-8DBB-4CF3-BA%2CB8%2CD7%2CF0%2C68%2CEF%2C9C%2C 6F=2 Example: Opening a cube parameter If 8401682C-9B1D-4850-8B%2C5E%2CD9%2C1F%2C20%2CF8%2C1%2C62 is the URL-encoded uniqu
27 Printing Web Intelligence documents Overview You print Web Intelligence documents report-by-report. You can print one or multiple reports from a single document. Web Intelligence generates a temporary PDF file from the selected report before printing. This chapter tells you how to: • print reports in Web Intelligence documents using the Java Report Panel NOTE: To print Web Intelligence reports, you need Adobe Acrobat Reader installed on your local computer.
How Web Intelligence organizes page breaks Web Intelligence prints reports from left to right, and then top to bottom. If a report is wider than the width of the paper size defined in the Report Page Layout, Web Intelligence inserts page breaks for the printout, as illustrated below: If the report page is wider than the paper size defined in the Report Page Layout Page 1 Page 2 Page 3 Page 4 ...the page breaks go from left to right.
To print a report: 1. Make sure you are in Edit Report View. The Edit Report button on the Web Intelligence toolbar is pressed in when you are in Edit Report View. 2. Make sure you are in Results View. Results View displays the data in the report tables and charts. 3. Click the report tab of the report you want to print. 4. Click the Export to PDF for Printing button on the Report toolbar. The Make Printable Version dialog box appears. 5.
1. To change the paper size, click the arrow next to the list box below Paper Size, and then select a different paper size from the list. 2. Under the Orientation options, select the page orientation that suits the report page layout: For this page orientation... select... Portrait Landscape 1. Click OK. Web Intelligence generates a copy or the report to a PDF file. 2. If you receive a prompt offering you the choice to open or save the file to your computer, select Open.
Index #DATASYNC error 353 #DIV/0 error 351 #ERROR error 354 #INCOMPATIBLE error 352 #MULTIVALUE error 351 #OVERFLOW error 352 #RANK error 236, 352 #SYNTAX error 352 3D look charts 185 508-compliancy 19 A accessibility 19 adding breaks 224 sub-alerters 274 table rows, columns 138 Adobe installing Reader 413 aggregation show/hide duplicate rows 158 alerters adding conditions 280 adding sub-alerters 283 creating 268 defining conditions 269 defining formatting with formulas 298, 302 describing 263 describing s
describing 220 editing 224 inserting 221, 222 removing 224 sorting data within 221 using multiple 223 building a query 37 multiple queries 63 query overview 38 C calculation context default 314 defined 314 describing 314 modifying 318 calculations inserting standard 260 removing 261 using in reports 257 cells AutoFit size 154 backgrounds 168 borders 168 clearing 146 defining height, width 154 format values 199 formatting free-standing cells 168 formatting how values display 200 free-standing 161 hidden in
selecting 51 selecting properties 50 use in query 50 copying tables 134 Count inserting 257 creating alerters 268 charts 171 documents 23 formulas 307 query 36 sections 214 tables on reports 130 variables 343 crosstabs creating from tables 145 displaying object names 156 template for 126 with dimensions in body 127 cube description 37 stored data 37 currency formats 199 custom formats for cell values 202, 203, 208, 210 report filters 248 D data returned to cube 37 data markers show/hide on charts 196 data
multiple query documents 359, 385 duplicating alerters 292 queries 68 reports for drill analysis 357 tables 134 E editing alerters 291 breaks 224 formulas 310 variables 343 emptiness hiding empty charts 196 Excel saving documents as spreadsheets 399 extended syntax modifying calculation context 318 F Filter Editor using for reports 248 filtering linked documents 401 objects 85 queries 84 filters combining on queries 89, 95, 96 combining on reports 252 creating on queries 91, 93 custom report filters 248 d
labelling other cells 165 functions business examples 339, 340, 341 describing 312, 337 prototypes 337 using 338 G grid report panel display 28 grids showing on charts 193 grouping data in breaks 220 H headers on crosstabs 156 on tables 155 height setting for cells 154 setting for charts 183 hierarchies and drill 362 viewing 363 HTML code in cells 163 HTML Report Panel description 19 hyperlinks including in cells 163 report linking 401 URL types in reports 304 I If function business example 340 images de
Max inserting 258 max retrieval time setting limits 45 max rows retrieved setting limits 45 measure describing 35 measures drilling 376, 382 merged dimensions adding to reports 76 and block filters 81 and report filters 80 and section filters 81 creating 74 deleting 76 drilling on 81 editing 76 extending returned values 78 filtering 80 using in reports 76 Min calculation inserting 257 modifying calculation context 318 moving queries in a document 69 table rows, columns 141 multiple breaks using 223 multiple
paper size for printing reports 414 PDF saving documents as 400 Percentage inserting 258 Percentage function business example 339 pie charts describing 170 polar charts describing 170 popup calendar for prompts 119 predefined filters describing 89, 90 using 91 printing 413 reports 415 prioritizing alerters for a cell 289 alerters for a document 290 sorts 228 prompt order setting for query (Java) 46 prompts creating 120 describing 114 display options 118, 120 for dates 119 merging on multiple queries 119 on
objects from query 40 report filters 254 table rows, columns 141 renaming queries 68 report 252 report panel 25 reports filtering 245, 246, 248, 252, 253, 254 printing 415 using quick filters 246 viewing filters 253 resizing charts 174, 176 Results View describing 128 retrieve duplicate rows setting (Java) 45 reusing custom formats 210 rows adding to tables 138 deleting from tables see rows - removing removing from tables 141 replacing on tables 143 show/hide empty 157 swapping on tables 142 running query 3
T tables adding columns, rows to 138 adding rows, columns 140 alternate row colors 150 AutoFit cell size 154 background colors 149 cell size 154 clearing cells 146 creating 130 crosstab template 126 deleting rows, columns see removing duplicate row aggregation 158 duplicating on reports 134 filtering 245, 248 format fonts and text 153 formatting 149 formatting borders 152 hidden cells in migrated reports 154 hiding cells 154 horizontal template 126 making into crosstabs 145 moving rows, columns 141 page bre
