Hi, in the post I would like to consider a little trick with parameters derivation in the analytical query report. It could be useful to form report variants logic.
Users need to have opportunity to select different variants of analytical report. Difference is placed in some filtration variables, not in logic part of report.
- use variants at the BI Tool side (Analysis for Excel or some other) (not reliable, someone have to maintain it and other mustn’t have authorization for maintenance etc.)
- use different reports with predefined variable selections (not convenient from usability and support)
- alternative methods
The post is dedicated for demonstrate one of possible alternative methods.
1. You could create a real system table with report variants in your system, but it is also possible to have a virtual one.
In following example we will consider a financial report with two main variants. Variants will be based on accounting principles:
- IFRS ( International Financial Reporting Standard)
- RAS (Russian Accounting Standard)
ABAP CDS view ZFI_I_ACCSTANDAND:
First trick: use of some kind of DUMMY table for generating accounting standards dictionary table. I’m using RSADMINA table with one row instead of DUMMY table.
Data result is showed below:
Each row of the table is scenario or report variant. Columns p_r_la, p_r_la_wo_pd and p_r_ta are parameters that we are using in analytical query.
2. Report logic in based S4/HANA Universal Journal table (ACDOCA). I’m not going to demonstrate full scenario, in analytical query ABAP CDS view we have:
Second trick: use parameter p_scenario for report variant selection. Derive two register parameters p_rldnr_1 and p_rldnr_2.
For first register parameter p_rldnr_1 derive value:
- from ‘ZFI_I_ACCSTANDAND’ (lookupEntity)
- select column with name ‘p_r_la'(resultElement)
- select row in lookup entity by key filed ‘accstdid’ (binding.TargetElement)
- substitute it with #PARAMETER (type)
- that takes value from analytical query parameter ‘p_scenario’ (value).
You could find derivation annotations in SAP Documentation.
Business users should fill only one scenario parameter in selection screen of analytical report, register parameters will be derived automatically.
We considered parameter derivation like a way for report variants generation. Also It was shown how to virtually fill CDS view from some kind of DUMMY table.
Thank you for attention!
P.S. I’d like to thank Olga Kondratenko for her help with clarifying derivation annotations.