BW Bex 3.x Macros to create Formatted Workbooks – Part 1
Objective of this web log is to enable creating Formatted reports with 3.x Bex Analyzer. First we will look into some of the limitations in creating formatted reports with the 3.x web reports and bex reports. Then we will see how to overcome some of these issues with a generic macro that can be attached to any Bex Workbook.
Top 3 BW Reporting issues are
Printing is one of the most common complaints of BW. Printing in neither environment limits you to the standard functionality found within Excel or a Web Browser. Reports that have too many columns will be difficult to print on a single page. The settings has to be modified every time the columns are changed.
Any formatting created on BEx reports will disappear when the query is refreshed. The formats must be recreated every time the query is refreshed. There is no reporting tool that provides both analytical features and formatting features. Some tools are good for analytics and some are good for formatted reports.
BW Bex Macros provides convenient functions for formatting business intelligence data interactively in Microsoft Excel. The Macros can be embedded in Microsoft Excel as an add-in that overcomes some of the bw report formatting limitations.
How the BW Bex Macros Work
BW Macros are attached to BEx Workbooks. If you see the structure of a BEx workbook, you will see a report title section, navigation, filter and query section as shown in the slide. There are four buttons that appear when the macro is added to BEx Workbook.
When the format button is executed, two additional sheets are created besides the main query sheet. One is the Format sheet where all the formatting for that report is maintained. Another sheet is the Print Sheet which is created mainly for Printing. The print sheet is protected so that the users do not format the print sheet than the format sheet. The two sheets contain the same format of the query result set sheet except that the navigation and the filters area are omitted.
When the Refresh button is executed, all the formats maintained in the format sheet is taken along with the data that is present in the query results sheet and are put together in the print sheet. Thus the print output will have the format maintained in the format sheet with the current data present in the query results. The print sheet is optimized for printing. Based on the the query result set, the print settings are set automatically.
How to Embedd BW Macro Addin to Bex Workbook. For more information have a look at the presentation.
http://www.netsaptech.com/SDN/demo/BIM301.pdf For a demo click the below 2 links
Very handy and useful.
Where can we get this macros ?
Thanks in advance,
Thanks for you reply. The prerequisites and the steps for adding Macros is part of the second blog which would be out in a couple of days.
Will provide the xla along with the step by step instructions.
Thank you for the blog. This is a great way to get the formatting capability needed in our reports!
This is really some great stuff. It's something I have been looking for. Where can I get the BW Macros? If possible please email the xla to email@example.com.
Really great stuff for BEx report Developers. These BW Macros will really help the Business Users and the developers in creating, formatting and print enabled workbooks.
Awaiting for the second Blog of BW Macros.
Please, where can we get this macros ?
Thanks in advance,