Database Reference
In-Depth Information
Step 4: Build the Report Query
We will be using the
Sales (query)
namespace inside the
Sales and Marketing (query)
, as well
as the newly added
Orders
namespace, to create the data items for the report.
1.
In the
Explorer Bar
, mouse over the
Query Explorer
tab and select the
Query1
query.
2.
In the
Properties
pane, change the name of
Query1
to
MainQuery
.
3.
From the
Source
tab of the
Insertable Objects
pane, drag the
Orders
query item from the
Orders
query subject (they are under the
Orders
namespace) into the
Data Items
pane.
4.
From the
Source
tab of the
Insertable Objects
pane under the
go_data_warehouse
namespace, drag the following query items into the
Data Items
pane:
•
Employee name
from the
Employee by manager
query subject
•
Retailer
,
Contact first name
,
Contact last name
,
Contact phone number
,
Contact extension
, and
Contact e-mail
from the
Retailers
query subject
•
Date
from the
Time
query subject
•
Product number
from the
Codes
folder in the
Products
query subject
•
Product
from the
Products
query subject
•
Quantity
,
Unit Sale Price
,
Revenue
, and
Gross Profit
from the
Sales fact
query
subject
5.
From the
To o l b o x
tab of the
Insertable Objects
pane, drag a
Query Calculation
into
the
Data Items
pane.
The Create Calculation dialog box is displayed.
6.
Type
Full contact number
for the name of the calculation.
7.
Click
OK
.
The Data Item Expression window opens. We want to create a calculation that will con-
catenate the phone number and extension. However, if the extension is blank, we want to
eliminate the use of the extension field.
8.
Create the following expression in the
Expression Definition
window:
IF ( [Contact extension] IS NULL) THEN
( [Contact phone number] )
ELSE
( [Contact phone number] + ' ext. ' + [Contact extension])
9.
Va l i d a t e t h e e x p r e s s i o n a n d c l i c k
OK
.
