Web Intelligence, from first login to agent-built reports

Web Intelligence (often "WebI") is an on-premise reporting and dashboarding tool for business users. You use it on the web, on a desktop, or on a mobile device. The guide says it lets you filter data, drill down to more detail, merge data from several sources, and show data in charts. What you can actually do depends on your licence, your user rights and your security rights.

Key words you will see everywhere:

• Document: the file you open. It holds one or more reports (shown as tabs).
• Query: a business question you ask the application. It retrieves data. Example: sales margin per product over a period.
• Objects: the pre-defined pieces of data you pick to build a query (dimensions, measures and so on).
• Data source: where the data is stored. Examples: universes (.unx and .unv), existing Web Intelligence documents, Excel or text files, Google Sheets, BEx queries, SAP HANA views, Analysis View workspaces, OData web services, Free-Hand SQL.
• Data provider: what you build to pull data from a data source. Reports are made from the data in data providers. You can merge data providers and use them to create variables with the formula language.

Worked example (eFashion-style): your question is "Sales revenue and Margin by Year and State". That question is the query. Year and State are objects. The answer fills a table in a report.

Gotchas

• The administrator (CMC) can hide panels, panes, toolboxes, menus and menu items. If something in this guide is missing for you, ask your CMC administrator.
• Entry fields are not meant for personal data. The guide also says the application is optimised for a 1920x1080 screen at 100% scale.

The BI launch pad is the web portal you log in to. From it you find documents, start Web Intelligence, and set your settings.

It has five tabs: Home, Favorites, Recent Documents, Recently Run and Applications. The Home tab has six tiles: Folders, Categories, Documents, BI Inbox, Instances and Recycle Bin. As a Web Intelligence user you mostly use Documents, Folders and Instances. Next to a document, the actions menu lets you view, organise, schedule, send, edit and manage it.

To log in

