Skip to Content
Suppose, we implemented the long texts scheme as has been shown in my blog: Long texts in SAP BW: Modeling We created an infoprovider, loaded data into it and want to create our first report. For this we need to create a query first.

Creating a query

Launch SAP BEx Analyzer. Using either menu or icons open a query. image Locate and expand your infoarea looking for your cube. Since the cube has no queries built on it yet, it has not been shown in the infoarea. To see it click on a ‘New’ icon. image The screen is refreshed and you see your cube. image Suppose, we want to see the simplified report showing my goods groups codes and their long descriptions in rows and some key figures and formulas in columns. image The column structure contains selections with my sales key figure restricted by a reporting or previous month (entered through the user entry variable and an offset for the previous month) and an export/import flag. There also two formulas calculated as ratios. In rows I placed my ZGROUP_1 infoobject and selected its three attributes containing the three parts of the long description. The first peace of the description will be also shown as a long text of the infoobject itself. To see not only groups description but also a groups code I chose ‘Display As’ option as ‘Key and Text’ in ZGROUP_1 properties.

The query output

After saving the query and running it, I got this ugly looking report with peaces of long description shown separately and a header and colors that doesn’t comply with my requirements (I decreased the columns width for more vivid apparition): image If this report is to be a one of many in the reporting package I must submit to any government/controlling bodies, I have to make some manual manipulations, such as removing navigation block, setting up proper colors, redrawing header and footer etc. I hate to do this manually. To avoid it I wrote a Visual Basic code formatting my report.

Modifying the workbook

I have 3 worksheets in my workbook. I renamed them as follows. image The first, BEx worksheet, contains output of BEx query, as shown earlier. The second sheet contains the formatted report template. And the last one – formatted report itself.

The formatted report template

image Template consists of three areas: a header, a format row and footer. Header starts from the first row. The last row of it is designated in the template. The footer’s start and end rows are also specified. During creation of the formatted report, both, the header and the footer are copied into the output worksheet without changes, with all formatting done. You can place in the header area all the features you need (proper descriptions, borders, fonts, merged cells etc.). Signatures of a chief accountant and a manager may be placed in the footer. Here you can add some comments, remarks etc.

The Format Row

The format row contains all the formatting we want to see in the report’s result area (except header and footer). Since a Goods Group Description is to have the long text, we set up the property of this cell in the format row with ‘Wrap text’ flag. It also has the Italic font style. image Also for demonstration purposes we set up the font for the Export reported month column with red color and the next column for previous month – with the shown below borders. image

Adding a button

There is a special SAPBEXonRefresh subroutine in BEx workbooks. We can place our formatting code in there. Though, I don’t like this. I prefer the workbook doesn’t get refreshed during the opening. For I may want to see the results of the last run (for the previous reporting period). It’s suitable for me to get the formatted report on demand, i.e. by pressing a button. So, we’ll add a button (a CommandButton standard Excel/VB object). First of all, make sure that your Control Toolbox toolbar is shown: image Find a CommandButton icon in the toolbar. image Click on it, position cursor in the ‘BEx’ worksheet to the place we want the button be. Pressing the mouse key draw a rectangular and then release the mouse. Edit the appeared button image giving the meaningful description. image Click somewhere out of the button leaving the edit object mode, and then double click on the button. You’ll be brought to the Visual Basic editor screen. image Make sure that you see the SAPBEX module. If you don’t see it, follow the next section. As a variant, one can access the Excel VBA code using the following menu path. image

Setting up Excel security

From the Excel menu path: Tools -> Options image choose ‘Security’ tab and press ‘Macro Security…’ button. image In the next screen, on the ‘Security Level’ tab make sure that the security level is set no higher than to Medium level. image On the ‘Trusted Publishers’ tab make sure that the ‘Trust access to Visual Basic Project’ flag is set. image We are ready to implement the code. See the part II: Long Texts in SAP BW: Displaying in BEx Analyzer. Introduction to Excel Workbooks Formatting. Part II.

To report this post you need to login first.

2 Comments

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

Leave a Reply