Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
uladzislau_pralat
Contributor
Comparative Analysis is a common requirement, for example, Y2Y. The challenge is to model CDS View in such a way that make data selection flexible and easy for both reporting period and reference period.

Below are two examples of comparative analysis in Query Browser. One for QTD and another one for YTD. QTD 2019 and FTD 2019 are compared respectively with QTD 2018 and FTD 2018. What user needs to enter is just rolling period name (Date Function) and CDS view calculates both Seat Occ Rate for selected period and Seats Occ Rate Ref for reference period (selected period offset by either 365 or 366 days).











The same examples of QTD and YTD comparative analysis in Smart Business:













CDS views are modeled in such a way that:

  • There are two sets of measures one for reporting period and another one for reference period;

  • Reporting period is selected by Date Function (rolling period) which is converted to date range selection by C_SglGregorianCalDateFunction

  • Date Function parameter value help is assigned for ease of use;

  • ZX_CalendarDate View Extension and ZI_CalendarDate Basic View are used to offset reporting period date range by either 365 or 366 days to get reference period date range


See following blogs for more information:

Date Function for dynamic date filtering in Fiori apps

How to add value help for parameter in ABAP CDS Analytical Query

 

Following CDS views need to be created for the demo:

 

ZI_DateFunction Date Function Value Help View
@AbapCatalog.sqlViewName: 'ZIDATEFUNC'
@EndUserText.label: 'Date Function'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@ClientHandling.algorithm: #SESSION_VARIABLE
@VDM.viewType: #BASIC
define view ZI_DateFunction as select from C_GregorianCalDateFunction( P_Language: $session.system_language )
{
DateFunction
}

Note: I_CalendarDate view is extended with CalendarYearMinus1 field. This field will be used late on for leap year offset

 

ZX_CalendarDate Calendar Date View Extension
@AbapCatalog.sqlViewAppendName: 'ZXCALDATE'
@EndUserText.label: 'Date'
extend view I_CalendarDate with ZX_CalendarDate
association [0..1] to I_CalendarYear as _CalendarYearMinus1 on $projection.CalendarYearMinus1 = _CalendarYearMinus1.CalendarYear
{
@ObjectModel.foreignKey.association: '_CalendarYearMinus1'
case when calendaryear = '0000'
then calendaryear
else cast(cast(cast(calendaryear as abap.int2) - 1 as abap.char( 12 ) ) as abap.numc( 4 ) ) end as CalendarYearMinus1,
_CalendarYearMinus1
}

 

ZI_CalendarDate Calendar Date Basic View
@AbapCatalog.sqlViewName: 'ZICALENDARDATE'
@EndUserText.label: 'Date'
define view ZI_CalendarDate as select from I_CalendarDate
{
key CalendarDate,
case when I_CalendarDate.CalendarMonth = '01' and _CalendarYearMinus1.IsLeapYear = ' '
then dats_add_days( CalendarDate, -365 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '01' and _CalendarYearMinus1.IsLeapYear = 'X'
then dats_add_days( CalendarDate, -366 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '02' and _CalendarYear.IsLeapYear = ' ' and _CalendarYearMinus1.IsLeapYear = ' '
then dats_add_days( CalendarDate, -365 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '02' and _CalendarYear.IsLeapYear = ' ' and _CalendarYearMinus1.IsLeapYear = 'X'
then dats_add_days( CalendarDate, -366 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '02' and _CalendarYear.IsLeapYear = 'X' and I_CalendarDate.CalendarDay = '29'
then dats_add_days( CalendarDate, -366 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '02' and _CalendarYear.IsLeapYear = 'X'
then dats_add_days( CalendarDate, -365 , 'FAIL' )
when I_CalendarDate.CalendarMonth between '03' and '12' and _CalendarYear.IsLeapYear = ' '
then dats_add_days( CalendarDate, -365 , 'FAIL' )
when I_CalendarDate.CalendarMonth between '03' and '12' and _CalendarYear.IsLeapYear = 'X'
then dats_add_days( CalendarDate, -366 , 'FAIL' ) end as CalendarDateMinus1Year,
case when I_CalendarDate.CalendarMonth = '01'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '02'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '29' and _CalendarYear.IsLeapYear = ' '
then dats_add_days( CalendarDate, -29 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '30' and _CalendarYear.IsLeapYear = ' '
then dats_add_days( CalendarDate, -30 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '31' and _CalendarYear.IsLeapYear = ' '
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '03' and _CalendarYear.IsLeapYear = ' '
then dats_add_days( CalendarDate, -28 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '30' and _CalendarYear.IsLeapYear = 'X'
then dats_add_days( CalendarDate, -30 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '03' and I_CalendarDate.CalendarDay = '31' and _CalendarYear.IsLeapYear = 'X'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '03' and _CalendarYear.IsLeapYear = 'X'
then dats_add_days( CalendarDate, -29 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '04'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '05' and I_CalendarDate.CalendarDay = '31'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '05'
then dats_add_days( CalendarDate, -30 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '06'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '07' and I_CalendarDate.CalendarDay = '31'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '07'
then dats_add_days( CalendarDate, -30 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '08'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '09'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '10' and I_CalendarDate.CalendarDay = '31'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '10'
then dats_add_days( CalendarDate, -30 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '11'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '12' and I_CalendarDate.CalendarDay = '31'
then dats_add_days( CalendarDate, -31 , 'FAIL' )
when I_CalendarDate.CalendarMonth = '12'
then dats_add_days( CalendarDate, -30 , 'FAIL' )
end as CalendarDateMinus1Month,
CalendarYear,
CalendarQuarter,
CalendarMonth,
CalendarWeek,
CalendarDay,
YearMonth,
YearQuarter,
YearWeek,
WeekDay
}

Note: CalendarDateMinus1Year - whenever offset cross February 29 of leap year then it is required to subtract 366 days instead of 365

Note: CalendarDateMinus1Month - mimics MONTH_PLUS_DETERMINE function module logic in CDS terms. Will be used in second part of the blog

 

ZSAPBC_Carr Carrier Basic View
@AbapCatalog.sqlViewName: 'ZCARR'
@AccessControl.authorizationCheck:#NOT_REQUIRED
@VDM.viewType: #BASIC
@EndUserText.label: 'Airline'
define view ZSAPBC_Carr as select from scarr
{
carrid as CarrID,
url as Url,
currcode as CurrCode,
cast(case
when carrid = 'LH' or carrid = 'AB' then 'Germany'
when carrid = 'AA' or carrid = 'CO' or carrid = 'DL' or
carrid = 'NW' or carrid = 'WA' then 'US'
when carrid = 'AC' then 'Canada'
when carrid = 'AF' then 'France'
when carrid = 'AZ' then 'Italy'
when carrid = 'BA' then 'UK'
when carrid = 'FJ' then 'Fiji'
when carrid = 'NG' then 'Austria'
when carrid = 'JL' then 'Japan'
when carrid = 'QF' then 'Australia'
when carrid = 'SA' then 'South Africa'
when carrid = 'SQ' then 'Singapure'
when carrid = 'SR' then 'Swirzerland'
when carrid = 'UA' then 'US'
else 'Other'
end as abap.char( 13 )) as Region
}

 

ZSAPBC_Region Region Basic View
@AbapCatalog.sqlViewName: 'ZREG'
@AccessControl.authorizationCheck:#NOT_REQUIRED
@EndUserText.label: 'Region'
@VDM.viewType: #BASIC
define view ZSAPBC_Region as select distinct from ZSAPBC_Carr {
key Region,
case
when Region = 'Germany' or Region = 'France' or Region = 'Italy' or
Region = 'UK' or Region = 'Austria' or Region = 'Swirzerland' then 'Europe'
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 MainRegion
}
where Region <> 'Australia'

union

select distinct from scarr {

key 'Europe' as Region,
'World' as MainRegion
}

union

select distinct from scarr {

key 'North America' as Region,
'World' as MainRegion
}

union

select distinct from scarr {

key 'Asia' as Region,
'World' as MainRegion
}

union

select distinct from scarr {

key 'Australia' as Region,
'World' as MainRegion
}

union

select distinct from scarr {

key 'Africa' as Region,
'World' as MainRegion
}

union

select distinct from scarr {

key 'World' as Region,
'' as MainRegion
}

 

ZSAPBC_CarrText Carrier Text View
@AbapCatalog.sqlViewName: 'ZCARRTEXT'
@Analytics: {dataCategory: #TEXT, dataExtraction.enabled: true}
@AccessControl.authorizationCheck: #NOT_ALLOWED
@EndUserText.label: 'Carrier'
define view ZSAPBC_CarrText as select from scarr {
key carrid as CarrId,
@Semantics.text: true
carrname as CarrierName
}

 

ZSAPBC_CarrDimension Carrier Dimension View
@AbapCatalog.sqlViewName: 'ZCARRDIM'
@Analytics: {dataCategory: #DIMENSION, dataExtraction.enabled: true}
@ObjectModel.representativeKey: 'Carrid'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Carrier'
define view ZSAPBC_CarrDimension as select from ZSAPBC_Carr
association [0..1] to ZSAPBC_CarrText as _Text on $projection.CarrID = _Text.CarrId
{
@ObjectModel.text.association: '_Text'
key CarrID,
Url,
CurrCode,
@EndUserText.label: 'Region'
Region,
_Text
}

 

ZSAPBC_RegionHierarchy Region Hierarchy View
@AbapCatalog.sqlViewName: 'ZREGIONHIER'
@Analytics: { dataCategory: #HIERARCHY, dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'Region'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Region'
@Hierarchy.parentChild.name: 'REGION_GEO'
@Hierarchy.parentChild.label: 'Region Geography'
@Hierarchy.parentChild:
{ recurse: { parent: 'ParentNode', child: 'HierarchyNode' } }
define view ZSAPBC_RegionHierarchy as select distinct from ZSAPBC_Region
association[0..1] to ZSAPBC_RegionDimension as _Dimension on $projection.HierarchyNode = _Dimension.Region
{
@ObjectModel.foreignKey.association: '_Dimension'
key Region as HierarchyNode,
MainRegion as ParentNode,
_Dimension
}

 

ZSAPBC_RegionDimension Region Dimension View
@AbapCatalog.sqlViewName: 'ZREGIONDIM'
@Analytics: { dataCategory: #DIMENSION, dataExtraction.enabled: true }
@ObjectModel.representativeKey: 'Region'
@AccessControl.authorizationCheck: #NOT_REQUIRED
@EndUserText.label: 'Region'
define view ZSAPBC_RegionDimension as select from ZSAPBC_Region
association[0..*] to ZSAPBC_RegionHierarchy as _Hierarchy on $projection.Region = _Hierarchy.HierarchyNode
{
@ObjectModel.Hierarchy.association: '_Hierarchy'
key Region,
@EndUserText.label: 'Main Region'
MainRegion,
_Hierarchy
}

 

ZSAPBC_FlightFact Flight Fact Table View
@AbapCatalog.sqlViewName: 'ZSFLIGHTFACT'
@AbapCatalog.compiler.compareFilter: true
@Analytics.dataCategory: #FACT
@AccessControl.authorizationCheck:#NOT_REQUIRED
@EndUserText.label: 'Flight'
define view ZSAPBC_FlightFact with parameters
@Consumption.defaultValue: 'CAD'
P_DisplayCurrency : s_currcode
as select from sflight inner join ZSAPBC_Carr as scarr
on sflight.carrid = scarr.CarrID {
key sflight.carrid as CarrId,
key sflight.connid as ConnId,
key sflight.fldate as FlightDate,
scarr.Region as Region,
$parameters.P_DisplayCurrency as Currency,
currency_conversion(
amount => sflight.paymentsum,
source_currency => sflight.currency,
target_currency => $parameters.P_DisplayCurrency,
exchange_rate_date => sflight.fldate,
exchange_rate_type => 'M',
error_handling => 'SET_TO_NULL' // otherwise data inconsistencies cause a dump
) as Payment,
cast('EA' as abap.unit(3) ) as Unit,
seatsmax + seatsmax_b + seatsmax_f as SeatsMax,
seatsocc + seatsocc_b + seatsocc_f as SeatsOcc
}

 

ZSAPBC_FlightCube Flight Cube View
@AbapCatalog.sqlViewName: 'ZSFLIGHTCUBE'
@AbapCatalog.compiler.compareFilter: true
@Analytics: { dataCategory: #CUBE, dataExtraction.enabled: true }
@AccessControl.authorizationCheck:#CHECK
@EndUserText.label: 'Flight Cube'

define view ZSAPBC_FlightCube with parameters
P_DisplayCurrency : s_currcode,
P_StartDate: dats,
P_EndDate: dats
as select from ZSAPBC_FlightFact( P_DisplayCurrency: $parameters.P_DisplayCurrency ) as flight inner join I_CalendarDate as Calendar on flight.FlightDate = Calendar.CalendarDate
association [1..1] to ZSAPBC_CarrDimension as _Carr on $projection.CarrId = _Carr.CarrID
association [1..1] to ZSAPBC_RegionDimension as _Region on $projection.Region = _Region.Region
{
key 'CURR' as PeriodType,
@ObjectModel.foreignKey.association: '_Carr'
key flight.CarrId,
key flight.ConnId,
@EndUserText.label: 'Date'
key Calendar.CalendarDate as FlightDate,
@EndUserText.label: 'Region'
@ObjectModel.foreignKey.association: '_Region'
flight.Region,
@Semantics.calendar.yearWeek: true
@EndUserText.label: 'Week'
Calendar.YearWeek as FlightWeek,
@Semantics.calendar.yearMonth: true
@EndUserText.label: 'Month'
Calendar.YearMonth as FlightMonth,
@Semantics.calendar.year: true
@EndUserText.label: 'Year'
Calendar.CalendarYear as FlightYear,
@EndUserText.label: 'Currency'
Currency,
@Semantics.unitOfMeasure: true
@EndUserText.label: 'UOM'
flight.Unit,
@Semantics.unitOfMeasure: true
@EndUserText.label: '%'
cast( 'Z%' as abap.unit( 3 ) ) as PercentUnitOfMeasure,
@Semantics.amount.currencyCode: 'currency'
@EndUserText.label: 'Payment'
@DefaultAggregation: #SUM
flight.Payment,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max'
SeatsMax,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ'
SeatsOcc,
@Semantics.amount.currencyCode: 'currency'
@EndUserText.label: 'Payment Ref'
@DefaultAggregation: #SUM
cast(0 as s_sum) as payment_ref,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max Ref'
cast(0 as s_seatsmax) as SeatsMaxRef,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ Ref'
cast(0 as s_seatsocc) as SeatsOccRef,
@Semantics.quantity.unitOfMeasure: 'PercentUnitOfMeasure'
@DefaultAggregation: #MAX
@EndUserText.label: '100%'
100 as HundredPercent,
_Carr,
_Region
}
where flight.FlightDate between $parameters.P_StartDate and $parameters.P_EndDate


union all

select from ZSAPBC_FlightFact( P_DisplayCurrency: $parameters.P_DisplayCurrency ) as flight
inner join ZI_CalendarDate as Calendar on flight.FlightDate = Calendar.CalendarDateMinus1Year
association [1..1] to ZSAPBC_CarrDimension as _Carr on $projection.CarrId = _Carr.CarrID
association [1..1] to ZSAPBC_RegionDimension as _Region on $projection.Region = _Region.Region
{
key 'PREV' as PeriodType,
@ObjectModel.foreignKey.association: '_Carr'
key flight.CarrId,
key flight.ConnId,
@EndUserText.label: 'Date'
key Calendar.CalendarDate as FlightDate,
@EndUserText.label: 'Region'
@ObjectModel.foreignKey.association: '_Region'
flight.Region,
@Semantics.calendar.yearWeek: true
@EndUserText.label: 'Week'
Calendar.YearWeek as FlightWeek,
@Semantics.calendar.yearMonth: true
@EndUserText.label: 'Month'
Calendar.YearMonth as FlightMonth,
@Semantics.calendar.year: true
@EndUserText.label: 'Year'
Calendar.CalendarYear as FlightYear,
@EndUserText.label: 'Currency'
Currency,
@Semantics.unitOfMeasure: true
@EndUserText.label: 'UOM'
flight.Unit,
@Semantics.unitOfMeasure: true
@EndUserText.label: '%'
cast( 'Z%' as abap.unit( 3 ) ) as PercentUnitOfMeasure,
@Semantics.amount.currencyCode: 'currency'
@EndUserText.label: 'Payment'
@DefaultAggregation: #SUM
cast(0 as s_sum) as payment,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max'
cast(0 as s_seatsmax) as SeatsMax,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ'
cast(0 as s_seatsocc) as SeatsOcc,
@Semantics.amount.currencyCode: 'currency'
@EndUserText.label: 'Payment Ref'
@DefaultAggregation: #SUM
flight.Payment as payment_ref,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Max Ref'
flight.SeatsMax as SeatsMaxRef,
@Semantics.quantity.unitOfMeasure: 'unit'
@DefaultAggregation: #SUM
@EndUserText.label: 'Seats Occ Ref'
flight.SeatsOcc as SeatsOccRef,
@Semantics.quantity.unitOfMeasure: 'PercentUnitOfMeasure'
@DefaultAggregation: #MAX
@EndUserText.label: '100%'
100 as HundredPercent,
_Carr,
_Region
}
where Calendar.CalendarDate between $parameters.P_StartDate and $parameters.P_EndDate

Note:

  • Reporting period data is union with reference period data;

  • Extended I_CalendarDate CDS View is used to offset reporting period date range by 365 or 366 days and such a way get reference period date range


 

ZSAPBC_FlightQuery Flight Query View
@AbapCatalog.sqlViewName: 'ZFLIGHTQUERY'
@AbapCatalog.compiler.compareFilter: true
@AccessControl.authorizationCheck:#CHECK
@VDM.viewType: #CONSUMPTION
@Analytics.query: true
@OData.publish: true
@EndUserText.label: 'Flight Query'
define view ZSAPBC_FlightQuery with parameters
@EndUserText.label: 'Display Currency'
@Consumption.defaultValue: 'CAD'
P_DisplayCurrency : s_currcode,
@EndUserText.label: 'Date Function'
@Consumption: { valueHelp: '_DateFunction.DateFunction', defaultValue: 'YEARTODATE' }
P_DateFunction : datefunctionid,
@Consumption.derivation: { lookupEntity: 'C_SglGregorianCalDateFunction',
resultElement: 'DateFunctionStartDate', binding: [ {
targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' },
{ targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE' } ]
}
@Consumption.hidden: true
P_StartDate: /srmsmc/puc_spend_valid_from ,
@Consumption.derivation: { lookupEntity: 'C_SglGregorianCalDateFunction',
resultElement: 'DateFunctionEndDate', binding: [ {
targetParameter: 'P_DateFunction', type: #PARAMETER, value : 'P_DateFunction' },
{ targetParameter : 'P_Language' , type : #SYSTEM_FIELD, value : '#SYSTEM_LANGUAGE' } ]
}
@Consumption.hidden: true
P_EndDate: /srmsmc/puc_spend_valid_to
as select from ZSAPBC_FlightCube( P_DisplayCurrency: $parameters.P_DisplayCurrency,
P_StartDate: $parameters.P_StartDate,
P_EndDate: $parameters.P_EndDate )
association[1] to ZI_DateFunction as _DateFunction on _DateFunction.DateFunction <> ''
{
Region,
@AnalyticsDetails.query.axis: #FREE
ConnId,
@AnalyticsDetails.query.axis: #FREE
@Consumption.filter: {selectionType: #SINGLE, multipleSelections: true, mandatory: false }
CarrId,
@AnalyticsDetails.query.axis: #FREE
FlightDate,
@AnalyticsDetails.query.axis: #FREE
FlightWeek,
@AnalyticsDetails.query.axis: #FREE
FlightMonth,
@AnalyticsDetails.query.axis: #FREE
FlightYear,
@AnalyticsDetails.query.axis: #FREE
Currency,
@AnalyticsDetails.query.axis: #FREE
Unit,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.hidden: true
Payment,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.hidden: true
SeatsMax,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.hidden: true
SeatsOcc,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.hidden: true
payment_ref,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.hidden: true
SeatsMaxRef,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.hidden: true
SeatsOccRef,
@AnalyticsDetails.query.axis: #COLUMNS
@AnalyticsDetails.query.decimals: 1
@EndUserText.label: 'Seats Occ Rate'
@AnalyticsDetails.query.formula: 'NDIV0( NODIM( SeatsOcc ) / NODIM( SeatsMax ) ) * HundredPercent'
0 as SeatsOccRate,
@AnalyticsDetails.query.axis: #COLUMNS
@AnalyticsDetails.query.decimals: 1
@EndUserText.label: 'Seats Occ Rate Ref'
@AnalyticsDetails.query.formula: 'NDIV0( NODIM( SeatsOccRef ) / NODIM( SeatsMaxRef ) ) * HundredPercent'
0 as SeatsOccRateRef,
_DateFunction
}

Note:

  • ZI_DateFunction value help is assigned P_DateFunction parameter for ease of user

  • Reporting period is selected by name (P_DateFunction parameter) which is converted to date range selection by C_SglGregorianCalDateFunction view


 



 
1 Comment