The primary source of information for every Custom Analytical Query is an underlying (Custom) CDS View. This data source is very important as it provides the query with all the information it needs. Every field/ element that is added to the CDS View will also be later available in the query for selection. But it can happen that some fields might not be displayed in the field selection tab of the query even though they are included in the underlying Custom CDS View. If you have experienced a similar situation, then this blog post might help you identify one possible root cause as why certain fields are excluded from the query.
Use Case
The use case where I discovered that a field was not available in the query was when I joined the CDS Views ‘I_BusinessPartner’ with ‘I_BusinessPartnerBank’ in a new Custom CDS View. I have added 8 fields to my CDS View which I wanted to have displayed in the query:
But in the field selection tab of the query, I realized that the ‘BusinessPartnerName’ was missing:
Knowing that I have added the fields correctly and have not done any other configurations that might have excluded that field from the query, I went back to my Custom CDS View and took a look at the element properties.
There I have noticed that ‘Text’ has been automatically filled as semantic for ‘BusinessPartnerName’ and in the column ‘Text Element’ the ‘BusinessPartnerName’ was populated for the ‘BusinessParter’ field:
Note: The ‘Text Element’ column in the Custom CDS View is not being displayed by default. You must enable it first by clicking on the gear symbol in the upper right corner and selecting it from the list.
These settings have the effect that the ‘BusinessPartnerName’ is not being listed as a separate field in the field selection tab, but it still can be displayed in another way. Opening the query and making a right click on the ‘BusinessPartner’ (Number) will give me the option to display the ID and a description. By doing so, every ID will have a description right next to it which is the respective ‘BusinessPartnerName’:
The only downside of this method is that I cannot insert a filter for the name. If I wanted to search for a specific business partner name, I need to enable the ‘BusinessPartnerName’ to be listed as a separate field in the field selection tab. And there is a simple trick to it.
After removing the text settings in the element properties of the Custom CDS View (leaving the content of the two cells blank) and after saving and publishing the Custom CDS View and opening the query again, the ‘BusinessPartnerName’ will become available in the field selection tab:
Ticking the checkbox of the ‘BusinessPartnerName’ will include this field in the query and in the filters tab I have now the opportunity to set this field as a filter.
Conclusion
In this blog post you have learned that due to the text element property settings, certain fields can be excluded from being displayed and selected in the field selection tab of a Custom Analytical Query. By checking the element properties of the Custom CDS View that is serving as the data source of the query, you are now able to identify a possible root cause and the removal of the text semantics/ elements should help you make those fields available again.
In case you have any questions, please use the
SAP S/4HANA Cloud Q&A area. You are also welcomed to provide your feedback and thoughts in the comment section below. For similar content you can follow my profile or the blog feed
SAP S/4HANA Cloud.