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. 
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. 
The screen is refreshed and you see your cube. 
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. 
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. 
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. 
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. 
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. 
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. 
Find a CommandButton icon in the toolbar. 
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 
giving the meaningful description. 
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. 
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. 
choose ‘Security’ tab and press ‘Macro Security…’ button. 
In the next screen, on the ‘Security Level’ tab make sure that the security level is set no higher than to Medium level. 
On the ‘Trusted Publishers’ tab make sure that the ‘Trust access to Visual Basic Project’ flag is set. 
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 I...
Creating a query
Launch SAP BEx Analyzer. Using either menu or icons open a query. 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. The screen is refreshed and you see your cube. 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. 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): 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. 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
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. 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. 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: Find a CommandButton icon in the toolbar. 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 giving the meaningful description. 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. 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. Setting up Excel security
From the Excel menu path: Tools -> Options choose ‘Security’ tab and press ‘Macro Security…’ button. In the next screen, on the ‘Security Level’ tab make sure that the security level is set no higher than to Medium level. On the ‘Trusted Publishers’ tab make sure that the ‘Trust access to Visual Basic Project’ flag is set. 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 I...
2 Comments
