Skip to Content

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.

For Example:

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 Solution:

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.

You must be Logged on to comment or reply to a post.