Dynamic generation of Tabs in Excel, depending upon the Hierarchical Dimensional LOVs in Report.
There would be a scenario where customer ask for the report to get download into Excel and depending upon the N number(distinct) of LOVs of a Hierarchical Dimension, the excel report should generate N number(distinct) of tabs dynamically, also each tab should show the information related to a individual value of LOVs as well as naming the Tab name on the LOVs. But there is a another problem that there is a query prompt on this Hierarchical Dimension, hence its next to impossible to create N number of report in same document.
There is Monthly Profit & Loss Report, which is has a section on Profit Center , and there are more than a 100 Distinct Profit centers per Profit Center Node and the report filter is on Profit Center hierarchy Node.
The requirement is to generate with n number of tabs (n = number of profit centers in the Profit Center hierarchy Node) in the Report.
The using Excel VBA Macro it can be achieve. We have developed a report that has a header on Profit Center ,as mentioned below
Excel Report Format
Feel free to provide me your feedback.