Skip to Content

Hi, in this post I would like to share trick for adding value help to parameter in analytical query.

Parameters in ABAP CDS view could be used as mandatory, single value variable, which could be used when split of calculation logic required. By default parameters don’t have value help, like filtration option based on fields at consumption level.

Step-by-step:

I. Create ABAP CDS view for dictionary based on table or some dummy table (like in this blog).

II. Create ABAP CDS cube with required logic.

Notes:

  • For demo I use just select form T000 table.
  • There is parameter p_par used in logic.
  • This parameter is not linked to any of fields in ABAP CDS view.

III. Create ABAP CDS analytical query with required logic.

Notes:

  • Usually all needed associations are created at cube level, but at this case I suppose it is possible to make exception
  • Don’t forget to code name of field in association
  • Don’t forget to add consumption view type
  • Now I couldn’t make texts visible for value help, but to have value help for key values is much more better compared to free field option for parameter
  • User can manually enter value not from value help, restriction is not guaranteed

IV. Result query in Analysis for Excel. Value help button exist.

For simple parameter dictionaries this approach could be useful.

Thank you for attention!

To report this post you need to login first.

11 Comments

You must be Logged on to comment or reply to a post.

  1. Sergey Shablykin

    Another nice blog by Maxim!

    Some 5 cents: because of absence of text for parameter’s value you may decide to use text as parameter key. Please bear in mind that:

    a) text being as key will conver to uppercase automatically

    b) no spaces allowed. No error will be generated but you just won’t see your value in list.

    So you can’t use key values like ‘Magic Wand’ or ‘MAGIC WAND’. You have to use ‘MAGIC_WAND’.

    Hope, issue with absence of text for parameter’s value will be fixed soon and my comment will depreciate.

    (0) 
  2. Pierfrancesco La Spada

    Hello, nice how-to. Another way to accomplish the same goal could be creating a new domain in SAP and then associating the parameter to the domain( Parameter as domainName) .

    Thanks

    (0) 
  3. Jitin Khanna

    Hi Maksim,

     

    I am using I_Ledger in an association to populate value help for RLDNR (Ledger of General Ledger account) parameter in my view on ACDOCA table, but unfortunately value help is not displaying.

    Any clue, what I am missing here. Appreciate your help.

     

    Thanks

    JK

    (0) 
    1. Maksim Alyapyshev Post author

      Hi, Jitin!

      If it is possible in your case try to use analytical query consumption filter instead of parameter. They allow more agile way to enter values. In this case you need to create assosiation at cube level.

      if not it should work:

      @AbapCatalog.sqlViewName: 'ZVFI_C_TST'
      @AbapCatalog.compiler.compareFilter: true
      @AccessControl.authorizationCheck: #CHECK
      @EndUserText.label: 'Test Query'
      @Analytics.query: true --Important to have this
      @VDM.viewType: #CONSUMPTION --Important to have this
      define view ZFI_C_TST
      with parameters
      @Consumption: {valueHelp: '_LedgerVH.Ledger'} --Do not forget to select "Ledger" after point(".")
      p_rldnr : rldnr
       as select from zfi_c_ledgercube --Cube based on acdoca table
          association [0..1] to I_Ledger as _LedgerVH on  _LedgerVH.Ledger like '%'                          
      {
         rldnr,
         rbukrs,
         hsl
      } 
      where rldnr = $parameters.p_rldnr
       

      Br, Maksim

      (0) 
  4. Jitin Khanna

    Hi Maksim,

     

    Thanks for your recommendations, but I am trying to create a view using ACDOCA only and therefore using @Analytics.datacategory: #CUBE annotation for same. So, when I am try to include @Analytics.query: true within same code, then ACDOCA table becomes inaccessible.

    In the end, I have to comment @Analytics.query: true to successfully activate the program, but value help for Ledger still not showing up in Analysis for office.

    Below is piece of code developed by me.

    @AbapCatalog.sqlViewName: ‘ZVFIOTACDOC’
    @AbapCatalog.compiler.compareFilter: true
    @AccessControl.authorizationCheck: #CHECK
    @EndUserText.label: ‘ACDOCA view’
    @VDM.viewType: #CONSUMPTION
    @Analytics.dataCategory: #CUBE
    @Analytics.dataExtraction.enabled: true
    @ObjectModel.representativeKey: ‘AssignmentReference’
    //@Analytics.query: true

    define view ZVFIOT_ACDOCA_CDS
    with parameters
    @Consumption: {valueHelp: ‘_LedgerVH.Ledger’}
    P_Rldnr :rldnr,
    P_RBUKRS :bukrs,
    P_GJAHR :gjahr,
    P_BELNR :belnr_d,
    P_VRGNG :co_vorgang,
    P_AWTYP :awtyp,
    P_AWREF :awref
    as select from acdoca as AD

    association [0..1] to I_CompanyCode as _CompanyCode on $projection.CompanyCode = _CompanyCode.CompanyCode
    association [0..1] to I_Ledger as _LEDGERVH on $projection.Ledger = _LEDGERVH.Ledger

     

    Appreciate your help.

    Thanks

    JK

    (0) 
    1. Maksim Alyapyshev Post author

      Hi, Jitin!

      Analytical query CDS view should be another view above Cube CDS view.

      In you case it is better:

      • to delete all parameters from Cube;
      • define associations for other fields like you did for Company code;
      • create analytical query above CUBE with consumption filtration option (see examples here)

      In result value help will be formed automatically from associations.

      BR, Maksim

      (0) 
  5. Jitin Khanna

     

    Hi Maksim,

    I am able to display the value help for Ledger as recommended by you, but  facing issues while coding value help for other parameter like ‘Company Code’. Below is the code, I am trying to design.

     

    @AbapCatalog.sqlViewName: ‘ZVFIOTAVH’
    @AbapCatalog.compiler.compareFilter: true
    @AccessControl.authorizationCheck: #CHECK
    @EndUserText.label: ‘ACDOCA Valuehelp view’
    @Analytics.query: true
    @VDM.viewType: #CONSUMPTION
    define view ZVFIOT_ACDOCASQL_CDS
    with parameters
    @Consumption: {valueHelp: ‘_LedgerVH.Ledger’}
    p_rldnr : fins_ledger,
    @Consumption: {valueHelp: ‘_CompanyCode.CompanyCode’}
    P_RBUKRS :bukrs,
    P_GJAHR :gjahr,
    P_BELNR :belnr_d,
    P_VRGNG :co_vorgang,
    P_AWTYP :awtyp,
    P_AWREF :awref
    as select from ZVFIOT_ACDOCA_CDS                                             // ACDOCA cube
    association [0..1] to I_Ledger as _LedgerVH on _LedgerVH.Ledger like ‘%’
    association [0..1] to I_CompanyCode as _CompanyCode on $projection.CompanyCode = _CompanyCode.CompanyCode
    {

    Ledger,
    CompanyCode,
    _LedgerVH,
    _CompanyCode

    }
    where
    Ledger = $parameters.p_rldnr
    and CompanyCode = $parameters.P_RBUKRS
    and Fiscalyear = $parameters.P_GJAHR
    and DocumentNumber = $parameters.P_BELNR
    and COBusinessTrn = $parameters.P_VRGNG
    and RefProcedure = $parameters.P_AWTYP
    and RefDocNum = $parameters.P_AWREF

    Let me know, what I am missing here.

    Appreciate your help.

     

    Thanks

    JK

    (0) 
  6. Maksim Alyapyshev Post author

    In you case it is better:

    • to delete all parameters from Cube and query;
    • define associations for other fields like you did for Company code at cube level
    • use in analytical query @consumption.filtration instead of parameters (see examples here)

    In result value help will be formed automatically from associations.

    Br, Maksim

    (0) 

Leave a Reply