Get the URL (for example http://[hostname]:8080/BOE/BI), your user name, password and authentication type from your administrator.

1. Open a web browser and go to the BI launch pad URL.
2. If the System box is blank, type the server name, a colon, then the port number. The default port is 6400.
3. Type your name in Username and your password in Password.
4. If there is an Authentication list, pick the type your administrator gave you.
5. Click Sign In. The BI launch pad home page appears.

To log out

Click the icon in the upper right corner, then click Log Out. Do not just close the browser. Logging out makes sure your changed preferences are saved.

To start Web Intelligence

Do one of these:

• Click Applications›Web Intelligence.
• Select Web Intelligence in the application shortcuts.

To open a document from the repository

1. Click the Documents tile (every document), or the Folders tile and use the tree to reach the folder.
2. Open the actions menu next to the document, then choose View (opens in Reading mode) or Modify (opens in Design mode).

To delete a document

1. Go to the document through the Documents or Folders tile.
2. Open the actions menu (or right-click), then click Delete. Click OK to confirm. You need permission to delete.

Worked example: you log in, click the Folders tile, find "eFashion Sales by Region", and choose View. It opens in Reading mode, ready to read.

Gotchas

• By default the server name is not shown on the log-on page.
• If Refresh on open is set in the document properties, the document shows the latest data every time you open it. This also depends on CMC settings your administrator controls (the Automatic Refresh security setting and the "Document - disable automatic refresh on open" right).
• View opens Reading mode. Modify opens Design mode.

The Java Applet and DHTML interfaces are gone. In 4.3 there are two clients, and they share the same redesigned interface.

The guide says Rich Client has the same features and capabilities as the web client. The differences it lists:

• Local and offline use: Rich Client.
• Build queries on Excel or text files saved on your own computer: Rich Client only. Excel and text files saved to the CMS work in both. Files on Google Drive work in both (Rich Client in online mode only).
• Send a document to another BI platform user: web client only.
• Save a document to the BI platform repository: web client directly. In Rich Client you save locally and then publish, in online mode only.
• Choose default local folders for documents and universes: Rich Client only.
• SAP HANA, BEx, Free-Hand SQL and Google Sheets queries in Rich Client: online mode only.
• Export to .csv, .pdf, .txt, .xlsx or .html: both.

Choose Rich Client when: you do not want to install a CMS or application server, you cannot reach a CMS (travelling, no network), you want to keep working through server problems, or you want better calculation performance.

The web client and Rich Client have the same main toolbar, panels and modes (see topics 5 and 6).

Worked example: you read your weekly eFashion report in the web client (no install). When you need to add a query on an Excel file saved on your own PC, you must use Rich Client, because the web client cannot build queries on locally saved Excel files.

Gotchas

• Rich Client has limits: you can view comments written in the web client but cannot create or edit them, importing universes is not supported, there is no built-in help, no Change Password menu, no full screen mode, and no Send by Email.
• You cannot install Rich Client and a BI platform server on the same machine.

In 4.3 the View and Modify interface choice (HTML, Applet, Desktop, PDF) no longer exists, because there is only one web interface. Preferences now live under Settings in the BI launch pad.

What you still choose:

• Which client opens a document for editing. In Settings›Application Preferences›Web Intelligence, the Open in Edit Mode option lets you pick Rich Client (after you have downloaded and installed it).
• The other Web Intelligence preferences on the same page: the locale used to format data, preferred document orientation, measurement unit, drill options and Excel preferences.

To change the settings

1. In the BI launch pad, open Settings.
2. Under Application Preferences, select the Web Intelligence tab.
3. Change the options and save.

Gotcha: View from the actions menu always opens Reading mode. Creating a document or choosing Modify opens Design mode.

Mode controls what you can do. Switch modes with the dedicated dropdown on the right of the toolbar. Reading is "look and explore", Design is "build and change", Structure is "build without data", and Data is "prepare the datasets".

Shortcuts from the guide: Alt + 1 Reading, Alt + 2 Design, Alt + 3 Design/Structure, Alt + 4 Data (Mac: Opt).

Instant Apply: in Design mode you can switch on Instant Apply, so each change to a report or a format is applied at once, without clicking Apply.

Worked example: a manager opens the eFashion document in Reading mode and drills from Year to Quarter. An analyst switches to Structure mode, adds a Margin column, then switches back to Design to see it with data.

Gotchas

• Without the "Reporting - enable formatting" right, Design and Data mode are not available.
• The guide recommends working with the structure when you make many changes, and populating the report with data when you finish.
• Data mode does not support delegated measures.

Components:

• Main toolbar: six sections: File, Data, Insert, Analyze, Display and Navigate. It opens, saves and prints documents, tracks data changes, shows the filter bar, drills, creates conditional formatting, refreshes, opens the query panel, folds and unfolds data, and so on.
• Filter bar: shows and manages the filters that affect your data: input controls and groups, prompts, filters, drill filters and element links.
• Main panel: always available in Reading and Design mode. It groups several panes (below).
• Build panel: in Design, Structure and Data modes. Its content depends on what you select. It has three panes: Data (feed, filter, sort, rank), Format (all formatting) and Properties (name, qualification, description, data type of the selected object).
• Quick Access panel: shortcuts to create your document.

Main panel panes:

• Objects: the objects from your data providers. Also where you manage variables. You can order them alphabetically, by folder or by query.
• Structure: the elements used in the document (reports, tables, charts, cells).
• Map: navigate the sections of the report you are viewing.
• Comments: view, add and manage comments.
• Properties: document properties and statistics, and some options.
• Shared Elements: shared elements used in the document and their instances.

The application remembers settings such as zoom, panel open or closed, filter bar, formula bar and Instant Apply. These are saved per user action and used when you create a new document.

Gotcha: the panes Document Summary, Navigation Map, Report Map, Input Controls, User Prompt Input, Available Objects and Web Service Publisher from older versions are not in the 4.3 guide. Use the panes above.

In the BI launch pad, use these paths:

• User Account›Account Information›Change Password: old password, then new password twice. (The application only asks you to change it when it prompts you. After the change you are logged out.)
• Settings›Account Preferences›Locale and Time Zone: Product Locale, Preferred Viewing Locale, Current Time Zone.
• Settings›Application Preferences›Web Intelligence: the locale used to format data, preferred document orientation, measurement unit, drill options, Excel preferences, and Open in Edit Mode (Rich Client).

Other display settings:

• Zoom: in Design mode, click the magnifying glass in the Display section of the toolbar and adjust the slider, 10% to 200%. In Reading mode, use the vanishing toolbar at the bottom of the report.
• Measurement unit: Settings›Application Preferences›Web Intelligence. Useful when you must fit headers or footers in a set space.

In Reading mode, depending on your rights, the toolbar lets you: create, open and save documents; undo or redo; export; mark as favorite; print (Print); send (Send to); refresh; show the filter bar; drill; show or hide tracked changes; track changes (Track Data Changes); maximise the screen (hide the main toolbars) and pin the toolbar; freeze headers; fold or unfold data; start Presentation Mode; open Help, Shortcuts and About.

The vanishing toolbar at the bottom of the report has: the page browser, zoom, the toggle between quick display and print layout, Fit to width and Fit to page, and a pin button.

If a button is missing, it is probably a right your administrator has not granted.

What and why. Look for a word, such as a store name, on screen.

Steps

1. In Web Intelligence Rich Client, press Ctrl+F to open the search bar. It searches the active window, dropdown lists and dialog boxes.
2. All matches are highlighted and the number of matches shows on the right.
3. Use the up and down arrows to move between matches.
4. Click the blue cross to delete the text. Click the black cross on the right to close the bar.

What and why. There are three viewing modes. Quick display is the default. It paginates by data, not paper size, and shows just the tables, reports and free-standing cells. Use it to analyse results, add calculations, breaks and sorts. Print layout simulates a printout or PDF with headers, footers and margins. Use it to fine-tune the look. Presentation mode suits dashboards: it refreshes the document regularly and locks the controls.

Steps

1. In Design mode, use the toggle in the toolbar. In Reading mode, the toggle is in the vanishing toolbar at the bottom of the report canvas. Off is quick display. On is print layout.
2. Open the Format panel with nothing selected on the canvas. Set Rows and Columns (quick display records) or Size, Orientation, Margins, Adjust to and Fit to (print layout).
3. For presentation mode, in Design mode go to the Display section of the toolbar and choose Presentation Mode. In Reading mode, click its button in the Display section. Options: Auto-refresh every, Switch reports after, Display in fullscreen, Show reports tabs, Show refresh bar, All reports.
4. Zoom: in Design mode, use the magnifying glass in the Display section. The range is 10% to 200%.

Freezing keeps part of a table on screen while you scroll. Use it on long tables so the column names stay visible.

You can freeze up to 5 data rows or columns.

Freeze headers

1. In the Display section of the toolbar, click the Freeze headers icon. By default it freezes the headers of every table in the report.
2. For more control: in Reading mode, right-click the table and use the quick actions menu. In Design mode, select the table, right-click it and click Freeze Headers.
3. In the dialog, for a vertical table choose whether to freeze header rows and how many columns. For a horizontal table choose header columns and how many top rows. For a cross table choose header columns, header rows, or both.

Unfreeze

• Click the same Freeze headers icon. It is blue when something is frozen. Clicking it unfreezes everything.
• For fine control, use the same dialog and enter 0 for the columns or top rows.

Worked example: a vertical table lists Store, Product line, Sales revenue, Margin for hundreds of rows. Click the Freeze headers icon and the column titles stay in view as you scroll. In the dialog enter 1 for columns to keep Store visible too.

Gotcha: it works in both Reading and Design mode.

Folding hides detail so you see the summary. In 4.3 the Outline button is replaced by Fold / Unfold.

• Folded section: details hidden, only free cells shown.
• Folded table or break: rows hidden, only headers and footers shown. Tables need headers and footers to be folded.
• Vertical, horizontal and cross tables can be folded.
• A table break can be folded only if its Enable fold/unfold property is on.

Steps

1. In the Display section of the toolbar, in Reading mode click the fold/unfold icon. In Design mode click the menu icon and select Fold / Unfold.
2. Click the fold and unfold icons for tables, breaks and sections. A separate icon handles cross tables: after you click it, choose rows or columns.

To show hidden content again: right-click the report and click Hide›Show All Hidden Content.

Worked example: a report has a section per State with a table of Cities. Turn on fold/unfold and fold every State section. You see only each State's free cells (for example its total Sales revenue). Unfold only California to see its cities.

Gotcha: folding and unfolding is not supported on mobile devices.

Print sends one or several reports to a PDF you can print. The application generates the PDF first.

1. Open a document.
2. In the File section of the toolbar, click the menu icon, then click Print (shortcut Ctrl+P).
3. Set the printing options and click Print to generate the PDF.
4. Open the PDF and print it.

Gotchas

• When printing, the application sets the report to print layout and drops quick display.
• If a report is wider than the paper width set in the layout, page breaks are inserted.
• The paper size and orientation for printing can differ from the ones you see in the Rich Client.
• Save As saves only in .wid format. To get PDF, Excel, text, CSV or HTML, use Export (topic 21).

From SAP BI 4.3 SP3 Patch 1, one command sends a document to a BI Inbox, email, FTP server, SFTP server, or file system.

1. Open a document.
2. In the File section of the toolbar, click the menu icon, then click Send to.
3. In the Send to dialog, pick your destination with the tabs.
4. Set the options for that destination.
5. Click Send.

Gotchas

• Destinations are defined in the CMC by administrators. If your profile does not allow a destination, it is missing or gives an error.
• Rich Client does not support Send by Email, and cannot send a document to another BI platform user.
• The Send to right also covers the Scheduler, the BI platform Inbox, and hyperlinks in email.

• Save As (in the File section, menu icon > Save As) saves the document in the .wid format only. Browse to the folder, name it, then optionally add a description and keywords, tick Refresh on open, tick Permanent regional formatting, tick Save document with comments, and pick Categories. Click Save.
• You can save only in a public folder where you have the right to save, or in your personal folder. Without the edition right, use Save As to make a copy. You cannot modify and save a scheduled instance.
• If you close without saving, you are asked to save.
• Export (menu icon > Export) writes .pdf, .csv, .xlsx, .txt or .html. In the side panel pick the format, tick the reports, set the Options tab for Excel, PDF or CSV, then click Export. The file is created on your computer. For Excel or CSV you can export datasets by choosing the Data radio button.
• You cannot export hidden reports in Reading mode.
• Export rights: Export the report's data covers Text, CSV, Excel, PDF and HTML. Export the cube's data covers CSV only. Rights also gate Refresh the report's data, Edit query and Send to.

In Rich Client, Save always saves on your computer. Use Save Copy for a copy and Publish to BI Platform Repository (online mode only) to put it in a CMS folder (topic 23).

Gotchas

• With Refresh on open on, the document is purged of data when saved and refreshed every time it opens.
• Excel export is limited to 16383 columns. Text export is limited to 5 MB by default (CMC setting).

Small icons flag chart problems. They show at the top left of the chart:

• Red X on white: chart cannot be generated (may be cache; try clearing temporary objects).
• White X in red circle: image not found. Ask your BI administrator to check load balancing and service monitoring.
• Yellow warning: for example dataset too large, need to refresh, other cube errors.
• Blue alert: limit for optimal rendering.
• Small yellow icon on a data point: data incompatible with the chart (for example negative values in a pie chart, a log scale, or inconsistent tree map values), if Show alert when incompatible data present is on.

To switch the incompatible data alert on or off: Format panel > Display Settings tab > Errors and Warnings section.

Gotchas

• Maximum 50,000 rows for charts. Only part of the data is drawn, with a warning and a tooltip. It is hard-coded and cannot be changed.
• The Hide warning icons in chart document property turns off the general warning icons.

A query is the question you ask the database. You build it in the Query Panel by choosing the objects you want. You run it, and the answer comes back into a report. You then analyse that answer further (filter, rank, chart). Think of the query as "fetch the data" and the report as "show and shape the data".

Data sources you can start from: No data source (blank document), Universe (.unx or .unv), Web Intelligence document, Excel, Text, Google Sheets, SAP BW (BEx query, BW/4HANA, S/4HANA), SAP HANA views, SAP Datasphere, Free-hand SQL, OData web service.

The data sources you can use depend on your client: the web interface (BI launch pad) or the Web Intelligence Rich Client (desktop). Almost everything works in both. The differences:

When you build a new query or change a source, the Select a Data Source dialog has a Recents list with your 20 latest sources. You can sort it by date, name or type.

Blank document (no data source)

Useful for a "template": title page, copyright text, formatted empty tables and charts. You connect it to a query later.

1. Open Web Intelligence (on the BI launch pad home screen, scroll to Applications and click Web Intelligence).
2. In Reading mode click the New icon in the toolbar. In Design mode, in the File tab, click the New icon. (Rich Client, just launched: click No Data Source in the New Document dialog.)
3. Select No data source, and click OK.

Gotchas

• Your rights (set by the BI administrator) decide which data sources you can use and whether you can create documents.

Objects appear in the Data Outline pane on the left of the Query Panel. In a report, the same objects appear in the Objects pane. You can arrange them by alphabetical order, query, data source or navigation paths.

Key idea: a measure always depends on the other objects in the query. Sales revenue with Store name gives revenue per store. Sales revenue with Year gives revenue per year.

Gotchas

• If an attribute returns more than one value for one parent value (bad universe design), the cell shows #MULTIVALUE.
• On OLAP connections, Web Intelligence only supports dimensions and hierarchies based on STRING data types. DATE or INTEGER data is converted to STRING.
• Smart measures are calculated by the database and returned already aggregated. In some cases this changes how calculations display.
• A hidden object (hidden by the universe designer) cannot be used in new reports. Old reports using it still work. If you remove it from a query, it is lost for good because it no longer appears in the universe outline.
• You can change some object properties after the query is built, in the Properties pane of the side panel (Name, Description, Aggregation, Formula; Qualification and Data Type for Text, Excel, Free-hand SQL and Google Sheets). Not every property is editable on every source.

Non-hierarchical queries use dimensions, attributes and measures. Each object gives one flat column. They do not include hierarchies, levels, members or named sets.

Hierarchical queries contain at least one hierarchy object. Each hierarchy gives a hierarchical column in the report that you expand to see child members. Measures aggregate depending on the member they sit next to.

Whether a universe query is hierarchical depends on the database the universe reads.

Worked example

• Non-hierarchical: State + Sales revenue gives one row per state.
• Hierarchical: a Geography hierarchy + Sales revenue gives revenue at all levels. Expand USA to see states, then expand a state to see cities.

Gotchas

• With two hierarchies in one query, you get every combination of members of both.
• Tip from the guide: when you run or refresh a BEx query with a hierarchical object, put it first in the Query Panel. It reduces execution time.
• For some OLAP .unv and .unx universes, you must select a measure.

A universe presents database data as business-named objects. You pick objects; you never write SQL. A universe holds relational data (dimensions, details, measures) or hierarchical data.

Steps

1. On the BI launch pad home screen, scroll to Applications and click Web Intelligence.
2. In the Select a Data Source dialog, click Enterprise Repository (or Local in the Rich Client), select Universe on the right, click OK and pick the universe. The Query Panel opens.
3. Drag dimensions and measures into the Result Objects pane.
   • Double-click a class folder to add every object in it.
   • Hover over an object to see its details. Right-click and select Object Description to copy the details.
4. Repeat until all objects are in.
5. Drag objects to the Query Filters pane to filter. For a quick filter, select an object in Result Objects and click the Add a quick filter icon in the Result Objects toolbar.
6. Set the scope of analysis and other query properties if needed.
7. To remove an object, click the Remove icon in the corner of the pane. Remove All clears the pane.
8. Click Run Query. With several queries, click the arrow next to the Run button and pick the one to run.

The Query Panel has four panes you can show or hide with toggles: Data Outline, Query Filters, Result Objects area with Data Preview, and Scope of Analysis. A bullet on the Query Filters button tells you the query has a filter.

Worked example

• Result Objects: Year, Store name, Sales revenue, Margin.
• No filters.
• Run Query. You get revenue and margin per store per year.

Gotchas

• If a document has two queries on the same universe and you change the source of one, the other is not changed unless you tick Apply changes in all queries sharing the same data source.
• Only some universe display properties carry over from the Format Editor. The guide lists the Data tab for .unx and the Number tab for .unv, but its wording is unclear, so check on your own universes.
• Stored procedures: both .unv and .unx relational universes support them (4.2 SP6 on). You cannot add query filters or sorts, or view or edit the script, on objects based on stored procedures.

Useful query-panel helpers

• Preview: in Design mode, open the Query Panel with the button in the Query section of the toolbar, then click the Data Preview toggle on the Query Panel toolbar. You need result and filter objects defined first.
• View script: click the Query Script Viewer icon on the Query Panel toolbar. SQL can be edited after you click Use custom query script (then Validate). MDX can be viewed, not edited. You cannot view the script of queries that call stored procedures. You cannot edit the script if the query has optional prompts: the prompt answers then appear directly in the SQL. In the Rich Client you can also Copy or Print the script.

Refresh a query to fetch current data. Refreshing re-runs the query and replaces the data. Prompts appear again (with your last values if Keep last values selected).

Refresh one query only

• Tick Refreshable in the query properties of each query that may be refreshed. If no query is refreshable, the refresh icon is disabled.
• In the Query section of the toolbar, open the dropdown next to the refresh icon and click Advanced Refresh. The dialog lists each query with its source, last refresh date, duration, rows and status. Select queries and click Refresh. Greyed-out queries have Refreshable off.

Refresh automatically

• In the Display section of the toolbar, select Presentation Mode. In Automatic Refresh set the interval, choose the reports to cycle through, then OK. You cannot edit the document in this mode.

Purge: in Design mode click Purge Data to empty the saved data of chosen queries.

Cancel

1. Click Cancel while the query is refreshing.
2. Choose:
   • Restore previous results
   • Purge data (structure and formatting stay)
   • Return partial results
3. Click OK.

Gotchas

• Cancelling works only if the database supports it. If it does not, control returns to you but the query keeps running in the background. The limit of abandoned queries is 10 by default. After that, you wait.
• BW databases cannot cancel after a refresh starts. The refresh finishes in the background.
• With "Return the partial results", the document mixes new and old values, so it does not reflect the query definition.

Parallel refresh [Advanced]

Web Intelligence can refresh several data providers at once. On by default, up to 64 per document. Per connection the default is 4 (text files: 1). Extra data providers wait in a queue. Excel data providers are not supported (they refresh one after another). Administrators set limits in the CMC: Servers›Web Intelligence Services > right-click Web Intelligence Processing Server›Properties›Maximum Parallel Queries (0 to 64; 0 disables). Enable Parallel Queries for Scheduling can be unticked there. For an OLAP connection: OLAP Connections > right-click > Organize›Edit›Maximum Parallel Queries (1 to 64; 1 = sequential).

What and why. One Web Intelligence document can hold several reports. Each report is a tab along the bottom of the report panel. Use tabs to split a document into pages with a clear purpose, for example one tab for sales by region and one for stock.

Steps (in Design mode)

1. Click the menu icon next to a report name. You can also use the Structure tab in the Main side panel.
2. In the contextual menu, choose to add, duplicate, delete, hide, show, rename, move, or copy a report link.
3. To hide a report, choose Hide. Then open the secondary panel and tick Hide always, or tick Hide when formula is true and enter a formula.

Worked example. Add a second report, rename it "Stock by State", and move it after the sales report. Hide a working report with Hide always so readers do not see it.

What and why. The application has three modes. Reading is for viewing reports, tracking changes, changing filter values on the filter bar, drilling and folding. Design (also called Edit mode) is where you build: add and delete tables and charts, apply conditional formatting, add formulas and variables, and work with the report structure or with data. Structure is the same as Design but with metadata only, so you see the skeleton of the report. Data mode is a separate mode for preparing datasets (cubes) before you design.

Steps

1. Use the mode dropdown on the right of the toolbar to switch between Reading, Design and Structure.
2. Shortcuts: Alt + 1 opens Reading mode, Alt + 2 Design mode, Alt + 3 Design/Structure mode, Alt + 4 Data mode (Opt on Mac).
3. In Design mode, use Instant Apply if you want the report to update after every format or data change. Otherwise use the Apply button in the panel.

Worked example. You plan a report of Year, Quarter, State, Sales revenue and Margin. Switch to Structure mode, lay out the tables, then switch back to Design mode once the layout is right.

What and why. A table (a "block") is how you show data.

• Vertical table: headers across the top, data in columns. This is what you get after the first query run.
• Horizontal table: headers down the side, data in rows.
• Cross table: one dimension across the top, another down the side, and a measure in the body at each crossing.
• Form: detailed information per customer, product or partner, such as address labels.

Steps: create

1. In Design mode, drag objects from the Objects pane onto the canvas. They become columns in a vertical table.
2. Drag more objects onto an existing table. Drop on a column border to add a column. Drop on the middle of a column to replace it.
3. You can also drag objects in the Data Assignment section of the Data panel (Build›Data›Feeding).
4. Or click the Insert table button in the Insert section of the toolbar. Pick another table type in its dropdown, click the canvas to place a ghost table, then drop objects onto it.

Change type: select the table, then in Build›Data›Feeding open the Turn Into section, pick a table or chart type and click Apply.

Add rows/columns: right-click a cell > Insert, then choose row above or below, or column left or right. Drag an object from the Objects pane into the new empty row or column.

Remove rows/columns: right-click > Delete, pick Row or Column, click OK. Move: drag a column or row before or after another (or reorder in the Data Assignment section). Swap: drag onto the one to swap with. Clear contents: right-click a cell > Content›Clear Content. Remove a table: right-click its top edge > Delete.

Worked example. Drag Year, Quarter and Sales revenue to the report for a vertical table. In Turn Into, pick a cross table. Put Quarter across the top, State down the side and Sales revenue in the body. Each body cell is revenue for one state in one quarter.

What and why. A single cell outside any table. Use it for report titles, last refresh date, page numbers or a one-number KPI. A blank cell takes any text or formula. Pre-defined cells show set information.

Pre-defined cells: Blank Cell, Comment (a general comment about the whole report), Drill Filters, Last Refresh Date, Document Name, Query Summary, Prompt Summary, Report Filter Summary, Page Number, Page Number/Total Pages, Total Number of Pages.

Steps

1. In Design mode, in the Insert section of the toolbar, click the insert cell button, or pick a pre-defined cell in its dropdown.
2. Click the report canvas to place it.
3. For a blank cell, type the text or formula in the formula bar. If you cannot see the bar, click its button in the Analyze section of the toolbar.
4. To insert an icon, choose Icon in the same dropdown, search or filter the list in the Insert an icon dialog, select one, click Insert, then click the canvas.

Hide: right-click the cell > Format Cell›Hide, then in the Format pane choose Hide always, Hide when empty, or Hide when formula is true and type the formula.

Copy: right-click > Copy, then right-click where you want it > Paste. You can also paste into Word or Excel.

Worked example. Insert a blank cell above a table and type the title text. Then add Last Refresh Date and Page Number/Total Pages in the page footer.

What and why. Change how values display, for example 1500000 as a currency or 0.07 as a percentage, without changing the data.

The format shown follows this priority: the formatting rule first, then the cell or chart, then the document object, then the universe.

Steps: assign a predefined format

1. To an object: in Design mode, select it in the Main›Objects tab, then Build›Properties›Edit Format.
2. To a cell or chart: right-click it > Format Display....
3. In the Format Display dialog box, pick a predefined format category, then a format from the list. Click OK.
4. To a formatting rule: Analyze›Formatting Rules..., select the rule, click the edit icon, click Format..., and under Display click Edit Format.

Steps: unassign. In the same Format Display dialog, select "No format is explicitly assigned. Use the format defined in the source object, if any".

Steps: custom format [Intermediate]

1. Open the Format Display dialog and select the Custom category.
2. To reuse one, pick it and click OK.
3. To create one, click Add Custom Format. Select the data type: Number, Date/Time or Boolean.
4. Type your format in the Positive, Negative and Equal to zero boxes (for Boolean: True and False). You can start from a predefined or custom format as a template.
5. Click OK, then OK again to assign it.

Worked example. Custom number format #,##0 shows 1234567 as 1,234,567. A format using [%]% shows 0.50 as 50%.

What and why. Put values in the order you want, such as stores from highest to lowest revenue.

Steps

1. In Design mode, select the table column and right-click it.
2. Click Data›Add Sort. An ascending sort is applied. The sort icon in the Data panel gets a subscript.
3. To change the order, open the sort tab in the Data panel and click the icon to switch to descending.
4. Remove: in the sort tab, hover over the object and click the delete icon.
5. Priority: in the sort tab, hover over a dimension, click the menu and choose Move Up or Move Down.
6. Custom order: hover over a dimension, click the menu and choose Create Custom Order. Use the arrows, Add Value and Reset Order. Click OK.

Sort orders: Default (ascending numbers, chronological dates, alphabetical text), Ascending, Descending, Custom Order.

Worked example. Sort State ascending, then add Sales revenue descending and move it second. States are alphabetical and revenue is highest first inside each.

A query filter limits the data that comes back from the database. Reasons: answer one specific question, hide data from some users, and keep the data small so it is faster.

A filter has three parts: filtered object, operator, operand. Example: [Country] In list (US;France). Country is the object. In list is the operator. US;France is the operand.

Filtered object: can be a dimension, attribute, measure, hierarchy or level. Except for BEx queries, it does not have to be in Result Objects.

Operand types

• Constant: type the value.
• Value(s) from List (List of Values): pick from the object's list of values.
• Prompt: asked at refresh.
• Object from this query (universe object): compare against another object.
• Result from another query: compare against another query's results.

Gotchas

• A constant operand is not allowed on a hierarchy, unless you use Matches pattern or Different from pattern.
• You cannot select a universe object as operand on some OLAP sources, or when the filtered object is a hierarchy.

Types of query filter

• Predefined filters: built by the BI administrator and saved in the universe. They are listed with the other objects. You cannot view or edit their parts. Double-click one, or drag it to the Query Filters pane. Sets are a kind of predefined filter built in the information design tool.
• Quick filters: a simplified filter set without opening the filter editor. One value gives Equal to. Several values give In list. Not available in BEx queries.
• Custom filters: you build them yourself.
• Prompts: asked each time you refresh (topic 8).

Add a quick filter

1. In Design mode, open the Query Panel (button in the Query section of the toolbar).
2. Select the object in Result Objects.
3. Click the Add a quick filter icon in the top corner of the Result Objects pane.
4. Select the values you want and click OK. The new filter appears in the Query Filters pane.
5. To remove it, select it in the Query Filters pane and click the remove icon.
6. Click Run Query, then save.

Add a custom filter

1. Drag the object to the Query Filters pane. The default operator is In list.
2. Click the operator dropdown and choose another.
3. Hover over the filter, click the icon and choose Constant, Value(s) from List, Prompt, Object from this query, or Result from another query (Any/All).
4. Type or select the value.
5. Remove with Delete, or Remove / Remove All in the corner of the pane.

Selecting from a list of values

• If the list does not show, refresh it or search. Some lists need an initial search.
• Lists can be sorted ascending, descending or in server order (the default) from the column header.
• Large lists may be split into ranges. Use the control above the list.
• Dependent lists (for example City depends on Country and Region) first ask for the parent values in a Prompts dialog.
• In OLAP or BEx queries, Show/hide key values shows unique keys.
• Search options (from the Search icon dropdown): Match case, Search in keys, Search on database. Wildcards are * (any string) and ? (one character). Put \ before them to match them literally.
• Search on database is useful when the list was cut off by Max rows retrieved.
• You can also type values separated by semicolons, and paste them from an Excel column.

Worked example (from the guide's pattern) "Which Texas stores made at least 130,000 margin in Q4 2002?" Filters, all joined with And:

• Year Equal to 2002
• Quarter Equal to Q4
• State Equal to Texas
• Margin Greater than or equal to 130000 Result Objects: Store name, Sales revenue, Margin only. Year, Quarter and State are removed from Result Objects so they do not show as columns.

Notes from the guide

• Wildcard in Matches pattern is % for every source except BEx, where it is *.
• With a hierarchical list, In list lets you pick members from different levels (for example Paris at City and Canada at Country). In a report filter, In list gives a flat list.
• Except is stricter than Different from or Not in list. Example: Lines Different From 'Accessories' removes accessory sales rows, but a customer who bought both still appears, with only non-accessory spend. Lines Except 'Accessories' keeps only customers who bought no accessories at all.

Gotchas (restrictions)

• Not Equal to, Greater than, Greater than or equal to: cannot be used on OLAP .unx parent-child hierarchies or BEx queries.
• Less than, Less than or equal to: cannot be used on OLAP .unx universes, hierarchies in filters, or BEx hierarchies.
• Between, Not between: cannot be used on OLAP .unx or BEx hierarchies in filters.
• Not In List: only certain hierarchies, for example level-based.
• Matches pattern: not for BEx hierarchies. Different from pattern: not for BEx or OLAP .unx parent-based hierarchies.
• Both: not for hierarchy objects or OLAP-based universes. Except: not for OLAP-based universes.
• Operators allowed by hierarchy type: level-based = Equal to, Not equal to, In list, Not in list, Matches pattern, Different from pattern. Parent-child = Equal to, In list, Matches pattern. BEx hierarchy = Equal to, In list.

Worked example

• Quarter In list (Q1;Q2) and Sales revenue Greater than 1000000: returns rows in Q1 or Q2 where revenue passes one million.
• Product line Not In List (Accessories) removes that line from the data.

Business questions often need several conditions. Filters in the Query Filters pane join with And by default.

Combine

1. Create the filters in the Query Filters pane.
2. Double-click the And operator to toggle between And and Or.

Nest (control the order of evaluation)

1. Drag an object onto an existing query filter. A new filter appears nested in an And relationship with the existing one.
2. Define the new filter. Change And/Or as needed.

Worked example (from the guide) "All sales in Japan, either in Q4 or with revenue over 1,000,000":

• And
  • Country Equal To Japan
  • Or
    • Quarter Equal To Q4
    • Revenue Greater Than 1000000 The Or pair is evaluated first, then restricted to Japan.

Gotchas

• Or is not supported on some OLAP sources: BEx queries, and OLAP .unx universes over Microsoft Analysis Services (MSAS) and Oracle Essbase.
• A mixed query can combine filter types: predefined, quick, custom, prompts.

A prompt is a filter that asks a question each time the document is opened or refreshed. Different people can then see different slices in the same report. It also cuts retrieval time. A prompt has a filtered object, an operator and a message. Example: Year Equal To ("Which year?").

Build a prompt

1. In Design mode, open the Query Panel.
2. Drag the object to the Query Filters pane.
3. Choose the operator (the list depends on the object type).
4. Hover over the filter, click the icon and select Prompt.
5. Open the prompt settings. Type the Prompt text (for example "Enter a City"). Optionally add a Prompt hint to explain how to answer. Hint text is not translated at run time.
6. Tick any of these options:
   • Prompt with List of Values: users pick from the object's list of values.
   • Select only from list: users can only choose existing values.
   • Keep last values selected: the prompt pre-selects the previous answer.
   • Set default value(s): type a value or pick defaults from the list.
   • Optional prompt: if left empty, the prompt is ignored.

Other prompt tasks

• Use an existing prompt: drop the object in the Query Filters pane, select Prompt, click Parameter from universe, pick one, then OK. Only compatible prompts show (for example same data type).
• Remove: hover over the prompt in the Query Filters pane and click the remove icon.
• Change prompt order: click the Query Properties icon on the Query Panel toolbar and use the arrows in the Prompt Order section. In a document you can also open the main panel, select Show prompts, and reorder prompts there (Design mode only; Reset All goes back to the default).
• Group prompts: in the Show prompts panel, create a prompt group of optional prompts. A group can be optional, or exclusive (only one prompt in the group is answered). An optional prompt can sit in one group only.
• Combine prompts with each other, or with fixed filters. Example from the guide: filters Year Equal to This Year and Job title Not equal to Senior Executive, plus a prompt "Which employee?". Users pick the employee but cannot see other years or senior executives.

Worked example

• Prompt on Quarter, Equal to, message "Which quarter?" with a list of values and Keep last values selected. Each person refreshes and picks their own quarter.

Gotchas

• For dates: if you want users to see a calendar, untick Prompt with List of Values (and Select only from list).
• Merged prompts: with several data providers, prompts with the same data type, same operator type and same prompt text merge into one message. The list shown is the one from the object with the most display constraints. You get a warning when you create one.
• On BEx queries and OLAP .unx universes, only And can link prompts.
• You cannot edit the script of a query with optional prompts.
• Complex prompts (several answer values for one prompt) work on BEx Selection Option variables and HANA Range variables.
• HANA universes: prompts for HANA variables and input parameters may already exist. Run the query before adding your own to avoid duplicates.
• The Prompts tab shows all prompts, including HANA and BEx variables and merged prompts.
• Hierarchies, levels and dimensions with a hierarchical list of values show as a tree in the prompt.
• In the script, a prompt shows as @prompt(...) syntax or as the supplied value, for example Resort_country.country In ('UK').

Open: in Design mode, open the Query Panel, then click the Query Properties icon on the Query Panel toolbar. Click OK to return.

Facts to remember

• Max rows retrieved is applied at the database if supported. Otherwise rows are thrown away after retrieval.
• Max rows retrieved does not distinguish levels in hierarchical data. With a limit of 3, a US > CA > OR > Albany table is cut after the third row.
• Sample result set works at the database level, in the generated script. It is more efficient than Max rows retrieved.
• Not Fixed means random: a different sample at each refresh.
• If Max rows retrieved is 2000 and Sample result set is 1000, you get at most 1000 rows.
• Your BI administrator's security profile can override you: set 400, profile allows 200, you get 200.
• By default queries can only be edited by their creator.
• Use query stripping with care: the query then fetches only what the report needs. It is not available for Free-hand SQL.

Worked example You are testing a query on every store transaction. Set Sample result set to 1000 and tick Fixed. Each refresh returns the same 1000 rows, so your test report is stable and fast.

What and why. A break splits one table into mini tables, one per unique value of a column, each with its own footer. Use it for subtotals, for example revenue per State inside one table.

Steps

1. In Design mode, right-click a cell in the column (not in a form).
2. Click Data›Add Break.
3. Remove: right-click the column > Data›Remove Break.
4. Manage: open the break tab in the Data panel. Hover over a break and use its menu: Move Break Up or Move Break Down. Click the settings icon for properties. Click Apply.
5. Value based break: in the break settings tick Value based break, click Values, choose values, OK.
6. Same level break: click the Add a Break dropdown in the Data panel, pick two or more objects, OK, Apply.

Break properties: Break header, Break footer, Apply Sort, Enable fold/unfold, Value base break, Duplicate values (Display all, Display first, Merge, Repeat first on new page), Start on a new page, Avoid page breaks in block, Repeat header on every page, Repeat footer on every page.

Worked example. In a table of State, City and Sales revenue, add a break on State. Each state becomes its own mini table. Insert a Sum standard calculation and the subtotal appears in each break footer.

What and why. A section splits a report into smaller parts, one per value of a dimension, each with its own table. Use it for "one page per State" or "one block per Quarter".

Steps

1. From a column: right-click the column > Set as Section.
2. From a dimension not in a table: in the Insert section of the toolbar click the section button, click the canvas, pick the dimension in the Define a New Section dialog, OK.
3. Remove: right-click the section cell or section > Delete.
4. Layout: right-click the section > Format Section›Layout Settings. Options: Start on a new page, Start instances on a new page, Avoid page breaks in section, Repeat section cell on every page. Click Apply.
5. Colours and images: right-click the section > Format Section›Appearance Settings.
6. Hide: right-click the section > Hide, then Hide, Hide When Empty, or Hide When with a formula (tick Hide when the following formula is true in the Format panel, then Apply).

Worked example. Table of City, Quarter, Sales revenue. Make Quarter a section. You get Q1, Q2, Q3, Q4 sections, each listing cities with revenue.

What and why. Keep only the top or bottom records, for example the top 3 states by revenue. It sorts and filters behind the scenes.

Steps

1. In Design mode, right-click the element (for example the block).
2. Click Data›Add Rank, then Add a rank.
3. Tick Top or Bottom and set the number with the - and + signs.
4. Pick the measure in the Based on list.
5. Optional: pick a dimension in the Ranked by list.
6. Pick a Calculation mode: Count, Percentage, Cumulative Sum or Cumulative Percentage.
7. Click OK.

Worked example. Block of State and Sales revenue. Ranking Top 3, Based on Sales revenue, mode Count, keeps the three highest-revenue states, sorted highest first. Mode Percentage with Top 10 keeps the top 10% of records.

What and why. Collect values of a dimension into a named group, for example New York, Washington and Boston into "Eastern branch offices". The grouped values are aggregated in the table.

Steps

1. In Design mode, select a dimension in the Objects pane, click its menu and choose Manage Groups.
2. Tick the values to group and click Group. Name the group in the New Group dialog and click OK.
3. Optional: use Ungrouped Values›Automatically Grouped to put all remaining values in one named group.
4. To remove values, show them with the All Groups dropdown, select them and click Ungroup. Click OK to close.
5. For a custom value order instead, choose Custom Order from the dimension menu.

Worked example. Group the cities in City into "East" and "West". The City column header becomes "City+" and a group variable appears under Variables in the Objects pane.

What and why. Hide tables, columns or empty rows to keep a report clean, or fold sections and tables to see only totals.

Steps: hide

1. Table: right-click its top edge > Hide, then Hide, Hide When Empty or Hide When... (formula). Click Apply. You can also hide a table from the Report Structure pane, but without these options.
2. Column or row: right-click it > Hide, with the same three options. In a vertical table you hide columns. In a horizontal table you hide rows. To hide just a dimension: right-click it > Hide›Hide Column or Hide Row.
3. Empty or zero rows: right-click the table frame > Format Table›Display Settings. In the Format panel, Columns and Rows section, use options such as Show rows with empty measure values and Shows rows for which all measure values = 0. Click Apply.
4. Redisplay: hover over the hidden table in the Report Structure pane > Show. Dimensions and measures: right-click the table frame > Hide›Show All Hidden Objects. Everything: right-click the report > Hide›Show All Hidden Content.

Steps: fold

1. In the Display section of the toolbar: in Reading mode click the fold button. In Design mode open its menu and choose Fold / Unfold.
2. Use the fold and unfold icons on tables, breaks and sections. For a cross table, pick rows or columns in the menu.

Steps

1. Open the Format panel in Design mode. Its tabs are Display Settings, Layout Settings, Appearance Settings, Text Settings and Style Settings.
2. Report: with the report selected, use Layout Settings for records per page, page size, orientation, scaling and margins. Use Appearance Settings for border and background. Use Display Settings to rename the report or tick Report header and Report footer.
3. Header or footer: select it, then use Layout Settings for size and Appearance Settings for border and background.
4. Background: select the report, header, footer, section, table or cells > Appearance Settings›Background. Pick a colour. Under Pattern, choose Skin, an image (URL or file) or Linear Gradient. Choose None to remove it.
5. Alternating colours: select the table > Appearance Settings›Alternate Color. Set Frequency and the colour.
6. Headers and footers: right-click the table frame > Format Table›Display Settings›Layout section. Tick or untick Header and Footer. Cross tables also have top and side headers and bottom and side footers.
7. Cross table labels: same place, tick Show object names.
8. Pagination: right-click the table frame > Format Table›Layout Settings. In Page Break, use Avoid page breaks and the repeat-on-every-page option (vertical and horizontal sub sections). In Layout, use Repeat vertical header on every page, Repeat horizontal header on every page and the matching footer options.

What and why. A chart shows your data as a picture. You pick the chart by the question you are asking. The 4.3 guide groups charts by "analysis type". Use the table below to choose. Some charts are "classic report elements" only, some are "widgets" only. The table says so where it matters.

The chart families, in plain words

• Bar: horizontal rectangles. Good for comparing similar groups, for example revenue from one period to another. Variants: Bar, Stacked Bar, 100% Stacked Bar.
• Column: vertical bars grouped by category. Good for change over a period or comparing items. Variants: Column, Column with Dual Value Axes, Combined Column and Line, Combined Column and Line with Dual Value Axes, Stacked Column, 100% Stacked Column, 3D Column.
• Line: connects values with lines. Good for trends over time. Variants: Line, Line with Dual Axes, Area.
• Pie: segments of a whole. You can have only one measure in a simple pie, or two in a pie with depth. Several measures? Pick another chart type. A Donut is a pie with an empty centre. Data labels can wrap: in the Data Values pane of the Format Chart tab, set the Text Policy option Wrap.
• Point: Scatter plot (XY points, no connecting line), Bubble (extra variable shown as point size), Polar scatter, Polar bubble.
• Radar (spider): several axes from one origin on a common scale.
• Waterfall (bridge): floating bars, each starting where the last ended. Good for increases and decreases.
• Map charts: Tree Map (nested coloured rectangles) and Heat Map (colours from a measure).
• Box plot: a five-number summary (maximum, minimum, first quartile, third quartile, median).
• Gauge: Angular Gauge, Linear Gauge, Speedometer. A gauge has a primary measure compared with a maximum and optional target and minimum.
• Tag cloud: words sized by relative weight.
• Geomap: data on a geographic map (topic 4.5).

Worked example. Question: "Which product line earns the most revenue?" That is a comparison, so use a Column chart or Bar chart with Product line and Sales revenue. Question: "How did revenue move over the four years?" That is a trend, so use a Line chart with Year and Sales revenue. Question: "What share of revenue does each product line have this year?" That is a proportion, so use a Pie chart (one measure only).

What and why. A new chart starts empty (a light grey "ghost chart"). You then "feed" it by dropping objects onto it. Each kind of object goes to a different "driver" of the chart.

Steps: add a chart

1. Open the document in Design mode.
2. In the Insert section of the toolbar, click the Insert chart button, or open its drop-down and pick a chart category and chart. The button then remembers the last chart type you picked.
3. Click in the report canvas to place a ghost chart.
4. Optional: to change the chart type, open the Data panel, expand Turn Into, click a chart category and select a chart. If the Data panel does not open by itself, open the side panel from the toolbar.

What each kind of object feeds

Steps: assign data. Do one of the following:

• From the Objects pane, drag dimensions and measures onto the chart.
• Drag them into the Data Assignment section of the Data panel.
• Right-click the ghost chart, click Assign Data, then drag objects onto the chart or into Data Assignment.

Worked example. Add a Column chart. Drag Product line to the category axis, Sales revenue to the value axis. Drag Year to Region Color, so each year becomes a coloured series.

What and why. You rarely need to rebuild a chart to see the same data differently. Turn Into switches the chart type in place.

Steps: change chart type

1. In Design mode, select the chart and open the Data panel.
2. On the Feeding tab, under Turn Into, click the drop-down next to a chart category and select a chart. Edit the chart values if needed.
3. Click Apply. The chosen template is applied to the block.

Steps: delete a chart. Right-click the chart frame and click Delete. Or in the side panel, select the Document Structure and Filters tab, right-click the chart name and select Delete. Or select the chart and click the Delete icon in the side panel toolbar.

Steps: set position. In Design mode, select the chart and open the Format panel. On the Layout Settings tab, use the Relative Position section to set the margins.

Steps: resize. Click the chart once and drag a handle on its border. Or use the Size section of Layout Settings (Width and Height).

Relative positioning. If you have more than one block, you can position a chart relative to another block. If new data changes the size of the blocks, they still do not overlap. If the reference block moves, the chart moves with it. Steps: select the chart, open the Format panel, Layout Settings tab, Relative Position section. Adjust the left, right, top and bottom margins, and say whether each margin applies to the report edge or to another report element.

Worked example. You built a Column chart of revenue by Product line. A colleague wants share of the total. Select the chart, open Turn Into, pick Pie. The same data now shows as a pie (one measure only).

What and why. Formatting makes a chart readable: titles, legend, colours, axis ranges, labels. Almost everything is in the Format panel.

Steps: open the Format panel

1. In Design mode, select the chart and open the Format panel from the side panel.
2. Use the tabs at the top: Display Settings, Appearance Settings, Style Settings and Layout Settings.
3. To format one part (title, legend, plot area, axes), use the drop-down next to the chart name at the top of the panel.
4. Click Apply to save your changes.

Common tasks (paths as the 4.3 guide writes them)

Stacking options (Value Axis > Stacking)

• Unstacked: no stacking.
• Stacked Chart: slice one dimension by another, for example revenue per state and year. Measures are not stacked.
• Globally Stacked: dimensions and measures in one stack per bar or column.
• 100% Stacked: each bar is 100% of the category. Good with three or more series. To make zero bars lie flat, choose Plot Area in the chart-name drop-down, open style settings and check Flatten zero values.

Formulas in chart elements. The formula editor works in the chart title, legend title, axis titles, and the axis minimum and maximum values.

Zero values. Display Settings tab > Dimensions and Measures section. For charts: Show measure values when values = 0 and Show measure values for which the sum of values = 0 suppress items that are zero. Empty values count as zero.

Worked example. Chart: Year on the category axis, Sales revenue as value, Product line as colour. In the Format panel, Display Settings›Value Axis > style settings > Stacking, choose Stacked Chart to slice each year by Product line. Then check 100% Stacked to compare product-line mix across years. Add data labels under Plot Area. Set the title with a formula in the formula editor.

What and why. Save keeps your document. Export hands a report or its data to someone who does not use Web Intelligence.

Save (web interface). In the File section of the toolbar, click the drop-down and choose Save As. Browse to a folder, name the file, then click Options to add a description and keywords. Optional: Refresh on open, Permanent regional formatting, Save document with comments. Click Categories, pick one or more, then click Save. You can save only in a public folder where you have the right, or in your personal folder. Without the edit right, use Save As to make a copy. You cannot save a scheduled instance.

Save (Rich Client). The guide says a document is always saved locally on your machine. You cannot save directly on the CMS.

Export reports

1. In the File section of the toolbar, click the drop-down and choose Export.
2. In the side panel, select the format. For Excel or CSV, make sure the Reports radio button is selected.
3. Tick the reports to export.
4. For Excel, PDF or CSV, open the Options tab to change the options. Set as default values saves them.
5. Click Export. The file is created locally.

Export datasets. File›Export, choose Excel or CSV, select the Data radio button, tick the queries or cubes, set CSV options if needed, click Export.

Autosave and recovery. If a session times out, the document is recovered. Open it from the Document Recovery tab of the Open a Web Intelligence Document dialog, or from the ~WebIntelligence folder in BI launch pad. Recovered versions are kept 24 hours by default. To delete one, use the delete icon in that dialog. Administrators set the size, auto-save delay and clean-up delay.

Worked example. Finance wants the quarterly revenue table in Excel to pivot it. File›Export, choose Excel, select Reports, tick the report, and choose to remove all formatting. The chart on that report arrives as an image. For a plain data feed, select Data, tick one query, and export to CSV.

New in 4.3: after a session timeout, you can recover your new and existing documents.

To open and save a recovered document

1. Click Open in the File section of the toolbar. A dot on the button (for example orange) tells you there are recovered documents. The tooltip says "Access your recovered documents here".
2. In the Open a Web Intelligence Document dialog, select the Document Recovery tab.
3. Select the recovered version and click Open.
4. If it is right, click Save in the File section. The recovered content goes into the original document.

To delete a recovered version

Open the Document Recovery tab, select the version, and click the delete recovered document icon.

Worked example: your session times out while you edit "eFashion Sales by Region". Next time, Open shows a dot. In Document Recovery you pick the latest version, open it and save.

Gotchas

• Recovered versions are kept 24 hours by default.
• Existing documents appear under their own name, latest on top. New documents appear as "Unnamed new document". You do not need to have saved a new document first.
• The dot shows only after you open a document that has recovered versions.
• You can also find them in the ~WebIntelligence folder in the BI launch pad.
• Administrators set the limits. Maximum autosaved data: default 30 MB. Auto-save interval: default 600 seconds (10 minutes), range 60 s to 24 h. Clean-up delay: default 24 hours, up to 30 days.
• The auto-save delay is the maximum work you can lose. It also depends on the server's Idle Document Timeout.

What and why. Comments let people discuss figures inside the report. They live in the Comments pane of the side panel. You can comment on a report (free cell), a section, a table cell, a report cell, or a visualization (chart or table).

Steps

• Global comment: in Design mode, in the Insert section of the toolbar, click the cell button and select Comment in the drop-down. Click the report page to place the cell. Open the Comments pane, type the comment, click Save.
• Section comment: the same steps, but place the cell inside a section. It shows only in that section.
• Cell comment: right-click the cell (twice for a table cell in Reading mode) and click Comments in the contextual menu. In Reading mode, a report cell uses the quick actions widget instead. Write the comment. A yellow ribbon appears at the top right.
• Visualization comment: right-click the chart or table. In Reading mode use the quick actions widget. In Design mode click Comments. A yellow ribbon appears.
• Copy a thread: select the element, open the Comments pane, click Copy All, highlight the text in Copy Comments, press Ctrl+C or Cmd+C.
• Delete: click the yellow ribbon, then the delete icon next to the comment in the Comments pane.
• Save with comments: Save As›Options›Save document with comments (off by default, greyed out without the right).

Showing a specific comment. The Comment() function takes parameters to show one comment when a cell has several. It works only with empty cells. The comments database has OptionKey1 to OptionKey4. Example from the guide: Comment("OptionKey1";"Validated") shows only the comment marked Validated. If several match, the first or last is shown, as set in the document properties.

Worked example. The Sales revenue for Texas in Q3 looks odd. Right-click that cell, Comments, type "Check promotion timing", save. A colleague replies from the Comments pane.

What and why. Scheduling means the BI Platform runs a document for you at a set time and saves the result. You do not have to open the report and click Refresh. Each successful run makes an instance: one version of the document holding the data from that run. You schedule so that people open a report that is already fresh.

Core words:

• Instance: a single version of a document or publication. The platform keeps a history of instances on the default Enterprise server. Open the list from the Instances tile on the BI Launch Pad home page, or from the actions menu next to the document > History. Columns: Instance time, Title, Status, Created by, Type, Parameters.
• Recurrence: how often the document runs.
• Prompts: questions that filter the data. When you schedule, you give answers up front.
• Formats: the file type of the saved instance.
• Destinations: where the instance goes. You can now pick several destinations in one schedule.
• Events: a trigger that must happen before the run starts.
• Delivery rules: conditions that stop empty or incomplete documents being sent.
• Publication: a bundle of documents sent to many people, each with their own filtered view.

What and why. Use this to get a report refreshed and delivered on a timetable, for example a weekly sales report that lands in your BI Inbox each Monday.

Before you start: the Web Intelligence document must have a context set. If it has several contexts, refresh it with the right one before scheduling.

Steps (BI Launch Pad):

1. Browse to the document in Recent Documents, or on the Documents or Folders tile.
2. Open the actions menu next to the document and click Schedule.
3. On the Instance Title tab, name the instance. By default it takes the document's name.
4. In the Select delivery destinations section, click Add. The default is Default Enterprise Location. Pick a destination from the Destination drop-down.
5. Set the Recurrence, Events and Scheduling Server Group sections.
6. Click the Report Features tab. Set the Output Format, Prompts and Delivery Rules sections.
7. Click Schedule.

Time zone: the default is local to the web server that runs the BI Platform, not the CMS you connect to. Check your time zone in BI Launch Pad preferences first. You also need the security right to schedule to each destination (File System, FTP, SFTP, SMTP, BI Inbox, Google Drive). If you cannot see or set preferences, ask your administrator.

Worked example. Document: "Sales by Store" (Year, Quarter, State, City, Store, Product line, Sales revenue, Quantity sold, Margin). Open its actions menu > Schedule. Instance Title: "Sales by Store - Monday". Destination: BI Inbox. Recurrence: Weekly, Monday. On Report Features, Output Format: Microsoft Excel - Reports. Click Schedule.

What and why. Recurrence is the pattern that decides when the document runs. Pick the pattern that matches how often the business needs the numbers.

For publications, the Recurrence property also has Number of retries allowed and Retry interval in seconds. They make the platform retry on failure (auto-retry).

Worked example. Month-end margin report: Specific Day of a Month, Day of the month, start time 06:00, with start and end dates covering the year.

What and why. Every run leaves an instance. You need to find the latest one, open an old one, pause a job or tidy your Inbox.

View instances:

1. On the BI Launch Pad home page, click the Instances tile. Or browse to the document in Recent Documents, Documents or Folders.
2. Open the actions menu > History.
3. To open one, use the actions menu next to the instance > View. For the newest one, use the document's actions menu > View Latest Instance.

A Web Intelligence instance can be edited, but you cannot save over it. Use Save As instead.

Pause or resume (only for instances with a Pending or Recurring status):

1. Open History as above.
2. Tick the checkboxes of the instances.
3. Open the actions menu next to them and click Pause or Resume.

Delete BI Inbox instances:

1. In the BI Launch Pad, click BI Inbox.
2. Click Organize›Delete All Messages.
3. Click OK to confirm.

Worked example. The job server is down for maintenance on Sunday. Pause the Monday report in History, then Resume it afterwards.

A query filter limits the data fetched from the database. A report filter only hides values in the report. The hidden data stays in the document. You can change or remove a report filter and see the hidden values without touching the query. Use report filters to slice a report you already have. Use query filters to keep the data set small.

A report filter has four parts:

• a filtered object
• an operator
• filter values
• the report element to filter (whole report, section, or block)

You can apply different filters to different parts of one report. For example, filter the whole report to one product line, then filter one table further to one region.

To see which elements are filtered, look for the filter icon next to them in the Structure pane of the main panel. The filter bar shows the filters that affect your data: input controls, prompts, filters, drill filters and element links.

Example. The query returns all US states. A report filter "State Equal to California" shows only California. Remove the filter and the other states are back. No new query runs.

In 4.2, simple report filters lived in a separate Report Filter toolbar. In 4.3 SP5 the same job is done by the filter bar and the Data panel. The filter bar displays and manages the filters that affect your data. The Data panel gives a quick way to add or edit simple filters by drag and drop. You can filter on dimension, measure or detail objects. For OLAP universes or BEx queries you can filter on hierarchies, characteristics or attributes, but not at hierarchy level and not on measures.

Steps

1. Switch to Design mode. You can add filters only in Design mode.
2. Open the side panel from the toolbar, then open the Data panel.
3. With no selection, the Filters pane applies to the whole report. Select a table or chart first and it applies to that visualization.
4. Drag an object from the Objects pane to the placeholder in the Filters section.
5. In the Select Values dialog, pick the values.

To show the filter bar, click the filter bar icon in the Analyze section of the toolbar. It also shows drill filters, prompts and input controls.

Example. Drag State into the whole-report filter placeholder and pick "California". The whole report shows California only. Drag in Year and pick 2006 to narrow further.

Operators compare the filtered object to your values. The guide lists these.

In list detail. In a query filter with a hierarchical list of values, In list lets you pick members from any level (for example Paris at City level and Canada at Country level). In a report filter, In list gives a flat list. Separate typed values with ;.

Example (eFashion). [Sales revenue] Greater than or equal to 1500000 keeps rows with at least 1.5M. [Quarter] In list Q3;Q4 shows two quarters.

Report filters can use any operator and filter on several values. They apply to the whole report or to one visualization (table, chart or section).

Create

1. In Design mode, click the side panel icon in the toolbar, then open the Data panel.
2. To filter one visualization, select it and open the Filters pane. To filter the whole report, clear the selection and open the Filters pane.
3. Drag an object from the Objects pane to the placeholder in the Filters section.
4. In the Select Values dialog, click the operator icon to choose the operator and see the advanced search options. The default is In List.
5. Select the values. You can type some values, depending on the operator.
6. Repeat to add more filters.

Edit or delete. The guide does not give separate steps. Open Manage Filters from the menu next to the Filters section of the Data panel to work with the filters on the element.

Example. Select the Sales table. Drag Lines to the Filters section. Operator In list. Pick Accessories and Outerwear. The table shows only those two lines.

Selecting values from a list of values [Intermediate]

• Search options in the Select Values dialog: Match case, Search in keys (only for lists that support keys), and Show keys (OLAP and BEx queries only). Match case is not available when Search in keys is on.
• If the list is split into ranges, the search covers all ranges.
• Wildcards: * is any string, ? is any single character. "March" matches "M*" or "Mar?h". Put \ before * or ? to match them literally.
• Dependent lists of values appear in prompts, where you answer the parent prompt first.

A nested report filter holds several filters combined with AND and OR. Use it for conditions like "State is California OR Quarter is Q3".

Steps

1. In Design mode, create a filter and add it to the existing list in the Data panel.
2. In the Data panel, click the menu icon next to the Filters section.
3. Click Manage Filters.
4. Double-click the operator to change it from AND to OR, or back.
5. Click Apply, then OK.

Example. Filter 1: State Equal to California. Filter 2: Lines In list Accessories;Outerwear. Joined by AND, you see only California rows for those two lines. Double-click AND to switch to OR and the result widens to California rows plus those two lines in any state.

Gotcha. Multiple filters default to AND. Double-clicking the operator is how you change it.

Drilling lets you look deeper into data to find the detail behind a good or bad result in a table, chart or section. You can drill on dimensions and measures, on hierarchical and non-hierarchical data. The guide's example: sales of accessories, outerwear and overcoats were much higher in Q3. Drilling down showed jewelry sales were much higher in July.

To enable drilling, click the Drill icon in the Analyze section of the toolbar and check Drill. You do not need this for hierarchical data, because the drill path comes from the hierarchy definition.

Restrictions

• BEx queries: you cannot use a Navigation path (formerly "drillpath"). It is replaced by the collapse/expand workflow on the real hierarchy.
• .unv and .unx universes: you can drill only if the drill paths were defined in the universe.

Scope of analysis [Intermediate]

The scope of analysis decides how many levels you can drill without a new query. Objects in the scope are part of the query, so drilling to them does not hit the database. You set it in the query panel (levels: None, One, Two, Three, Custom), if your security profile allows it.

Drill paths and hierarchies [Beginner]

A drill path is the route you follow when you drill. Paths come from dimension hierarchies the universe designer set: summary objects at the top, detail at the bottom. Time usually runs Year > Quarter > Month > Week. Custom hierarchies are possible. When you drill, measures such as Revenue and Margin recalculate for the new level.

Example. Drill on 2006 in a Year/Revenue table and you see Q1 to Q4 of 2006 with revenue recalculated.

To view drill hierarchies. In the query panel, click the icon next to the universe name and select Display by Navigation Paths.

Switch drilling on. Click the Drill icon in the Analyze section of the toolbar and check Drill. In Reading mode, once drilling is on, click a cell or data point to drill down.

Drill options. The Synchronize drill on report blocks option is in the BI launch pad preferences (Application Preferences›Web Intelligence, under the drill options). It controls how you drive your analysis:

What changed from 4.2. The 4.3 SP5 guide does not describe the old Drill toolbar, the "Hide Drill toolbar on startup" option, the "Start drill session" choices (existing or duplicate report), or drill snapshots. Drill filters now live in the filter bar (topic 10).

Gotcha. The guide describes drill mode the same way for the web interface and Rich Client. It does not call out a difference.

Drill down or up.

1. Enable drilling (Analyze›Drill) if you work with non-hierarchical data.
2. Select a table cell or chart data point. For a table cell, click twice: the first click selects the table, the second selects the cell.
3. In the contextual menu, click Drill, then Drill Up or Drill Down.
4. A new drill filter appears in the filter bar and in the filters section of the Data panel.

In Reading mode, after you enable drilling, click a cell or data point to drill down. Drill up to see how detail aggregates to a higher result. Drill down to see the detail behind a summary.

Drill by. Moves to a different hierarchy. It is only available for non-hierarchical data. Right-click a dimension value in a table or section cell, click Drill By, then choose the dimension.

Drilling on measures. Drilling on a measure value goes one level down for every related dimension in the block.

Worked example (eFashion).

1. A crosstab shows Accessories revenue fell in 2006.
2. Drill down on 2006. A drill filter for 2006 appears in the filter bar. The chart shows the fall began in Q4.
3. Drill down on Accessories. The crosstab shows which categories caused the Q4 drop.

Drill by example. A table shows quarterly revenue by state. Drill down on California would go to City. Instead, right-click California, choose Drill By, and go through the Products hierarchy to Lines. You see revenue by product line for California.

Choosing a drill path [Intermediate]

When a dimension is in several hierarchies, you must answer a prompt to select the drill path.

You can drill on dimensions (chart axes or legend) and on measures (bars or markers).

Enable drilling first: Analyze›Drill.

Example. A 3D bar chart has State on the X axis and Lines on the Z axis. Drill down on the bar for Accessories in California. State moves to City and Lines moves to Category. You see revenue per city per category for accessories.

When you drill on a value, the result is filtered by what you drilled on, and the filter applies to everything on the drilled report. Drill filters show in the Drill Filters section of the filter bar. Each filter has one or more values. Change the value to see other items at the same level (drill on California, then pick Colorado).

In the drill widget, the Free Elements section adds drill filters on other dimensions in the document. The Merged Dimensions section adds drill filters from merged dimensions.

Add or remove a drill filter

1. Click the Drill icon in the Analyze section of the toolbar and check Drill.
2. Click the filter bar icon in the Analyze section to show the filter bar.
3. Click the Drill Filters section in the filter bar, then click the add icon.
4. Select an object in the widget. It appears as a drill filter set to All Values.
5. Click the filter, select a value and click OK.
6. To reset a drill filter, set it to All Values. To remove it, hover over it in the filter bar and click the remove icon.

Refreshing with prompts. If a drilled report is filtered to 2003 and you refresh and answer the prompt with 2002, the report shows 2002.

Input controls are widgets (lists, entry fields, sliders) that let readers filter report data. In 4.3 they sit in the filter bar, which is designed for reading workflows. You link each one to tables, sections or charts, or to the whole document. Choosing values filters the linked elements. They also let you test scenarios: attach a slider to a variable with a constant value used in a formula and watch the formula result change.

Available input controls

The type table in the 4.3 guide lists List where 4.2 had Combo box, Radio buttons, List box and Check box. The guide's property text still mentions Combo box, Radio buttons, List box and Check boxes for null values, so it is not fully consistent.

Hierarchical data [Advanced]

In hierarchical input controls you can search by key if Show Keys is on. Turn on Enable complex selection to select members implicitly with the Children and Descendants functions in the filter bar.

Add (needs enough document modification rights)

1. In Design mode, click the input control icon in the filter bar. If you do not see the filter bar, click the filter bar icon in the Analyze section of the toolbar.
2. Click New Input Control.
3. Select an object. Give it a name and an optional description.
4. Check Document or Current Report. To tie a report control to one visualization, uncheck the report name on the left and check the visualization.
5. In the Type drop-down, pick a control type. The list depends on the object's data type.
6. Set the properties (below). Give a default value in Default values, or the control starts at All Values.
7. Click OK. The control now appears in the filter bar.
8. If you gave no default, click the control's name in the filter bar, pick values and click OK.

Properties

• Name and Description.
• List of values: all values of the object (default) or a custom list. You can paste values from an Excel column.
• Use restricted List of Values: with a custom list, values outside it are excluded even when nothing is selected. Example: restricted to US and France, a filtered table shows only US and France with no selection. If deselected, all countries show when nothing is selected.
• Sort List of Values: None, Ascending or Descending, applied on refresh. Not available for restricted lists.
• Allow selection of all values: shows or hides All Values. Hide it when aggregating makes no sense.
• Operator.
• Default value(s): can be a variable (dynamic default, for example yesterday's date). Not available for tree lists, spinners, sliders and double sliders.
• Enable complex selection: Children and Descendants for hierarchies.
• Reset on refresh: resets the default value each time you refresh.
• Allow selection of null values: makes [NULL_VALUE] available.
• Min Value, Max Value, Increment: for numeric controls.

Use. Click the control's name in the filter bar, pick values, search if needed. Linked elements filter. Example: Country = US with Equal To filters the table to [Country]="US". Click Reset to revert to the default value. In Reading mode, a reset-all icon resets every control. In Design mode use the menu > Reset All.

Edit

1. Show the filter bar and click the control's name. Pick values and click OK.
2. To edit properties, in Design mode open the menu > Manage Filter Bar, or use Advanced Settings from the control. In Manage Filter Bar, click the right arrow next to the control, edit, then OK.

Organize. In Manage Filter Bar, use the up and down arrows to reorder. To delete a control, use the menu > Delete, then OK.

Example. Create a list input control on Year, operator Equal to, for the current report. Pick 2006 and every table and chart in that report shows 2006. Pick [NULL_VALUE] (if allowed) to see rows with no value.

Gotcha. The old dependency Map view and the Dependencies tab are not in the 4.3 SP5 guide. Link controls to elements when you create them, or edit them in Manage Filter Bar.

What and why. Standard calculations are one-click totals on a table column: Sum, Count, Average, Min, Max and Percentage. Use them when you just need a quick total or average and do not want to write a formula. Each calculation adds a footer under the column.

Steps (insert).

1. In Design mode, right-click the table cell that contains the data you want to calculate.
2. Click Footer Calculation and select a calculation.
3. Repeat step 2 to add more calculations to the same column. One footer is added per calculation.

Steps (remove).

1. Open the document in Design mode.
2. Right-click the cell that contains the calculation and select Delete.

Tip from the guide. Double-click a cell to launch the Formula Editor toolbar and edit the formula.

Worked example. Table: Year, Sales revenue. Right-click a Sales revenue cell, then Footer Calculation›Sum. A footer appears with total revenue. Add Average too: a second footer appears.

What and why. If a standard calculation is not enough, write a formula. A formula can contain report objects, variables, functions, operators and calculation contexts. Example: revenue per item sold is [Sales revenue]/[Quantity sold].

Key rules

• Text in a report cell always begins with =.
• Literal text goes in quotation marks; formulas do not. =Average([Sales revenue]) is a formula. ="Average Revenue?" is text.
• Mix text and formulas with +: ="Average Revenue: " + Average([Sales revenue]). Keep a space at the end of the text so it does not touch the number.
• Parameters are separated by ; and enclosed in ( ).
• Function syntax is shown in the Formula Editor when you select the function. Example from the guide: num Abs(number) means one number in, one number out.

Steps: type a formula.

1. In Design mode, click the formula bar button in the Analyze section of the toolbar to show the formula bar.
2. In the Insert section of the toolbar, open the menu and choose Blank Cell, then drag the blank cell onto the report canvas. (Or select an existing cell.)
3. In the formula bar, type the formula in the dedicated field.
4. If the formula starts with a comment, add a carriage return after the comment so the cell displays properly.

Steps: use the Formula Editor.

1. In Design mode, select the table cell for the formula.
2. Click the formula bar button in the Analyze section of the toolbar to display the formula bar.
3. Click the Formula Editor button in the formula bar.
4. Double-click (or drag and drop) an object from the Objects panel, a function from the Functions panel, or an operator from the Operators panel.
5. Click OK to confirm and apply.

The Formula Editor has a code editor with parenthesis matching, syntax analysis, colour coding, auto-completion, keyboard shortcuts and line numbering. Hover over an object, function or operator for a tooltip. Click a function or operator to get a link to its help page.

To pick values from a list inside the formula: select an object in the Operators list, double-click Prompts to open the prompt editor and define a prompt, or double-click Values to open the List of Values dialog. Tick one or several values, then click OK.

Worked example. Add a column "Revenue per item": =[Sales revenue]/[Quantity sold].

What and why. A variable stores a formula under a name. Use variables to break a long formula into small parts that are easy to read and less error-prone. Variables live in the Objects pane, under the Variables section. Use the Description field to say what the variable is for; it shows when you hover over the variable in the Query Panel. You can edit it when you create, edit or rename the variable.

Steps: create a variable.

1. In Design mode, do one of the following: click the create-variable button in the Objects pane, or select a table cell and click the create-variable button in the formula bar. From the formula bar, the new variable is assigned to the selected cell.
2. Add a name.
3. Select a qualification (dimension, detail or measure).
4. Optional: add a description. Use the Show/Hide description panel toggle; the description field is hidden by default.
5. Build the formula in the dedicated text field. Drag and drop from the Objects, Functions and Operators panels if you like.
6. Click the check button to look for errors. If there is an error, a message helps you fix it, and the cursor highlights the error in the formula.
7. Click OK. The variable appears in the Variables section of the Objects pane.

Steps: edit, rename, duplicate or delete a variable.

1. In Design mode, in the Objects pane, select the variable and open its menu.
2. Choose Edit, Rename, Duplicate or Delete.
3. Make the change and click OK. A duplicate appears below the original with a number in brackets, for example (1).

Worked example (guide's variance example, in sales terms). Instead of one huge formula, create four variables: Average Sold = Average([Quantity sold] In ([Quarter])) In Report; Number of Observations = Count([Quantity sold] In ([Quarter])) In Report; Difference Squared = Power(([Quantity sold] - [Average sold]);2); Variance = Sum([Difference squared] In ([Quarter]))/([Number of Observations] - 1). You can read each piece on its own. (The built-in Var function does this in one step.)

What and why. Operators link the parts of a formula.

• Mathematical: + - * /. With text, + joins strings: "John" + " Smith" gives "John Smith".
• Conditional (used with If): = > < >= <= <>.
• Logical: And, Or, Not, Between, InList. They return True or False.
• Context operators: In, ForEach, ForAll (section 6).
• Function-specific operators: keywords some functions accept, for example Self with Previous, Row/Col, Top/Bottom, Distinct/All, IncludeEmpty.
• Where: restricts the data used for a measure (section 8).

Examples from the guide:

• If [Resort] = "Bahamas Beach" And [Revenue]>100000 Then "High Bahamas Revenue"
• If [Resort] = "Bahamas Beach" Or [Resort]="Hawaiian Club" Then "US" Else "France"
• If Not([Country] = "US") Then "Not US"
• If [Sales revenue] Between(800000;900000) Then "Medium revenue"
• If [Resort] InList("Bahamas Beach";"Hawaiian Club") Then "US Resort"

What and why. Where restricts which rows feed a measure, for example "revenue for one state only".

Examples from the guide:

• Average([Sales Revenue]) Where ([Country] = "US")
• Average([Sales Revenue]) Where ([Country] = "US" Or [Country] = "France")
• [Revenue] Where (Not ([Country] Inlist ("US"; "France")))

Worked example. Margin for one state: Sum([Margin]) Where ([State] = "California"). A variable "High Revenue" = [Revenue] Where [Revenue > 500000] shows the revenue or nothing; Average([High Revenue]) then averages only the high rows.

Syntax is as printed in the guide. Parameters separated by ;.

9.1 Aggregate functions

Notes:

• Count defaults to Distinct for dimensions and All for measures. Average excludes empty rows unless you add IncludeEmpty.
• Rank: Top is the default. Reset is over a section or block break by default. Example with reset: Rank([Revenue];([Country];[Year]);([Country])).
• RunningSum does not reset by itself after a block break or new section. To reset per section the guide recommends RunningSum(measure;section) form, for example RunningSum([Sales revenue];([Quarter])) in a section on Quarter. If you sort the measure, the running sum is computed after the sort.
• Sibling functions: RunningAverage, RunningCount, RunningMax, RunningMin, RunningProduct, Median, StdDev, Var.

9.2 Character functions

Gotchas:

• If any Concatenation input is a string, all inputs become strings. If both are numbers, they are summed: with a numeric A = 1, Concatenation([A];[A]) returns "2"; with a text A = 1 it returns "11".
• Count([Store]+[Quarter]+[Product line]) is the way to count combinations of dimensions.
• Color strings such as [Red] cannot be used in FormatDate or FormatNumber.

9.3 Date and time functions

Gotchas:

• RelativeDate: period defaults to days (DayPeriod). Other values: MillisecondPeriod, SecondPeriod, MinutePeriod, HourPeriod, WeekPeriod, MonthPeriod, QuarterPeriod, SemesterPeriod, YearPeriod. num must be an integer; negative goes back. If the day does not exist in the new month, the last day of that month is used.
• Dates passed to DaysBetween (and all date maths) must be in the same time zone.
• ToDate: the optional cutoff_year (default 2029) decides the century for two-digit years; a value below 100 gives an error. Use four-digit years where you can. ToDate returns #ERROR if the string cannot be read with the format you give. Use "INPUT_DATE_TIME" to follow the viewer's Preferred viewing locale; then both date and time must be in the string.
• Tip: write literal text in a FormatDate pattern between apostrophes, e.g. HH'h'mm.

9.4 Logical, data provider and miscellaneous

Gotchas:

• If: true and false values can mix data types. Without Else, non-matching rows show nothing.
• IsNull: placed straight in a column it returns an integer (1 true, 0 false); format it with a Boolean number format, or use it inside If.
• NoFilter: with no keyword it ignores report and block filters; All ignores all filters; Drill ignores report and drill filters. It applies to measures, not dimensions. NoFilter(obj;Drill) does not work in query drill mode.
• RowIndex returns #MULTIVALUE in a table header or footer.
• UserResponse with several chosen values returns them separated by semicolons.
• RefValue in a variable qualified as dimension or detail returns the current values, not reference values. Qualify the variable as a measure to get the reference values. Formulas typed directly in a table, section, form or chart are measures already.
• LastExecutionDate: you can omit the data provider if the report has only one. Not supported in SAP HANA Online mode (nor are RefValue, RowIndex, NoFilter, UserResponse).
• A few numeric helpers: Round(number;round_level): Round(9.45;1) returns 9.5; Round(123.76;-1) returns 120; Mod(10;4) returns 2.

Functions added in 4.3 (SP1 to SP3), with syntax as printed in the guide:

Also in 4.3: Trim, LeftTrim and RightTrim accept a character to remove, Pos accepts start and end positions, ToDate accepts a cutoff year, and formulas can contain comments.

Tip: you can format cells that return error messages using conditional formatting.

What and why. Colour or restyle cells by their value, for example red for low margin. It is dynamic: after a refresh the rules re-evaluate.

Works on: columns in vertical tables, rows in horizontal tables, cells in forms and cross tables, section cells, free-standing cells. A rule can change text colour, size and style, cell border, background (colours, images or hyperlinks), or replace the value with text, a formula, an image or a hyperlink.

Steps: build a rule

1. In Design mode, in the Analyze section of the toolbar, open the menu and click Formatting Rules.
2. Click the add icon.
3. Enter a name and description.
4. Click ... next to the Filter field. Choose to filter the cell contents, or an object or variable.
5. Pick an operator and an operand (type it or use the menu).
6. Click the add icon by the condition to add another test. All tests in a condition must be true.
7. For a formula condition, click Condition›Formula Editor. The formula must return True or False.
8. Click Add for further conditions.
9. Click Format under a condition and set text, background or border. In the Display tab you can set Read content as HTML, Image URL or Hyperlink.
10. OK, then OK.

Steps: apply Select the element > Analyze›Formatting Rules > pick the rule. Or right-click a column or row > Formatting Rules and tick the rules. Save.

Steps: manage In the Formatting Rules dialog, use the icons at the bottom to add, edit, remove or duplicate rules.

Worked example. Rule on Margin: if Margin is less than the block average times 0.8, red. The guide's formula style is [Sales revenue] < ((Average([Sales revenue]) In Block) * 0.8). Conditions run in order: first match wins.

What and why. Compare the current data with an earlier refresh and highlight what was inserted, deleted, changed, increased or decreased. Use it to find where to investigate, for example a store that dropped out of the top list.

Steps: switch on

1. In the Analyze section of the toolbar, open the menu and click Track Data Changes.
2. Choose the reference data:
   • Compare with last data refresh (Auto-update).
   • Compare with data refresh from and pick a refresh (Fixed data).
3. Select the reports that get data tracking. Tick Refresh data now if wanted. Click OK.

Steps: show and format

1. Show/hide: Analyze menu > tick or untick Show Changes.
2. Appearance: Analyze›Track Data Changes›Tracking Options tab. Pick each change type and click Format, then OK.

Worked example. Last month Store X had Sales revenue 1000 and this month 1200. After refresh with tracking on, the 1200 shows in the "increased" format. A store absent this month shows as deleted.

What and why. The same measure gives different numbers depending on which dimensions are used to calculate it. That set of dimensions is the calculation context. Understanding it is the key to nearly every "why is this number wrong?" question. A context has two parts:

• Input context: the dimensions that feed the calculation. Written inside the function's parentheses, in their own parentheses, separated by ;. Example: Sum([Sales revenue] In ([Year];[Store])).
• Output context: the dimensions at which the result is output (what it is "by"). Written after the function's closing parenthesis. Example: Min([Sales revenue]) In ([Year]).

Full form with both: Min([Sales revenue] In ([Year];[Quarter])) In ([Year]) means: work out revenue by year and quarter, then output the smallest of those for each year.

Default contexts (what happens if you write nothing)

Example from the guide: a report by Customer, in sections by Year. Report total = all revenue; section header and block footer = Year; each customer row = Year;Customer.

What and why. These context operators let you change the default context so a number is calculated at the level you need.

ForEach and ForAll are easier than In when the default context has many dimensions.

Steps. Select the cell, show the formula bar (Analyze section of the toolbar), open the Formula Editor from the formula bar and type the formula.

Worked example A: In. Block shows Year and Sales revenue. Quarter exists in the query but is not in the block. Add "Max Quarterly Revenue": Max([Sales revenue] In ([Year];[Quarter])) In ([Year]) Each year shows its best quarter. Because the block's default output context is Year, you do not have to write the output context.

Worked example B: ForEach. Same result: Max([Sales revenue] ForEach ([Quarter])) In ([Year]) Year is already in the default input context; ForEach adds Quarter to give (Year;Quarter).

Worked example C: ForAll. Block shows Year, Quarter and Sales revenue. To add yearly total: Sum([Sales revenue] ForAll ([Quarter])) Same as Sum([Sales revenue] In ([Year])).

Worked example D: percentage of total with explicit context. [Sales revenue]/(Sum([Sales revenue] In Report)) gives share of the whole report. [Sales revenue]/(Sum([Sales revenue] In Section)) gives share of the section (for example the year).

What and why. Keywords are shorthand for "this whole area" so you do not hard-code dimensions. Formulas keep working if dimensions are added or removed.

Examples (guide):

• Sum([Sales revenue]) In Report - total of all revenue.
• Sum([Sales revenue]) In Section - total for the current section, such as the year.
• Sum([Sales revenue]) In Break - total for the part between breaks.
• Average([Sales revenue]) In Block - respects a filter on the block, whereas In Section ignores it.

What and why. Use these for "versus last quarter" or "versus last year" columns.

Previous. Returns the previous value of an object. The result depends on the report layout, because it works on the rows as they are filtered and sorted.

• Offset default is 1. Previous([Revenue];2;NoNull) looks back 2 rows and returns the first non-null value from there.
• 2*Previous(Self) returns 2, 4, 6, 8, 10 ... (Self refers to the cell's own previous value).
• Previous([Revenue];([Country])) resets at each country.

RelativeValue. Returns a previous or later value, independent of layout. You give three things: the expression (a measure or a detail in the block), the slicing dimensions, and the offset. The function finds the row whose slicing-dimension values are offset rows away in the sorted list, while the other ("sub-axis") dimensions match the current row.

Worked example. Block: Year, Quarter, Store, Sales revenue. Column formula: RelativeValue([Sales revenue];([Year]);-1) Each 2008 row shows the revenue for the same quarter and store in 2007. Change the slicing list to ([Year];[Quarter]) and it becomes "same store, previous quarter". Swap the order to ([Quarter];[Year]) and it becomes "same quarter, previous year". Order matters.

What and why. A smart measure is calculated by the database itself (relational or OLAP), not by Web Intelligence. Classic measures (Max, Min, Count, Sum, Average) can be re-aggregated locally, so removing City from a block just re-sums. A smart measure needs the database to do it again, so you must refresh.

How it works. Each calculation context is a grouping set in the generated query. The first run includes the most detailed grouping set, for example (Country, Region, City). When you change the report so a new context is needed, such as removing City, the cells show #TOREFRESH until you refresh the data. Unused grouping sets are dropped at the next refresh. For databases without GROUPING SETS, the SQL uses UNION.

Steps (if you want it automatic). In the Document properties dialog, select the Auto-refresh document option.

Worked example. Query: State, City, Sales revenue (smart). Remove City from the block: Sales revenue shows #TOREFRESH. Refresh: the (State) grouping set is added and values appear. A column with [Sales revenue] ForAll ([City]) also shows #TOREFRESH first, because it needs the (State) grouping set.

A data provider is a query (or file) that feeds data into the document. A document can hold one or many, on different sources. Use several when data lives in different sources or when you want different queries on the same source. Guide recommendation: no more than 15 data providers per document.

Three ways queries relate

• Basic multiple queries: unrelated data from different sources.
• Synchronized queries: related through merged dimensions (you merge after running).
• Combined queries: UNION / INTERSECTION / MINUS (topic 15).

Add a query to an existing document

1. In Design mode, open the Query Panel.
2. Click the Add Query dropdown in the top left corner and select a data source.
3. Add objects to the query.
4. Click Run.
5. In the Add Query dialog choose: Insert a table in a new report, Insert a table in the current report, or Include the result objects in the document without generating a table. Click OK.

Manage queries

• Rename: open the menu beside the query name in its tab, select Rename, type the name, OK, then Run or Apply and Close.
• Remove: click the dropdown next to the query, Delete, then Yes.
• Duplicate: you must run the query first. Click the dropdown next to the query, then Duplicate. Handy for a variation on the same universe.
• Data mode (topics 20 and 21) is where you view and combine the data of your queries as cubes. It replaces the old Data Manager.
• Key dates (SAP BW or OLAP .unv sources): in the Query Panel, open the key date settings. Options: Use the default date for all queries, Set date for all queries, Prompt users when refreshing data.
• Purge data: in Design mode click Purge Data in the toolbar. In the Purge Data Providers dialog pick the queries. Optionally tick Purge last selected prompt values.

Worked example Query 1 on a sales universe: Product line, Sales revenue. Query 2 on a customer universe: Age group, Customers. Both feed one document and sit in two tables on one report.

New in 4.3. Data mode lets you prepare data before you design reports. You work with cubes. A cube is a list of objects plus its data. It can be the result of a query, or a cube you create in Data mode (a child cube or a combined cube). Data mode has its own toolbar and a graph showing your data providers, queries and cubes.

View a dataset

• Select a cube in the graph, or in Show main panel›Show document objects. A tab opens with a table of its data.
• Turn on distinct values to hide duplicate lines.
• Turn on facet view to see one facet per dimension, with a count of each value. You can aggregate by count or by another measure in the cube.
• In the properties panel, Show data shows the dataset. In the Data Assignment section you can remove objects, reorder them, or Reset. The sort panel sorts only what you see on screen, not what is saved.
• Toggle Show only visible cubes / all cubes to see either every cube or only what users see in Design mode.

Transform values You can transform string values to clean a dataset. In the dataset view, open the dropdown on a column or facet header and add a transformation:

• Upper Case, Lower Case.
• Replace: swap the text in Find what for the text in Replace with. Right-click a cell to start from that value.
• Trim: remove spaces or another character at the start, end or both.
• Fill: pad strings to one length with a pattern, at the start or end.
• Group: gather values under one name. In Manage Groups, select values, choose Create Group, and name it.

Objects with transformations show an icon in the main panel. The transformations tab of the properties panel lets you add, edit, remove and reorder them.

Create a child cube Select a cube and click Create Child in the toolbar. You get a copy linked to its parent, so the original data stays untouched. Its object identifiers differ from the parent's.

Worked example Query on a store universe returns State values typed as "CA", "ca" and "California ". Create a child cube, apply Trim and Upper Case, then Replace "CALIFORNIA" with "CA". The reports built on the child show one clean State value.

Gotchas

• Transformations only work on string values, and not on multidimensional data.
• Data mode does not support delegated measures.
• Transformed and combined cubes are not supported in shared elements, and are not exposed when Web Intelligence is used as a data source.
• Hide a cube or an object (context menu > Hide) so it does not appear in Design mode. Show reverses it.

New in 4.3. Combining cubes joins two or more cubes on shared keys, like a join in SQL. It is the Data mode way to synchronize data from different queries before you build the report.

Operators (for each secondary cube): left join, full join, inner join, left join without intersection, full join without intersection, append.

Combine cubes

1. In the graph, select two cubes with Ctrl-click or a lasso.
2. Click Create Cube in the toolbar.
3. In the Create Cube dialog, type a Name. Optionally add a description.
4. Use Move Up and Move Down to set the order in which cubes are combined.
5. For each secondary cube, pick the Operator.
6. Click Add Keys and choose the objects that match between the cubes.
7. Click Create. A tab shows the result, and the graph shows the new cube linked to its parents.

Add another combination Select the child or combined cube, then Ctrl-click the other cubes and click Add combination. The Edit Cube dialog opens for the first cube. Set operators and keys, then click Update.

Worked example Cube 1: Store name and Sales revenue by store. Cube 2: Store name and Target by store. Combine with a left join on Store name. Every store from Cube 1 is kept, with its target when one exists. An inner join would keep only stores in both.

Gotchas

• You cannot use hierarchies as keys, and cannot combine multidimensional datasets.
• Combined cubes created in 4.3 SP3 are no longer supported. They are removed from documents opened in Design or Data modes.
• Do not confuse this with combined queries (topic 15), which build UNION, INTERSECTION or MINUS in the Query Panel before the data is fetched. Cube combinations happen after the data is in the document.

What and why. When a report uses two queries (data providers), merging a shared dimension, such as Year, synchronises them so related values line up. Without it the second query's total repeats on every row.

Steps

1. In Design mode, in the Objects pane, hold Ctrl and select the dimensions or hierarchies. Click the menu icon and choose Merge.
2. The merged object appears in the Objects pane, with the originals beneath it.
3. Add to a merge: select the merged object, Ctrl-select more objects of the same data type, click the menu icon and choose Add to Merge.
4. Edit: click the menu next to the merged dimension. In Edit Merged Dimension set the name, Description and Source Dimension, then OK.
5. Unmerge: click the menu next to the merged dimension > Unmerge, or right-click one object > Remove from Merge. Click Yes.
6. Auto-merge: open the document properties in the toolbar and switch on Auto-merge dimension in the Data Option section.
7. Extend values: in the same properties, switch on Extend merged dimension values and click Apply.

Worked example. Query 1 has State and City. Query 2 has City and Sales revenue. Unmerged, each State/City row shows the grand total. Merge the two City dimensions and each city shows its own revenue.

You can filter one query using values returned by another. Example: keep countries in Query 1 that also appear in Query 2, by filtering [Query 1].[Country] on [Query 2].[Country].

Steps

1. Add the filter object, click the icon on the filter and choose Result from another query (Any) or (All).
2. Pick the filtering query and object.

Supported combinations

Gotchas

• The filtered query must be on a relational universe. The filtering query can be relational, OLAP or local.
• The filtering query only shows in the list once it has been run or saved.
• Performance can suffer with large data because of conversion and formatting. Use it with small data sets.
• If you do not choose an operator from the table, the Result from another query menu item is not available.

A subquery is a smarter filter. It changes the generated SQL so an inner query restricts an outer query. Use it when an ordinary filter cannot ask your question. It can compare values of one object against values of another, and can add a WHERE condition. Guide example: "customers and their revenue where the customer bought a service that was reserved (by any customer) in Q1 of 2003".

Parameters

• Filter object: the object whose values restrict the result.
• Filter by object: decides which Filter object values the subquery returns.
• Operator: relation between the two.
• WHERE condition (optional): extra condition on the Filter by object. Can be a report object, predefined condition, or existing filter or subquery.
• Relationship operator: AND or OR between multiple subqueries. Click it to toggle.

Build a subquery

1. In Design mode, open the Query Panel.
2. Add your result objects.
3. Select the object in Result Objects and click the Add a subquery icon in the Query Filters pane. The object appears as both Filter object and Filter by object.
4. To add a WHERE condition, drag an object or a predefined filter into the area below the "Drop an object here" boxes. Set operator and value.
5. To add another subquery, click the add icon. By default they are joined with AND. Double-click AND to toggle to OR.
6. To nest, drag an existing subquery onto another. Hold Control while dragging to copy instead of move.

Worked example (eFashion-style, following the guide's pattern) "Customers and revenue where the customer bought a Product line that was sold in Q1 of 2003." Result Objects: Customer, Sales revenue. Select Product line, click the subquery icon. Add WHERE conditions Year Equal to 2003 and Quarter Equal to Q1. Run Query.

Gotchas

• Not every database supports subqueries. If not, the option does not appear.
• You cannot use hierarchical objects in subqueries.
• If the two objects share no values, the subquery (and so the query) returns nothing.
• Some operator and Filter by combinations get rejected by the database. Example: Equal to with a Filter by object that returns many values. The database error text is shown.
• Several objects in Filter object or Filter by have their values concatenated.

A combined query is a group of queries that return one result. Three relationships:

• UNION: all data from both, duplicates removed.
• INTERSECTION: data common to both.
• MINUS: data in the first and not in the second.

Example from the guide: Query 1 = US, UK, Germany, France. Query 2 = US, Spain. UNION = US, UK, Germany, France, Spain. INTERSECTION = US. MINUS = UK, Germany, France.

Why: answers questions that are hard in one query. Guide example: one list of years where more than n guests stayed OR more than n guests reserved. Those two measures are incompatible in one block, but a UNION of two queries works.

Build

1. In Design mode, open the Query Panel and build the first query.
2. Click the Add a combined query icon on the Query Panel toolbar. The Combined Queries pane appears under the objects. The new query is joined to the first with UNION and named Combined Query #n.
3. Select a query in the pane to work on it. Delete a query: select it and press Delete, or drag it to the universe outline.
4. Double-click the operator and select UNION, MINUS or INTERSECTION.
5. Build each query normally and click Run Query.

Rules

• Every query must return the same number of objects, of the same data types, in the same order. You cannot combine Year with Year + Revenue.
• Keep the meaning sensible. Year with Region may be allowed by type but is rarely useful.
• Precedence: results are computed pair by pair in order. For INTERSECTION of Q1, Q2, Q3: first Q1 and Q2, then that result with Q3.
• Nest for control. Click the add combined query node icon, drag a query onto another, then set each group's operator. The new node is UNION by default. Example: (Query 1 MINUS Query 2) INTERSECT Query 3.

Worked example Query 1: States with revenue above 1M in 2002. Query 2: States with revenue above 1M in 2003. INTERSECTION gives states above 1M in both years. MINUS gives states strong in 2002 but not 2003.

Gotchas

• Relational universes only. The guide says in one place that the option is not available for OLAP or .unx relational and only for .unv relational universes. Check your universe type.
• Not supported on Excel, Text, Google Sheets or Free-hand SQL queries.
• If the database cannot do the combination, Web Intelligence runs several queries and combines the result after retrieval.
• If the database supports it directly, precedence follows the database's own rules. Ask your database administrator.
• Locale matters: the guide says groups are processed right to left and top to bottom in a left-to-right locale, and the opposite in a right-to-left locale.

Contexts

An ambiguous query has an object that can mean two things. Example: Country as the country where a holiday was sold, or the country where it was reserved. The universe designer defines contexts (for example a sales context and a reservations context). You can mix objects within one context, or across contexts. If Web Intelligence cannot choose, it asks you to choose a context when you run or refresh.

• To choose a context: run the query or refresh the document. The Select a Context dialog appears.
• To re-ask each refresh: Query Properties›Reset contexts on refresh›OK.
• To drop the saved answers: Query Properties›Clear contexts›OK. The next context prompt still shows the last choice; remove it first if you want another.
• Before you schedule a document with several contexts, run it once and pick a context.
• With several contexts, the Data mode lists them as Result n.

Scope of analysis

Extra data fetched so you can drill down later. It is kept in the data cube, not shown in the first report. Only available for relational .unx universes (not OLAP, not BEx).

Levels: none (only Result Objects), one / two / three levels (levels below each result object in the hierarchy), custom (dimensions you drag in).

Steps

1. In Design mode, open the Query Panel.
2. Click the Scope of Analysis toggle. The pane appears at the bottom. Default is None.
3. Pick a level in the Scope level dropdown, or drag dimensions from the data outline into the Scope of analysis pane.
4. To deactivate, set Scope level to None and click Run Query.

Worked example Result Objects: Year, Sales revenue. Scope of analysis: one level. The Quarter data is fetched with the query. Later you can drill from Year to Quarter without a new database trip.

Gotchas

• A scope of analysis increases document size significantly. Use it only if users will really drill.

Free-hand SQL (FHSQL) lets you write or paste your own SQL against a relational database. Use it when you need advanced database functions the semantic layer does not support. It uses secured relational connections published by the BI administrator.

Build

1. On the BI launch pad home screen, scroll to Applications and click Web Intelligence.
2. In the Select a Data Source dialog, click Enterprise Repository, select Free-hand SQL on the right, and click OK. (To add FHSQL to an existing document, use Add Query in the Query Panel and pick a Free-hand SQL source.)
3. Select a relational connection.
4. Enter or paste a SQL statement.
5. Click Validate. The database runs it and returns any error.
6. Fix errors, then OK.
7. In the Query Panel you can view the objects, edit their properties, or change the connection.
8. Click Run Query.

Object properties (defaults): Name = column name. Qualification = Dimension for STRING and DATE/DATETIME, Measure for NUMBER (can be Dimension, Measure, Attribute). Aggregate function for a measure = Sum by default (None, Sum, Max, Min, Count, Average). You can set an Associated Dimension for an attribute. The guide says you cannot change the data type in the FHSQL object table.

Query properties: Max Rows Retrieved, Max Retrieval Time (s) and Refreshable. The two limits are off by default. If a limit is hit you get a partial result set.

Prompts and variables: you can use @Variable and @Prompt in the SQL.

Worked example SELECT STATE, SUM(SALES_REVENUE) FROM SALES GROUP BY STATE gives State (dimension) and a Sales revenue (measure, Sum by default). (Example SQL: your own table names will differ.)

Gotchas

• The BI administrator must grant the connection right Use connection for Free-Hand SQL scripts (denied by default) together with Query script - enable viewing. Without both, the connection does not appear.
• Only the first result set is shown if the SQL returns several.
• You cannot use Change Source on an FHSQL query.
• Combined queries, subqueries, list-of-values filters and query stripping are not supported. Only constants and prompts work in filters.
• Not supported: DROP TABLE, TRUNCATE TABLE, DELETE FROM, CREATE TABLE, ALTER TABLE, INSERT, UPDATE. No SQL BLOB/BINARY types. ORDER BY is accepted but does not sort the report; sort the block, or sort on a variable built with RowIndex().
• If you submit changed SQL: new columns become new objects, same-name-and-type columns are kept, unmatched old objects are deleted. (The guide also lists "changing the SQL" as unsupported, so test this on your system.)
• Hadoop sources work, but custom SQL does not.

Files (Excel, Text, Google Sheets)

• Web interface: the file must be in the BI Platform repository, Google Drive, or Microsoft OneDrive (including SharePoint Online). In the Select a Data Source dialog click SAP BI Platform Repository and select Excel or Text; use the upload button to put a local file in the repository. For cloud files click Cloud Storage and pick Google Drive or Microsoft OneDrive. The administrator must set up OAuth in the CMC. Upload limit is 100 MB by default.
• Rich Client: click Local, select Excel or Text. Only the Rich Client reads local files, and only in online mode.
• Excel import options: sheet name, field selection (all fields, range definition, range name; contiguous cells only), and First row contains column names.
• To edit the query, open the Query Panel in Design mode. A new source file must have the same structure as the old one.
• Not yet supported for these sources: combined queries, change source, subqueries and lists of values in filters (constants and prompts only), query stripping.

BEx queries (SAP BW)

• Built in SAP BEx Query Designer, reached through a BICS connection. No universe needed. You cannot rename, modify or add metadata.
• The BEx query needs the flag Allow External Access to the Query enabled. Connection authentication must be predefined at creation (prompted authentication is not supported).
• Many features are missing for BEx: quick filters, Or, Sample result set, Retrieve duplicate rows, max retrieval time, and several operators on hierarchies.
• BEx variables become prompts. A mandatory variable with no default must be answered.

SAP HANA

• HANA Direct Access: pick SAP HANA as the data source, choose a secured relational or OLAP connection and a HANA view. A transient universe is generated on the fly.
• HANA variables and input parameters show up as prompts. They can be merged.
• HANA OLAP InA connections do not support filters on measures and attributes.
• Also in 4.3: SAP Datasphere artifacts, S/4HANA CDS views, OData web services and Web Intelligence documents as sources. A document-based query reads the source document's cube; tick Keep data up-to-date on refresh to reload from the underlying sources.

Why: build on a test universe, then move to production. Or move a .unv universe to .unx. You map each object in the old source to an object in the new one.

Steps

1. In Design mode, in the Query section of the toolbar, click the data source button and select Change Source.
2. Select a query and click OK.
3. Choose an existing source already in the document or a new one (pick a source type first, then browse). Tick Apply changes in all queries sharing the same data source if you want all of them changed.
4. Click Next. Answer HANA or BEx mandatory variable prompts if shown.
5. Set the mapping strategy order (left and right arrows to add or remove, up and down to order). Click Settings for the validation rules. Click Next.
6. Review the results. To fix one, tick it and click Strategies, or map the object by hand.
7. Click Finish.
8. Save the document to apply the change.

Default strategy order: Same ID, Same technical name, Same path, Closest name. (Same name is also available as a strategy.)

Validation rules: Same / Similar / Any object type; Same / Similar / Any data type.

Gotchas

• Not available for OData and web service sources. For Free-hand SQL, Text, Excel and Google Sheets you can change the source settings without creating a new source. (One guide section also says Change Source is not yet supported for the newer Text, Excel and Google Sheets sources and for FHSQL; the paths table says yes. Test it.)
• Custom SQL is kept only if the target uses SQL on a relational universe or HANA relational connection, supports custom SQL, and has the same number and data types of result objects. There is no SQL check until you refresh.
• The target can have different limits. Filters on measures or attributes may be dropped, dimension filter values are copied as is, and a member selection becomes every member. Check filters and members after the change.
• An object with no match is flagged "Remove object" and is permanently removed if you do not map it.
• Mapping between UNX universes on relational and OLAP sources may need extensive remapping.
• A .unx universe cannot be changed back to a .unv universe.
• Moving to a BEx query, HANA view or Datasphere view with mandatory variables with no defaults: Web Intelligence applies "the most appropriate values".

If you drill to a dimension that is outside the document's scope of analysis, the data is not in the query. The application must hit the database and run a new query. A prompt asks whether you want to bring the missing data into the report. This works only if your security profile (set by the BI administrator) allows it.

What the guide says

• Objects in the scope of analysis are part of the query. Reaching them needs no new query.
• Objects outside the scope need a new query, so the application prompts you first.
• Set the scope in the query panel: None, One, Two, Three or Custom levels. Custom means every object you add by hand.

Query drill changes the underlying query as you drill. It adds or removes dimensions and query filters, plus drill filters. Use it when your report has aggregate measures calculated at database level. It suits databases with aggregate functions that Web Intelligence does not support or cannot calculate accurately during drill. It also cuts the data held locally, because drilling up shrinks the scope and purges data not needed.

Example (guide). Month is the lowest dimension in the query and Week is below it. Drill down on Month = January and three things happen:

1. Week is added to the scope of analysis.
2. A query filter restricts Month to January.
3. A drill filter restricts Month to January.

Drill up from Week to Month reverses all three. Drill filters are not strictly needed, but they are applied for consistency. For example, the DrillFilters function returns the correct value in query drill.

To activate. In Design mode, open the document properties. In the Data Options section, turn on the Use query drill toggle.

An element link makes a table or chart a "parent". Selecting values in it filters the "child" elements. Element links are displayed in the filter bar.

Define

1. In Design mode, right-click the table or chart and choose Element Link›Add.
2. All Objects is the default, so every object filters other visualizations. Pick one object to use a single filtering object.
3. Add a name and a description.
4. Optional: turn on Reset on refresh.
5. In Target Visualizations, tick the elements to filter.
6. Click OK.

Modify. Right-click the table or chart > Element Link›Edit. Remove. Right-click > Element Link›Remove. Reset. Show the filter bar and use the reset icon from its menu.

Use. Select dimension values in the table (rows, columns, cells) or the chart (clickable data areas).

Example. A small table of State and Revenue is the parent. A detailed table by Store and Product line is the child. Click "California" in the parent and the child shows California stores only.

A filter path records the order in which you pick values across grouped input controls. Each choice narrows the lists of the next. Instead of searching a huge city list, pick Country, then Region, then City.

Create a group

1. In Design mode, click the input control icon in the filter bar (show the filter bar from the Analyze section if needed).
2. Click New Group of Controls and choose whether it applies to the report or the whole document.
3. In New Group, name the group.
4. Click Add Control. Tick at least two eligible controls and click OK, or click New Control to create one from scratch.
5. Use the up and down arrows to set the order of the filter path.
6. By default every control joins the filter path. To build the path by hand, uncheck Add all input controls to filter path.
7. Click OK.

Create a filter path (Reading or Design mode). Click the group name. Click the add icon to select the first control, then use Available Controls to add the next ones, most general first. Click each control's name and pick values. Later lists are restricted by earlier picks.

Manage groups (click the group name, open the menu, then Available Controls›Manage Group)

• Add: Add Control, tick a control, OK.
• Remove: hover over the control, menu > Remove from Group.
• Move to another group: open the other group's Manage Group, Add Control, tick the control, OK.
• Reset a filter path: in the filter bar, click the reset icon next to each control's name.
• Delete a group: Manage Filter Bar, hover over the group, menu > Delete. The controls stay in the filter bar but the filter path is removed.

Worked example (guide). Group Business holds Country, City, Product. Year and Sales Revenue controls already exist.

1. Pick Jamaica in Country. The City list shrinks to Jamaica's cities.
2. Pick Kingston in City. The Product list narrows.
3. Pick Swimming Suits in Product. The table shows swimming suit revenue in Kingston for 2019.

The path reads Jamaica > Kingston > Swimming Suits. Reset City to see other Jamaican cities.

What and why. A hyperlink lets a cell open another document: another Web Intelligence document, a web page, a PDF, Excel or Word file. Links can be static (always the same) or dynamic (they change with the data). Links are defined with OpenDocument syntax, generated for you. You can also write OpenDocument by hand (see the Viewing Documents Using OpenDocument guide). By default, hyperlinks and JavaScript are disabled. An administrator must allow them in the CMC, and must authorise the URLs.

Three kinds of hyperlink

1. Cell text is the hyperlink. Best for static links.
2. A hyperlink associated with a cell. Recommended for dynamic links. You build it in a dialog, and the cell text can differ from the link.
3. A link to another document in the CMS.

Steps: make the cell text a hyperlink

1. In Design mode, select or type a hyperlink in a cell.
2. Open the side panel, then the Format panel, then the appearance settings.
3. Under Display, set Read content as to Hyperlink.

Steps: link to a URL

1. In Design mode, right-click the cell and select Add hyperlink to›A URL.
2. In the Hyperlink dialog, enter the URL in Target URL.
3. In URL Options, type a Label.
4. In Open In, choose a new window or the current window.
5. Type a Tooltip. Click OK.

Steps: link to another document in the CMS

1. In Design mode, right-click the cell and select Add hyperlink to›Another document.
2. In Select a Target Document, choose the document and click Select. Target URL and Document Parameters fill in.
3. Optional: tick a parameter and type a value, or choose Select Object or Build Formula from its drop-down. Prompts and contexts are under Prompts and Context Parameters. Extra URL parameters are under Other Parameters.
4. To add or remove a parameter, edit the Target URL text and click Parse URL.
5. Add a Label and Tooltip (text, Select Object or Build Formula).
6. Choose Open In: new window or current window. Click OK.

Steps: link to another report in the same document

1. Right-click the cell and select Add hyperlink to›This document.
2. In Targeted Report within the Document, choose the report. Hidden reports are not listed.
3. Optional: set input controls so the link filters the target report by the clicked value.
4. Add a label and tooltip. Click OK.

Instance options (for linking to large documents)

• Most recent: opens the latest instance. No parameter values are passed.
• Most recent - current user: latest instance owned by the current user.
• Most recent - matching prompt values: latest instance whose prompt values match those the link passes.

Other tasks

• Open a link: in Reading mode, click it, or click the cell and use the floating menu (Open URL or copy the URL). In Design mode, right-click the cell and pick Open Document, Open URL or Open Report. Hover to see the tooltip.
• Edit: right-click the cell, Hyperlink›Edit Link..., change it, OK. Do not edit the syntax in the Formula Bar.
• Delete: right-click the cell or column, Hyperlink›Remove Link.
• Colours: right-click a blank area, Format Report›Appearance Settings. In the Hyperlink section, set Visited and Unvisited colours, then Apply.

Worked example. A report lists Region and Sales revenue. You build a link on Region to a large "Sales by Region" document that has a Region prompt. Pass [Region] as a parameter. Pre-schedule four instances (North, South, East, West). Set the instance option to Most recent - matching prompt values with [Region]. A click opens the matching ready-made instance, not a fresh query. The guide says this is more efficient for large targets.

What and why. A Geomap draws data on a map. For that, each value of an object (for example each State) must be matched to a place. That matching is called geo-qualifying. Web Intelligence has an embedded geographical database. You can match by name, or by latitude and longitude. Geo chart types are classic report elements only.

Geomap types

• Choropleth: zones coloured by a measure.
• Geo Bubble: bubbles on zones, sized by a measure.
• Geo Pie: pies on zones, sized by a measure.

How matching by name works. Web Intelligence sorts values into three groups:

• Resolved: exactly one location matches at 100%. It is bound automatically.
• Unresolved: several locations match at 100%, or match above 85% but below 100%. You choose.
• Missing: no location found, or matches below 85%. You search for one.

Steps: match values to a location by name

1. In Design mode, go to the Objects pane.
2. Hover over the object, click the icon that appears, and click Geo-Qualify By: Name.
3. Select a level: Country, Region, Sub-Region or City.
4. Optional: use the Show drop-down to filter the list.
5. Click the drop-down next to a value and select a location.
6. Click Apply, then OK.

Steps: pick a location manually. Follow the steps above. In the value drop-down, if the right place is missing, click Select location..., then either type the name, select it and click OK, or click Add Location, enter coordinates and click OK. Use the correct level, because the search applies only to the level you set.

Steps: match by latitude and longitude

1. In the Objects pane, hover over the object and click the icon.
2. Click Geo-Qualify By: Latitude / Longitude.
3. Select the latitude and longitude objects in the drop-downs.
4. Click Apply, then OK.

Steps: change or reset. To change: use the same menu (Geo-Qualify By: Name or Latitude / Longitude), change the values, Apply, OK, then refresh the document. To reset: click Reset Geography.

Once an object is geo-qualified, an icon appears next to it. Click the right arrow to see the matched name, latitude and longitude.

Geomap settings (selected): Display invisible area as point and Symbol Size (Choropleth), Geographic Context (none, neighbors or parents), Precision (0 highest to 10 lowest), Bubble scale (2 to 10), Bubble scaling mode (proportional or perceptual), Edge Color, Pie Title, Manual Range (set the latitude and longitude range). Choropleth charts also have an option to reserve space at the top and bottom for labels.

Worked example. You want revenue by State on a map. In Objects, hover over State, click Geo-Qualify By: Name, level Region. Fix any Unresolved or Missing states in the drop-downs. Apply, OK. Insert a Geo Choropleth chart and assign State and Sales revenue. Choropleth shades states by revenue.

What and why. Custom Elements are visualisations drawn by an outside rendering service. They sit in the report like charts or tables, and appear at the bottom of the chart list.

Steps

1. Open the document in Design mode.
2. In the Insert section of the toolbar, open the chart drop-down and click Custom Element. The option is greyed out until a service is set up.
3. Select a visualisation.
4. Place it on the canvas.
5. Drag dimensions and measures from the Objects pane onto it.

What and why. A shared element is a report element (table, chart, header or footer) stored in the CMS repository so others can reuse it. Example from the guide: save the company-name header once, insert it into every new report. You manage them in the Shared Elements pane of the side panel.

Steps

• Create: in Design mode, right-click a report element > Shared element›Save as. On the General tab, enter a name and pick a folder. On Options, add a description and keywords, and choose whether to keep formatting and link to the current document. On Categories, pick a category. Click Save.
• Insert from the toolbar: in the Insert section, click the insert button, then Shared Element. Browse or search in the Folders, Categories or List tab. Select one, click Insert, then click the report page. If asked to update, click OK.
• Insert from the side panel (an element already used in the document): Shared Elements pane > the menu next to the element > Insert, then click the page.
• Update manually: in the Shared Elements pane, click the check-for-updates icon. Update all, or open the menu for one element and click Update.
• Update automatically: side panel > Properties tab > Document Options›Update shared element(s) on open toggle. Also enable Check for shared element updates on open to be told.
• Unlink all: Shared Elements pane > the menu next to the element > Unlink. Unlink one instance: right-click it on the page > Shared Element›Unlink.
• Edit properties: toolbar insert button > Shared Element > select one > menu > Properties > change name, description or keywords > Save.

Worked example. Save your "Sales by Product line" column chart as a shared element named "Revenue by Product line". A colleague inserts it into her report. Later you republish it with the same name. Her document is out of date until she updates it.

What and why. A style sheet sets the default look of every report in the document, such as company logo or colours, instead of formatting by hand. It follows W3C CSS core syntax, so you need to know CSS.

Steps

1. In Design mode, open the Properties pane and use Default Style›Export.
2. Edit the file in a text editor.
3. Use Default Style›Import.
4. Reset to the standard style: Properties pane > Default Style›Reset Default Style.
5. Clear manual formatting: select the element, then in the Format pane click the menu and choose Reset Format.

Elements you can style include REPORT, PAGE_BODY, PAGE_HEADER, PAGE_FOOTER, SECTION, TABLE, VTABLE, HTABLE, CELL, FORM, XELEMENT, BREAK.

What and why. A prompt is a question that filters data, for example "Which State?". Nobody is there to answer when a schedule runs, so you set the answers when you create the job. Use this to schedule one version per region, year or product line.

Steps:

1. In the Schedule dialog box, click the Report Features tab.
2. In the Prompts section, set the value for each prompt.
3. Click Schedule.

Rules from the guide:

• Scheduled prompts can have static values, set when you create the job.
• You can tick Use prompts values from source document. The prompts are then answered with the answers saved in the document (from an earlier refresh, or the defaults).
• If you cannot see a Prompts tab or section, the document has no prompts.
• You can click Modify to edit a value, or Constant Value or Dynamic Value to switch its type. Constant values are fixed and schedule immediately. Dynamic values contain expressions, so Web Intelligence hands the calculation to the back end (the universe engine, SAP BEx or SAP HANA) and schedules the document once the values are computed.
• Dynamic values work for SAP BEx variables, SAP HANA variables and universe prompt parameters with dynamic default expressions.
• For dynamic BEx prompt values, the guide says to: select Use BEx query defined default values at runtime in the Variable Manager wizard; purge document data with Purge Last Selected Prompt Values; and purge the prompt values when creating the scheduling job.

Worked example. Prompt "Enter Year". Schedule one job with Year = 2026 and another with Year = 2025. Name each instance clearly so you can tell them apart in History.

What and why. The format decides what file the instance is. Pick Excel for people who will work with numbers, PDF for reading and printing, CSV or TXT for feeding other systems.

Formats you can choose when you schedule a document:

• Web Intelligence: .WID
• Microsoft Excel - Data: .XLSX (one sheet per data provider, named after it; needs the Export the cube's data right)
• Microsoft Excel - Reports: .XLSX
• Adobe Acrobat: .PDF
• Comma Separated Values (CSV) - Data: .CSV
• Comma Separated Values (CSV) Archive - Reports: .ZIP (one CSV per report)
• Plain text: .TXT
• HTML archive: .ZIP

Formats you can choose when you publish: .WID, Microsoft Excel (.XLSX), Adobe Acrobat (.PDF) and MIME HTML (.MHTML, used to embed content in an email, see topic 9).

CSV options: switch the Default options toggle off to set a text qualifier, a column delimiter (you can type your own, such as a pipe) and the charset. You can also ask for one CSV file per data provider.

HTML archive: choose the reports to include and give each report a unique name. The ZIP holds a default index.html with links to the reports, a report.js file and a sub-folder per report. You can replace index.html with your own. For ZIP files sent to File System, FTP or SFTP, you can name the ZIP automatically or explicitly.

What and why. A destination is where the finished instance goes. Which ones you see depends on what your administrator turned on and your rights. For most destinations you must fill in extra details. You can add several destinations in one schedule, which cuts the number of schedules you need.

Default Enterprise Location. Runs on the Output File Repository Server (FRS). No extra options. History instances are saved to the default Enterprise server only.

BI Inbox. Options:

• Keep an instance in the history: on by default. Untick it to let the platform delete the instance from the Output FRS and save space. Instances that were not sent because they failed a delivery rule stay in the history anyway.
• Use default settings: uses the Adaptive Job Server defaults. Switch it off to set recipients yourself.
• Available Recipients and Selected Recipients: pick users or groups, click > to add them. Use Find title to search by user name, full name or email.
• Target Name: Use Automatically Generated Name, or Use Specific Name with the Add placeholder list (Title, ID, Owner, DateTime, Email Address, User Full Name, File Extension).
• Send As: Shortcut (a link to the instance) or Copy.

Email. Options: From, To, Cc, Bcc, Subject, Message, Add Attachment, File Name, Add File Extension, Enable SSL. Separate addresses with a semicolon. From may be unavailable depending on your system configuration. Message now has a rich text editor with formatting options.

FTP Server. Options: Host (IP address), Port (default 21), User Name, Password, Account, Directory, File Name. SFTP is also available as a destination.

File System. Options: User Name and Password (Windows servers only), Directory (a local path, mapped location or UNC path), File Name. For Web Intelligence you can use a placeholder in the directory to make folders by title, owner, date and time, or user.

Google Drive and Microsoft OneDrive. Options: Cloud Drive Folder Details (the folder path) and File Name. If you have not finished authentication in the BI Launch Pad, it asks you to authenticate when you choose either one.

Delivery rules (scheduling). On the Report Features tab, Delivery Rules stop erroneous or empty documents being sent to BI Inbox, Email, FTP, File System or SFTP. You can pick one or both conditions:

• The scheduled content has been successfully refreshed and is not partial: sent only if all data providers refreshed.
• The scheduled content contains data: sent only if at least one report has data. You also choose the status a failing document shows in the history: Warning (default) or Failed. If one condition gives Warning and the other Failed, the history shows Failed.

Worked example. Send "Sales by Store" to the regional managers. Destination Email. To: a placeholder or typed addresses. Subject: "Sales by Store" plus the DateTime placeholder. Tick Add Attachment, Use Specific Name, and Add File Extension. Under Delivery Rules, tick The scheduled content contains data.

What and why. An event holds a job back until something happens. Use it when data must be ready first, for example "run after the overnight load finishes".

How it works:

• Create the event first in the CMC. Then select it in the BI Launch Pad when you schedule.
• If and only if the event occurs, the platform runs the job.
• In a schedule, the Events section sets the conditions. For a publication, use the Wait For drop-down to pick the event that triggers it, or the Trigger drop-down to pick an event to fire when the job has run.
• Tick Any event to run after any one of the chosen events.

Server group (the Scheduling Server Group section):

• Use first available server: default. Uses the server with the most free resources.
• Give priority to a server group: tries that group first, then the next available server.
• Use this server group: the guide says that if no group server is available, the document runs on the next available server.
• If you use federation and want to run where the document lives, check Run at origin site.

Worked example. Schedule the daily Sales by Store report with the event "Nightly load complete". Recurrence: Daily. It runs only after that event fires.

What and why. A publication is a collection of documents sent to a mass audience. As publisher you define its source documents, recipients and personalization. Use it when many people need the same report but each should see only their own slice, for example each State manager seeing only their State. It also cuts load, because users do not each send process requests to the database. You can create publications in the BI Launch Pad or the CMC.

9.1 Recipients [Advanced]

• Enterprise recipients: users who are part of the BI Platform. Can receive via BI Inbox, email, FTP, file system and collaboration.
• Dynamic recipients: non-enterprise users (for example, suppliers outside your network). Email only. Local profiles only. They have no BI Platform account, so they cannot log on to subscribe.
• Dynamic setup: create a source file (the data, with a unique ID such as "Supplier ID") and a recipient file (the same ID, plus names and email addresses). Then set up the publication in the BI Launch Pad, then schedule it.
• Tip from the guide: sort dynamic recipient sources by the Recipient ID column. This reduces deliveries to recipients with several personalization values.

9.2 Create a publication

1. In the BI Launch Pad, click the Folders tile.
2. Browse to the folder, and open its actions menu > Publication. The New Publication dialog opens.
3. Give the publication a name, keywords and a description.
4. In the Source Documents section, add one or more documents. Refresh At Runtime is on by default for each. Untick it to skip the refresh.
5. In Selected delivery destinations, click Add and pick a destination. The default is Default Enterprise Location.
6. Select the Enterprise and/or dynamic recipients.
7. Set Recurrence, Events and Scheduling Server Group. Then on the Report Features tab set Output Format, Prompts and Delivery Rules. They match the Schedule dialog box.
8. Click Save & Close.

To open one: Folders tile, then the publication's actions menu > View. To see a summary (recipients, personalization, format, destination): actions menu > Properties›Summary.

9.3 Personalization [Advanced]

Personalization filters the data in source documents so each recipient sees only relevant data. It changes the view, not the data queried from the source.

• Enterprise recipients: apply a profile. Profiles must already be created and configured in the CMC.
• Dynamic recipients: map a field in the source document to data in the dynamic recipient source (for example Customer ID to Recipient ID).

Global profile target:

1. On the Folders tile, browse to the publication. Open its actions menu > Schedule.
2. Click the Report Features tab.
3. In the Personalization section, pick a global profile in the drop-down.
4. Click OK.

Filtering by field (local profiles):

1. Same start: actions menu > Schedule›Report Features tab > Personalization section. Pick a local profile.
2. Under Local Profiles, for each profile in the Title column, pick a field in the Report Field column.
3. In Enterprise Recipient Mapping, pick a profile.
4. In Dynamic Recipient Mapping, pick a profile.
5. Repeat for each field to filter, then OK.

To see who gets unpersonalized instances: Additional Options›Advanced in the New Publication dialog box, then tick Display users who have no personalization applied.

Worked example. Publish "Sales by Store" with profile "State". The Texas manager gets a copy filtered to Texas. The Ohio manager gets one filtered to Ohio. Name each file with the personalized placeholder %fieldname_VALUE% so the file name shows the State.

9.4 Report bursting [Advanced]

Bursting is the refresh and personalize step before delivery. Two methods:

Pick the method in the CMC only:

1. In the CMC, click Folders and find the publication.
2. Right-click the publication job > Schedule.
3. In the Schedule dialog box, expand Additional Options and click Advanced.
4. Under Report Bursting Method, choose one.
5. Click Schedule.

9.5 Placeholders [Advanced]

• Source file names: %fieldname_VALUE% (unique per recipient) and %fieldname_NAME% (same for everyone).
• Email fields: %Field - Query 1-VALUE% and %Field - Query 1-NAME%. Usable in From, To, Cc, Bcc, Subject, Message and Use Specific Name.
• Placeholders appear in the Add placeholder list only if all source documents are personalized on the same field.

Steps for file names: on the Folders tile, publication actions menu > Schedule›Destinations section > Add. Pick a destination. In Target Name check Use specific name and pick from Add Placeholder. For per-document names, switch on the Use specific name per document toggle. Click OK.

Steps for email fields: same route, choose Email, set options (including placeholders) in the System Details section, OK.

9.6 Embed content in an email [Advanced]

1. On the Folders tile, publication actions menu > Schedule.
2. On the Report Features tab, in Output Formats, click the format next to a document name. Select HTML and choose the entire document or a single report.
3. On the General tab, in Destinations, click Add and pick Email.
4. In Message, put the cursor where the content goes and pick Report HTML Content from Add Placeholder. This inserts %SI_DOCUMENT_HTML_CONTENT%.
5. Optional: tick Add attachment to attach the other source documents.
6. OK.

Gotcha: formatting may look off in Outlook 2007 or web mail such as Hotmail or Gmail. The guide prefers Outlook 2003.

9.7 Delivery rules [Advanced]

For Web Intelligence you can set only recipient delivery rules:

• Deliver individual document when condition is met
• Deliver all documents only when all conditions are met

Conditions: Always deliver, Never deliver, If scheduled content contains data, If scheduled content has been fully refreshed. If a document fails the condition, you can cancel that document or the whole publication.

Worked example. Set "If scheduled content contains data" so a State manager with no sales that week gets no empty report.

9.8 Other publication options [Advanced]

• Publication extensions (CMC only): code that merges documents of the same type, adds password protection or encryption, converts formats, or writes custom logs. In the CMC, right-click the publication > Properties›Additional Options›Publication Extension. An administrator must deploy the extension first.
• Live Office: dynamic content documents must be Web Intelligence documents in original format. Dynamic recipients not supported. The only destination is Default Enterprise Location. Recipients with several profile values may get several instances but see only the first in the Live Office Client.

9.9 Test, monitor, fix [Intermediate]

Test mode:

1. On the Folders tile, publication actions menu > Test Mode.
2. Optional: in Enterprise Recipients, click Select to choose recipients.
3. Optional: in Dynamic Recipients, click Browse, fill in the fields, and pick specific recipients.
4. Click Test. You receive exactly what recipients would get. Destinations switch to your own Inbox or email.

View progress: click the Instances tile. Status shows Success, Failed or Running. For the log, actions menu > Details›Download Log. In History, you can also click the status and then View Log File. Log files update every two minutes (status may show Pending in the first two minutes). Max log size is 10 MB, so large runs make several logs. Click the Instance Time link to see all logs after the personalized instances.

Redistribute (resend without rerunning): History, select a successful instance, then More Actions›Reschedule (BI Launch Pad). Choose Enterprise recipients with >, or Dynamic recipients (Use entire list or pick some), then Redistribute. Only the original recipients can receive it.

Retry a failed run: read the log, fix the errors, then More Actions›History, right-click the failed instance and click Retry. Retry overwrites the failed instance and, after a partial failure, processes only the failed recipients. Run Now and Reschedule create new instances.

Subscriptions: a subscription lets users who are not recipients see the latest instance. Right-click the publication > Subscribe or Unsubscribe. Enterprise recipients can also subscribe to the first recurring instance from History. Dynamic recipients cannot subscribe. You need a BI Platform account, View rights and Subscriber rights.

Performance tips from the guide: uncheck Refresh At Runtime when a refresh is not needed; view and schedule each document on its own before adding it to a publication; for large publications that need no redistribution, do not select the default destination.

What and why. In 4.2 you could publish a table, chart or form as a "BI service" (Publish as Web Service, the Web Service Publisher, QaaWS import, GetReportBlock and Drill functions). The 4.3 SP5 guide has no such chapter. Those features are not described, so do not plan around them. What the 4.3 guide describes instead is OData web services: Web Intelligence exposes report data through OData REST links, and another Web Intelligence document (or any consumer that follows the OData protocol) can read them.

Steps to get a link:

1. Open an existing Web Intelligence document.
2. Right-click a visualization and choose Copy Link For OData Web Services. You now have a valid OData URL.
3. In Data mode, you can also get a link from a cube: next to the cube, choose Copy OData Web Services Link.

Steps to use a link in a new document:

1. On the BI Launch Pad home page, click Web Intelligence to create a document.
2. In the Select a Data Source dialog box, click Web Services on the left, click OData on the right, and click OK.
3. Paste the OData URL.
4. Add objects to the query and click Run Query.

Steps to add an OData query to an existing document:

1. In Design mode, open the query panel from the toolbar.
2. Click the Add Query drop-down at the top left.
3. In Select a Data Source, click Web Services, then OData.
4. Paste the URL, add objects, click Run Query.

Worked example. Copy the OData link from the "Sales revenue by State" table in a document. Create a new document on Web Services›OData, paste the link, add State and Sales revenue, and click Run Query. The second document now reads the data of the first.

What and why. Most "I can't do that" moments are missing rights. Your administrator grants them. Know the name so you can ask for the right one. In 4.3, check the defaults for the new rights below if you migrated from an older version.

New in 4.3:

• Query: View Free-Hand SQL and Query: Edit Free-Hand SQL: view or edit Free-Hand SQL scripts.
• Export the report's data secures exports to Excel, PDF, Text, CSV and HTML. Export the cube's data secures exporting a cube's data to CSV.
• General: Enable Desktop client access lets a user use Web Intelligence Rich Client. To open a document there you must import it locally, which needs the document right Import document locally.

Application rights (Web Intelligence):

• General: Enable Web client access, General: Enable Desktop client access, General: Edit Web Intelligence preferences.
• Documents: Enable creation, Documents: Enable auto-save, Documents: Disable automatic refresh on open.
• Desktop: Publish to Enterprise and Desktop: Grant access to everyone (Rich Client only).
• Query: View script generated from universe, Query: Edit script generated from universe.
• Data: Enable data tracking, Data: Enable formatting of changed data.
• Reporting: Create formulas, variables, groups and references; Create and edit breaks; Create and edit sorts and rankings; Create and edit filters and consume input controls; Create and edit input controls and group input controls; Create and edit conditional formatting rules; Create and edit predefined calculations; Enable document change; Merge objects; Insert and remove reports, tables, charts and cells.

Document rights:

• Edit query, Refresh the report's data, Use Lists of Values, Refresh List of Values (needs Use Lists of Values too), View script.
• Export the report's data, Export the cube's data, Import document locally.
• Schedule document to run, and Schedule to destinations (parent of Schedule to File System, FTP, Inbox, SFTP, SMTP, Google Drive). View document instances, Delete instances, Pause and Resume document instances, Reschedule instances (CMC only).
• Comment rights (BI Commentary and general comments).

Connection rights: Data access (retrieve content from the database in the connection), Use connection for Free-Hand SQL scripts, Download connection locally (use universes in Rich Client offline mode). Universe rights (.UNV and .UNX): Create and edit queries based on the universe and Data access. Data access on a universe also needs the refresh rights on the application and document, and data access on the connection.

Publishing rights (summary): a publisher needs Add on the publication folder, View and Schedule on source documents, View on recipients and profiles, Data Access on universes and connections, and Schedule on the publication. Recipients need View and View Instance on the publication. Only the publisher should have the Schedule a publication right.

What and why. Chapter 11 of the 4.3 guide explains how Web Intelligence supports data protection rules such as GDPR. The guide gives no legal advice. Decisions must be taken case by case.

Key points:

• Web Intelligence documents are stored in the BI Platform, so only authenticated and authorized users can open them. Web Intelligence does not collect personal data. It processes data generically and cannot tell which metadata is personal. Identifying documents that hold personal data is the customer's job.
• Build reports with Refresh on Open. The document is purged and refreshed every time it opens, using the user's rights. Data removed from the database is then removed from tables and charts too. This also holds if the document is saved locally.
• Do not put personal data in open or free-text fields.
• Retention: use scheduling to create instances regularly. Administrators can set rules that delete instances after a set period, per folder or per document.
• Read access logging: administrators can turn on auditing for document access or refreshes on given universes. Logs go to a database. You can build a Web Intelligence document on it to see which documents each user has read.
• Server logs may link users to documents they opened. Administrators should delete logs regularly in the CMC, or disable them if needed.
• Saving documents locally: outside the BI Platform repository, protecting the content is the customer's job. The guide recommends third-party tools with operating-system-level encryption.

Worked example. The eFashion "Sales by Store" report has a free-text comment cell where staff type customer names. Remove the names, turn on Refresh on Open, and let your administrator set a retention rule that deletes scheduled instances after 90 days.

The 4.3 guide groups errors by source: WIO (Desktop, Rich Client), WIS (server), IES (query engine), RWI (report engine) and CDS (custom data sources). The old WIJ and WIH prefixes (Java and HTML interfaces) are gone.

Also worth knowing:

• There is no more memory available (WIS 30280 / WIO 30280) and Cannot continue because memory is low (WIO 30284): Rich Client is out of memory. Close open documents.
• The document is too large to be processed by the server (WIS 30271 / 30272): the output exceeded the size limit (PDF or Excel for 30271, HTML for 30272). Ask your administrator to raise the limit.
• A filter contains a wrong value (IES 00007): for example an empty constant, or text where a number is expected. Correct the filter.
• The numeric value for the query filter is invalid (IES 10703) and The date for the prompt is invalid (IES 10706): enter a valid value.
• The object is not unique in the report (IES 10005): use the fully qualified name.
• Circular reference (IES 10042): a variable's formula refers back to itself. Remove the loop.

To download Rich Client from the BI launch pad

1. Open the BI launch pad and sign in.
2. Open the menu and click Settings.
3. Select Application Preferences›Web Intelligence.
4. Under Web Intelligence Rich Client Setup, click Download.

To log in

1. Launch Rich Client.
2. Fill in System and Authentication, then your user name and password. Click the eye icon to check the password.
3. Optional: switch Work Offline to Yes. It works only after you have logged in at least once in online mode.
4. Click Start.

Three connection modes

You cannot switch mode in the middle of a session.

Data sources by mode (from the guide):

• .unv and .unx universes: Offline yes (imported universe, CMS password still needed), Online yes. Creating documents offline on .unx universes is not supported.
• SAP HANA views, BEx queries, Free-Hand SQL, Web Intelligence documents: Online only.
• Excel and text files: Offline and Online yes (Offline: local only).
• OData web services: all modes.
• Standalone: universe, Excel, text, OData and no data source.

To work in Standalone mode

1. Open Rich Client. On the login screen, switch the Standalone toggle on (it shows Yes).
2. Click Start. Next time, Rich Client goes straight to the login screen with the toggle on. To skip the login screen, use the top right Welcome menu > Settings›General›Launch Web Intelligence Rich Client in Standalone mode.

To use a CMS document in Standalone mode: import it, click Save Copy in the Save button menu, and select Remove Security.

Delegating refresh to the server (HTTP mode)

1. In the BI launch pad, go to Settings›Application Preferences›Web Intelligence tab, and select Rich Client under Open in Edit Mode.
2. Open a document in the BI launch pad. You get a .zabowi file. Open it to start Rich Client. The window then shows "(HTTP)". HTTP mode needs the Database Access and Security (connectivity) node unchecked at install, and the Download Connection Locally right granted in the CMC.

Other Rich Client settings

• Default folders: Settings›General. Use Browse next to the fields for universes and documents imported from the CMS, then Save.
• Measurement unit: BI launch pad Settings›Application Preferences›Web Intelligence tab, in the measurement unit section.
• Search: Ctrl+F opens a search bar for the active window, drop-down lists or dialog boxes (see topic 11).

Worked example: you travel with the eFashion document. Before you leave, import it in Rich Client online. On the plane you log in with Work Offline on, open the document and edit the layout.

Gotchas

• Offline mode needs a prior online session so the LSI (local security information) file exists.
• If the Download connection locally right is denied, no local refresh happens; the refresh is delegated to the server. In a fully offline mode with secured connections, reports can be opened, viewed and modified, but not refreshed, and the query cannot be modified.
• With several queries, refresh works only for non-secured connections. A warning shows if any query uses a secured connection.
• Standalone mode does not support prompt variants, commentary, shared elements, or publishing to a CMS. Translation of documents is also unavailable.
• In offline mode you cannot edit and refresh a document on BEx, Free-Hand SQL, SAP HANA or text sources, and you cannot create documents on .unx universes, BEx or SAP HANA.

On the Rich Client home screen:

• Import (online mode only): select a document from the BI platform repository. Import & Open starts work at once. Documents go to the userDocs folder by default.
• New: select a data source type (depends on mode), click OK, then use the save icon menu and Save, and pick a folder.
• Open: pick a local document in the browsing window. It appears in Recent Local Documents.
• Save: always saves locally. Changes do not reach the CMS.
• Save Copy: save a copy to a folder you choose.
• Publish to BI Platform Repository: put the document in a CMS folder, publicly or privately. Online mode only. To keep the original, use a new name or a different folder.

Workflow: import a document (or create one), save it, edit it, publish it back.

Gotchas

• Publishing needs the "Desktop: Publish to Enterprise" right.
• Standalone mode hides Import and Publish to BI Platform Repository.
• Rich Client needs the "General: Enable Desktop client access" right, and opening a document there needs the "Import document locally" right.

Locales control how the interface and the data look for your region. There are three.

To set the Product locale or Preferred viewing locale

1. In the BI launch pad, open Settings›Account Preferences›Locale and Time Zone.
2. Select the Product Locale and the Preferred Viewing Locale.
3. Save.

To choose which locale formats the data

In Settings›Application Preferences›Web Intelligence, choose Use my preferred viewing locale to format the data or Use the document locale to format the data.

Lock a locale to a document

Turn on Permanent regional formatting in the document properties (the Properties pane of the main panel). When on, it applies to all users and formats the data with the locale you set.

Right-to-left (RTL)

• Choose Arabic or Hebrew as Product locale and the interface is always RTL. The side panel moves to the right.
• Choose Arabic, Hebrew or Farsi as Preferred viewing locale and, depending on administrator settings, document elements may be RTL. In a cross table the side header column moves to the right.
• For one document, in Design mode, use the Right to Left Content Alignment toggle in the document properties. You can view an Arabic or Hebrew document left to right without changing the original.

Worked example: a UK colleague and a US colleague open the same eFashion report. Both see the same numbers but different date formats, because by default the browser locale is used. If you turn on Permanent regional formatting, everyone sees the document locale format.

Gotchas

• Your Preferred viewing locale is assigned as the initial document locale when you create a document.
• Permanent regional formatting applies to all users who view the document.
• The user interface, printouts, PDF and Excel output and scheduled documents inherit the orientation. An RTL document gives an RTL PDF.
• Charts are always LTR.

What and why. Calculation extensions add your own functions to the Web Intelligence function list. This is a developer and administrator task, not a report-writer task.

Outline from the guide

1. Declare the function in an XML file (name, GUID, return type, category, hint).
2. List that file in externalcatalogs.xml (the only catalogs file).
3. Implement the function in a C++ library (DLL on Windows, SO on Linux or UNIX) using the API.
4. Compile it.
5. Copy the XML and library into the WebiCalcPlugin folder on the server and on every machine with the desktop rich client.
6. Restart the Web Intelligence server. The function then appears in the Formula Editor and the formula bar help.

Since 4.1 SP3, documents migrated from older versions are automatically rewritten for three patterns so they return the same results as before: (1) Where with a dimension as parameter in a condition, (2) running calculations with reset in sections, (3) running calculations with reset in crosstabs (which gain a FORCE_COL keyword, for example RunningSum([Sales revenue];FORCE_COL;([State]))). Save the document afterwards so the rewrite is stored.

The BI platform exposes REST web services. They let a program do what a user does in Web Intelligence: browse universes, run queries, build and refresh documents, schedule them. Any language that can send HTTP requests can use them. So can a tool that sends HTTP requests without code. The WebApplicationContainerServer (WACS) hosts the services, so it must be running.

The guide describes two SDKs. They have different jobs.

The two APIs

• Web Intelligence ("raylight"). Works with documents, reports, report elements, data providers, refresh, schedules, input controls and more. Short name in the URL: raylight/v1.
• BI Semantic Layer ("sl"). Works with universes and queries on top of them. You describe a query, run it, and read the rows. Short name in the URL: sl/v1.

A third base, the plain BI platform service, handles logon, logoff and session.

Default base URLs

A single-server install uses these defaults. 6405 is the default HTTP port for the REST services.

You can change the configured URL in the CMC under Applications, REST Web Service, Properties, Access URL. The API reference chapters in the guide leave out the base URL to keep paths short. When you see GET /documents, read it as GET http://<server_name>:6405/biprws/raylight/v1/documents.

Useful first calls

Check the server version (no login needed): GET /about (on the Web Intelligence base). It returns title, version and build.

Check who you are logged in as.

GET /session

<session>
    <user>
        <id>6357</id>
        <name>user</name>
        <cuid>AZFmWPGBwwRBm3almUJGkfk</cuid>
        <timeZone>Japanische Normalzeit</timeZone>
        <preferredViewingLocale>ja-JP</preferredViewingLocale>
        <productLocale>de</productLocale>
    </user>
</session>

Every call except /about needs a logon token. You get it from the BI platform base URL, in two steps. First you ask the server what it needs. Then you send the credentials back in the same shape. The token goes in a request header on every later call.

Step 1: get the logon form

GET http://<server_name>:6405/biprws/logon/long

<attrs xmlns="http://www.sap.com/rws/bip">
    <attr name="userName" type="string"/></attr>
    <attr name="password" type="string"></attr>
    <attr name="auth" type="string"
possibilities="secEnterprise,secLDAP,secWinAD,secSAPR3">secEnterprise</attr>
</attrs>

Step 2: post the credentials, get the token

POST http://<server_name>:6405/biprws/logon/long

Use the step 1 response as the request body, with your values filled in.

<attrs xmlns="http://www.sap.com/rws/bip">
    <attr name="userName" type="string"><myUserName></attr>
    <attr name="password" type="string"><myPassword></attr>
    <attr name="auth" type="string"
possibilities="secEnterprise,secLDAP,secWinAD,secSAPR3">secEnterprise</attr>
</attrs>

The response contains the token:

<attrs xmlns="http://www.sap.com/rws/bip">
    <attr name="LogonToken" type="string">COMMANDCOM-LCM:6400@{3&2=5595,...,UP</attr>
</attrs>

Use the token

Put the token value between double quotes in the X-SAP-LogonToken request header of every further request.

Log off

POST /logoff (on the BI platform base URL, http://<server_name>:6405/biprws/logoff)

The guide's workflow tables end every flow with this call. It points to the separate BI Platform RESTful Web Service Developer Guide for the details, so check that guide for the exact request.

The API speaks both XML and JSON. Most reference pages say "Response type: application/xml or application/json". Pick one with the Accept header. The guide's own examples are mostly XML.

Headers the guide names

The service opens one copy of a document in memory for each Preferred Viewing Locale requested.

XML to JSON rules

The SDK needs this JSON syntax in request bodies. An at sign (@) marks an attribute. A dollar sign ($) marks an element's text value.

XML:

<status id="1">Completed</status>
<format type="webi"/>

JSON:

"status": {"@id": 1, "{{PAGES}}quot;: "Completed"},
"format": {"@type": "webi"}

Arrays changed in 4.2 SP3

Before 4.2 SP3, an empty list came back as "report":"", and a list with one item came back as a single object, not an array. Since 4.2 SP3, empty and single-item lists are proper arrays:

{"reports":{"report":[]}}

{"reports":{"report":[{...}]}}

If you parse JSON from an older server, handle the old shapes.

Extra fields are ignored

Fields that do not apply to a call are ignored, and the call still succeeds.

HTTP status codes

Success message

Successful calls that change something return a message with the object's id:

{"success":
    {"message": "The resource of type \"Document\" with identifier \"16706\" has been successfully updated.",
     "id": "16706"
    }
}

In rare cases id is missing.

Error message

<error>
    <error_code>WSR 00400</error_code>
    <message>The expression "[DUMMY]" cannot be found in the document dictionary.</message>
</error>

Web Intelligence error codes:

The Semantic Layer SDK has its own, more specific codes. The guide refers to the separate Error Messages Explained guide for them.

Web Intelligence follows REST principles, but document handling is not fully stateless. This is for performance. When you create or edit a document through the API, the changes are not saved to the repository. They sit in server memory until you save.

The states

Save in place (or close)

PUT /documents/<documentID>

The request body is optional. If you send <state>, you cannot send other tags.

Close an unmodified document:

<document>
    <state>Unused</state>
</document>

Keep a document alive:

<document>
    <state>KeepAlive</state>
</document>

Save a modified document: send PUT /documents/9326 with no body. The reply is:

<success>
    <message>The resource of type "document" with identifier "9326" has been successfully updated.</message>
    <id>9326</id>
</success>

Save as a new version

POST /documents/<documentID>?overwrite=<boolean>&withComments=<boolean>

This copies the document to a destination folder and creates a new version. An identifier is assigned automatically.

<document>
    <name>doc_tables_V2</name>
    <description>2nd version</description>
    <keywords>webi tables</keywords>
    <categories>
        <category>
            <id>6598</id>
        </category>
        ...

Body fields: <name>, <description> (optional), <keywords> (optional), <folderId> (use -1 for the same folder), <categories> (optional) and <properties> (optional).

In the Semantic Layer SDK, a query is an XML document called a query specification. It names the universe, the objects you want back, the sort order and the filters. The server stores the compiled query in memory, not in the repository. You can run it many times. This works on any universe type, whether the data is relational, OLAP or other.

Create a query

POST /queries (on the sl/v1 base). Request type: application/xml.

<query dataSourceType="unx" dataSourceId="5808" xmlns="http://www.sap.com/rws/sl/universe">
    <querySpecification version="1.0">
        <queryData>
            <resultObjects>
                <resultObject path="Customer|folder\Geography|folder\City|dimension"
                    id="_IBo8L7IhEeCk0Ylv-tlF2Q"/>
                <resultObject path="Customer|folder\Customer|dimension"
                    id="_IBo8OrIhEeCk0Ylv-tlF2Q"/>
                <resultObject path="Inventory|folder\Stock Level|measure"
                    id="_IB8eFrIhEeCk0Ylv-tlF2Q"/>
            </resultObjects>
        </queryData>
    </querySpecification>
</query>

The reply holds the new query id:

<success>
    <message>The resource of type "query" with identifier "6089913651317040730" has been successfully created.</message>
    <id>6089913651317040730</id>
</success>

Other query calls: GET /queries, GET /queries/<queryID>, DELETE /queries/<queryID>. Query results come from the OData service at GET /queries/<queryID>/data.svc/.

Parts of the body

• <query> has dataSourceType (unv or unx) and dataSourceId (the universe id). An optional id is the query id.
• <querySpecification version="1.0"> holds everything else.
• <queryOptions>: optional settings. An option you do not list is not validated.
• <queryData>: result, sort and filter objects. There is at least one.

Result objects

<resultObject id="..." path="..."/>. id comes from the universe metadata. path is the full path and is optional.

Sort objects

<sortObject id="..." path="..." sortType="Ascending|Descending"/>. A sort object can also be a result object.

Query options

Each option can have activated="false" to switch it off. If it is missing, it counts as active.

<queryOptions>
    <queryOption name="duplicatedRows" value="true"/>
    <queryOption name="maxRowsRetrieved" activated="true" value="5000"/>
</queryOptions>

Filters

Filters sit in <filterPart>. There are two groups.

• Predefined filters: a filter already defined in the universe.

<filterPart>
    <predefinedFilter path="Inventory|folder\Stock Below Minimum|filter"
        id="_V5_GkLR_EeCZotjuycya1A"/>
</filterPart>

• Custom filters: comparison with constants, with a list, with a prompt, or with another object. Also ranking filters, subquery filters and AND/OR combinations.

Comparison operators and how many right-hand values each takes:

Equal to a constant:

<filterPart>
    <comparisonFilter operator="EqualTo" path="Time|folder\Calendar|folder\CalendarYear|dimension"
        id="_IBo8FLIhEeCk0Ylv-tlF2Q">
        <constantOperand>
            <value>
                <caption type="String">2011</caption>
            </value>
        </constantOperand>
    </comparisonFilter>
</filterPart>

Two cities from a list:

<comparisonFilter operator="InList" id="_zPwYENK-EeSNS_-8mYpikg"
    path="Store Names|folder\Associate_LOV_City|dimension">
    <constantOperand>
        <value><caption type="String">London</caption></value>
        <value><caption type="String">Paris</caption></value>
    </constantOperand>
</comparisonFilter>

Combine filters with <and> or <or> inside <filterPart>:

<filterPart>
    <and>
        <comparisonFilter ...>...</comparisonFilter>
        <comparisonFilter ...>...</comparisonFilter>
    </and>
</filterPart>

A filter can also ask a question at run time. This uses <parameterOperand>. See topic 6.

<parameterOperand>
    <parameter constrained="true" keepLastValues="true" optional="false" promptWithLov="true">
        <question>Enter Order ID</question>
    </parameter>
</parameterOperand>

Ranking filter: <rankingFilter level="3" function="Top"> with <dimension> and <basedOnMeasure> children. function is Top, Bottom, topPercent or bottomPercent.

Combined queries

One specification can hold several queries joined by union, minus or intersect. Only one result comes back. In the guide's example, two <queryData> blocks sit inside <minus>:

<querySpecification version="1.0">
    <queryOptions>...</queryOptions>
    <minus>
        <queryData>...</queryData>
        <queryData>...</queryData>
    </minus>
</querySpecification>

A different schema inside documents

Inside a Web Intelligence document, a data provider has its own query specification. It has a different, "queryspec" schema and uses text/xml:

GET /documents/<documentID>/dataproviders/<dataProviderID>/specification

PUT /documents/<documentID>/dataproviders/<dataProviderID>/specification

<queryspec:QuerySpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xmlns:queryspec="http://com.sap.sl.queryspec" dataProviderId="DP0">
    <queryParameters>
        <maxRowsRetrievedProperty value="90000"/>
        ...
    </queryParameters>
    <queriesTree xsi:type="queryspec:QueryOperatorNode" queryOperator="Union">
        <children xsi:type="queryspec:QueryDataNode">
            <bOQuery name="Query" identifier="...">
                <resultObjects identifier="DS0.DObc" name="Year"/>
                ...

Do not mix the two schemas.

A "parameter" is a question the server needs answered before it can run. That covers three kinds: a context (which of several paths a universe query should take), a prompt (an @Prompt or object parameter, such as "Enter a value for Country"), and an SAP variable. In Web Intelligence you answer them to refresh a document. In the Semantic Layer you answer them to run a query.

The pattern is a conversation. You ask for the parameters. You send answers. The server may then return the next unanswered parameter. You repeat until none are left.

Web Intelligence: refresh a document

List the parameters:

GET /documents/<documentID>/parameters?formattedValues=false&lovInfo=true&summary=false

All three query parameters are optional.

• formattedValues: format dates and numbers by the locale in X-SAP-PVL.
• lovInfo: set false to skip computing lists of values.
• summary: set true to get a summary of previous values.

Answer them and refresh:

PUT /documents/<documentID>/parameters?<optional_parameters>

Optional parameters: dataproviderScope (all or accessible, new in 4.2 SP04), formattedValues, lovInfo, refresh (default true), strict and variantIds.

With no body, the call returns the context or prompt that must be filled. If nothing needs filling, the document refreshes.

Cancel a running refresh: PUT /documents/<documentID>/parameters/execution?cancel=<mode>.

Get one parameter: GET /documents/<documentID>/parameters/<parameterID>.

Semantic Layer: run a query

GET /queries/<queryID>/parameters?formattedValues=false

PUT /queries/<queryID>/parameters

What a parameter looks like

A context with two choices:

<parameters>
    <parameter optional="false" type="context" dpId="DP0">
        <id>0</id>
        <name>Select a context</name>
        <answer constrained="true" type="Text">
            <info cardinality="Multiple">
                <lov hierarchical="false" partial="false" refreshable="true" searchable="false"/>
                ...
            </info>
        </answer>
    </parameter>
</parameters>

Key fields:

• type of the parameter: context, prompt or sapVariable.
• optional: always false for a context.
• dpId: the data provider the parameter belongs to (Web Intelligence only).
• dpLinks: set when one prompt is used in several queries.
• <name>: the question text.
• <answer type>: Text, Numeric, Date or DateTime.
• constrained: true means the user must pick from the list of values. False means free typing is allowed.
• cardinality: Single, Multiple or Interval (two values).

The list of values (<lov>)

partial says the list was cut. refreshable and searchable say whether you can refresh or search it. hierarchical marks a tree list. mandatorySearch means no values come back until you send a search pattern. Long lists come back as intervals. The default is 50 values per interval.

Previous and default values

When you list parameters, the server fills <answer>/<values> this way:

1. Previous values, if there are any.
2. Otherwise default values, if there are any.
3. Otherwise nothing.

Contexts have no default.

Sending answers

Send back a <parameters> body with each parameter <id> and its <values>. A context answer looks like this:

<parameters>
    <parameter optional="false" type="context">
        <id>0</id>
        <answer type="text">
            <values>
                <value id="1">Sales</value>
            </values>
        </answer>
    </parameter>
</parameters>

When all parameters are answered and refresh is true, you get:

<success>
    <message>The resource of type 'Document' with identifier 'XX' has been successfully updated.</message>
    <id>XX</id>
</success>

If refresh=false, the message says the resource "has not been modified".

You can also ask the server for a list of values while answering. In <info><lov><query ...> use intervalId, intervalSize (a number, -1 or Unlimited for all, or Server), refresh, <sort order="Ascending|Descending|None"/> and <search>. In a search pattern, ? means zero or one character and * means zero or more.

"Change source" swaps the data objects a document's queries use for objects from another data source. The guide gives two uses. One is to relink a document to a universe converted from .unv to .unx. The other is to link a document uploaded to the repository to a data source stored in the repository. It does not support text files, Excel, SAP HANA Online or web services as sources.

The server works out a mapping: for each source object, which target object replaces it. It does this using strategies. You can accept the suggestion or edit it.

The flow

1. Ask for the suggested mapping.
2. Apply it with a POST, either as suggested or edited.

Since 4.1 SP6 you can also choose the strategies yourself.

Get the suggested mapping (default strategies)

GET /documents/<documentID>/dataproviders/mappings?originDataproviderIds=<DP1>,<DP2>&targetDatasourceId=<dataSourceID>

originDataproviderIds is optional. If left out, all data providers in the document are used. targetDatasourceId is required.

<mappings>
    <content>
        <mapping status="Ok">
            <source><id>DS0.DO12</id></source>
            <target><id>DS1.DO12</id></target>
        </mapping>
        ...

Status is Ok, Ambiguous or Not found.

Get the mapping using strategies you choose

PUT on the same path. You set the order of strategies in the body.

Apply the mapping

POST /documents/<documentID>/dataproviders/mappings?originDataproviderIds=<DP1>&targetDatasourceId=<dataSourceID>&skipChecking=true

skipChecking is optional and skips verifications to speed things up.

<mappings>
    <content>
        <mapping>
            <source><id>DS0.DO12</id></source>
            <target><id>DS1.DO12</id></target>
        </mapping>
        ...

What gets used depends on the body:

Strategies

Default order: SameId, SameTechnicalName, SamePath, CloseName.

Tolerances

The <policy> element sets how strict matching is:

<policy qualificationTolerance="Low|Normal|High" dataTypeTolerance="Low|Normal|High">

Low means the same qualification or type. High allows different ones. Example: qualificationTolerance="Low" dataTypeTolerance="High" means qualifications must match exactly, but data types may differ.

The API runs as the logged-in user. It cannot do more than that user's rights allow. Rights are applied before the API is used. The caller does not see them. For example, objects hidden by the user's access level are simply not returned in the universe outline.

Universe and connection rights

Connections, Objects, Rows and Table Mapping rights also apply when you read query results through OData or read lists of values through GET /queries/<queryID>/parameters.

For .unx universes, the same rights apply. Overloads are managed through business security profiles (rights on the business layer) and data security profiles (rights on the data foundation).

Check the current user's Web Intelligence rights

GET /session/rights

<rights>
    <right>
        <id>use_interactive_viewing</id>
    </right>
    <right>
        <id>edit_documents</id>
    </right>
    <right>
        <id>publish_documents_real</id>
    </right>
    ...
</rights>

JSON:

{"rights": {"right": [{"id": "use_interactive_viewing"}, {"id": "edit_documents"}, ...]}}

The call returns only allowed rights. Examples of ids: create_documents, edit_documents, edit_query_sql, use_formula_language, read_corporate_documents, publish_documents_real.

Per-document rights: GET /documents/<documentID>/rights.

Before you build a query, you need to know what the universe offers. The universe details call returns an outline: folders, and inside them the dimensions, measures and filters. Each item has an id, a name and a path. You will use these ids when you write the query specification. Start with the list call to find the universe id. Then ask for its details.

List the universes

GET /universes?type=<type>&offset=<offset>&limit=<limit>

type is unv, unx or all (default all). limit is 1 to 50 (default 10). offset starts at 0.

GET /universes?type=unx&limit=2

<universes>
    <universe>
        <id>6773</id>
        <cuid>AXyRzvmRrJxLqUm6_Jbf7lE</cuid>
        <name>efashion.unx</name>
        <type>unx</type>
        <folderId>6771</folderId>
    </universe>
    ...
</universes>

Get the details of one universe

GET /universes/<universeID>?aggregated=<aggregated>

aggregated is optional and defaults to false. It only matters for UNX universes. With false, you get the master view if you are granted it, otherwise the default view (its name comes back in <businessViewName>). With true, you get one merged outline of everything you are granted. The guide says this is the behaviour of SDK versions from 4.1 SP5 on. Earlier SDKs behaved as aggregated=false.

GET /universes/5808?aggregated=false

<universe>
    <id>5808</id>
    <cuid>AUW2qRdU0IdPkyhlpZWrxvo</cuid>
    <name>Warehouse.unx</name>
    <type>unx</type>
    <folderId>5807</folderId>
    <maxRowsRetrieved>5000</maxRowsRetrieved>
    <maxRetrievalTime>600</maxRetrievalTime>
    <outline aggregated="false">
        <folder>
            <id>_IBo8OLIhEeCk0Ylv-tlF2Q</id>
            <name>Customer</name>
            <item dataType="Numeric" type="Dimension" hasLov="false">
                <id>_IBo8ObIhEeCk0Ylv-tlF2Q</id>
                <name>Customer Id</name>
                <path>Customer|folder\Customer Id|dimension</path>
            </item>
            ...
        </folder>
        <folder>
            <name>Inventory</name>
            <item dataType="Numeric" type="Measure" hasLov="false">
                <id>_IB8eFrIhEeCk0Ylv-tlF2Q</id>
                <name>Stock Level</name>
                <path>Inventory|folder\Stock Level|measure</path>
            </item>
            <item type="Filter"> ... </item>
        </folder>
    </outline>
</universe>

Items carry type (Dimension, Measure, Filter), dataType and hasLov. Some measures also carry <aggregationFunction> such as Sum.

The Web Intelligence version of this call (section 8.16.2) shows a slightly different response. It adds <path> and <connected> to the universe. Its item types read BODimension and the ids look like DO1, DO2. The guide shows both shapes, so read the type and id you actually get back.

A new document starts empty. You give it a name and a folder. The response gives you the new document id. Keep it: every later call uses it. You can find folder ids with GET http://<server-name>:6405/biprws/infostore, as the guide says.

POST /documents

POST /documents

<document>
    <name>My Document</name>
    <folderId>5151</folderId>
</document>

Response:
<success>
   <message>The resource of type "Document" with identifier "5022" has been
successfully created.</message>
   <id>5022</id>
</success>

JSON works too, with the same fields under a document key. The response is a success object holding message and id.

Check the document afterwards

GET /documents/<documentID>?trackerDocumentId=<trackerDocumentID>

Use this to read state, path, size, scheduled and refreshOnOpen. trackerDocumentId is optional and only for the trackdata feature, only when the state is Unused.

GET /documents/4958

<document>
    <id>4958</id>
    <cuid>AQtkbbSqN4NOj3ydf.Sw1lY</cuid>
    <name>Formatting Sample</name>
    <folderId>8246</folderId>
    <path>My Folders/web Intelligence</path>
    <scheduled>true</scheduled>
    <state>Unused</state>
    <createdBy>Administrator</createdBy>
    <lastAuthor>User1</lastAuthor>
    <size>29410</size>
    <refreshOnOpen>false</refreshOnOpen>
</document>

List documents

GET /documents?offset=<offset>&limit=<limit>

Documents come back sorted by name. limit is 0 to 50, default 10. The guide also says you can search with the /searches API.

Copy a document

POST /documents?sourceId=<documentID>

The body is optional (<name>, <folderId>). Without them the service names the copy and keeps the original folder. The guide says a copy does not open the document in memory.

A data provider is a data source for the document. For a universe, you add it by name and universe id. Then you describe what the query returns in a query specification. The specification lists the result objects, the filter conditions, and some query limits.

Add the data provider

POST /documents/<documentID>/dataproviders

<dataprovider>
    <name>Query1</name>
    <dataSourceId>...</dataSourceId>
</dataprovider>

Response:
<success>
    <message>The resource of type "Data provider" with identifier "DP3" has been
successfully created.</message>
    <id>DP3</id>
</success>

<name> is the data source name. <dataSourceId> is the data source id. For a universe, this is the universe id from topic 1. The guide's body examples for this call cover a BEx query (11990;Z_BOBJ;AAQUERY_SAMPLE), an Excel file and free-hand SQL. It shows no universe example for this call. The response id (here DP3) is the data provider id.

Read the current query specification

GET /documents/<documentID>/dataproviders/<dataProviderID>/specification

The response type is text/xml.

GET /documents/7738/dataproviders/DP0/specification

<queryspec:QuerySpec ... xmlns:queryspec="http://com.sap.sl.queryspec"
dataProviderId="DP0">
    <queryParameters>
        <duplicatedRowsProperty activated="true" value="true"/>
        <maxRetrievalTimeInSecondsProperty value="300"/>
        <maxRowsRetrievedProperty value="90000"/>
        <removeEmptyRowsProperty activated="true" value="true"/>
        ...
    </queryParameters>
    <queriesTree xsi:type="queryspec:QueryOperatorNode" queryOperator="Union">
        <children xsi:type="queryspec:QueryDataNode">
            <bOQuery name="Query" identifier="_1y8aENsVEeGswMB7H6m1Qw">
                <resultObjects identifier="DS0.DObc" name="Year"/>
                <resultObjects identifier="DS0.DOda" name="State"/>
                <resultObjects identifier="DS0.DOa5" name="Lines"/>
                <resultObjects identifier="DS0.DO93" name="Sales revenue"/>
                <conditionPart/>
            </bOQuery>
        </children>
    </queriesTree>
    <propertyBag key="DUPLICATED_ROWS_UNDEFINED_VALUE" value="-1"/>
    ...
</queryspec:QuerySpec>

Update the query specification

PUT /documents/<documentID>/dataproviders/<dataProviderID>/specification

The request type is text/xml. The body is the same shape as the GET response, with your resultObjects in the bOQuery. The guide's PUT example uses dataProviderId="DP0" and the same four result objects.

PUT /documents/7738/dataproviders/DP0/specification

<queryspec:QuerySpec xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xmlns:queryspec="http://com.sap.sl.queryspec"
    dataProviderId="DP0">
    <queryParameters>
        <maxRetrievalTimeInSecondsProperty value="300"/>
        <maxRowsRetrievedProperty value="90000"/>
        ...
    </queryParameters>
    <queriesTree xsi:type="queryspec:QueryOperatorNode" queryOperator="Union">
        <children xsi:type="queryspec:QueryDataNode">
            <bOQuery name="Query" identifier="_1y8aENsVEeGswMB7H6m1Qw">
                <resultObjects identifier="DS0.DObc" name="Year"/>
                ...
                <conditionPart/>
            </bOQuery>
        </children>
    </queriesTree>
    ...
</queryspec:QuerySpec>

Response:
<success>
    <message>The resource of type "Data provider" with identifier "DP0" has been
successfully updated.</message>
    <id>DP0</id>
</success>

Get the details of the data provider

GET /documents/<documentID>/dataproviders/<dataProviderID>

This returns id, name, dataSourceId, dataSourceType (unx, unv, bex, excel or fhsql), dataSourcePrefix, updated, rowCount, and a dictionary of expressions. Each expression has an id such as DP1.DO1, a name and a formulaLanguageId such as [City]. You will use these in report formulas.

<dataprovider>
    <id>DP0</id>
    <name>Query 1</name>
    <dataSourceId>7599</dataSourceId>
    <dataSourceType>bex</dataSourceType>
    <dataSourcePrefix>DS0</dataSourcePrefix>
    <rowCount>284</rowCount>
    <dictionary>
        <expression dataType="String" qualification="Dimension">
            <id>DP1.DO1</id>
            <name>City</name>
            <dataSourceObjectId>AZ_CITY</dataSourceObjectId>
            <formulaLanguageId>[City]</formulaLanguageId>
        </expression>
        ...
    </dictionary>
    <query>...</query>
</dataprovider>

Change the source later

GET /documents/<documentID>/dataproviders/mappings?originDataproviderIds=<dataProviderID[,...]>&targetDatasourceId=<dataSourceID>

This proposes object mappings from one data source to another. targetDatasourceId is mandatory. The guide also lists a call to apply the mapping ("Changing the Data Objects of a Data Provider"). That call's body is not covered in this module.

A refresh runs the query. If the document has prompts or contexts, the first refresh call returns them instead of running. You answer them and call again. When everything is answered, the query runs.

Get the refresh parameters

GET /documents/<documentID>/parameters?formattedValues=<bool>&lovInfo=<bool>&summary=<bool>

All three are optional. formattedValues defaults to false. lovInfo defaults to true. Set it to false to skip the lists of values. summary defaults to false.

<parameters/>

That is the answer when there is nothing to fill. Otherwise you get a parameter for each context or prompt:

<parameters>
    <parameter optional="false" type="context" dpId="DP0">
        <id>0</id>
        <technicalName>0</technicalName>
        <name>Select a context</name>
        <answer constrained="true" type="Text">
            <info cardinality="Multiple">
                <values>
                    <value id="2">Reservations</value>
                    <value id="1">Sales</value>
                </values>
                <previous>
                    <value id="2">Reservations</value>
                </previous>
            </info>
            <values>
                <value id="2">Reservations</value>
            </values>
        </answer>
    </parameter>
</parameters>

Refresh, with or without answers

PUT /documents/<documentID>/parameters?<optional_parameters>

With no body, the service returns the context or prompt to fill. If nothing needs filling, it refreshes. Optional parameters:

• refresh (default true): set to false to stop before the data refresh.
• dataproviderScope: all (default) or accessible. With accessible, the refresh works even if some data providers are not accessible. New since 4.2 SP04.
• formattedValues, lovInfo: as above.
• variantIds: comma-separated variant ids that answer prompts. The last value wins.
• strict (default false): used with variantIds. If true, a mismatch is an error.

PUT /documents/xxx/parameters

<parameters>
    <parameter optional="false" type="context">
        <id>0</id>
        <name>Select a context</name>
        <answer constrained="true" type="Text">
            <values>
                <value id="2">Reservations</value>
            <values/>
        </answer>
    </parameter>
</parameters>

Response:
<success>
    <message>The resource of type 'document' with identifier 'xxx' has been
successfully updated.</message>
    <id>xxx</id>
</success>

A context plus a prompt in one call (the guide's example):

<parameters>
    <parameter optional="false" type="context">
        <id>0</id>
        <answer type="text">
            <values><value id="1">Sales</value></values>
        </answer>
    </parameter>
    <parameter type="prompt">
        <id>1</id>
        <answer type="text">
            <values><value>France</value></values>
        </answer>
    </parameter>
</parameters>

If refresh=false, the success message says the document "has not been modified".

Refresh one data provider only

PUT /documents/<documentID>/dataproviders/<dataproviderID>/parameters?<optional_parameters>

The parameters call for one data provider is GET /documents/<documentID>/dataproviders/<dataproviderID>/parameters. It works the same way.

Cancel a running refresh

PUT /documents/<documentID>/parameters/execution?cancel=<mode>

Modes are partial, restore and purge. Partial keeps the new values retrieved so far. Restore brings back the previous values. Purge empties the values but keeps structure and formatting.

Read the data

Two ways, both shown in the guide.

Per data provider flow, as XML or CSV:

GET /documents/<documentID>/dataproviders/<dataProviderID>/flows/count returns plain text such as 1.

GET /documents/<documentID>/dataproviders/<dataProviderID>/flows<flowID> returns the flow data. The guide's examples are written as /flows/0.

GET /documents/7744/dataproviders/DP0/flows/0

"Year";"State";"Sales revenue";"Margin"
"2001";"California";"1704210.8";"774893.4"
...

The XML shape for the same call is <DATA_PROVIDERS><DATA_PROVIDER><ROW><CELL INDEX="0">2006</CELL>.... CSV gives values only.

Per report element, as a dataset:

GET /documents/<documentID>/reports/<reportID>/elements/<elementID>/dataset?datapath=<datapath>

reference=<reference> works in place of datapath. Do not send both.

<dataset>
    <metadata>
        <value dataObjectId="DP1.DO84" type="String">Country</value>
        <value dataObjectId="DP1.DO7" type="Numeric">Revenue</value>
    </metadata>
    <row>
        <value>Bahamas Islands</value>
        <value>65600</value>
    </row>
    ...
</dataset>

A document holds reports (tabs). A report holds elements. Elements are bound to data through axes. Each axis lists formulas such as =[Country]. The formula names come from the data provider's formulaLanguageId values.

Create a report

POST /documents/<documentID>/reports

The body is optional. If you leave the name out, the service names it.

POST /documents/12782/reports

Response:
<success>
    <message>The resource of type "Report" with identifier "2" has been
successfully created.</message>
    <id>2</id>
</success>

With a name, send <report><name>...</name></report>. The guide's JSON example sends {"report": {"name":"Chart Report"}}.

List the report's elements

GET /documents/<documentID>/reports/<reportID>/elements?unit=<unit>&allInfo=<boolean>

Use this to find ids. Each element has a type: PageZone, Cell, VTable, HTable, XTable, Form, Visualization or Custom. allInfo=true includes the details of every element (since 4.2 SP04). Other list calls: GET /documents/<documentID>/reports for the report ids.

Create a table

POST /documents/<documentID>/reports/<reportID>/elements

POST /documents/14695/reports/1/elements

<element type="XTable">
    <parentId>2</parentId>
    <content>
        <axes>
            <axis role="Row">
                <expressions>
                    <formula dataType="String">=[Country]</formula>
                    <formula dataType="String">=[Year]</formula>
                </expressions>
            </axis>
            <axis role="Column">
                <expressions>
                    <formula dataType="String">=[Resort]</formula>
                    <formula dataType="String">=[Service]</formula>
                </expressions>
            </axis>
            <axis role="Body">
                <expressions>
                    <formula dataType="Numeric">=[Number of guests]</formula>
                    <formula dataType="Numeric">=[Revenue]</formula>
                </expressions>
            </axis>
        </axes>
    </content>
</element>

Table types are VTable, HTable, XTable and Form. The guide's parentId is 2 in these examples. In the element list, the PageZone elements and some tables have ids and a parentId. The guide does not say outright which id to use for the body of a new report. Read it from the element list of a report you made.

Create a chart

POST /documents/13069/reports/6/elements?unit=centimeter

<element type="Visualization">
    <parentId>2</parentId>
    <content>
        <chart type="HorizontalBar">
            <axes>
                <axis role="Color">
                    <expressions>
                        <formula dataType="String">=[Resort]</formula>
                        <formula dataType="String">=[Country]</formula>
                    </expressions>
                </axis>
                <axis role="Category">
                    <expressions>
                        <formula dataType="String">=[Year]</formula>
                    </expressions>
                </axis>
                <axis role="Value">
                    <expressions>
                        <formula dataType="Numeric">=[Revenue]</formula>
                    </expressions>
                </axis>
            </axes>
        </chart>
    </content>
</element>

The chart types come from GET /configuration/visualizations (4.1 SP1). Examples include HorizontalBar, VerticalBar, Pie, Line, Radar, Waterfall and Scatter. Axis roles depend on the chart. A bar or line chart uses Color, Category and Value. A Pie uses PieSectorSize and PieSectorColor.

Change the formulas on an axis later

PUT /documents/<documentID>/reports/<reportID>/elements/<elementID>/axes/<axisID>/expressions

PUT /documents/16995/reports/1/elements/8/axes/0/expressions

<expressions>
    <formula dataType="String">=[Resort]</formula>
    <formula dataType="Numeric">=[Revenue]</formula>
</expressions>

Axis ids by element: a Section, HTable or Form has one axis (id 0, row). A VTable has one axis (id 1, column). An XTable has three (0 row, 1 column, 2 body). A chart's axes depend on its type.

Subtotals on a table

POST /documents/<documentID>/reports/<reportID>/elements/<elementID>/calculations?strip=column

POST /documents/15231/reports/5/elements/13/calculations?strip=row

<calculation>
    <id>Sum</id>
</calculation>

strip is row or column. In cross tables it only applies to body cells. On a vertical table the guide adds <basedOn><id>30</id></basedOn> to name the column.

A variable is a named formula stored in the document. Once created, it appears as an object you can use in report formulas. Use qualification to say whether it is a Measure, an Attribute or a Dimension.

POST /documents/<documentID>/variables

POST /documents/4326/variables

<variable qualification="Measure">
    <name>new variable</name>
    <description>your description</description>
    <definition>=[RevenueThreshold]*[Threshold factor]</definition>
</variable>

Response:
<success>
    <message>The resource of type "Variable" with identifier "LB" has been
successfully created.</message>
    <id>LB</id>
</success>

Other variable calls

• GET /documents/<documentID>/variables lists variables (id, name, dataType, qualification).
• GET /documents/<documentID>/variables/<variableID> returns the definition.
• PUT /documents/<documentID>/variables/<variableID> edits it.
• DELETE /documents/<documentID>/variables/<variableID> removes it.

GET /documents/1234/variables/L9

<variable dataType="Numeric" qualification="Measure">
    <id>L9</id>
    <name>Threshold Max</name>
    <description>This is the maximum threshold.</description>
    <formulaLanguageId>[Threshold Max]</formulaLanguageId>
    <definition>=[RevenueThreshold]*(1+[Threshold factor])</definition>
</variable>

Check available functions

GET /configuration/functions lists the formula functions, with id, name, description and syntax. There is also a call for operators, listed in the guide under Getting the Formula Engine Operators (section 8.1.15.2).

Grouping variables

A grouping variable maps values of a dimension into named groups. The request uses grouping="true", a <dimensionId> and <groups>. The guide's example groups months 1 to 4 as "From January to April" and 7 and 8 as "Summer Holidays".

These calls shape what an element shows. Sort changes the order. A ranking keeps the top and bottom records. A data filter keeps rows that match conditions.

Sort

First read the sorts to get their ids:

GET /documents/<documentID>/reports/<reportID>/elements/<elementID>/sorts

<sorts>
    <sort order="Descending">
        <id>R7.B289.S0</id>
        <assignedDataObject refId="DP0.DO7" kind="Expression"
dataType="Numeric">=[Revenue]</assignedDataObject>
    </sort>
    <sort order="Ascending">
        <id>R7.B289.S1</id>
        <assignedDataObject refId="DP0.DO39" kind="Expression"
dataType="String">=[Country]</assignedDataObject>
    </sort>
</sorts>

Then change the order:

PUT /documents/<documentID>/reports/<reportID>/elements/<elementID>/sorts

PUT /documents/9134/reports/4/elements/12/sorts

<sorts>
  <group>
    <sort order="Descending">
      <id>R4.B12.S1</id>
    </sort>
    <sort order="None">
      <id>R4.B12.S0</id>
    </sort>
  </group>
  ...
</sorts>

For one table cell, the body is just <sorts><sort order="Descending" /></sorts>. Values for order in the guide's examples are Ascending, Descending and None.

Rank

POST /documents/<documentID>/reports/<reportID>/elements/<elementID>/ranking

POST /documents/17281/reports/1/elements/20/ranking

<ranking calculation="Count" top="3" bottom="3">
    <basedOn>=[Number of guests]</basedOn>
    <rankedBy>=[Year]</rankedBy>
</ranking>

Response:
<success>
    <message>The resource of type "Ranking" has been successfully created.</message>
</success>

PUT on the same path updates it. GET reads it. DELETE removes it.

Filter

POST /documents/<documentID>/reports/<reportID>/elements/<elementID>/datafilter

<datafilter>
    <and>
        <condition key="[Lines]" operator="InList">
            <value>City Trousers</value>
            <value>Shirt Waist</value>
            <value>Jackets</value>
        </condition>
        <condition key="[State]" operator="NotEqual">
            <value>Colorado</value>
        </condition>
        <or>
            <condition key="[Sales revenue]" operator="Greater">
                <value>5000</value>
            </condition>
            <condition key="[Sales revenue]" operator="Less">
                <value>10</value>
            </condition>
        </or>
    </and>
</datafilter>

Operators listed in the guide: Equal, NotEqual, Greater, GreaterOrEqual, Less, LessOrEqual, Between, NotBetween, InList, NotInList, IsNull, IsNotNull, IsAny, Like, NotLike, Both, Except. PUT on the same path updates the filter. The guide also shows a delete call and a get-details call for it.

When you are done, you save the document to the repository. You can then export it to a file. You can also schedule it so the server runs it later and delivers the result.

Save by changing state

PUT /documents/<documentID>

The guide says to use this method to save a document to the CMS repository. Body is optional. Without a body, a Modified document is updated and saved. Sending <state>Unused</state> on a Modified document updates and closes it. The guide's own table says "From Modified to Unused: The document is updated and closed." This is also how you release server memory.

PUT /documents/9326

Response:
<success>
    <message>The resource of type "document" with identifier "9326" has been
successfully updated.</message>
    <id>9326</id>
</success>

To keep a session alive without changing anything, send <state>KeepAlive</state>.

Save as, into a folder

POST /documents/<documentID>?overwrite=<boolean>&withComments=<boolean>

POST /documents/7400?overwrite=false&withComments=true

<document>
    <name>Document Save As Example</name>
    <keywords>Save as</keywords>
    <folderId>6773</folderId>
    <categories>
        <category><id>5980</id></category>
    </categories>
    <properties>
        <property key="refreshonopen">true</property>
        <property key="permanentregionalformatting">true</property>
    </properties>
</document>

Response:
<success>
    <message>The resource of type "Document" with identifier "32271" has been
successfully created.</message>
    <id>32271</id>
</success>

overwrite defaults to true. Set it to false to get an error if the document exists. withComments defaults to false. Use -1 as the folderId to save to the same folder.

Export the whole document

GET /documents/<documentID>?<optional_parameters>

Set the format with the Accept header. The guide lists these formats for a whole document: XML, zipped HTML, PDF, Excel 2003 and Excel 2007. CSV is not on this list.

curl -G -s -H "accept:application/pdf" -H X-SAP-LogonToken:"""%tokenValue%"""
 <base_webi_REST_URL>/documents/8022" > exportpdf.pdf

curl -G -s -H "accept:application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
 -H X-SAP-LogonToken:"""%tokenValue%""" <base_webi_REST_URL>/documents/8022?optimized=true" > excel2007.xls

Excel 2007 is application/vnd.openxmlformats-officedocument.spreadsheetml.sheet. Excel 2003 is application/vnd.ms-excel. The optimized=true parameter tunes Excel output for calculations. dpi (75 to 9600) sets chart resolution.

Export one report, or one element, as CSV

GET /documents/<documentID>/reports/<reportID>?<optional_parameters>

This supports HTML, zipped HTML, MHTML, XML, PDF, Excel 2003, Excel 2007, CSV and plain text. CSV options are textQualifier (' or "), columnDelimiter (comma, semicolon or Tab) and charset.

GET /documents/<documentID>/reports/<reportID>/elements/<elementID>?<optional_parameters>

curl -G -s -H "accept:text/csv" -H X-SAP-LogonToken:"""%tokenValue%"""
 "<base_webi_REST_URL>/documents/9227/reports/1/elements/4?
textQualifier='&columnDelimiter=,&charset='UTF-8'" > reportelement4.csv

Valid charsets come from the guide's "Getting the Charsets" call (section 8.1.13.14).

Export as pages

GET /documents/<documentID>/pages?<optional_parameters>

GET /documents/<documentID>/reports/<reportID>/pages is the report-level version in the guide's table of contents. Optional parameters include mode (normal or quickDisplay), orientation and unit.

Schedule

POST /documents/<documentID>/schedules

A schedule can run now, once, daily, hourly, weekly or monthly. The format type is one of webi, pdf, xls, csv, txt, csvArchive or htmlArchive (default webi). With no recurrence element, it runs now. This is the guide's "now" example with a file system destination:

POST /documents/8002/schedules

<schedule>
    <name>nameOfSchedule</name>
    <format type="pdf"/>
    <destination>
        <filesystem>
            <username>user name</username>
            <password>user password</password>
            <directory>C:/tmp/</directory>
        </filesystem>
    </destination>
</schedule>

A daily schedule to the inbox, answering a prompt:

<schedule>
    <name>nameOfSchedule</name>
    <format type="webi"/>
    <destination>
        <inbox/>
    </destination>
    <daily retriesAllowed="2" retryIntervalInSeconds="60">
        <startdate>2012-08-26T15:58:51.000+02:00</startdate>
        <enddate>2012-09-14T15:58:51.000+02:00</enddate>
        <dayinterval>1</dayinterval>
    </daily>
    <parameters>
        <parameter optional="false" type="prompt" dpId="DP0">
            <id>0</id>
            <technicalName>Enter State:</technicalName>
            <answer constrained="false" type="text">
                <values><value>Texas</value></values>
            </answer>
        </parameter>
    </parameters>
</schedule>

Other destinations in the guide are FTP, SFTP and mail (<mail> with from, to, cc, bcc, subject, message and addAttachment).

Manage schedules with:

• GET /documents/<documentID>/schedules lists them, with id, name, format and status. Status values are Pending, Running, Paused, Completed, Warning, Expired and Failed.
• GET /documents/<documentID>/schedules/<scheduleID> returns one schedule.
• DELETE /documents/<documentID>/schedules/<scheduleID> removes one.

CSV schedule options (textQualifier, columnDelimiter, charset, onePerDataProvider) go in <format type="csv"><properties>...</properties></format>.

This is the order that follows the topics above. Steps 6 to 8 can be reordered. Step 4 can be skipped when a document has no prompts.

1. GET /universes?type=unx then GET /universes/<universeID>?aggregated=true to find the universe and read its objects.
2. POST /documents with name and folderId to create the empty document. Keep the returned id.
3. POST /documents/<documentID>/dataproviders with name and dataSourceId (the universe id). Keep the returned data provider id, such as DP0.
4. PUT /documents/<documentID>/dataproviders/<dataProviderID>/specification with the result objects (ids from step 1, prefixed with dataSourcePrefix for UNV).
5. GET /documents/<documentID>/parameters, then PUT /documents/<documentID>/parameters with answers, until the response says the document was updated. This runs the query.
6. GET /documents/<documentID>/dataproviders/<dataProviderID> to read the dictionary and note each formulaLanguageId. Optionally read raw data from .../flows/0.
7. POST /documents/<documentID>/reports to add a report. Then GET /documents/<documentID>/reports/<reportID>/elements to find the ids you need.
8. POST /documents/<documentID>/reports/<reportID>/elements with a table (XTable, VTable or HTable), then again with a chart (Visualization).
9. POST /documents/<documentID>/variables for any calculated object. Then change the axis formulas with PUT .../elements/<elementID>/axes/<axisID>/expressions. Refresh again if you edited an existing variable.
10. PUT .../elements/<elementID>/sorts, POST .../elements/<elementID>/ranking and POST .../elements/<elementID>/datafilter to sort, rank and filter.
11. GET .../elements/<elementID>/dataset to check the result.
12. PUT /documents/<documentID> to save the modified document, or POST /documents/<documentID>?overwrite=false to save a copy to a folder.
13. GET /documents/<documentID>/reports/<reportID> with an Accept header (PDF, Excel, CSV) to export.
14. POST /documents/<documentID>/schedules to set up a recurring run.
15. PUT /documents/<documentID> with <state>Unused</state> to close the document and free server memory.

What and why. An AI agent is a language model that can take actions on its own. You give it a goal. It plans the steps, calls tools (here, the Web Intelligence REST API), reads what comes back, and decides the next step. It keeps going until the goal is met or it needs you.

That loop matches report building well. Most of the effort in Web Intelligence is not clicking. It is working out which objects to use, writing filters and formulas, refreshing, spotting that a number is wrong, and fixing it. An agent can do that cycle in minutes, as many times as it takes.

Who does what

Where the time goes down most

• Question to first draft. A request that waits days in a queue becomes a draft the same hour.
• Formulas. Calculation contexts (Level 4) are where people get stuck. An agent can try a formula, refresh, read the result and correct itself.
• Work across many documents. Changing a source, renaming objects or documenting 200 reports is repetitive. Repetitive work through an API is what agents do best.
• Checking. An agent can refresh every report after a change and tell you which numbers moved.

What and why. Before an agent builds anything, set up a safe place for it to work. Do this once. After that, every request is quick and low risk.

Steps

1. A service account. Ask your BI administrator for a dedicated account for the agent, not your own login. Give it the rights it needs and no more: log on to the REST API, create and edit documents, refresh, use the universes it needs. Do not give it delete rights in shared folders.
2. A sandbox folder. Create one folder, for example Public Folders > AI Sandbox, where the agent can save. Everything it builds lands there first.
3. Network access. The machine running the agent must reach the REST service. By default it is on port 6405 at /biprws.
4. Keep the password out of the conversation. Store the account password in your password manager or secret store. The tool that logs on reads it at run time. Never paste it into a chat with the model.
5. Give the agent tools. Either let it run HTTP calls (for example with curl) and give it the API reference, or wrap the common calls in a small script with named commands: log on, list universe objects, create document, add query, refresh, add table, add variable, export, save. A small set of named commands means fewer mistakes than raw HTTP.
6. Build a context pack. These are files the agent reads before every job:
   • the object catalogue of each universe (names, types, folders, descriptions), exported once through the Semantic Layer API;
   • your business definitions ("Net revenue = Sales revenue minus returns");
   • naming rules for documents, reports and variables;
   • two or three good existing reports, as examples of house style;
   • the trusted numbers to check against (for example last year's revenue by quarter).

Worked example. The eFashion catalogue file lists Time period > Year, Quarter, Month, Store > State, City, Store name, Product > Lines, Category, and Measures > Sales revenue, Quantity sold, Margin. With this file the agent never invents an object such as "Revenue 2024", because it checks every name against the list.

What and why. The agent is only as good as the request. A short structured brief removes most rework. It also makes the request reusable: next quarter you change one line and run it again.

Brief template

1. Question. One sentence: what decision will this report support?
2. Reader. Who opens it, and in Reading mode or Design mode?
3. Measures and dimensions. Use the names from the catalogue where you know them.
4. Filters. Fixed filters (for example "Lines not equal to Accessories") and prompts the reader answers at refresh.
5. Time window. Fixed dates, or a prompt.
6. Layout sketch. "One table per State as a section, a bar chart of revenue by quarter above it."
7. Calculations. Say each one in words: "share of state revenue", "change from last year".
8. Check figure. A number the agent must match, taken from a source you trust, for example "2024 total Sales revenue as reported in the finance ledger".
9. Done means. Saved in the sandbox, exported to PDF, with a short note of the formulas used.

Worked example (brief)

Question: which stores should get the Q4 stock top-up? Reader: regional managers, Reading mode. Use Year, Quarter, State, Store name, Sales revenue, Margin. Prompt the reader for Year. Section on State. Table of the top 5 stores by Margin in each state, with margin as a % of state margin. Bar chart of Margin by Quarter for the state. Check: the sum of state margins equals total Margin for the chosen year. Save as "Store top-up – draft" in AI Sandbox and export a PDF.

What and why. Never let an agent build the whole report in one go and hand it over. Make it work in short steps, checking after each one. Errors then surface at the step that caused them, while they are still cheap to fix.

The loop

1. Plan. The agent restates the brief as a list of objects, filters, prompts, variables and blocks. Read it. This takes 30 seconds and catches most misunderstandings.
2. Query. It creates the document and data provider, refreshes, and reports the row count and the total of each measure.
3. Check the data. It compares totals with the check figure. If they differ, it stops and investigates before it builds anything else.
4. Blocks. It adds sections, tables and charts one at a time and reads back each block's data.
5. Formulas. For each variable it shows the formula, the result for two or three rows, and why the context is right.
6. Scan for errors. It exports and searches the output for #MULTIVALUE, #CONTEXT, #SYNTAX, #DIV/0, #INCOMPATIBLE and #ERROR.
7. Hand over. It saves to the sandbox and gives you the link, the export, the formulas and anything it was unsure of.

What the agent should check every time

• Totals match the check figure, within rounding.
• The row count is plausible. A sudden jump often means a missing join or an extra object in the query.
• No error codes appear in any cell.
• Prompts work with at least two different answers.
• Every object it used exists in the catalogue.

What and why. This is the everyday use: a business user asks a question and gets a working draft quickly.

Steps

1. Write the brief (topic 3).
2. The agent reads the catalogue and proposes the plan. You approve it.
3. The agent builds through the API in this order: create document › add data provider with the query › refresh and answer prompts › add report › add sections, tables and charts › add variables › sort, rank and filter › save › export. The two API modules earlier in this level show each call.
4. It runs the checks from topic 4.
5. You open the draft in Design mode, tidy the layout and move it to the shared folder.

Worked example. For the store top-up brief in topic 3, the plan the agent returns could look like this:

• Query: Year, State, Store name, Quarter, Margin, Sales revenue; prompt on Year.
• Section on State.
• Table: Store name, Margin, a variable Margin share = [Margin]/([Margin] In Section); ranking Top 5 on Margin.
• Chart: vertical bar, Quarter on the category axis, Margin as the value.
• Check: Sum of Margin across all sections = Margin In Report for the chosen year.

You read the plan, notice that "Top 5" should rank on Margin rather than Sales revenue, confirm, and the agent builds it.

What and why. You do not need the API for this one. Use the agent as a tutor and checker for formulas, especially calculation contexts. It is the quickest win for anyone on Levels 3 and 4.

Steps

1. Describe the block: which dimensions it has, and whether it sits in a section or break.
2. Say what the number should mean in words, or paste the formula and the error code.
3. Ask for the formula, an explanation of its input and output context, and a test you can do: "on this row you should see X".
4. Test the formula in a copy of the document. If you have the API set up, the agent can create the variable in a sandbox copy, refresh and read the result itself.

Worked example

Block: Year, Quarter, Sales revenue, in a section on State. I want each quarter's share of the state's annual revenue. My formula [Sales revenue]/Sum([Sales revenue]) gives 100% on every row.

The agent explains that Sum([Sales revenue]) takes the context of the row, so it equals the row value. It suggests [Sales revenue]/([Sales revenue] In ([State];[Year])). It then adds a check: the shares for one state and year should add up to 100%.

What and why. This is where the time saved is largest. Examples: a universe is replaced, an object is renamed, a standard filter must be added everywhere, or reports must move from a .unv to a .unx universe. By hand that is weeks of clicking. Through the API it is a script the agent writes and runs.

Steps

1. Inventory. The agent lists every document in scope with its data providers and the universe each one uses.
2. Plan the mapping. For a source change, the agent proposes how old objects map to new ones. The Change Source APIs accept a mapping, and the agent asks you about any object it cannot match.
3. Copy first. It copies each document into the sandbox and makes changes only on the copies.
4. Baseline. Before the change, it refreshes the original and exports the key blocks to CSV.
5. Change and compare. It applies the change to the copy, refreshes, exports again and compares with the baseline.
6. Report. You get a table: document, status (identical / changed within tolerance / different / failed) and a link.
7. Promote. A person reviews the differences and replaces the originals.