Create Key/Text feature in custom Analytical Query – How to use Key/Text feature in a custom Analytical query
This blog is all about using key/text feature in a custom Analytical query. In custom analytical query some times we require a feature where for an element we could have display based on key/ Text/ key and Text/ Text and Key values.
Let me elaborate this scenario with an example. Let’s assume we have an analytical query based on material details. Now in the material details one of the key element is material number. We need the following feature for material number. There could be four options.
- If we select based on Key then material numbers data would be displayed in the query.
- If we select based on Text then material descriptions for the respective material numbers would be displayed in the query.
- If we select by Key and Text then first material numbers and then respective material descriptions would be displayed.
- If we select by Text and Key then first material descriptions and then respective material numbers would be shown.
So basically it would be shown based on condition.
Assumptions – The analytical query is already being created.
Now lets say in the analytical query, we need this feature for material number. So to demonstrate this scenario I have created two custom tables one with material number and some other fields – ZCDSKEYTXT and another with material description with some relevant fields – ZCDTXT(This is the custom text table associated with material number).
Table – ZCDSKEYTXT
Table – ZCDTXT
After that, using this two custom tables two custom CDS views have been created.
CDS view ZI_TEXT- This cds view has been created to fetch the details of material description based on material number from table zcdtxt.
Now the most important annotation with respect this scenario is – @Semantics.text: true. This annotation is being used to map the field from which the text will be fetched. So basically it helps to link the text field.
In this scenario material number will be linked with material description.
Then this text cds view will be associated with main cds view for material details.
In this CDS annotation – @ObjectModel.text.association: ‘_Text’ does the job. It is basically being used to map to the text cds view ZI_TEXT.
Now, here the text field is being fetched from another text cds view as it belongs to a different table ZCDTXT(Text table) rather than the main table (ZCDSKEYTXT).
However, lets just assume material description would exist in the same table where material number is. Then we could use the following way to get the desired solution on key/text feature.
@Semantics.text: true – This annotation will be used however will need to be used in same CDS and @ObjectModel.text.element: Materialnum will be used instead the one used above(@ObjectModel.text.association: ‘_Text’).
Adding to that, this cds(ZI_TEST_TEXT) will be called in the analytical query using @ObjectModel.foreignKey.association based on condition.
Conclusion – The above approach can be used in any custom Analytical query using either text cds or within the same CDS to get the Key/Text feature on the same.
I hope you find this blog interesting and helpful. I wrote this blog to share my knowledge with you so if i have missed anything important please feel free to add the same in comment box.
Stay happy and blessed.