Review: Web Intelligence core report design1. A Web Intelligence document consists of three components. What are they?
A Web Intelligence document contains:
○ A data provider or query
○ A report
○ Blocks of data
2. A query filter contains three components. What are they?
A query filter contains:
○ An object
○ An operator
○ An operand
3. What object type does not have a list of values?
4. When you list values in a formula, what must you use to separate the values?
5. What are the two benefits provided when you apply a break to a table?
You can use the break to:
○ Group data
○ Display sums
6. What are the differences among a query filter, a report filter, and a block filter?
A query filter limits the data returned from the database.
A block filter allows selected data to display in a single block and hides the other data.
A report filter allows selected data to display in a page and hides the other data. The report
filter applies to all data blocks on the page.
7. What is the difference between a section and a break?
A section impacts the whole report page; a break impacts the table or crosstab to which you
apply the break.
A section allows you to display multiple blocks of data on the same page. A break does not
allow you to display multiple blocks of data on the same page.
8. What is the difference between an ascending or descending sort and a custom sort?
Ascending and descending sorts impact the column of the block to which you apply the
Custom sorts impact the data in an object. When you apply a custom sort to an object, the
custom sort impacts all the blocks that use the object.
9. Why purge the data in your document?
You can use the purge function:
○ When you want a document to refresh when the report user opens it, but you do not
want the report user to see the existing data, for reasons of security, for example.
○ When you want to send a data-heavy document to a report user. You can make the
document lighter when you purge the data. Consequently, the document arrives more
10.What is the difference between a dimension and a detail?
A dimension displays values that report users want to see in their documents. You can use
a dimension for a scope of analysis. You can merge a dimension with a dimension from
another data source to synchronize two different data sources.
A detail provides additional, complementary information to the information contained in
a dimension and must be associated with a dimension. A detail cannot serve in a scope of
analysis or merge with an object from another data source to synchronize two different data
11.You have two queries on two different data sources. Query 1 returns 10 rows for Region.
Query 2 returns 12 rows for Region_Area . You synchronize the two data sources by merging
the Region and Region_Area objects.
How many rows appear after the synchronization?
When the Region and Region_Area objects contain data in the same format and case, after
○ 12 rows appear when the 10 values for Region match some of the 12 values for
○ 22 rows appear when 10 values for Region do not match the 12 values for Region_Area.
When the Region and Region_Area objects contain data in different format or case, after
synchronization 22 rows appear. Web Intelligence handles similar values with different
format or case as different values.
12.You have a report with the objects Region, Year, and Sales revenue in Query 1, and you
have another report on a different data source with Region, Area, and Sales total in Query
2. Which objects do you choose to merge to synchronize the information in one table?
You choose the Region objects because they are the only common dimensions. You cannot
merge a measure so you cannot use the Sales revenue and Sales total objects.
13.When you synchronize two queries from different data sources, what rules must you respect?
Respect the following rules:
○ Object names can differ.
○ The data type for the merged objects must be the same.
○ Data must have the same case because the data provider is case-sensitive.
○ You can only synchronize on dimensions.
○ You can only display measures with synchronized dimensions. When you use them for
another purpose, duplicate values appear.
14.Can you synchronize the data between a Web Intelligence document and an Excel
In Web Intelligence XI 3.x, when a dimension appropriate for merging exists in the two
documents, yes, you can.
Quiz: Advanced Query Techniques
1. What are the three types of query techniques used in this lesson?
Answer: Combining, Subquery, and Duplicating a query to build another.
2. If you were to use the UNION operator to combine queries, what would be the result?
Answer: The result returns all records that satisfy one of the two conditions. This is equivalent
to using an OR between query filters.
3. If you were to use the INTERSECTION operator to combine queries, what would be the
Answer: Only records that matched ALL criteria would be returned. This is the equivalent
of doing an AND between query filters.
4. If you were to use the MINUS operator to combine queries, what would be the result?
Answer: All records that match the first query condition and not the second condition.
Where you place the queries determines which is the first condition and which is the second
5. List two reasons why you might want to use combined queries.
○ To make the construction of the query easier.
○ When it is not possible to apply the required query filters using Boolean logic.
6. Can you do a subquery and return exactly the same results as a combined query?
Answer: Depending on the query, yes.
7. Which subquery operand type means that Web Intelligence will look for only one instance
of a value from the subquery that meets the query filter?
○ a. ALL
○ b. ANY
Answer: ANY. The Any option means that Web Intelligence only looks for one instance of
a value from the subquery that meets the query filter. If it finds one, it then ceases the
comparison with the remainder of the subquery output.
8. Why would you choose to do a combined query rather than a subquery?
Answer: Combined queries tend to be faster at returning the data.
9. When you change a data source, what can you choose to do to the objects in the original
Answer: You can map them to objects in the new data source or delete them from the query.
Quiz: Calculation Contexts1. What is an input context?
The input context is the list of dimensions that are included in the calculation itself.
2. What is an output context?
Answer: The output context is the dimension level in which the calculation is to be displayed
in the report. For example, the output context can be the dimension displayed at the row
level; the dimension displayed at the break level, and so on.
3. Which of the following is an extended syntax context operator?
NoFilter() is a function. Block is an extended syntax keyword.
4. Which of the following is an extended syntax keyword?
Quiz: Character and Date String Functions
1. Name at least three character string functions used in this lesson.
Answer: Choice of Right(), Substr(), Pos(), Replace(), Length()
2. To find the occurrence of a comma in an object’s value, which function would you use?
3. What is the syntax for the Replace() function?
Answer: Replace([string] ; “old string” ; “new string”)
4. When would you choose to use the Length() function?
Answer: To identify the length of a field.
5. Give an example of string concatenation.
Answer: Lastname+ “, “ + Firstname
6. If you want to create a formula to return the number of years between two dates, what
would the syntax be?
Answer: =DaysBetween(first date;second date)/365.25
7. Can you do calculations on a date?
136 Web Intelligence Advanced Report Design—Learner’s Guide
Quiz: Using If Logic
1. What is another term for the logic used by the If() function?
Answer: If...Then...Else logic
2. Which of the following are examples of why you might use the If() function to group values
in a variable:
• To display categories of values
• To show values aggregated at higher levels of detail
Answer: 1 and 3
3. True/False. You can use the If() function to define a variable so that a different calculation
is used depending on the value retrieved by the object specified in the variable’s formula.
Quiz: Additional Reporting Techniques1. When you apply a standard calculation such as a Sum or an Average on a column in a table
with a break, where is the calculation inserted?
• In a data cell
• In the break footer
• In the section
• In the break header
Answer: In the break footer.
2. How do you format a break?
Select the column with the break and go to the Breaks folder in the Properties tab.
3. Which function can you use to determine whether a character string matches a particular
4. What is the syntax for creating a running sum on Margin with a reset on Year?
Answer: =RunningSum([Margin] ; [Year]))
5. Which function would you use if you want to show the SQL for a particular query?
6. Which function can you use to override a report filter or a ranking?
7. How can you display a report filter?
Answer: With the Drill Filters cell or the DrillFilter() function.
Quiz: Creating Hyperlinks1. How do you create a hyperlink in the Web Intelligence Interactive panel?
Answer: With the Create Hyperlink dialog box.
2. When you use the Create Hyperlink dialog box to create a hyperlink to another document,
what options can you associate with the hyperlink?
The options include:
○ Refreshing the target document when it opens.
○ Linking to a specific document instance.
○ Linking to a specific area in the document.
○ Passing a prompt value to the document.
○ Customizing the look and behavior of the hyperlink, for example, to have the hyperlink
display the document in a new window or to display a tooltip that describes the document
when you move the mouse over the hyperlink.
3. How do you create a hyperlink in the Web Intelligence Rich Client main window or the
Java Report Panel?
To create a hyperlink to:
• A URL or website, you use the Hyperlinks dialog box.
• Another document, you use the OpenDocument command.
Review: Dimensions and details1. A group of reports include Store name, Product line, SKU description, Quantity sold, and
Color. The first four objects retrieve important data for the reports. The Color object appears
in only one report. What object type did the universe designer assign to the objects and
Answer: The universe designer defines the Store name, Product line, SKU description and
Quantity sold as dimensions and defines the Color as a detail.
2. The Purchasing director wants to analyze the Product Line information and to drill down
to Category, SKU Description, and Promotion. He does not want to drill to SKU number or
Answer: The universe designer defines SKU number and Color as details to keep the default
hierarchy easy to read and defines the other objects as dimensions.
3. A Merchandising manager has access to more than one data source – an Excel Spreadsheet
and a number of universes. She wants to bring the information from these different sources
into one table and to merge for data synchronization on the Store name and Product line
Answer: The universe designer defines the Store name and Product line objects as dimensions
so the merchandising manager can merge them.