Let’s start with why?
Delivered compensation reporting using Adhoc and BIRT reporting. This blog is about learning/challenges faced along the journey.
Background:
Customer has two variable pay templates for Leadership and executive bonus and two compensation templates for merit and LTI. Now, the most obvious question that should come up in mind would be why 4 templates? Why were these not combined into one? Well, there were reason -
All the 4 templates are launched at the same time, around beginning of November and closed at the same time, around February first week. 14 countries were in scope.
Requirements:
Major requirement was to prepare a report by combining data from all the 4 templates.
There were some more detailed requirements:
How was it approached?
ORD - Customer were using online report designer for their employee central reporting requirements. So, first thought was to use online report designer to meet the compensation reporting requirements too but this thought was soon ruled out as online report designer is a different database which does not allow for real time compensation reporting.
Advanced Reporting (live reporting) – We could combine one compensation and one variable pay template but not 2 of each. Hence, this could not be leveraged for reporting.
Adhoc Report – Being aware of the different options available to create a report – cross domain reporting seemed like the most feasible option. Later, I learnt that cross domain reporting should only be used to combine up to maximum of 2 templates. We were successfully able to combine four templates but the moment we added the fifth one, some fields would show up with technical names. Total of 58 fields! 5 templates were needed in total as one was used as a base template to create left outer joins with all the other templates.
Fields in the base domain.
Next, we looked at the possibility of other reporting options.
How was it solved?
Multidataset Adhoc Report:
With multidataset adhoc report, I could generate a report with all the required data in one excel sheet with four different tabs (Dataset1, Dataset2, Dataset3, Dataset4). An excel ‘macro’ was then written to combine these 4 datasets into one tab.
Using 4 domains to generate multidataset report:
Report generated had 4 datasets as every report in multidataset is a separate report which is combined into one excel sheet.
Compensation team could use this as a base report to create custom reports for higher management.
BIRT Reporting:
What is BIRT?
The Business Intelligence and Reporting Tools is an open source software project that provides reporting and business intelligence capabilities for rich client and web applications. It utilizes the data extracted from adhoc report and that data can be manipulated as needed. This tool provides seamless integration with adhoc tool.
Customizing BIRT-
BIRT reporting tool was leveraged to meet all the other requirements of the report like renaming the columns, adding custom column and having data in both functional and local currency.
BIRT team was engaged to deliver this report. There were some initial challenges as the concept of combining multidataset report into one single BIRT report was new to them but those challenges were soon overcome to get the consolidated report that met the customer requirements.
An “.rptdesign” report is provided by BIRT team which needs to be uploaded in the adhoc report under datasets - ‘Upload template’.
Once BIRT template has been uploaded, it becomes a ‘custom report’.
Every column with values had both functional and local currency and could rename columns exactly as there were in worksheet for easy consumption by local and global business teams.
Extra column was added to display total compensation received by an employee in USD by adding merit (USD)+Leadership(USD)+Executive(USD)+LTA(USD).
Some columns in report from variable pay templates can show technical names instead of the functional names. Hence, it can get challenging for customer to work with those columns. BIRT reporting tool was utilized to rename columns. Now, columns in report mirrored the columns in worksheet giving customer the comfort of familiarity.
Important Lesson learnt:
The most important thing to keep in mind is Compensation reporting needs to be worked on before the worksheets are launched as fields cannot be added/edited after compensation worksheets have been launched. Another important lesson is to create reports after configuration changes are complete.
Conclusion:
We looked at ways to prepare reports using multiple templates via Adhoc and BIRT reporting tool. Multidataset report was utilized from Adhoc reporting tool with a combination of ‘macro’ tool from excel to combine four datasets into one. Raw data from Multidata set report (Adhoc report) was used as an input to BIRT reporting tool to meet custom requirements. Final report was generated with data in both functional and local currency, custom column was added to get total compensation paid and columns in report were renamed to mirror the columns in worksheet.
PS: Please do leave your comments/suggestions.
Delivered compensation reporting using Adhoc and BIRT reporting. This blog is about learning/challenges faced along the journey.
Background:
Customer has two variable pay templates for Leadership and executive bonus and two compensation templates for merit and LTI. Now, the most obvious question that should come up in mind would be why 4 templates? Why were these not combined into one? Well, there were reason -
- Variable pay templates – Leadership and Executive had different bonus calculation formulas.
- Compensation Templates – Merit and LTI had different hierarchy. Merit had standard hierarchy whereas LTI had roll-up hierarchy.
All the 4 templates are launched at the same time, around beginning of November and closed at the same time, around February first week. 14 countries were in scope.
Requirements:
Major requirement was to prepare a report by combining data from all the 4 templates.
There were some more detailed requirements:
- Have data in both functional and local currency
- Adding a custom column called ‘Total compensation payout’ (merit+ bonus+ LTI)
- Name of the columns in report to mirror the names in worksheet
How was it approached?
ORD - Customer were using online report designer for their employee central reporting requirements. So, first thought was to use online report designer to meet the compensation reporting requirements too but this thought was soon ruled out as online report designer is a different database which does not allow for real time compensation reporting.
Advanced Reporting (live reporting) – We could combine one compensation and one variable pay template but not 2 of each. Hence, this could not be leveraged for reporting.
Adhoc Report – Being aware of the different options available to create a report – cross domain reporting seemed like the most feasible option. Later, I learnt that cross domain reporting should only be used to combine up to maximum of 2 templates. We were successfully able to combine four templates but the moment we added the fifth one, some fields would show up with technical names. Total of 58 fields! 5 templates were needed in total as one was used as a base template to create left outer joins with all the other templates.
Fields in the base domain.
Next, we looked at the possibility of other reporting options.
How was it solved?
Multidataset Adhoc Report:
With multidataset adhoc report, I could generate a report with all the required data in one excel sheet with four different tabs (Dataset1, Dataset2, Dataset3, Dataset4). An excel ‘macro’ was then written to combine these 4 datasets into one tab.
Using 4 domains to generate multidataset report:
Report generated had 4 datasets as every report in multidataset is a separate report which is combined into one excel sheet.
Compensation team could use this as a base report to create custom reports for higher management.
BIRT Reporting:
What is BIRT?
The Business Intelligence and Reporting Tools is an open source software project that provides reporting and business intelligence capabilities for rich client and web applications. It utilizes the data extracted from adhoc report and that data can be manipulated as needed. This tool provides seamless integration with adhoc tool.
Customizing BIRT-
BIRT reporting tool was leveraged to meet all the other requirements of the report like renaming the columns, adding custom column and having data in both functional and local currency.
BIRT team was engaged to deliver this report. There were some initial challenges as the concept of combining multidataset report into one single BIRT report was new to them but those challenges were soon overcome to get the consolidated report that met the customer requirements.
An “.rptdesign” report is provided by BIRT team which needs to be uploaded in the adhoc report under datasets - ‘Upload template’.
Once BIRT template has been uploaded, it becomes a ‘custom report’.
Every column with values had both functional and local currency and could rename columns exactly as there were in worksheet for easy consumption by local and global business teams.
Extra column was added to display total compensation received by an employee in USD by adding merit (USD)+Leadership(USD)+Executive(USD)+LTA(USD).
Some columns in report from variable pay templates can show technical names instead of the functional names. Hence, it can get challenging for customer to work with those columns. BIRT reporting tool was utilized to rename columns. Now, columns in report mirrored the columns in worksheet giving customer the comfort of familiarity.
Important Lesson learnt:
The most important thing to keep in mind is Compensation reporting needs to be worked on before the worksheets are launched as fields cannot be added/edited after compensation worksheets have been launched. Another important lesson is to create reports after configuration changes are complete.
Conclusion:
We looked at ways to prepare reports using multiple templates via Adhoc and BIRT reporting tool. Multidataset report was utilized from Adhoc reporting tool with a combination of ‘macro’ tool from excel to combine four datasets into one. Raw data from Multidata set report (Adhoc report) was used as an input to BIRT reporting tool to meet custom requirements. Final report was generated with data in both functional and local currency, custom column was added to get total compensation paid and columns in report were renamed to mirror the columns in worksheet.
PS: Please do leave your comments/suggestions.
- SAP Managed Tags:
3 Comments
