RSRTQ – Query Definition – Multipurpose T-Code on BEx Queries
Our SCN fellow consultants are already briefed about RSRTQ, on how-to and when to use. Even though I would like to share my experience with this blog, to thank SAP and give back some useful real time scenarios to forum (especially for new SAP BW Consultants).
For all BW consultants, BEx queries are integral and main part of their job. As all of you know, user always interested to see the reports and get the data according to their requirements. So we all include complex logics at BEx query level. In terms of
- Customer exits,
- Restricted Key Figures (RKF), Calculated Key Figures(CKF), Query level Selections and Formulas
- Cell level calculations (each cells will have CKF/RKF OR IF..ELSE logics) List will go on.
Let’s restrict on these 4 items and allow me to brief the scenario with business/project requirement.
Take example of 100 Management Reports and 180+ Financial Reports for different group of users. Data submission happens by versions (ex: Monthly, Quarterly, Half Yearly, Yearly [Actuals and Forecasts], Quarterly Finance Actuals, Finance Adjustments)
On every year there are changes at organizational level
- Changes on Data Submission version ( new versions, Monthly version become Quarterly and vice versa)
- Changes on their products and product category.
- Finance is quarterly reporting, few reports have variables w/o customer exits to default the Fiscal Year and Period to previous quarter.
- Calculation of measure and its formula always changing.
So every time we will have to change the reports
- To show new products in current year and old products in old year, both should come under same Product Category.
–> So we have IF Else logic to show right products and aggregate to same Product Category.
2. In Management Reporting, the report should show (Monthly/Quarterly) Actuals and Forecasts dynamically based on user input OR by system date. For ex: If the Monthly version becomes Quarterly, user wants to see the old year data in by Month and current year by Quarter.
–>We have used extensive cell level logics to meet the above requirements.
3. In Financial Reports, Every quarter we asked to manually change the Period and Year to Previous Quarter in all Reports (many reports are not having
customer exits for specific reasons). –> We used to change manually.
Along with above, a very good task given to us to look at each report and reduce the number of reports. In other words, simplify the reports (either by merging / to give base report).
Let me be clear and stick to the point of using RSRTQ and not discuss on the design OR solution to the above points 🙂
The main problem is to check all reports every year and analyze what is the old formula and its impact, find the customer exits (on all places of query). But it become easy now
Enter RSRTQ T-Code,
Once you get above screen, enter query technical name in first box and select desired ‘Output Options’. In my case I would like see all since we have to check the formulas, variables.
Check ‘Show Properties’ in Additional Output to know the processing type (to identify customer exits). Then click on execute, this will take into details.
This section gives you all information about the filter, it was helpful to me to identify the Hardcoded objects (ex: PH as shown).
There are cases where many hardcoded character restriction at query; which is causing ‘Yearly Maintenance’. Using this it was easy to find and replace hardcode restrictions with variables for all reports within less time.
Rows & Columns
Below screen shows the Formulas & Selection used in Rows & Columns, this was helped me to identify the formulas in reports which are to be changed ; when the calculation was changed for measures (ex: Capital Turnover).
Also it is easy to find in all places of the query at one go, otherwise it is difficult to find in Query Designer. In many cases you may forget/skip/overlook the change either in Rows/Columns
Important thing is ‘Cell level Definition’, Imagine you have more than 50 cells and needs to change the formula/version in many places. RSRTQ showing cell level information as a single document helped me a lot to analyze and change required cells at one go.
By selecting ‘Additional Output’ – Show Properties, you will be able to see details of variables. Below screen shows ‘default values’ and type of variable.
This option helped me to change the variable values in Financial Reports. We have downloaded all 180 query definition and saved as excels, prepared the list of report which requires manual change and which has customer exits (changes automatically).
Apart from the above,
- It helped to analyze the Structures, Characteristics, Key Figures of each report as documents (.xls) and merge the redundant queries. As a simplification of Financial Reports(180 queries); we have merged 60 queries into two queries, and created 60 views (based out of two queries). Later these delivered as workbooks to users.
- It will help you to understand and debug the formula (either in Cell Level/Rows/Columns) especially when the report is huge.
- We have used it extensively to compare query between two systems.
Comparison of query explained in below by Pushkar Dhale
Aparna Duvvuri explained scenario of transport issue on Query
Shanthi Bhaskar explained RSRTQ in below
Once again Thanks a Ton! To SAP for giving a powerful T-code like RSRTQ.
And special thanks to Pushkar, Aparna, Shanthi to put more light on this.
Hope it helps!