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 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 11 How Web Intelligence performs business intelligence over the web . . . . . . . . . . . . . . . . . . . . . . Interacting with Web Intelligence reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Viewing and printing reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Drilling results . . . . . . . . . . .
Viewing reports in page mode and draft mode . . . . . . . . . . . . . Zooming in on results . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Navigating to results in reports . . . . . . . . . . . . . . . . . . . . . . . . Searching for text in reports . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Viewing the document overview . . . . . . . . . . . . . . . . . . . . . . . . . . .. .. .. .. .. . . . . . .. .. .. .. .. .. .. .. .. .. . . . . . .. .. .. .. .. . . . . . .. .. ..
Available table types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Vertical table. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Horizontal table . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Crosstab. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Resizing charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Positioning charts on report pages . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Setting page breaks for charts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding and formatting chart titles. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
What types of report filter can you create? . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Values used with filter operators . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Operators available in report filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Adding and removing report filters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Combining and nesting filters. .
The ForAll context operator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Extended syntax keywords . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Report keyword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Section keyword . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . The Break keyword . . . . . . . . . . . . . . . . .
Saving Web Intelligence documents to InfoView. . . . . . . . . . . . . . . Saving documents as Excel, PDF, or CSV files . . . . . . . . . . . . . . . . Saving Web Intelligence documents as Excel Spreadsheets. . . . . Generating Web Intelligence documents to PDF . . . . . . . . . . . . Generating Web Intelligence documents as CSV files. . . . . . . . . Deleting Web Intelligence documents . . . . . . . . . . . . . . . . . . . . . . .................... .................... .................... ..............
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: • Query – HTML • Java Report Panel • HTML Report Panel This section explains the differences between each tool.
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 Java Report Panel The Java Report Panel is designed for users who need more flexibility with designing report layout and defining formulas and variables. A graphical Formula Editor enables you to build formulas rapidly using drag-and-drop.
Note: The Web Intelligence Java Report Panel is available if your administrator has deployed Web Intelligence in ASP mode and 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 Accessing Web Intelligence via InfoView Overview You access Web Intelligence reports via InfoView, the corporate business intelligence portal. This chapter tells you how to: • log into InfoView • log out of InfoView Logging into InfoView You access Web Intelligence by using your web browser to log into InfoView, the corporate business intelligence portal. Once you are in InfoView, you can analyze and enhance Web Intelligence reports.
7. Click Log On. The InfoView home page appears. Logging out of InfoView When you finish using InfoView or Web Intelligence you need to log out, instead of simply closing your web browser.
Performing On-Report Analysis with Web Intelligence 19
Accessing Web Intelligence via InfoView
Performing On-Report Analysis with Web Intelligence 21
Accessing Web Intelligence via InfoView
3 Setting your Web Intelligence options Overview Before you start to use Web Intelligence, you need to set your Web Intelligence Document Preferences appropriately to suit your query and reporting needs.
If you want to... then select... Modify formatting and data displayed on tables and charts, define filters, sorts, formulas, and and variables, and/or perform drill analysis, Interactive If you also want to modify the data definition of a document by editing the queries, it is recommended you select Query – HTML as your report panel. Using Query – HTML and Interactive view format provides you with a complete solution for building queries and designing reports in a pure HTML environment.
• how to select the drill options appropriate for your analysis Available drill options This section describes the various options available to you when you perform drill analysis on Web Intelligence reports. Start drill on the existing report or on a duplicate report When you drill on a report, you can: • Start drill on the existing report – the current report becomes drillable and when you end drill mode, the report displays the drilled values.
The following example shows how each option affects a report as you drill down on the Bahamas Beach resort in a table to analyze detailed results per service line.
Note: If the drilled report includes dimensions from multiple queries, a ToolTip appears when you rest your cursor on the value displayed on the filter. The ToolTip displays the name of the query and the dimension for the value. The Drill toolbar allows you to select alternative values on the same level, in order to filter the results differently.
1. In the General drill options section, select the options 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, synchronize drilling on all report blocks, hide the drill toolbar when you switch to drill mode, then select...
Note: If your security profile enables you to define the data included documents, by building queries, the ability to define your query rules is also available. For information about defining your query rules, see Building Queries Using Web Intelligence QueryHTML. Note: To set these preferences you need to have a Web Intelligence document open. To set the display properties of your Web Intelligence workspace 1.
Web Intelligence applies the preferences you selected the next time you open a Web Intelligence document. To define your reporting rules 1. Click the arrow next to View on the main toolbar above the selected report. 2. Click Preferences. The Preferences dialog box appears. 3. Make sure the Reporting Rules tab is selected. 4. Select the appropriate options. The following table provides information on the options: If you want to... then...
If you want to... then... during a drill session, highlight cells you select for drill with the color and font format you specify, • Select Apply custom highlighting to drillable cells when selected, as defined here: • To format selected cells with underlined text, click Underlined • To format selected cell backgrounds with a color, click the arrow next to Background color, then select a color • To format selected cell contents with a color, click the arrow next to Text color, then select a color 1.
Choosing the format locale on opening a document You can choose whether a document should use the format locale with which it was saved or your current format locale. You do so by setting the When viewing a document option. If you choose Use the document locale to format the data, Web Intelligence uses the formatting locale with which the document was last saved, and saves the document with this format locale the next time you save it.
4 Navigating reports Overview • This chapter tells you how to access and navigate Web Intelligence reports.
The list of documents appears. 3. Click the document title. 4. If the document contains any prompts that need to be refreshed, you need to select or type the value(s) you want returned to the document, and then click Run Query. For more information, see ”Refreshing results to view the latest results” on page 34. The results appear on the reports.
The Document Properties dialog box appears. 3. Select Refresh on open. 4. Click OK. Once you have saved the document, Web Intelligence refreshes the results in reports automatically each time you re-open it. Answering prompts Prompts are dynamic filters that display a question or a list of values each time you refresh the data in a Web Intelligence document. You answer the prompt by typing or selecting specific values.
3. Select the values from the list of choices in the pane on the left, for which you want to view results, or type the values into the text box, and click the >> button to add them to the prompt answer box on the right. NOTE: If you type multiple values, you need to separate each value with a semi-colon (;). For example: Florida;Texas 4. Click Run Query. Web Intelligence retrieves the data for the values you specified, and returns the results to the reports.
If the prompt has been answered previously, the values selected the last time appear in the prompt answer pane on the right. You can either leave those values selected or use the << button to remove them. 3. If the prompt allows you to select multiple dates, and you want to view results for more than one date, repeat step 2. 4. Click Run Query. Web Intelligence retrieves data for the values you specified, and returns the results to the reports. Cascading prompts Some prompts are cascading prompts.
In order to select the store, you must first select the state in which the store city is found, then the city in which the store is found, then the store itself. When you select the state, Web Intelligence restricts the cities in the list of values of the “Which city is the store in?” prompt to the cities in the state; when you select the city, Web Intelligence restricts the stores list of values to the stores in the city.
Note: The page margins, headers and footers implemented by Enhanced viewing mode do not impact the page definition you can apply to reports. To select Enhanced viewing mode 1. With a report in a Web Intelligence document open, click the arrow next to Document on the main toolbar above the report. 2. Click Properties. The Document Properties dialog box appears. 3. Select Enhanced viewing mode. 4. Click OK.
Navigating to results in reports To find the results you are looking for quickly in documents, you can navigate directly to: • reports • sections • pages To navigate report-to-report 1. Click the report tab of a report. Web Intelligence displays the report for the selected tab. To navigate to a section 1. With a report in a Web Intelligence document open, make sure that the Document Properties pane is displayed to the left of the selected report.
The search options appear. 3. In the text box below Find:, type the string of characters and/or numbers you want to find. 4. If you only want to find exact matches to the string you typed, check Match whole word. 5. If you want the search to be case sensitive, check Match case. By default, Web Intelligence searches the report page downwards. Web Intelligence highlights the first occurrence of the string you specified. 6. To search for more occurrences of the same string, click Find Next. 7.
This overview... provides you with...
3. Select the overview you want to view. In this example, the Navigation Map is selected The list you selected appears. To print out the Document Summary 1. Make sure the Document Properties pane is displayed, to the left of the open report, and that Document Summary is selected. 2. Click Printer Friendly. The Printer Friendly Options dialog box appears. You can print all or part of the Document Summary information. 3. Select General, Queries, Objects, and Prompts as appropriate. 4. Click OK.
Navigating reports
5 Analyzing results Overview Drill analysis allows you to look deeper into the reasons behind results displayed in reports.
Note: You can only drill on reports, if you security profile allows you. To switch to drill mode 1. Verify that the report you want to analyze is selected. 2. Click the Start Drill Mode button on the main toolbar. A hyperlink appears on each drillable result. • ”Drilling on tables” on page 46 • ”Drilling on sections” on page 48 • ”Drilling on charts” on page 50 Drilling on tables You can drill on: • dimensions - typically retrieve character-type data.
When you drill on measures, you drill one dimension down each dimension in the table. For example, if you drill on Sales revenue for Year and State, the drill table shows sales revenue results for Quarter and City: Table before drill Table after drill To drill down on a dimension value on a table 1. Verify you are working in Drill mode. The Start Drill Mode button is pressed in when you are in Drill mode. 2. Click the value you want to analyze.
The Start Drill Mode button is pressed in when you are in Drill mode. 2. Right-click the drillable dimension, then click Drill By... on the Drill menu. A series of sub menus list the dimensions to which you can drill. 3. Select the dimension you want to analyze. The drilled results appear on the table. To drill on a measure value on a table 1. Verify you are working in Drill mode. The Start Drill Mode button is pressed in when you are in Drill mode. 2.
cell that displays a value for State, such as “California,” the tables or charts in the drilled section display results for City: Section cell Section before drill Section after drill To drill on a section cell 1. Verify you are working in Drill mode. The Start Drill Mode button is pressed in when you are in Drill mode. Drillable values are indicated by hyperlinks. 2. Right click or rest your pointer on the measure value on which you want to drill.
Drilling on charts You can drill on: • chart body – drills to the next dimension up or down and displays the values for the drilled dimension on the X-axis. The measures displayed on the chart body are re-calculated according to the drilled dimension. • pie chart legends – this is useful, if you are working with a pie chart, because the axis labels, which display the names of the dimensions represented by each pie segment, are not often displayed.
1. Verify you are working in Drill mode. The Start Drill Mode button is pressed in when you are in Drill mode. 2. Right click or rest your pointer on the chart bar or marker on which you want to drill up. 3. Click the Drill Up arrow on the chart axis or click the chart bar, line, or marker directly. Drill up arrow The drilled results appear in the chart. To drill on pie chart legend 1. Verify you are working in Drill mode. The Start Drill Mode button is pressed in when you are in Drill mode. 2.
Note: You can set your Web Intelligence Document Preferences so that the Drill filters are either shown or hidden during your drill sessions. For information on how to do this, see ”To set your drill options” on page 27. To change the values that filter drilled results 1. Verify you are working in Drill mode. The Start Drill Mode button is pressed in when you are in Drill mode. 2. In the Drill toolbar, click the drop-down arrow in the appropriate list box.
Note: You can only extend the scope of analysis during your drill sessions, if your security profile allows you. Your security profile is controlled by your administrator. If your Drill options are not set to display the Extend the Scope of Analysis prompt message during drill, you will not be given the option to select filters if you drill beyond the data already available in the document.
for the Accessories line and the Jewelry category. If you don’t select the filter, Web Intelligence will return data for all product lines and categories to the document. 1. Select the check box(es) next to the dimension(s) to which you want to drill. Selecting filters to limit the new data to data necessary for your drill session reduces the time Web Intelligence needs to retrieve the data to the report and minimizes the size of the document. 2.
The InfoView Save page opens. 3. Save the document in the usual way. For full information, see ”To save a new Web Intelligence document in InfoView” on page 248. The next time the document is opened, it opens in Drill mode. To end drill mode 1. Verify you are working in Drill mode. The Start Drill Mode button is pressed in when you are in Drill mode. 2. Click the Start Drill Mode button. Web Intelligence returns the drilled report to results mode.
When do you use query drill? You use query drill when your report contains aggregate measures calculated at the database level. It is designed in particular to provide a drill mode adapted to databases such as Oracle 9i OLAP, which contain aggregate functions that Web Intelligence either does not support, or cannot calculate accurately at the report level during a drill session.
Query drill and the scope of analysis Query drill adds and removes dimensions from the scope of analysis in response to drill actions. For this reason, you cannot set the scope of analysis manually when using query drill. In addition, you cannot activate query drill if your document already has a scope of analysis defined. In this case the option is unavailable. You must remove the scope of analysis to make it available.
Analyzing results
6 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.
Web Intelligence creates a merged dimension with the same name, description, qualification and data type as the first dimension you selected. The original dimensions that make up the merged dimension appear beneath it when you expand the merged dimension. If you select any of these original dimensions, Merge/Unmerge is unavailable. To remove a merged dimension 1. Select the merged dimension in the Available Objects section of the Left Panel.
3.
Merging dimensions from multiple data providers
Performing On-Report Analysis with Web Intelligence 63
Merging dimensions from multiple data providers
Performing On-Report Analysis with Web Intelligence 65
Merging dimensions from multiple data providers
Performing On-Report Analysis with Web Intelligence 67
Merging dimensions from multiple data providers
Performing On-Report Analysis with Web Intelligence 69
Merging dimensions from multiple data providers
Performing On-Report Analysis with Web Intelligence 71
Merging dimensions from multiple data providers
Performing On-Report Analysis with Web Intelligence 73
Merging dimensions from multiple data providers
Performing On-Report Analysis with Web Intelligence 75
Merging dimensions from multiple data providers
7 Inserting and organizing reports Overview Web Intelligence documents can contain multiple reports.
The contextual menu appears. 2. Select Move. A sub-menu appears. You can select where you want to move the report here. 3. Click the appropriate option: To move the report... click... to the beginning of the document, First before the selected report, Previous after the selected report, Next to the end of the document, Last Web Intelligence moves the report to where you selected. To delete a report 1.
NOTE: When you rename reports in a document that you want to save as an Excel spreadsheet, the names of reports must not include more than 31 characters or include spaces or the following special characters: * : \ / [ ] 3. Press Enter. The new name appears on the report tab. To rename a report using the Format Report dialog box 1. Right-click an empty area of the report. The contextual menu appears. 2. Click Format Report. The Format Report dialog box appears. 3. Click the General tab. 4.
Property Description Last refresh date Informs you when the results were last refreshed with the latest data from the database. Duration of the previous Refresh Informs you how long it took for Web Intelligence to retrieve the data from the database the last time the results were refreshed. Locale Tells you the formatting locale of the document. Version Tells you the version of Web Intelligence software used to create the document.
Property Description Auto-merge dimensions When this option is checked, Web Intelligence automatically merges dimensions with the same name and from the same universe. You see the merged dimension in the list of available objects with the dimensions merged within it below. Merged dimensions are the Web Intelligence mechanism for synchronizing data from different data providers. For more information, see ”Merging dimensions from multiple data providers” on page 59.
Inserting and organizing reports
8 Formatting report pages Overview You can format the page layout of Web Intelligence reports to create presentation-level documents.
3. Click the Layout Properties tab. 4. In the Margins section, type the measurement you want for the Top, Bottom, Left, and Right margins. 5. Click OK. Web Intelligence applies the margin settings you specified to the selected report. To set page orientation 1. Right-click an empty area of the report. The contextual menu appears. 2. Click Format Report. The Format Report dialog box appears. 3. Click the Layout Properties tab.
4. In the Orientation section, click the arrow next to the list box, and then select Portrait or Landscape as appropriate: Portrait Landscape 1. Click OK. Web Intelligence applies the page orientation you specified to the selected report. To select paper size 1. Right-click an empty area of the report. The contextual menu appears. 2. Click Format Report. The Format Report dialog box appears. 3. Click the Layout Properties tab. 4.
2. Click Format Report. The Format Report dialog box appears. 3. Click the General tab. 4. To include a page header, select Show header, and then type the height you want for the header into the text box. 5. To include a page header, select Show footer, and then type the height you want for the footer into the text box. NOTE: If you want to use a different measurement unit from the one currently displayed.
3. Click the General tab. 4. Click the arrow next to Background color, and then either select a predefined color or click More colors... to define a custom color, and then click OK. 5. Click OK. Web Intelligence applies the color(s) you selected to the headers and footers on the selected report. To include page numbers 1. Verify that the Document Properties pane is displayed to the left of the selected report.
The available page number formats are listed here. Predefined formats for page numbers The following table provides examples of the three formats: To display... for example, select... the page number of the current report page, 1 Page Number the page number of the current report page over the total number of pages in the report 1/50 Page Number/Total Pages the total number of pages in the current report, 50 Total Number of Pages 1.
The Format Cells dialog box appears. 3. To format how the text and cell background appears, click the Font tab, and then select the appropriate settings. 4. To format how the borders around the page number cell appear, click the Border tab, and then select the appropriate settings for borders. You can apply the same colors and styles to all borders in the Settings applied to all borders section.
NOTE: Type the text you want to display before the [ bracket or after the ] bracket. You must not type any text inside the [ ] brackets. 7. Click OK. Web Intelligence applies the settings you specified to the page number cell on the selected report. Including titles on reports You can display a title in a report. This is useful if you want to print the report, and distribute it to colleagues. The report title appears at the top of the report page.
8. On the contextual menu, select Format > Cell. The Format Cells dialog box appears. 9. Click the General tab. 10.Type the title you want to give the report into the Name text box. 11.In the Size section, specify the width and height of the report title cell and select Wrap text, if you want to continue the title onto the next line. 12.Leave the Read content as option blank. 13.To format how the text and cell background appears, click the Font tab, and then select the appropriate settings. 14.
Note: You can also include page numbering, (see ”To include page numbers” on page 87) and insert a blank cell and then type text, such as a comment or message to report reviewers (see ”To insert a free standing cell” on page 104.) To include a formula to display document information 1. Verify that the Document Properties pane is displayed to the left of the selected report. To display the Document Properties pane, click the Show left pane arrow at the top left of the report. 2.
The following table provides information about each type of formula: If you want to display... then select... the filters applied to results during a drill session, Drill Filters NOTE: For full information about drill analysis, see ”Analyzing results” on page 45. the date the last time the results in reports were refreshed with up-to-date data on the database, Last Refresh Date the title of the document as it is listed in InfoView, Document Name 1.
The available cell formats are listed here. Predefined formats for formula and text cells 6. Select the Blank Cell format and drag it onto the area of the report page where you want to display the report title. The blank cell appears on the report. 7. Verify that the Formula bar is displayed above the report. To display the Formula bar, right-click the main toolbar above the report, and then click Formula on the contextual menu. 8.
1. Verify that the Document Properties pane is displayed to the left of the selected report. To display the Document Properties pane, click the Show left pane arrow at the top left of the report. 2. Click the arrow to the right of the drop-down list box, then select Chart and Table Types. The categories of chart, table, and cell types appear. 3. If the Cells category is not visible, click the + next to Report Elements. 4. Click the + next to Cells. 5. Click the + next to Formula and Text Cells.
10.Type the URL of the image in the Name box as described in the following table: If... then... the image file has been installed by your administrator in the images directory on the BusinessObjects Enterprise server, • type: boimg:// followed by the file name. For example, if the file name is “efashion_logo.gif,” then type: boimg://efashion_logo.
1. With a Web Intelligence document open, right-click an empty area of the report you want to format. The background of the selected report is highlighted gray. The contextual menu appears. Depending the area you selected, the options may be slightly different. 2. Select Format Report or Format > Report. The Format Report dialog box appears. 3. Click the Appearance tab. 4. In the Pattern section, select Image (URL). 5. In the Image (URL) text box, type the file name or URL for the image file.
NOTE: The size of the image is defined when the image is created. You cannot modify the size of the image using Web Intelligence. Click the arrow to the right of the Display drop-down list box, and then select how you want the image to be displayed: If you want to... display the image once at the top left of the report, section, table, or cell, stretch the image over the entire report area, then select...
5. Click the + next to Formula and Text Cells. The available cell formats are listed here. Predefined formats for formula and text cells 6. Select the Blank Cell format and drag it onto the area of the report page where you want to display the report title. The blank cell appears on the report. 7. Right-click the blank cell, then select Format > Cell. The Format Cells dialog box appears. 8. In the Name text box, type the hyperlink you want. NOTE: You must not include spaces in the URL or file path.
Web Intelligence inserts the hyperlink into the cell on the report. If you want to select visited and unvisited colors for the hyperlink, refer to ”To format visited and unvisited colors on a hyperlink” on page 100. To format visited and unvisited colors on a hyperlink 1. Right-click a blank area on the report that contains the hyperlink. To see how to insert a hyperlink, refer to ”To insert a hyperlink” on page 98. 2. On the contextual menu, select Format Report. The Format Report dialog box appears. 3.
9 Adding tables and cells to reports 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 insert more tables onto the same report or onto new reports within the same document.
Horizontal table Horizontal tables display header cells at the left of the table and the corresponding data in rows. 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. Crosstab Crosstabs display values for dimensions across the top axis (columns) and on the left axis (rows). You can include more than two dimensions in crosstab.
customer account, name, address, and so on. Forms are also useful for formatting address labels for envelopes. Note: When you create a new document and run the query the first time to display the results, Web Intelligence generates a report that includes all of the results in a vertical table.
The Left panel now displays all the types of tables, charts and free standing cells that you can add to the report. 3. Select the table type that you want to add to the report in the Left panel and drag the table onto the report. 4. Right-click the table in the report, select Insert from the menu, then select New column or New row to add rows or columns to the table. 5. Select Available Objects from the drop-down list at the top of the left panel. 6.
3. The Left panel now displays all the types of tables, charts and free standing cells that you can add to the report. 4. Open the free standing Cells item in the report template hierarchy to display the types of free standing cell you can add. 5. Drag the type of free standing cell you want to add to the report. If you choose Blank Cell, the cell remains blank. If you choose any other type of cell, the cell is populated based on the type of information that the cell displays.
The Turn To dialog box appears. 2. Make sure that Tables is selected in the left pane. 3. Select the type of table to which you want to change the current table in the Available Formats area on the right. 4. Click OK. Web Intelligence changes the table to the type you selected. To apply a different table type using drag-and-drop 1. Make sure that the Document Properties pane is displayed to the left of the selected report.
2. Select the General tab on the Format Table dialog box. 3. Check/uncheck Table headers to display/hide table headers. 4. Check/uncheck Table footers to display/hide table footers. Note: If the table is a crosstab, you have the option to hide either or both of the vertical (left) and horizontal (top) headers. In this case the options Left header and Top header appear in place of Table headers.
You can also swap the axes on crosstabs to gain a different perspective on results. To move columns in a table 1. Right-click in the table, select Format from the menu, then select Table. The Format Table dialog box appears. 2. Select the Pivot tab. 3. Select objects in the Available Objects area, and click >> to add them to the table. 4. Select objects in the Column(s) area and click << to remove them from the table. 5. Drag objects in the Column(s) area to set the order of columns in the table. 6.
The Pivot tab has sections for the row(s), column(s) and body of the crosstab. 3. Select objects in the Available Objects area, click Row(s), Column(s) or Body and click >> to add the objects to the rows, columns or body of the crosstab. 4. Click Row(s), Column(s) or Body and click << to remove objects from the rows, columns or body of the crosstab. 5. Drag objects in the Row(s), Column(s) or Body area to set the object order. 6. Click OK. To swap rows or columns on a crosstab 1. Select the crosstab. 2.
3. Delete the cell formula in the Formula bar. Web Intelligence removes the data from the cell(s). Removing tables and cells To remove a table 1. Select the table. 2. Right-click and select Remove from the menu, then select Table. To remove a cell 1. Select the cell. 2. Right-click and select Remove Cell from the menu. NOTE: You can only remove free standing cells. You cannot remove single cells from a table.
10 Formatting tables and cells Overview You can personalize the formatting and choose display properties for tables and cells.
2. Select the General tab in the Format Table dialog box. 3. Type the table name in the Name box. Aligning tables and cells To align a table or cell on a report page 1. Select the table, right-click, select Format from the menu then select Table or Cell from the sub menu. 2. Select the Layout Properties tab in the Format Table or Format Cell dialog box. 3.
Setting page breaks for tables To avoid page breaks in a table 1. Select the table, right-click, select Format from the menu then select Table from the sub menu. 2. Select the Layout Properties tab in the Format Table dialog box. 3. Check Avoid page breaks in a block. Sizing cells You can specify the exact width or height of cells. To specify cell width and height 1. Select the cell, right click, select Format from the menu and then select Cell from the sub menu. 2.
Formatting cell values You can change the font, color, style or alignment of the display of values in cells. To change fonts, colors, or styles applied to cell values 1. Select the cell, right-click, select Format from the menu then select Cell from the sub menu. 2. Select the Font tab in the Format Cells dialog box. 3. To change the font, select the font name from the list below the Font name box. 4. To change the font style, select the style from the list below the Font style box. 5.
1. Select the cell, right click, select Format from the menu and then select Cell from the sub menu. 2. Select the Alignment tab in the Format Cells dialog box. 3. Use the radio buttons to set the horizontal format (left -justified, centered or right-justified) and the vertical format (top, middle or bottom). 4. To set the vertical and horizontal padding (the distance of the cell content from the left and top cell borders) enter the padding distances in the Vertical and Horizontal boxes.
3. Select the Number tab. 4. Select the format from the list of formats and click OK. NOTE: If the cell contains a number, the list of formats contains number formats. If the cell contains a date, the list of formats contains date formats. Formatting cell borders You can format the style and color of cell borders. To choose a border style and color 1. Select the cell, right-click, select Format from the menu and then select Cell from the sub menu. 2. Select the Border tab in the Format Cells dialog box.
6. Click the down arrow beside each drop down list to display the color editor to choose the border color. 7. Select the color in the Color Editor or click More colors... to display the Custom Color Picker. 8. Select the color in the Custom Color Picker or enter the red, green and blue values of the color. Setting cell background color To choose a background color 1. Select the cell, right-click, select Format from the menu and then select Cell from the sub menu. 2.
4. Select the color in the color editor or select More colors... to display the Custom Color Picker. (To remove the color, select Default in the color editor.) 5. Select the color in the Custom Color Picker or enter the red, green and blue values of the color. Formatting tables with images or skins You can display images in a table by linking to an image file, or define a skin for the table. To link to an image file 1.
4. Type the URL of the image in the Image URL box as described in the following table: If... then... the image file has been installed by your administrator in the images directory on the BusinessObjects Enterprise server, • type: boimg:// followed by the file name. For example, if the file name is “efashion_logo.gif,” then type: boimg://efashion_logo.
1. Select the table, right-click, select Format from the menu and then select Table from the sub menu. 2. Select the Appearance tab in the Format Table dialog box. 3. Click Skin then select the skin from the list of server skins in the drop-down list next to the Skin radio button. 4. Type the skin spacing and padding in the Spacing and Padding boxes.
Avoiding duplicate rows You can set the table display so that numeric data in duplicate rows is aggregated. If you do not choose this option the duplicate rows appear in the table. To set duplicate row aggregation 1. Select the cell, right click, select Format from the menu and then select Table from the sub menu. 2. Select the General tab on the Format Table dialog box. 3. Uncheck Avoid duplicate row aggregation.
The Alerters dialog box appears. 4. Check the box next to an alerter to activate it, or uncheck the box to deactivate the alerter. The table appearance changes according to which alerters are activated.
11 Adding charts to reports Overview This chapter explains how to add charts to Web Intelligence reports and edit the data charts present. • chart types available with Web Intelligence • inserting and removing charts from reports • transforming charts to different chart types • modifying how data is included in charts • duplicating and removing charts For information on formatting charts, see ”Formatting charts” on page 133.
You can use more than one measure object on the Y-axis as long as the measures are of the same type and scale; for example, Number of Guests, and Future Guests. There are five types of area charts: absolute, stacked, percent, 3D area, and 3D surface. Pie charts Pie charts display data as segments of a whole. Pie charts are useful if you want to show how each part of your report data contributes to the total. You can only include one measure object in a pie chart.
5. To view the formats available for the chart type, click the + next to the appropriate chart type. For example, if you want to add a Bar chart to the report, click the + next to Bar. 6. Select the chart format you want to add to the report, and then drag it onto a blank area of the report. The Format Chart dialog box appears. 7. Verify the Pivot tab is selected. This tab allows you to allocate data to the chart axes. 8.
2. Right-click the table you want to turn into a chart. 3. Click Turn table to... The Turn To dialog box appears. 4. Select the chart type you want to create in the left pane. The different formats available for the selected chart type appear on the right. 5. Click the appropriate format, and then click OK. Web Intelligence turns the table into a chart according to the format you selected. You can modify how the data is allocated to the chart axes.
The Turn To dialog box appears. 3. Select the chart type you want in the left pane. The different formats available for the selected chart type appear on the right. 4. Click the appropriate format, and then click OK. Web Intelligence modifies the chart according to the format you selected and re-allocates the data to different chart axes, if necessary. You can modify how the data is allocated to the chart axes. To find out how to do this, see ”Modifying how data is allocated to charts” on page 128.
6. Select the chart format you want, and then drag it onto the chart you want to modify. Dragging a Line chart type onto a Bar chart Web Intelligence transforms the chart on the report to the new chart type you selected and re-allocates the data to different chart axes, if necessary. You can modify how the data is allocated to the chart axes. To find out how to do this, see ”Modifying how data is allocated to charts” on page 128.
To remove objects from the chart, select the object in the appropriate axis pane, and then drop the object onto the Available Objects list. Dragging [Quarter] from the X-Axis onto the Z-Axis NOTE: You can only place measure objects on the Y-Axis. 5. Click OK. Web Intelligence displays the chart with the data allocated to the chart axes as you specified.
NOTE: You can only swap axes on 3D Bar charts. The contextual menu appears. Z-Axis X-Axis 2. Select Swap axes. The data on the X-Axis is moved to the Z-Axis and the data on the Z-Axis is moved to the X-Axis. Duplicating and removing charts To avoid building a chart from scratch, you can duplicate an existing chart on a report and then modify its formatting and data content, as necessary. You can remove charts from reports at any time. To duplicate a chart 1. Select the chart you want to duplicate. 2.
Web Intelligence removes the selected chart from the report.
Adding charts to reports
12 Formatting charts Overview You can personalize the formatting settings applied to charts.
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 Legend Floor Y-Axis Values Wall X-Axis Label Data 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 Chart data Y-Axis label Y-Axis values Right wall Floor Z-Axis label X-Axis label X-Axis values Z-Axis values 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.
To resize a chart 1. Select 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. Web Intelligence resizes the chart according to your modifications. Positioning charts on report pages You can specify where you want charts to appear on the report page.
Note: By default, Web Intelligence names each chart with a default name Block1, Block2, Block3, and so on. You can rename charts with meaningful names to make it easier to select the formatting options for a specific chart or to align charts in relation to other tables or charts on the report page. This section tells you how to: • name a chart • position a chart on the report page To name a chart 1. Right-click the chart where you want to name. 2. Select Format > Chart. The Format Chart dialog box appears.
The Format Chart dialog box appears. 3. Click the Page Layout tab. 4. Select the appropriate page layout options: If you want to... then select... Insert a page break so that the chart appears at the top of a new page, Start on a new page Display the chart on every page of the report, Repeat on every page Prevent page breaks from occurring on the chart, wherever possible, Avoid page breaks in a block 1. Click OK. Web Intelligence applies the page layout properties to the chart, as you specified.
6. In the Chart Title pane on the right, type the title you want to give to the chart. 7. To view the title on the report, click OK. Or If you want to format the title now, go to step 4, below. To format a chart title 1. Right-click the chart with the title you want to format. To see how to insert a title for a chart, refer to ”To insert a title for a chart” on page 138. 2. Select Format > Chart. The Format Chart dialog box appears. 3.
Note: Axis legends are not available for 3D Bar charts. You can set Web Intelligence to display the legend to either the left, right, or bottom of the chart. You can also hide the legend. legend To show or hide a legend and position the legend next to the chart 1. Right-click the chart you want to modify. 2. Select Format > Chart. The Format Chart dialog box appears. 3. Click the General tab. 4.
6. Click OK. Web Intelligence shows or hides the legend for the chart, as you specified. To set formatting properties for a legend 1. Right-click the chart you want to modify. Verify the chart already has a legend displayed. If you need to insert a legend first, refer to”To show or hide a legend and position the legend next to the chart” on page 140. 2. Select Format > Chart. The Format Chart dialog box appears. 3. Click the Appearance tab. 4. On the chart preview pane, click Legend.
Applying 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. A 2D bar chart without the 3D look A 2D bar chart with the 3D look To select a 2D or 3D look for a chart 1. Right-click the chart for which you want to select a 2D or 3D look. 2. Select Format > Chart. The Format Chart dialog box appears. 3. Click the General tab. 4. If you want to apply a 3D look to the chart, select 3D look.
Formatting axis labels Axis labels help identify the data represented by the values along each of the chart axis. Y-Axis label X-Axis label In this example, above, the Y-Axis label shows you that the dollar totals on the Y-Axis correspond to Sales revenue results and the X-Axis label shows you that the figures along the X-Axis represent dates for Year. This section tells you how to: • show or hide axis labels • format axis labels To show or hide axis labels 1.
4. Click Label on the X-Axis, Y-Axis, or Z-Axis preview pane, as appropriate. 5. Use the options in the formatting toolbar on the Appearance tab to format the text, cell background, and cell borders of the chart title. 6. If you want to apply formatting changes to other axis labels, repeat steps 4 to 5. Or If you have finished your formatting modifications, click OK. Web Intelligence applies your formatting changes to the axis label(s).
When the same data is displayed on a 2D chart it looks like this: In the second example, the 2D bar chart includes the optional Z-Axis with the values for quarter. Including data on the Z-Axis enables you to show an additional break down of the results displayed on the chart bars. Just like the 3D chart above, the [Sales revenue] measure is on the Y-Axis, the [Year] dimension is on the X-Axis, and the [Quarter] dimension is on the Z-Axis.
This chart only displays a grid on the Y-Axis: To show or hide an axis grid 1. Right-click the chart you want to modify. 2. Select Format > Chart. The Format Chart dialog box appears. 3. Click the Appearance tab, and then click Values on the appropriate axis on the preview pane on the left. The options available for the selected axis appear. 4. To show the grid on the selected axis, select Show grid. Or To hide the grid on the selected axis, verify that the Show grid option is not selected. 5. Click OK.
3. Click the Appearance tab, and then click Values on the appropriate axis on the preview pane on the left. The options available for the selected axis appear. 4. Verify that the Show grid option is selected. 5. Click the arrow next to the color wash button, and then either select a predefined color or click More colors... to define a custom color, and then click OK. 6. Click OK. Web Intelligence applies the color(s) you selected to the chart axes.
3. Click the Appearance tab, and then click Values on the appropriate axis on the preview pane on the left. Formatting toolbar The options available for the selected axis appear. 4. Use the options in the formatting toolbar on the Appearance tab to format the text, cell background, and cell borders of the chart title. 5. If you want to change the number format applied to numerical values or dates, click the arrow next to the Number drop-down list box, and then select the appropriate format. 6. Click OK.
The options available for the selected axis appear. 4. If you want Web Intelligence to display values along the axis at an automatic frequency, select Auto axis value frequency. 5. If you want to specify the frequency at which the values appear, deselect Auto axis value frequency, and then type the frequency you want NOTE: If you type “2” every two values appear on the axis; if you specify “3” every three values appear on the axis, and so on. 6. Click OK.
Displaying a linear or logarithmic scale for the Y-Axis By default, Web Intelligence displays the Y-Axis on charts as a linear scale. You can set the axis to a logarithmic scale. Logarithmic scales allow you to examine values that span many orders of magnitude without losing information on the smaller scales. Linear scales In a linear scale, the axis markers are evenly spaced. Linear scales are based on addition.
Or To display the Y-Axis as a linear scale, verify that the Logarithmic scale option is not selected. 5. Click OK Web Intelligence applies the type of scale you specified to the Y-Axis on the selected chart. Formatting how data appears on the chart body Results for data appear on charts as a series of bars, lines, markers, or segments depending on the type of chart.
To show values on pie charts as percentages 1. Right-click the chart you want to modify. 2. Select Format > Chart. The Format Chart dialog box appears. 3. Click the Appearance tab. 4. In the Data section, select Show values as percentages. 5. Click OK. Web Intelligence displays the percentage figure for each result on the corresponding segment of the pie chart. Formatting chart borders You can select colors and styles for the borders and backgrounds on charts. To select a border style and color 1.
4. In the Chart borders section, click the arrow next to the drop-down list box, and then select None. 5. Click OK. Web Intelligence removes the borders from the chart, as you specified. Formatting chart backgrounds, walls, and floors You can insert a color background behind charts. You can also show or hide walls and floors on charts and define their color. Left wall Right wall Floor You can show both walls and floors on 3D charts. You can show floors on 2D charts with a 3D look.
4. Click the chart body area on the preview pane. Chart body area The options to show the chart floor and walls appear. NOTE: You can show both walls and floors on 3D charts. You can only show floors on 2D charts with a 3D look. 5. If you want to show the floor and/or walls on the chart, select Show floor, Show left wall, and/or Show right wall, as appropriate.
If you remove the [State] dimension from the chart, Web Intelligence aggregates the sales revenue values per quarter and displays the sum of the sales revenue sum of all states per quarter: If you select the Avoid duplicate row aggregation option, each duplicate value (in this case, for quarter) is displayed on a separate bar on the chart: To apply or avoid duplicate row aggregation 1. Right-click the chart where you want to add a title. 2. Select Format > Chart. The Format Chart dialog box appears. 3.
Formatting charts
13 Dividing reports into sections Overview Dividing reports into sections enables you to group related information together in reports. This is particularly useful on large reports, since using sections also helps you navigate reports from section-to-section, using the hyperlinks in the Navigation Map. (For information on using the Navigation Map to view report sections, see ”To navigate to a section” on page 40.
You then select “Year” in the table column header and set “Year” as a section below “State.” Web Intelligence divides the report into sections by state with a sub-section per year. Inserting sections To make reports easy to navigate, you can divide the report into sections that group related data together. The tables and charts in a section only display the data relevant to the dimension on which the section is created.
• select a table cell – using this method, you can base sections on cells in tables and forms • select an object available in the document – using this method, you can base sections on any objects listed in the Available Objects pane to the left of reports To create a section from a table cell 1. With a Web Intelligence document open, right-click the table cell you want to make into a section. The contextual menu appears. 2. Click Set as section.
Web Intelligence creates the new section and inserts each value on the dimension that you selected for the section, into the section cell. Creating sub-sections You can create a report with multiple sections.
Web Intelligence creates hyperlinks to each of the section values and includes the hyperlinks in the Navigation Map pane. To see how to view the Navigation Map and navigate reports section-to-section, refer to ”To navigate to a section” on page 40. Setting page breaks for sections You can control page breaks within sections, by selecting the following two options: • start on a new page • avoid page breaks in section Defining page breaks on a section 1.
Web Intelligence applies the page layout properties to the section, as you specified. Positioning sections on the report page You can specify the position of a section on a report page, by specifying where you want the top of the section to appear. To position a section on a report page 1. With a Web Intelligence document open, right-click an empty area of the section that you want to format. NOTE: Do not click the section cell.
Note: Section cells can be formatted just like free standing cells or cells in tables. To format section cells see ”Formatting cell values” on page 114 or ”Formatting tables and cells” on page 111. To select a background color for a section 1. With a Web Intelligence document open, right-click an empty area of the section that you want to format. The background of the selected section is highlighted gray. NOTE: Do not click the section cell.
The following table will help you specify the file name: If... then... the image file has been installed by your administrator in the images directory on the BusinessObjects Enterprise server, type: boimg:// followed by the file name. For example, if the file name is “efashion_logo.gif,” then type: boimg://efashion_logo.
1. If you selected Normal as the image display format, you can specify how you want the image to be vertically and horizontally aligned by clicking the arrows in the list boxes next to Position and selecting the appropriate vertical and horizontal position. 2. Click OK. Web Intelligence applies the image you selected to the section background. To display a skin on a section background 1. With a Web Intelligence document open, right-click an empty area of the section that you want to format.
Make sure that the section is selected, and not the section cell. In this example, the section for Year is selected The contextual menu appears. 2. Select Remove Section. Web Intelligence removes the section and regroups the report data accordingly.
14 Organizing results on tables using breaks and sorts Overview You can organize the information displayed in reports, by using sorts and breaks, so that reports are easy to read and navigate. Sorts enable you to order results alphabetically or numerically and breaks enable you to group related information together in tables.
Note: You can apply multiple sorts to a table or section and then prioritize the sorts, so that the information is sorted in the order you require. To do this, you need to set your Reporting Rules so that Web Intelligence always makes the last sort you apply either (a) take precedence over other sorts or (b) be the last sort to be applied after other sorts. For full information on how to do this, see ”To define your reporting rules” on page 30.
• details – provide descriptive data about a dimension. For example: [Age] is a detail associated with the [Customer] dimension. • measures – Retrieves numeric data that is the result of calculations on data in the database. For example: [Sales Revenue] is the calculation of the number of units sold multiplied by the unit price. Sorting dimensions and details helps you organize results chronologically, while sorting measures helps you see highest or lowest results at a glance.
The Custom Sort dailog box appears. 2. Select an item in the list then click the down or up button to the right of the list to move the item up or down in the list. 3. Repeat step 2 until the items are sorted in the order you want. 4. Click OK. Web Intelligence sorts the items according to the custom sort order you specified. To remove a custom sort 1. Right-click the table column/row or section cell on which you have applied a custom sort, then click Sort > Custom Sort on the menu.
NOTE: You can also define a custom sort from the Sort Properties dialog box by clicking Custom Sort, which takes you to the Custom Sort dialog box. Defining multiple sorts You can apply multiple sorts to a table or section and then prioritize the sorts, so that the information is sorted in the order you require.
alphabetical characters, then the values are sorted in alphabetical order from top to bottom. You can change this sort order at any time. For information on how to apply sorts, see ”To apply an ascending or descending sort to a table or section” on page 169. Inserting, removing, and prioritizing breaks You can insert one or multiple breaks on a table or crosstab. You can insert breaks on any columns or rows that display values for: • dimensions - typically retrieve character-type data.
To prioritize breaks on a table 1. With a Web Intelligence document open, right-click the cells in the table break. The contextual menu appears. 2. Click Break > Properties The Break Properties dialog box appears. Each break on the selected table is listed in the pane on the left. The breaks are listed here 3. Select the break you want to move, and then click either the Move break up or the Move break down arrow. 4. Click OK. Web Intelligence reorders the breaks on the table, as you specified.
Break display properties You can set the following display properties for a break: Format option When selected... Break header A header is displayed for each group of results or break on the table. For example, this table has a break on “Year,” so each value for year has a separate break header: the break header for Break footer A footer is added below each group of results or break on the table. When you apply a calculation to the results, the result is shown in the footer.
Break page layout properties You can set the following page layout properties for breaks: Page layout property Description Start on a new page Displays each part of the table crosstab, or form created by a break on a new page. 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.
Organizing results on tables using breaks and sorts
15 Filtering results on reports Overview This chapter describes how you can limit the data displayed in your Web Intelligence reports by using report filters after the report data has been retrieved from the data source.
the Is Null operator. The appearance of the Quick Filter dialog box changes slightly depending on the operator you select to accommodate the number of values used with the operator.
To obtain data... for example... select... to create the filter... 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...
2. Click the Filter button on the Report toolbar or right-click and click Quick Filter on the menu. (To display the Report toolbar, click View on the menu, then click Toolbars > Report.) The Quick Filter dialog box appears. Filter operators The list of all values associated with the dimension The list of values used in the filter 3. Select the operator you wish to use in the filter from the drop-down list. For a list of all supported operators, see ”Operators available in report filters” on page 178. 4.
Intelligence returns the data that matches the criteria in either one of the filters linked by the operator. You can mix the AND and OR operators when combining and nesting filters. For example, you can link three filters in the relationship (Filter1 OR Filter2) AND Filter3. In this case, Web Intelligence first restricts the data by the conditions in either Filter1 or Filter2. Web Intelligence then compares this data with the condition in Filter3 and returns only the data that corresponds to this condition.
4. Select the dimension, detail or measure that you want to include in the filter, then click >> or drag the filter to the Filter pane to add it to the filter. 5. To remove a dimension, detail or measure from the filter, select it in the Filter pane, then click << or drag the dimension, detail or measure back to the Available Objects pane. 6. Select the dimension, detail or measure in the Filter pane. 7.
6. Select the dimension(s) that you want to appear in the nested filter and click >>, or drag them to the right of the operator that links the nested filter to the initial filter. The image below shows a nested filter in the Edit Filter dialog box. Nested filter Insert nested filter To change the order of nested filters 1. Select a dimension, detail or measure in the filter. 2. Click Move up or Move down to change the place of the dimension, detail or measure.
To view the filters applied to reports 1. Display the Document Structure and Filters by selecting Left panel from the View menu then selecting Document Structure and Filters from the drop down list at the top of the Left panel. The Document Structure and Filters displays the overview of the filters in the report. To edit a report filter 1. Select the table on which the filter is applied in the Left panel and click Edit Filter. The Edit Filter dialog box appears. 2.
2. Select the filter you want to remove. 3. Click Remove. You can also remove individual parts of the filter by selecting the dimension, detail or measure individually then clicking Remove.
16 Inserting standard calculations Overview You can insert predefined calculations into Web Intelligence reports. These predefined calculations allow you to perform standard business calculations on your data.
Inserting and removing calculations You can insert predefined calculations into Web Intelligence reports. These predefined calculations allow you to perform standard business calculations on your data. For a list of available predefined calculations, see ”Available predefined calculations” on page 187. You add calculations from the toolbar or from the right-click menu. To insert a calculation from the toolbar 1. Select the column to which you wish to add the calculation. 2.
Performing On-Report Analysis with Web Intelligence 189
Inserting standard calculations
Performing On-Report Analysis with Web Intelligence 191
Inserting standard calculations
17 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 View > Toolbars > Formula on the main menu.
NOTE: To display the Report toolbar, click View > Toolbars > Report on the Main toolbar. 2. Display the Formula toolbar by clicking View > Toolbars > Formula on the Main toolbar. 3. In the Formula toolbar, type the formula, then click Enter. For example, to calculate the average revenue generated per item sold, type: =[Sales Revenue]/[Quantity Sold] NOTE: Formulas always begin with ‘=’ and report objects always appear inside square brackets in Web Intelligence formulas.
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 by clicking View > Toolbars > Formula on the Main menu 3. Click the Formula Editor button on the Formula toolbar. 4. Build the formula by double-clicking objects, functions and operators to add them to the Formula box.
To find out more about Web Intelligence functions, see ”What is a function?” on page 229. About operators Operators link the various components in a formula. Formulas can contain five kinds of operators: • Mathematical • Conditional • Logical • Context • Function-specific You can see all the operators supported by Web Intelligence by clicking on the Operators tab in the Formula Editor. The following sections describe each type of operator.
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. Logical operators Logical operators are used in expressions that return True or False. You use such expressions in the If function. The Web Intelligence logical operators are AND, OR, NOT, Between and InList.
measures in default contexts depending on where they appear in a report. However, you can change these default contexts. This is what is meant by defining the calculation context. You can see default contexts by creating a block containing Year and Sales Revenue objects. In this case the Sales Revenue object returns the revenue by year, because revenue is evaluated in the context of the Year dimension.
When the calculation is in the...
The calculation is in the... The input context is... The output context is... Body of the block The dimensions and measures used to generate the body of the block. The same as the input context. Footer 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. VBody footer The dimensions and measures used to generate the current column.
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 198, 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 204, 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 199.
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 199.
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 199. 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 210 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.
18 Saving formulas as variables Overview ”Creating Custom Calculations” on page 193 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.
To create a variable using the Create New Variable dialog box: 1. Select the cell where you want to create the variable, or make sure that no cell is selected. 2. Click Create a variable. The Create Variable dialog box appears. If you had selected a cell, the current cell formula appears in the Formula box. If not, the Formula box is blank. NOTE: The Create a variable button is available on the Formula toolbar when you select a cell that does not already contain a variable or when no cell is selected. 3.
Table 0.1: Object Description Detail Provides descriptive data about a dimension. A detail is always attached to the dimension for which it provides additional information. For example, Age is a detail object that is associated with Customer dimension. Address provides additional information on customers. Measure Retrieves numeric data that is the result of calculations on data in the database. For example; Revenue is the calculation of the number of items sold multiplied by item price.
You can remove the #MULTIVALUE error by activating the Avoid Duplicate Row Aggregation block property. For more information, see ”Avoiding duplicate rows” on page 121. Editing and deleting variables After you have created a variable you can edit it or delete it. Editing a variable To edit a variable: 1. Select the variable in the list of report variables under Available Objects in the Left Pane. 2. Click the right mouse button and click Edit on the shortcut menu, or click Edit at the top of the Left Pane.
• calculate the difference between each number of items sold and the average, then square this value • add up all these squared differences • divide this total by the number of values - 1 Without the use of variables to simplify it, this formula is as follows: Sum((([Quantity sold] - Average([Quantity sold] ForEach [Quarter]) In Report)*([Quantity sold] - Average([Quantity sold] ForEach [Quarter]) In Report)) In [Quarter])/(Count ([Quantity sold] ForEach [Quarter]) - 1) This formula is clearly unwieldy.
To create an Average Sold variable: 1. Display the Formula toolbar by clicking View > Toolbars > Formula on the Main toolbar. 2. Make sure that no cell is selected. 3. Click Create a variable on the Formula toolbar. 4. In the Formula pane, type the formula: Average([Quantity Sold] In ([Quarter])) In Report Or Double-click items in the Available Objects, Available Functions and Available Operators boxes to build the formula. 5. Set the formula qualification to Measure. 6.
6. In the Name text box, type: Average Sold: 7. In the Name text box, type: Difference Squared 8. Click OK. Creating the Variance variable 1. Display the Formula toolbar by clicking View > Toolbars > Formula on the Main toolbar. 2. Make sure that no cell is selected. 3. Click Create a variable on the Formula toolbar. 4.
Saving formulas as variables
19 Using Web Intelligence functions Overview This chapter looks at the functions available in Web Intelligence. You often use functions when creating Web Intelligence custom calculations. 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.
Using functions Often you need to build additional calculations or objects into a report beyond the report’s base objects or calculations. In some cases existing report objects are sufficient to create these additional objects or calculations. For example, if you have Revenue and Number of Sales objects you can create the formula [Revenue]/[Number of Sales] to show the revenue per sale. More often you need to use functions to provide the additional calculations and objects you need.
the following table shows revenues by year and quarter. The percentage column contains the formula Percentage ([Sales Revenue]) In this case the function calculates each revenue as a percentage of the total revenue. The surrounding context is in this case the total revenue; this is the only revenue figure that is relevant outside the breakdown by year and quarter in the table. If the report is split into sections by year, the surrounding context outside the table becomes the total revenue in the section.
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.
Using Web Intelligence functions
20 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.
Parameter Description Mandatory? Values accepted sReportName The report to open if the target document is multi-report. No. (First report is the default value.) Report name for Web Intelligence documents, sub-report for Crystal Reports, pages for OLAP Intelligence reports.
Example: Example of sReportPart http:///businessobjects/enterprise115/?sPath=Sales+Reports&sDocNa me=Sales+in+2003&sReportPart=Part1 Parameter Description Mandatory? Values accepted sPartContext The data context of the report part (Crystal Reports only). Yes if a value is specified for The data context of the report part. sReportPart.
Example: Example of sInstance http:///businessobjects/enterprise115/? sPath=Sales+Reports&sDocName=Sales+in+2003&sReportPart=Part1&sInstan ce=User Parameter Description Mandatory? Values accepted sReportMode For Crystal targets only, indicates whether the link should open the full target report or just the report part specified in sReportPart. No. (Default is Full.
Parameter Description Mandatory? Values accepted sWindow Indicates whether the target report will open in the current browser window or a new window. No • Same (current window) • New (new window) Example: Example of sWindow http:///businessobjects/enterprise115/? sType=wid&sDocName=SalesReport&iDocID=2010&sWindow=New Parameter Description Mandatory? Values accepted lsS[NAME] Specifies a single value for a prompt. [NAME] is the prompt text. No.
Example: Example of lsM[NAME] http:///businessobjects/enterprise115/? sType=rpt&sDocName=SalesReport&lsMSelect+Cities=[Paris],[London] Parameter Description Mandatory? lsR[NAME] Specifies a range No. of values for a prompt. [NAME] is the prompt text. Note: Not supported by OLAP Intelligence. Values accepted A range of values separated by a double period (..).
Example: Example of lsU http:///businessobjects/enterprise115/? sType=wid&sDocName=SalesReport&iDocID=2010&lsU=http://www.site.com Parameter Description Mandatory? Values accepted lsC Specifies a universe context (BusinessObjects and Web Intelligence documents only). No. Universe context.
To pass the string “World Sales Report” in the sDocName parameter, use sDocName=URLEncode(“World Sales Report”). This ensures that the string is passed URL-encoded as “World+Sales+Report” The examples given earlier in this section do not specify the URLEncode() function explicitly. They give URL parameter values in URL-encoded form. Trailing spaces Trim trailing spaces at the end of parameter values and prompt names. Do not replace them with a plus sign (+).
If 8401682C-9B1D-4850-8B%2C5E%2CD9%2C1F%2C20%2CF8%2C1%2C62 is the URL-encoded unique name for the cube parameter opening the warehouse cube in the catalogue FoodMart 2000 on MSAS, you would use the following URL to open this cube parameter: http:///businessobjects/enterprise115/?sType=car&sIDType=InfoObje ct&lsS8401682C-9B1D-4850-8B%2C5E%2CD9%2C1F%2C20%2CF8%2C1%2C62=CATALOG%3 DFoodMart%202000,CUBE%3Dwarehouse&iDocID=616 Using the lsM parameter with OLAP Intelligence lsM paramet
21 Printing, Saving, and Deleting documents Overview You can easily print out Web Intelligence reports, to share information on paper. When you print reports, Web Intelligence renders the layout and data of the original reports into Portable Document Format (PDF) for optimum print quality. 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.
Adobe Acrobat reader launches and the reports appear in PDF format. 3. Print the document by using the Acrobat Reader Print command. Saving Web Intelligence documents to InfoView You save documents to InfoView. Depending on whether you want exclusive access to documents or whether you want to share documents with other users, you can save documents to personal or public folders. Note: Before you can save a document, you need to run the query at least once. To do this, click Run Query or Refresh.
The document is saved in InfoView. Saving documents as Excel, PDF, or CSV files You can save Web Intelligence documents in the following file formats: • Microsoft Excel spreadsheets • Adobe Acrobat PDF files • Comma Separated Values (CSV) 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.
To share Excel files with other users, you can save them to InfoView. For more information, see the BusinessObjects Enterprise InfoView User’s Guide. Generating Web Intelligence documents to PDF Creating a copy of your documents as a PDF file is especially useful if you want to provide other users with the document information in a printable format. The page layout and formatting of the Web Intelligence document is retained in the PDF file. To save a Web Intelligence document as a PDF file 1.
Note: You can only delete documents from InfoView if you have the appropriate security profile. To delete your Web Intelligence documents from InfoView 1. From the InfoView home page, navigate to the folder that contains the document you want to delete. The documents are listed. 2. Select the check box next to the name of the document you want to delete. 3. Click Delete. A message prompts you to confirm if you want to delete the selected document. 4. Click OK. The document is deleted from InfoView.
Printing, Saving, and Deleting documents
Index 2D charts description 134 2D look for charts 142 3D charts description 134 3D look for charts 142 508-compliancy 15 A accessibility 15 adding data to charts 128 AND operator 181 apply modifications automatically 30 Area charts 123 Available Objects view list 42 B background colors for headers and footers 86 Bar charts 123 blank cells for report titles 90 bookmarks insert to sections 160 breaks applying sorts 171 inserting on tables 172 page layout for 175 removing from tables 172 show table headers
repeat on all pages 137 select single color 151 set page breaks 137 setting frequency for values 148 setting min/max values 149 setting page breaks 137 show percentage values 152 show/hide grids 146 show/hide legend 140 show/hide values 151 sizing 135 swap axes 130 turn to different chart type 126 view chart types 125 cm set measurement unit 29 columns deleting from tables 107 moving in tables 108 contextual menu hide or show 29 creating formulas 193 variables 221 crosstab moving columns in 108 swapping row
setting 80 enhanced viewing mode 39, 80 Excel saving documents as 249 extended syntax modifying calculation context 203 removing 110 functions business examples 230, 232, 233 describing 196, 229 prototypes 229 using 230 F G filter operators values used with 177 filtering linked documents 235 filtering reports 35 filters combining 181 for drill 51, 53 multi-value for document linking 236 nesting 183 single value for document linking 236 viewing viewing 42 filters on reports 42 find text on reports 40, 42
inserting charts 124 HTML 100 images in reports 94 images in tables 118 images on report sections 97, 163 reports 77 skins in sections 165 table breaks 172 inserting on report sections 165 J Java Report Panel description 14 L labels for chart axes 143 format for chart axes 143 landscape page orientation 85 legends formatting on charts 141 showing on charts 140 Line charts 123 linking documents 235 locale defining 79 logging in 17 InfoView login 17 logging out 18 InfoView log out 18 M margins defining for
page numbers 87 page orientation defining 84 paper size for reports 85 PDF format for printing 247 saving documents as 250 Percentage function business example 230 percentages show on charts 152 Pie charts 124 description 135 portrait page orientation 85 positioning charts 137 sections on reports 162 predefined calculations inserting and removing 188 list of 187 printing Document Summary 43 documents 248 printing reports 247 prompts answering 35 cascading 35 changing values for 42 entering dates 36 on links
S saving documents as CSV 250 documents as Excel 249 documents as PDF 250 drilled results 54 formulas as variables 196, 221 new documents 248 scales for charts 150 Scatter charts 124 scope of analysis extending for drill analysis 52, 53 searching for text 40 sections description 157 drilling 48 formatting 162 formatting on reports 162 hyperlink to 160 inserting on reports 158 inserting sub-sections on reports 160 on reports 157 positioning on reports 162 removing 165 setting page breaks 161 sizing charts 13
setting frequency for 148 setting min/max for chart axes 149 show/hide on charts 151 variables creating 221 deleting 221 editing 221 formulas saved as 221 saving formulas as 196 use to simplify formulas 224 variance formula simplify with variable 225 view formats Draft mode 39 Page mode 39 view modes enhanced 39, 80 viewing available data 42 available objects 42 document properties 41, 81 Navigation Map 42 prompts 42 Z zoom 39 Performing On-Report Analysis with Web Intelligence 259