Skip to Content
Author's profile photo Subhasish Sahu

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.

Assigned Tags

      3 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Alexey Zimin
      Alexey Zimin

      Hi Subhasish!

      How do you embed VBA macros in exported Excel file?

      Regards,

      Alexey

      Author's profile photo Subhasish Sahu
      Subhasish Sahu
      Blog Post Author

      Hello Alexey,

      Sorry for delay in response , you need to create an excel template and then use it.

      Follow the Link for more info.

      http://superuser.com/questions/626878/how-to-permanently-add-macros-to-excel-as-part-of-the-program

      Regards,

      Subhasish

      Author's profile photo Alexey Zimin
      Alexey Zimin

      Hi Subhasish! Thank you for reply! I am wondering of there any option to embed macros in exported file. In your examples this macros should already reside on the client machine, before export. Regards, Alexey