December 29, 2017 1 minute read

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

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!

16 Comments

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

Sergey Shablykin

January 10, 2018 at 4:17 pm

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.

Pierfrancesco La Spada

January 30, 2018 at 9:40 am

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

Prateek Sonthalia

January 31, 2018 at 11:34 am

Hi Maksim, you seem to have deep knowledge of CDS Annotations. Could you kindly reply to my question here ?

https://answers.sap.com/questions/402937/object-model-annotations-in-cds-views-without-corr.html

Regards

Former Member

February 19, 2018 at 10:52 pm

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

Maksim Alyapyshev

Blog Post Author

February 20, 2018 at 7:15 am

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

Former Member

February 20, 2018 at 1:45 pm

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

Maksim Alyapyshev

Blog Post Author

February 20, 2018 at 2:41 pm

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

Amardeep Burnwal

October 26, 2020 at 12:06 pm

Hi Maksim,

Do you mean to say that we should have one Cube view with the aggregated results and associations to other CDS views for Value helps. Then we use this cube view in a separate view which we mark as @Analytics.query: true and as i am planning for a analytical list report page, i have added @OData.publish: true

However unfortunately though the Visual Filters are working fine, the compact filters are not behaving as search helps. If i remove the analytical query annotation and use associations in my final consumption view then Compact Filters work as search help but the Visual filters does not load. So i guess there is no error in Value Help preparation.

Is there anything that I might have missed?

Thank you in Advance,

Amar

Former Member

February 20, 2018 at 4:36 pm

Hi Maksim,

Thanks for the inputs.

Will try both options.

Shall reach out to you in case required.

Appreciate your quick responses.

Thanks

Former Member

February 21, 2018 at 4:34 pm

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

Maksim Alyapyshev

Blog Post Author

February 21, 2018 at 4:46 pm

Too many things need to be corrected. Try to find standard cds in system and analyze how they are build...

Br, Maksim

Maksim Alyapyshev

Blog Post Author

February 21, 2018 at 5:22 pm

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

Sebastian Freilinger-Huber

September 21, 2018 at 7:19 am

Hi Maksim,

thank you for sharing your ideas - I think I can follow your descriptions. However I am wondering about a more general architectural question and I would be interessted in your opinion.

Your solution is based on an association to a "Value Help CDS View", which is implemented on Query View level. You already stated that "generally the associations should be implemented in the cube but here you think an exception is valid".

According to my general understand, I also know this guideline about "no associations in Query Views".

I am now wondering about my more general usecase, where I want to consume my Query View via FIORI Elements Analytical List Page. Here I need more value helps, especially on Dimension attributes, as I want to define some compact filters.

Do you know, how the guideline (or maybe a Best Practise) looks like here to achieve this? Technically I think I know, which annotations to use but I am struggling to find an answer, where to implement this - Query or Cube?

Best regards and thanks in advance,

Sebastian

Vijay Sharma

July 12, 2021 at 3:59 pm

Hi Sebastian ,

Did you get the answer to your above query? I am now struggling with the same.

Sebastian Freilinger-Huber

October 8, 2021 at 7:48 am

Hi Vijay Sharma

nope, I never received an answer or saw something actually working in a documentation with Analytical Query Views and Value Help.

However, my workaround was to create the Query View without associations and use OData.publish:true.

I also create one CDS Consumption View (non-Query) for each value help, also using OData.publish:true.

Then I create a project in SEGW and INCLUDE the major service and all value help services.

In this way I get all services combined into one service, which I can use in the front-end.

This generally works - drawback is just that it seems like the Annotations from Back-End cannot be consumed from the app, so you implement everything with local annotations - but this would be anyway the way to go if you are on a NetWeaver Release like 7.50.

Best regards,

Sebastian

Vijay Sharma

October 8, 2021 at 8:05 am

Thanks Sebastian for the response.

SEGW workaround helps, but as we are going away from SEGW with RAP coming along, I hope for a better solution from SAP for this, in time to come 🙂

Regards

Vijay

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

Assigned Tags