Database Reference
In-Depth Information
Step 6: Build Main_Query
1.
From the
Insertable Objects
pane, drag
Legacy_Query
to the
Data Items
pane.
The attributes of year, month, country, and product name will be displayed only if
expected volume or forecast revenue values are a part of both queries, so these attributes
need to be displayed if results exist in either query.
2.
In the
Data Items
pane, double-click
Ye a r
. Change the
Expression Definition
to
coalesce([Legacy_Query].[Year],[New_Model_Query].[Year])
and click
Va l i d a t e
and
OK
.
3.
Double-click
Month
. Change the
Expression Definition
to
coalesce([Legacy_
Query].[Month],[New_Model_Query].[Month])
and click
Va l i d a t e
and
OK
.
4.
Double-click
Country
. Change the
Expression Definition
to
coalesce([Legacy_
Query].[Country],[New_Model_Query].[Country])
and click
Va l i d a t e
and
OK
.
5.
Double-click
Product
. Change the
Expression Definition
to
coalesce([Legacy_
Query].[Product],[New_Model_Query].[Product])
and click
Va l i d a t e
and
OK
.
6.
From the
Insertable Objects
pane, drag
Expected volume
and
Forecast revenue
from
New_Model_Query
to the
Data Items
pane.
7.
In the
Data Items
pane, click
Expected volume
. Change the
Name
and
Label
proper-
ties to
New expected volume
.
8.
Click
Forecast revenue
. Change the
Name
and
Label
properties to
New forecast
revenue
.
9.
Click
Ye a r
and Shift-click
Product
. Change the
Aggregate Function
property to
None
.
10.
Click
Expected Volume
and Shift-click
New forecast revenue
. Change the
Aggregate
Function
property to
To t a l
.
11.
From the
To o l b o x
tab in the
Insertable Objects
pane, drag a
Data Item
to the
Data
Items
pane.
12.
In the Data Item Expression dialog box, enter the following statement:
TRIM(
CASE
WHEN [Expected volume] = [New expected volume]
AND [Forecast revenue] = [New forecast revenue]
THEN '4. Volume and revenue match'
WHEN [Expected volume] IS NULL
OR [Forecast revenue] IS NULL
THEN '3. Details only exist in new model query'
WHEN [New expected volume] IS NULL
OR [New forecast revenue] IS NULL
