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