
@AbapCatalog.sqlViewName: 'ZREGION3'
@AccessControl.authorizationCheck:#NOT_REQUIRED
@EndUserText.label: 'Flight - Region '
define view zsapbc_region3 as select distinct from zsapbc_carr {
key region,
case
when region = 'US' or region = 'Canada' then 'North America'
when region = 'South Africa' then 'Africa'
when region = 'Fiji' or region = 'Japan' or region = 'Singapure' then 'Asia'
else 'Other'
end as main_region,
cast( '00010101' as abap.dats ) as datefrom,
cast( '99991231' as abap.dats ) as dateto
}
where region <> 'Australia' and region <> 'Germany' and region <> 'France' and region <> 'Italy' and
region <> 'UK' and region <> 'Austria' and region <> 'Swirzerland'
union
select distinct from zsapbc_carr {
key region,
'Europe' as main_region,
cast( '00010101' as abap.dats ) as datefrom,
cast( '19931031' as abap.dats ) as dateto
}
where region = 'Germany' or region = 'France' or region = 'Italy' or
region = 'UK' or region = 'Austria' or region = 'Swirzerland'
union
select distinct from zsapbc_carr {
key region,
'EU' as main_region,
cast( '19931031' as abap.dats ) as datefrom,
cast( '99991231' as abap.dats ) as dateto
}
where region = 'Germany' or region = 'France' or region = 'Italy' or
region = 'UK' or region = 'Austria' or region = 'Swirzerland'
union
select distinct from scarr {
key 'Europe' as region,
'World' as main_region,
cast( '00010101' as abap.dats ) as datefrom,
cast( '19931031' as abap.dats ) as dateto
}
union
select distinct from scarr {
key 'EU' as region,
'World' as main_region,
cast( '19931101' as abap.dats ) as datefrom,
cast( '99991231' as abap.dats ) as dateto
}
union
select distinct from scarr {
key 'North America' as region,
'World' as main_region,
cast( '00010101' as abap.dats ) as datefrom,
cast( '99991231' as abap.dats ) as dateto
}
union
select distinct from scarr {
key 'Asia' as region,
'World' as main_region,
cast( '00010101' as abap.dats ) as datefrom,
cast( '99991231' as abap.dats ) as dateto
}
union
select distinct from scarr {
key 'Australia' as region,
'World' as main_region,
cast( '00010101' as abap.dats ) as datefrom,
cast( '99991231' as abap.dats ) as dateto
}
union
select distinct from scarr {
key 'Africa' as region,
'World' as main_region,
cast( '00010101' as abap.dats ) as datefrom,
cast( '99991231' as abap.dats ) as dateto
}
union
select distinct from scarr {
key 'World' as region,
'' as main_region,
cast( '00010101' as abap.dats ) as datefrom,
cast( '99991231' as abap.dats ) as dateto
}
@AbapCatalog.sqlViewName: 'ZREGION_TEXT3'
@analytics: {dataCategory: #TEXT, dataExtraction.enabled: true}
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Flight - Region Text'
define view zsapbc_region_text3 as select from zsapbc_region3 {
key region,
@Semantics: {language: true }
'E' as lang,
@Semantics.text: true
case
when region = 'EU' then 'European Union'
else region
end as region_text
}
union
select from zsapbc_region3 {
key region,
@Semantics: {language: true }
'D' as lang,
@Semantics.text: true
case
when region = 'EU' then 'Europäische Union'
when region = 'World' then 'Welt'
when region = 'US' then 'Vereinigte Staaten'
when region = 'Canada' then 'Kanada'
when region = 'Fiji' then 'Fidschi'
when region = 'South Africa' then 'Südafrika'
when region = 'Singapure' then 'Singapur'
when region = 'France' then 'Frankreich'
when region = 'Italy' then 'Italien'
when region = 'UK' then 'Vereinigtes Königreich'
when region = 'Germany' then 'Deutschland'
when region = 'Austria' then 'Österreich'
when region = 'Swirzerland' then 'Schweiz'
when region = 'Europe' then 'Europa'
when region = 'North America' then 'Nordamerika'
when region = 'Asia' then 'Asien'
when region = 'Australia' then 'Australien'
when region = 'Africa' then 'Afrika'
else region end as region_text
}
@AbapCatalog.sqlViewName: 'ZREGION_HIER3'
@analytics: { dataCategory: #HIERARCHY, dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'REGION'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Flight - Region Hierarchy'
@Hierarchy.parentChild.name: 'REGION_GEO'
@Hierarchy.parentChild.label: 'Region
Geography'
@Hierarchy.parentChild: { recurse: { parent: 'ParentNode',
child: 'HierarchyNode' } }
define view zsapbc_region_hierarchy3 as select distinct from zsapbc_region3
association[0..*] to zsapbc_region_dimension3 as _region_dim on
$projection.HierarchyNode = _region_dim.region
{
@ObjectModel.foreignKey.association: '_region_dim'
key region as HierarchyNode,
@Semantics.businessDate.to: true
key dateto,
@Semantics.businessDate.from: true
datefrom,
main_region as ParentNode,
_region_dim
}
@AbapCatalog.sqlViewName: 'ZREGION_DIM3'
@analytics: { dataCategory: #DIMENSION, dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'region'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Flight – Region Dimension'
define view zsapbc_region_dimension3 as select from zsapbc_region3
association [1..1] to zsapbc_region_text3 as _region_text on
$projection.region = _region_text.region
association[0..*] to zsapbc_region_hierarchy3 as _region_hier on
$projection.region = _region_hier.HierarchyNode
{
@ObjectModel.text.association: '_region_text'
@ObjectModel.Hierarchy.association: '_region_hier'
key region,
@Semantics.businessDate.to: true
key dateto,
@Semantics.businessDate.from: true
datefrom,
@EndUserText.label: 'Main Region'
main_region,
_region_text,
_region_hier
}
@AbapCatalog.sqlViewName: 'ZSFLIGHT_CUBE3'
@AbapCatalog.compiler.compareFilter: true
@analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }
@AccessControl.authorizationCheck:#CHECK
@EndUserText.label: 'Flight Cube'
define view zsapbc_flight_cube3
with parameters p_display_currency : s_currcode,
p_year_1: rscalyear,
p_year_2: rscalyear
as select from zsapbc_flight_fact( p_display_currency:
$parameters.p_display_currency ) as flight
association [1..1] to zsapbc_carr_dimension as _carr on
$projection.carrid = flight.carrid
association [0..*] to zsapbc_region_dimension3 as _region on $projection.region = flight.region
{
@ObjectModel.foreignKey.association: '_carr'
key flight.carrid,
key flight.connid,
@EndUserText.label: 'Date'
key flight.fldate,
@EndUserText.label: 'Region'
@ObjectModel.foreignKey.association: '_region'
flight.region,
_carr,
_region,
@Semantics.calendar.yearMonth: true
@EndUserText.label: 'Month'
flight.flmonth,
@Semantics.calendar.year: true
@EndUserText.label: 'Year'
flight.flyear,
@Semantics.currencyCode: true
@EndUserText.label: 'Booking Currency'
flight.currency,
@Semantics.amount.currencyCode: 'Currency'
@DefaultAggregation: #SUM
@EndUserText.label: 'Booking (bc)'
flight.payment,
@Semantics.quantity.unitOfMeasure: 'Currency'
@DefaultAggregation: #SUM
@EndUserText.label: 'Booking (bc) Year 1'
case when flyear = :p_year_1 then payment
else 0
end as payment_year_1,
@Semantics.quantity.unitOfMeasure: 'Currency'
@DefaultAggregation: #SUM
@EndUserText.label: 'Booking (bc) Year 2'
case when flyear = :p_year_2 then payment
else 0
end as payment_year_2,
@EndUserText.label: 'Display Currency'
cast('CAD' as abap.cuky( 5 )) as disp_curr,
@Semantics.amount.currencyCode: 'disp_curr'
@EndUserText.label: 'Booking (dc)'
@DefaultAggregation: #SUM
flight.payment_disp_curr,
@Semantics.quantity.unitOfMeasure: 'disp_curr'
@DefaultAggregation: #SUM
@EndUserText.label: 'Booking (dc) Year 1'
case when flyear = :p_year_1 then payment_disp_curr
else 0
end as payment_disp_curr_year_1,
@Semantics.quantity.unitOfMeasure: 'disp_curr'
@DefaultAggregation: #SUM
@EndUserText.label: 'Booking (dc) Year 2'
case when flyear = :p_year_2 then payment_disp_curr
else 0
end as payment_disp_curr_year_2,
@Semantics.unitOfMeasure: true
@EndUserText.label: 'UOM'
flight.unit,
@Semantics.unitOfMeasure: true
@EndUserText.label: '%'
cast( '%' as abap.unit( 3 ) ) as unit_percent,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max Econ.'
flight.seatsmax,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max Bus.'
flight.seatsmax_b,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max 1st'
flight.seatsmax_f,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max Total'
seatsmax_total,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max Year 1'
case when flyear = :p_year_1 then seatsmax_total
else 0
end as seatsmax_total_year_1,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max Year 2'
case when flyear = :p_year_2 then seatsmax_total
else 0
end as seatsmax_total_year_2,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ Econ.'
flight.seatsocc,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ Bus.'
flight.seatsocc_b,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ 1st'
flight.seatsocc_f,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ Total'
seatsocc_total,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ Year 1'
case when flyear = :p_year_1 then seatsocc_total
else 0
end as seatsocc_total_year_1,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ Year 2'
case when flyear = :p_year_2 then seatsocc_total
else 0
end as seatsocc_total_year_2,
@Semantics.quantity.unitOfMeasure: 'unit_percent'
@DefaultAggregation: #MAX
@EndUserText.label: '100%'
100 as hundred
}
@AbapCatalog.sqlViewName: 'ZFLIGHT_QUERY3'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck:#CHECK
@VDM.viewType: #CONSUMPTION
@analytics.query: true
@EndUserText.label: 'Flight Query'
define view zsapbc_flight_query3 with parameters
@Consumption.hidden: false
@Environment.systemField: #SYSTEM_DATE
@EndUserText.label: 'Key Date'
p_keydate: abap.dats,
@EndUserText.label: 'Display Currency'
@Consumption.defaultValue: 'CAD'
p_display_currency : s_currcode,
@EndUserText.label: 'Year 1'
@Consumption.derivation: {lookupEntity: 'ZSAPBC_CURR_YEAR_ADD_N', resultElement: 'p_year' ,
binding: [{ targetParameter: 'p_offset',
type: #CONSTANT,
value: -1 }]}
p_year_1: rscalyear,
@EndUserText.label: 'Year 2'
@Consumption.derivation: {lookupEntity: 'ZSAPBC_CURR_YEAR_ADD_N',
resultElement: 'p_year',
binding: [{ targetParameter: 'p_offset',
type:#CONSTANT ,
value: 0 }] }
p_year_2: rscalyear
as select from zsapbc_flight_cube3(
p_display_currency: $parameters.p_display_currency,
p_year_1: $parameters.p_year_1,
p_year_2: $parameters.p_year_2 ) {
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.display: #TEXT
@AnalyticsDetails.query.displayHierarchy: #ON
@Consumption.filter: { selectionType: #HIERARCHY_NODE,
multipleSelections: true,
mandatory: false }
@AnalyticsDetails.query.hierarchyInitialLevel: 3
zsapbc_flight_cube3._region[datefrom >= $parameters.p_keydate and
dateto <= $parameters.p_keydate].region,
@AnalyticsDetails.query.axis: #COLUMNS
seatsocc_total_year_1,
@AnalyticsDetails.query.axis: #COLUMNS
seatsmax_total_year_1,
@AnalyticsDetails.query.axis: #COLUMNS
@AnalyticsDetails.query.decimals: 1
@EndUserText.label: 'Seats Occ (%) Year 1'
@AnalyticsDetails.query.formula: 'NDIV0( NODIM(
seatsocc_total_year_1 ) / NODIM(
seatsmax_total_year_1 ) ) * hundred '
1 as seats_occ_prc_year_1,
@AnalyticsDetails.query.axis: #COLUMNS
seatsocc_total_year_2,
@AnalyticsDetails.query.axis: #COLUMNS
seatsmax_total_year_2,
@AnalyticsDetails.query.axis: #COLUMNS
@AnalyticsDetails.query.decimals: 1
@EndUserText.label: 'Seats Occ (%) Year 2'
@AnalyticsDetails.query.formula: 'NDIV0( NODIM(
seatsocc_total_year_2 ) / NODIM(
seatsmax_total_year_2 ) ) * hundred '
1 as seats_occ_prc_year_2,
@AnalyticsDetails.query.axis: #COLUMNS
payment_year_1,
@AnalyticsDetails.query.axis: #COLUMNS
payment_year_2,
@AnalyticsDetails.query.axis: #COLUMNS
payment_disp_curr_year_1,
@AnalyticsDetails.query.axis: #COLUMNS
payment_disp_curr_year_2,
@AnalyticsDetails.query.axis: #FREE
connid,
@AnalyticsDetails.query.axis: #FREE
@Consumption.filter: {selectionType: #SINGLE,
multipleSelections: true,
mandatory: false }
carrid,
@AnalyticsDetails.query.axis: #FREE
fldate,
@AnalyticsDetails.query.axis: #FREE
flmonth,
@AnalyticsDetails.query.axis: #FREE
flyear,
@AnalyticsDetails.query.axis: #FREE
currency,
@AnalyticsDetails.query.axis: #FREE
disp_curr,
nalyticsDetails.query.axis: #FREE
unit
}
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
3 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |