Hi there, this is my first post – please ignore if this is not relevant.
We have a business requirement to extract data to be used in SAP Analysis for Office by using CDS view
‘IFIFINSTMTCUDE’ with additional fields from a custom table function whereby the level text from FAGL_011QT needs to be displayed.
The custom table extract on it’s own display the data as expected, but in the custom CDS , the message ‘The entity zfi_financial_level requires parameter z_vonkt’ .
1. is it possible to do what I try to do ?
2. if it is not possible, is there anyone with an idea how to do it? I am stuck 🙂
3. I know there is still a bit of development to do, but for testing, I need to be able to activate the code
CDS copy and changed:
@AbapCatalog.sqlViewName: ‘Z_FIN_CUBE_VIEW’
@EndUserText.label: ‘Financial Statement Cube View’
@VDM.viewType: #CONSUMPTION
@analytics.query: true
@AccessControl.authorizationCheck: #PRIVILEGED_ONLY
@analytics.settings.maxProcessingEffort: #HIGH
@ClientHandling.algorithm: #SESSION_VARIABLE
@AbapCatalog.buffering.status: #NOT_ALLOWED
@Metadata.ignorePropagatedAnnotations: true
@ObjectModel.usageType.sizeCategory: #XXL
@ObjectModel.usageType.serviceQuality: #D
@ObjectModel.usageType.dataClass: #MIXED
@ObjectModel.modelingPattern: #ANALYTICAL_QUERY
@ObjectModel.supportedCapabilities: [#ANALYTICAL_QUERY]
define view Z_FinancialStatementCube_View
with parameters
@Consumption.hidden: true
@Environment.systemField: #SYSTEM_DATE
P_KeyDate : sydatum,
@Consumption.hidden: true
@Environment.systemField: #SYSTEM_LANGUAGE
P_Language : sylangu,
@Consumption.hidden: true
// @Environment.systemField: #APPLICATION_USER
@Environment.systemField: #USER
P_BusinessUser : syuname,
@Consumption.derivation: { lookupEntity: ‘I_Ledger’,
resultElement: ‘Ledger’,
binding:
[ { targetElement : ‘IsLeadingLedger’ ,
type : #CONSTANT,
value : ‘X’
}
]
}
@AnalyticsDetails.query.variableSequence: 10
@Consumption.valueHelp: ‘_Ledger’
P_Ledger : fins_ledger,
@AnalyticsDetails.query.variableSequence: 20
@Consumption.hidden: false
@Consumption.valueHelp: ‘_FinancialStatementHier’
@Consumption.derivation: { lookupEntity: ‘F_FinStmntVersionUserParam’,
resultElement: ‘FinancialStatementHierarchy’ }
P_FinancialStatementVersion : hryid,
——————————————————————————————————
— Time Parameters
——————————————————————————————————
@AnalyticsDetails.query.variableSequence: 30
@Consumption.hidden: false
@Consumption.defaultValue: ‘000’
P_FromFiscalPeriod : fis_period_from,
@AnalyticsDetails.query.variableSequence: 31
@Consumption.hidden: false
@Consumption.derivation: { lookupEntity: ‘I_CalendarDate’,
resultElement: ‘CalendarMonth’, binding: [
{ targetElement : ‘CalendarDate’ , type : #PARAMETER, value : ‘P_KeyDate’ } ]
}
P_ToFiscalPeriod : fis_period_to,
@AnalyticsDetails.query.variableSequence: 32
@Consumption.hidden: false
@Consumption.derivation: { lookupEntity: ‘I_CalendarDate’,
resultElement: ‘CalendarYear’, binding: [
{ targetElement : ‘CalendarDate’ , type : #PARAMETER, value : ‘P_KeyDate’ } ]
}
P_FiscalYear : fis_gjahr_no_conv,
——————————————————————————————————
— Time Comparison Parameters
——————————————————————————————————
@Consumption.derivation: { lookupEntity: ‘I_Ledger’,
resultElement: ‘Ledger’,
binding:
[ { targetElement : ‘IsLeadingLedger’ ,
type : #CONSTANT,
value : ‘X’
}
]
}
@Consumption.hidden: false
@Consumption.valueHelp: ‘_ComparisonLedger’
@AnalyticsDetails.query.variableSequence: 40
P_ComparisonLedger : fis_ledger_cmp,
@AnalyticsDetails.query.variableSequence: 41
@Consumption.hidden: false
@Consumption.defaultValue: ‘000’
P_FromComparisonFiscalPeriod : fis_period_from_cmp,
@AnalyticsDetails.query.variableSequence: 42
@Consumption.hidden: false
@Consumption.derivation: { lookupEntity: ‘I_CalendarDate’,
resultElement: ‘CalendarMonth’, binding: [
{ targetElement : ‘CalendarDate’ , type : #PARAMETER, value : ‘P_KeyDate’ } ]
}
P_ToComparisonFiscalPeriod : fis_period_to_cmp,
@AnalyticsDetails.query.variableSequence: 43
// @EndUserText.label: ‘Comparison Fiscal Year’
@Consumption.hidden: false
@Consumption.derivation: { lookupEntity: ‘I_CalendarDate’,
resultElement: ‘CalendarYear’, binding: [
{ targetElement : ‘CalendarDate’ , type : #PARAMETER, value : ‘P_KeyDate’ } ]
}
P_ComparisonFiscalYear : fis_gjahr_cmp_no_conv,
——————————————————————————————————
— Other Parameters
——————————————————————————————————
@AnalyticsDetails.query.variableSequence: 50
@Consumption.hidden: false
@Consumption.valueHelpDefinition: [{
entity: { name: ‘C_BalanceSheetCurrencyRoleVH’, element: ‘CurrencyRole’ },
additionalBinding: [{ localParameter: ‘P_Ledger’, element: ‘Ledger’ },
{ localElement: ‘CompanyCode’, element: ‘CompanyCode’ } ]
}]
// @Consumption.valueHelp: ‘_CurrencyRole’
@Consumption.defaultValue: ’10’
P_CurrencyRole : fins_curtp,
@AnalyticsDetails.query.variableSequence: 60
@Consumption.hidden: false
@Consumption.valueHelp: ‘_PlanningCategory’
@Consumption.defaultValue: ‘ACT01’
P_PlanningCategory : fcom_category,
@Consumption.derivation: {
lookupEntity: ‘I_FiscalPeriodValueList’,
resultElement: ‘FiscalPeriod’,
binding: [ { targetElement: ‘FiscalPeriod’, type : #PARAMETER, value: ‘P_FromFiscalPeriod’ } ]
}
@Consumption.hidden: true
P_Interval1FromFiscalPeriod : fis_period_from,
@Consumption.derivation: {
lookupEntity: ‘I_FiscalPeriodValueList’,
resultElement: ‘FiscalPeriod’,
binding: [ { targetElement: ‘FiscalPeriod’, type : #PARAMETER, value: ‘P_ToFiscalPeriod’ } ]
}
@Consumption.hidden: true
P_Interval1ToFiscalPeriod : fis_period_to,
@Consumption.derivation: {
lookupEntity: ‘I_FiscalPeriodValueList’,
resultElement: ‘FiscalPeriod’,
binding: [ { targetElement: ‘FiscalPeriod’, type : #PARAMETER, value: ‘P_FromComparisonFiscalPeriod’ } ]
}
@Consumption.hidden: true
P_Interval2FromFiscalPeriod : fis_period_from,
@Consumption.derivation: {
lookupEntity: ‘I_FiscalPeriodValueList’,
resultElement: ‘FiscalPeriod’,
binding: [ { targetElement: ‘FiscalPeriod’, type : #PARAMETER, value: ‘P_ToComparisonFiscalPeriod’ } ]
}
@Consumption.hidden: true
P_Interval2ToFiscalPeriod : fis_period_to
as select from I_FinancialStatementCube( P_FinancialStatementVersion: :P_FinancialStatementVersion,
P_FromFiscalPeriod: :P_FromFiscalPeriod,
P_ToFiscalPeriod: :P_ToFiscalPeriod,
P_FiscalYear: :P_FiscalYear,
P_FromComparisonFiscalPeriod: :P_FromComparisonFiscalPeriod,
P_ToComparisonFiscalPeriod: :P_ToComparisonFiscalPeriod,
P_ComparisonFiscalYear: :P_ComparisonFiscalYear,
P_Ledger: :P_Ledger,
P_ComparisonLedger: :P_ComparisonLedger,
P_CurrencyRole: :P_CurrencyRole,
P_PlanningCategory: :P_PlanningCategory
) as I_FinancialStatementCube
association [0..1] to I_FinancialStatementHier as _FinancialStatementHier on _FinancialStatementHier.FinancialStatementHierarchy = :P_FinancialStatementVersion
// association [0..1] to I_FinancialStatementHier as _FinancialStatementHier on _FinancialStatementHier.FinancialStatementHierarchy = $projection.FinancialStatementLeafItem
association [0..1] to I_CurrencyRole as _CurrencyRole on _CurrencyRole.CurrencyRole = :P_CurrencyRole
association [0..1] to I_PlanningCategory as _PlanningCategory on _PlanningCategory.PlanningCategory = :P_PlanningCategory
association [0..1] to I_Ledger as _Ledger on _Ledger.Ledger = :P_Ledger
association [0..1] to I_Ledger as _ComparisonLedger on _ComparisonLedger.Ledger = :P_ComparisonLedger
association [0..1] to zfi_financial_level as zfl on zfl.vonkt = I_FinancialStatementCube.GLAccount
{
——————————————————————————————————
— ROWS
——————————————————————————————————
@Consumption.filter: { selectionType: #HIERARCHY_NODE, multipleSelections: true, mandatory: false,
hierarchyBinding : [{type : #PARAMETER, value : ‘P_FinancialStatementVersion’, variableSequence: 80 } ] }
@AnalyticsDetails.query.displayHierarchy: #FILTER_ONLY
@AnalyticsDetails.query.axis: #ROWS
@AnalyticsDetails.query.totals: #HIDE
@AnalyticsDetails.query.display: #KEY_TEXT
FinancialStatementLeafItem,
——————————————————————————————————
— FREE
——————————————————————————————————
@AnalyticsDetails.query.axis: #FREE
@Consumption.filter: { selectionType: #SINGLE, multipleSelections: true, mandatory: true}
@Consumption.derivation: {
lookupEntity: ‘I_UserSettingsForCompanyCode’,
resultElement: ‘CompanyCode’,
binding:
[
{
targetElement : ‘BusinessUser’ ,
type : #PARAMETER,
value : ‘P_BusinessUser’
}
]
}
@AnalyticsDetails.query.variableSequence: 15
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
CompanyCode,
@AnalyticsDetails.query.variableSequence: 80
@Consumption.filter: { selectionType: #INTERVAL, multipleSelections: true, mandatory: false }
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
GLAccount,
@AnalyticsDetails.query.variableSequence: 90
@Consumption.filter: { selectionType: #INTERVAL, multipleSelections: true, mandatory: false }
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
ProfitCenter,
@AnalyticsDetails.query.variableSequence: 100
@Consumption.filter: { selectionType: #INTERVAL, multipleSelections: true, mandatory: false }
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
Segment,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.display: #KEY_TEXT
ChartOfAccounts,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.display: #KEY_TEXT
FunctionalArea,
@AnalyticsDetails.query.hidden: true
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
ControllingArea,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
AlternativeGLAccount,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
CountryChartOfAccounts,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
CorporateGroupAccount,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY_TEXT
CorporateGroupChartOfAccounts,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
Currency,
@AnalyticsDetails.query.hidden: true
@Consumption.hidden: true
@AnalyticsDetails.query.variableSequence: 110
@Consumption.filter: { selectionType: #SINGLE, multipleSelections: false, mandatory: false }
AlternativeGLAccountIsUsed,
——————————————————————————————————
— MEASURES
——————————————————————————————————
@Consumption.dynamicLabel: { label: ‘Period Balance &1 – &3.&4’, binding: [ { index: 1, parameter: ‘P_Interval1FromFiscalPeriod’},
// { index: 2, parameter: ‘P_FiscalYear’},
{ index: 3, parameter: ‘P_Interval1ToFiscalPeriod’},
{ index: 4, parameter: ‘P_FiscalYear’} ] }
PeriodBalanceAmount,
@Consumption.dynamicLabel: { label: ‘Comparison Period Balance &1 – &3.&4’, binding: [ { index: 1, parameter: ‘P_Interval2FromFiscalPeriod’},
// { index: 2, parameter: ‘P_FiscalYear’},
{ index: 3, parameter: ‘P_Interval2ToFiscalPeriod’},
{ index: 4, parameter: ‘P_ComparisonFiscalYear’} ] }
ComparisonPeriodBalanceAmount,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: ‘$projection.PeriodBalanceAmount – $projection.ComparisonPeriodBalanceAmount’
cast (1 as fis_abs_diff) as AbsoluteDifferenceAmount,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.formula: ‘NDIV0($projection.AbsoluteDifferenceAmount / $projection.ComparisonPeriodBalanceAmount) * 100’
cast (1 as fis_rel_diff) as RelativeDifferenceAmount,
@AnalyticsDetails.query.axis: #FREE
@AnalyticsDetails.query.totals: #SHOW
@AnalyticsDetails.query.display: #KEY
GLAccountHierarchyKeyDate,
@AnalyticsDetails.query.hidden: true
@Consumption.hidden: true
@AnalyticsDetails.query.variableSequence: 120
@Consumption.filter: { selectionType: #SINGLE, multipleSelections: false, mandatory: false }
ZeroBalanceAccountIsDisplayed,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.hidden: true
@AnalyticsDetails.query.formula: ‘HRY_NODE_SIGN_VALUE($projection.FinancialStatementLeafItem) * $projection.PeriodBalanceAmount’
@Semantics.amount.currencyCode: ‘Currency’
@Consumption.dynamicLabel: { label: ‘Period Balance Sign Adjusted &1 – &3.&4’, binding: [ { index: 1, parameter: ‘P_Interval1FromFiscalPeriod’},
// { index: 2, parameter: ‘P_FiscalYear’},
{ index: 3, parameter: ‘P_Interval1ToFiscalPeriod’},
{ index: 4, parameter: ‘P_FiscalYear’} ] }
cast ( 1 as fis_fsv_sa_repo_amount ) as PeriodBalanceSignAdjdAmount,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.hidden: true
@AnalyticsDetails.query.formula: ‘HRY_NODE_SIGN_VALUE($projection.FinancialStatementLeafItem) * $projection.ComparisonPeriodBalanceAmount’
@Semantics.amount.currencyCode: ‘Currency’
@Consumption.dynamicLabel: { label: ‘Comparison Balance Sign Adjd &1 – &3.&4’, binding: [ { index: 1, parameter: ‘P_Interval2FromFiscalPeriod’},
// { index: 2, parameter: ‘P_FiscalYear’},
{ index: 3, parameter: ‘P_Interval2ToFiscalPeriod’},
{ index: 4, parameter: ‘P_ComparisonFiscalYear’} ] }
cast ( 1 as fis_fsv_sa_comp_amount ) as ComprnPeriodBalSignAdjdAmount,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.hidden: true
@AnalyticsDetails.query.formula: ‘HRY_NODE_SIGN_VALUE($projection.FinancialStatementLeafItem) * $projection.AbsoluteDifferenceAmount’
cast ( 1 as fis_fsv_sa_abs_diff ) as AbsltDifferenceSignAdjdAmount,
@DefaultAggregation: #FORMULA
@AnalyticsDetails.query.hidden: true
@AnalyticsDetails.query.formula: ‘HRY_NODE_SIGN_VALUE($projection.FinancialStatementLeafItem) * $projection.RelativeDifferenceAmount’
cast ( 1 as fis_fsv_sa_rel_diff ) as RelativeDifferenceSignAdjdAmt ,
zfl.Level1,
zfl.Level2,
zfl.Level3,
zfl.Level4,
zfl.Level5
}
Table function:
@EndUserText.label: ‘ZFI Financial Level’
@AccessControl.authorizationCheck: #NOT_REQUIRED
define table function zfi_financial_level
with parameters z_vonkt: vonkt_011z
—- @Environment.systemField: #CLIENT clnt : abap.clnt–,
returns
{
clnt : abap.clnt;
vonkt : vonkt_011z;
Level1 : ztxt45_lvl1;
Level2 : ztxt45_lvl2;
Level3 : ztxt45_lvl3;
Level4 : ztxt45_lvl4;
Level5 : ztxt45_lvl5;
}
implemented by method
zcl_fi_stmnt_lvl=>get_level_txt;
Table method:
class zcl_fi_stmnt_lvl definition
public
final
create public .
public section.
interfaces if_amdp_marker_hdb.
class-methods get_level_txt
for table function zfi_financial_level.
protected section.
private section.
endclass.
class zcl_fi_stmnt_lvl implementation.
method get_level_txt by database function for hdb
language sqlscript
options read-only
using fagl_011zc
fagl_011pc
fagl_011qt.
declare i integer;
declare temp1 varchar( 50 );
declare temp2 varchar( 50 );
declare temp3 varchar( 50 );
declare temp4 varchar( 50 );
declare temp5 varchar( 50 );
declare counter integer;
declare counter_to integer;
/* structure to return */
declare return_table table ( clnt varchar(3),
vonkt varchar( 20 ),
Level1 varchar( 45 ),
Level2 varchar( 45 ),
Level3 varchar( 45 ),
Level4 varchar( 45 ),
Level5 varchar( 45 ) );
lt_ret =
select distinct zc.mandt as clnt,
zc.vonkt as vonkt,
pc.ergsl as ergsl1,
t1.txt45 as Level1,
pc2.ergsl as ergsl2,
t2.txt45 as Level2,
pc3.ergsl as ergsl3,
t3.txt45 as Level3,
pc4.ergsl as ergsl4,
t4.txt45 as Level4,
pc5.ergsl as ergsl5,
t5.txt45 as Level5
from fagl_011zc as zc
–1
left outer join fagl_011pc as pc
on pc.versn = zc.versn
and pc.ergsl = zc.ergsl
— and pc.mandt = clnt
left join fagl_011qt as t1
on t1.versn = zc.versn
and t1.spras = ‘E’
and t1.ergsl = pc.ergsl
— and t1.mandt = clnt
–2
left outer join fagl_011pc as pc2
on pc2.versn = zc.versn
and pc2.id = pc.parent
— and pc2.mandt = clnt
left join fagl_011qt as t2
on t2.versn = zc.versn
and t2.spras = ‘E’
and t2.ergsl = pc2.ergsl
— and t2.mandt = clnt
–3
left outer join fagl_011pc as pc3
on pc3.versn = zc.versn
and pc3.id = pc2.parent
— and pc3.mandt = clnt
left join fagl_011qt as t3
on t3.versn = zc.versn
and t3.spras = ‘E’
and t3.ergsl = pc3.ergsl
— and t3.mandt = clnt
–4
left outer join fagl_011pc as pc4
on pc4.versn = zc.versn
and pc4.id = pc3.parent
— and pc4.mandt = clnt
left join fagl_011qt as t4
on t4.versn = zc.versn
and t4.spras = ‘E’
and t4.ergsl = pc4.ergsl
— and t4.mandt = clnt
–5
left outer join fagl_011pc as pc5
on pc5.versn = zc.versn
and pc5.id = pc4.parent
— and pc5.mandt = clnt
left join fagl_011qt as t5
on t5.versn = zc.versn
and t5.spras = ‘E’
and t5.ergsl = pc5.ergsl
— and t5.mandt = clnt
— :clnt, :z_versn, :z_vonkt are all input parameters
where –zc.mandt = clnt
–and
zc.versn = ‘NPLC’ –:z_versn
and zc.ktopl = ‘NPL1’
and zc.vonkt = :z_vonkt — added 20241114 again for testing — added 20241114 for testing
— 20241115
group by
zc.mandt,
zc.vonkt, –as vonkt,
pc.ergsl, — as ergsl1,
t1.txt45, — as Level1,
pc2.ergsl, — as ergsl2,
t2.txt45, — as Level2,
pc3.ergsl, — as ergsl3,
t3.txt45, — as Level3,
pc4.ergsl, — as ergsl4,
t4.txt45, — as Level4,
pc5.ergsl, — as ergsl5,
t5.txt45 –as Level5
— 20241115
order by vonkt, Level1
;
i = 1;
for i in 1 .. 1 do –RECORD_COUNT(:lt_ret) do
temp1 := ‘ ‘;
temp2 := ‘ ‘;
temp3 := ‘ ‘;
temp4 := ‘ ‘;
temp5 := ‘ ‘;
if :lt_ret.Level5[ i ] is not null
then
temp1 = :lt_ret.Level5[ i ];
temp2 = :lt_ret.Level4[ i ];
temp3 = :lt_ret.Level3[ i ];
temp4 = :lt_ret.Level2[ i ];
temp5 = :lt_ret.Level1[ i ];
else
if :lt_ret.Level4[ i ] is not null
then
temp1 := :lt_ret.Level4[ i ];
temp2 := :lt_ret.Level3[ i ];
temp3 := :lt_ret.Level2[ i ];
temp4 := :lt_ret.Level1[ i ];
if :lt_ret.Level3[ i ] is not null
then
temp1 := :lt_ret.Level3[ i ];
temp2 := :lt_ret.Level2[ i ];
temp3 := :lt_ret.Level1[ i ];
if :lt_ret.Level2[ i ] is not null
then
temp1 := :lt_ret.Level2[ i ];
temp2 := :lt_ret.Level1[ i ];
else
temp1 := :lt_ret.Level1[ i ];
end if;
end if;
end if;
end if;
lt_ret.Level1[ i ] := :temp1;
lt_ret.Level2[ i ] := :temp2;
lt_ret.Level3[ i ] := :temp3;
lt_ret.Level4[ i ] := :temp4;
lt_ret.Level5[ i ] := :temp5;
return_table.vonkt[ 1 ] = :lt_ret.vonkt[ 1 ];
return_table.Level1[ 1 ] = :lt_ret.Level1[ 1 ];
return_table.Level2[ 1 ] = :lt_ret.Level2[ 1 ];
return_table.Level3[ 1 ] = :lt_ret.Level3[ 1 ];
return_table.Level4[ 1 ] = :lt_ret.Level4[ 1 ];
return_table.Level5[ 1 ] = :lt_ret.Level5[ 1 ];
end for;
— return :lt_ret;
return :return_table;
endmethod.
endclass.