Tips and tricks for using the report designer SAP BI

Based on the information I’ve reviewed at a number of Web sites and group discussion boards, several Report Designer issues in particular are prompting users to ask questions and request additional development in certain areas of this component.Here suggest approaches that you can take within the Report Designer to resolve them. The topics we will discuss here seem to be the functionality that is the most popular and required and the highest visibility in terms of the number of companies that are looking to use or are using the Report Designer.

One common question for which very little help seems to be available online is how to incorporate a change to the underlying query used in a Report Designer report. Unfortunately, once a query is used within the Report Designer, any changes made to that query definition are difficult to incorporate within the Report Designer report. This does not mean that if you change the query definition by adding a characteristic or key figure, your Report Designer report will not work; rather, it means that any changes made regarding the data provider to the Report Designer report will not be available for use in the report, in which case there’s a chance that the report itself will not work.

The Report Designer concept is to have a fixed format for the report so that we can offer displays of information that are usable by both internal and external management.

An adjustment to the data provider format or definition will require that we review the configuration of the Report Designer report and make sure that we still have all the necessary components and that all the query definition changes abide by the rules and requirements of the Report Designer. Unlike the situation in which an adjustment is made to the query definition and the report using that ad hoc query immediately picks up that change and can accommodate it, the Report Designer uses specific cells of the query definition to format the final results, and thus the report has to be in a specific structure. So, again, any changes to the query definition of the Report Designer more than likely will not be available in the Report Designer report. That being said, the only true solution for this situation is to create another Report Designer report for the changed query. Of course, there are exceptions to any rule, an example of which would be using some text from the new characteristic on a row or column heading—a change that is cosmetic in nature and not core to the report format or report information.

1.Using Hierarchies in Your Reports
Using hierarchies in the Report Designer process is a concept that seems to prompt many questions on the Web. The issues being raised address the overall use of hierarchies within the configuration of a report using Report Designer. The Report Designer is not limited to the use of “flat views” of the data within a query. The use of a hierarchy in the query does not preclude the query from additional formatting in the Report Designer. The hierarchy is presented in the normal fashion, as it would be in a query or on the Web, but additional formatting can be applied for each of the individual levels of the hierarchy. Each of these levels shows up in the Report Designer as a unique node, and you can use all the normal formatting options against each level. The Report Designer can accommodate any of the different versions of the hierarchies whether they are text type hierarchies, hierarchies with only one text node and all of the others are postable nodes, or even if there are several characteristics within one hierarchy. So the use of hierarchies has no limitations (unlike some of the other BI components). It is important to note that if you want the hierarchy to be displayed immediately at a specific level, you need to adjust the display level in the properties of the characteristic in the Query Designer. After you make the adjustment there, the hierarchy will be displayed at that level with the initial display of the Report Designer report in the WAD.

At each level, settings for text colors, text size, font type, bold, italic, cell colors, cell height, and borders are available. To demonstrate this functionality, we will use a basic Material hierarchy off of the InfoObject 0MATERIAL. The following illustration shows the Query Designer with the query we will use. Notice that the characteristic 0MATERIAL has a hierarchy assigned to it. This is a standard hierarchy for the material levels from an ECC system.

Using Hierarchies in Your Reports

The next illustration shows the actual hierarchy from the Data Warehousing Workbench. Notice that this hierarchy has uneven levels (0MATERIAL and 0PRODH3) that occupy the same level, has text levels, and has more than one InfoObject.

Using Hierarchies in Your Reports

Also note that a variable for the hierarchy node is included in this query. The following illustration shows this scenario.

following illustration shows

The query is initially inserted into the Report Designer, as shown in the following illustration. Here, the hierarchy and all levels can be seen in the Report Designer and specifically in the Report Designer work area. This is where you can set up unique views of each of the different levels of the hierarchy.

unique views of each of the different levels of the hierarchy.

In the following illustration, we begin the process of realigning the format by using the Edit Format option on the context menu of the cell. This option opens an Edit Format dialog box with a number of different options, as shown in the second illustration.

Edit Format dialog box with a number of different options

Edit format

As you can see, the dialog box has three tabs, Cell, Font, and Border. Each of these tabs has the same options as those you can access via the dropdown menus in the Report Designer toolbar or the context menu’s Cell Format and Text Format options. This dialog box allows you to make the adjustments all in one place. As an example, use the options on the Cell tab to adjust the background color. Click the small button (with three dots) to the right of the Background Color field to open the color pallet, shown next, which offers the different colors available as well as the ability to create a customized color for your use. Choose any of the different colors to use, and then click OK to go back to the Edit Format dialog box. The second illustration shows the end result.

color

Edit format

The following illustration shows the second tab, Font, which is used to change the font of the text. The options are straight forward.

second tab, Font, which is used to change the font of the text

The Border tab, shown next, enables you to create borders around any set of cells you need to emphasize, such as cells for certain text or titles.

cells for certain text or titles.

To adjust the font of the other levels of the hierarchy, we will choose Format | Font and select a font from the dropdown list, as shown in the following illustration.

dropdown list, as shown in the following illustration

Finally, upon executing the report, the initial screen that appears offers a field to enter the variable hierarchy node, as shown in the next illustration. The second illustration shows the results of assigning the variable. Notice that during the process of formatting, we included both the text and the technical naming convention with the appropriate background color and font format. We also could have changed the background color for key figures and made other adjustments.

dropdown list, as shown in the following illustration

dropdown list, as shown in the following illustration

Therefore, when it comes to hierarchies in the Report Designer configuration, all of the functionality and flexibility standard within the hierarchies delivered in BI are available and the additional enhancement using the Report Designer is that additional configuration can easily be done with the levels to differentiate between hierarchy text nodes and hierarchy postable nodes as we accomplished in this example. we would have to develop a completely new workbook template with all of the formatting embedded into the workbook template for the different levels of the hierarchy nodes. This would be quite difficult and time consuming.

2.Using Templates in the Report Designer
Another question or topic commonly asked in Report Designer–related online discussion forums is whether templates designed in the Report Designer can be used with other queries. This basically goes back to the whole idea of having queries that change and the data provider needs to be able to pick up that change. In this case, we are looking for a template to be used that will have the appropriate header, footer, or other information that is required for a specific set of reports and not incorporate any core changes within the query or report itself. So, creating a template to manage the “soft” content such as text is possible.

There are a couple of approaches to this issue and the easiest one would be to go into the Implementation Guide - IMG (transaction code SPRO) and review the template 0REPORT_DEFAULT_TEMPLATE to make the necessary adjustments so that every one of the Report Designer reports that you create will be using this changed template. To do this, go to the SAP Customizing Implementation Guide under SAP NetWeaver | Business Intelligence | Settings for Reporting and Analysis | BEx Web | Set Standard Web Templates | Enterprise Report. You can copy this Web template to make changes and set it as your new standard Web template for reports in the IMG. This approach is preferable if you want only one default template for the Report Designer reports to be used by all of the corporate departments.

On the other hand, if you want to have a specific template for each of the different departments in your corporation, you need to use a custom template and continue to reuse it as you develop your reports. This is similar to what you might do to create unique templates for each of the different groups that would be using the BI-IP (integrated planning) frontend, which included more than one detailed template for planners. We will also look at a similar situation for use with the WAD. In this case, we have multiple formats for each of the Report Designer reports. To accomplish this, we have to build our customized template within the Report Designer and save it to be used as a template. We can use the header and footer in this case to help us. If we start by creating a header and footer, as shown in the following illustrations, and using these as part of the template, we can develop the required outline for this template.

header and footer, as shown

header and footer, as shown

Now we step into the WAD and confirm that this template will display the results the way we would like to see them. We access the Report Web item and insert the Report Designer template we just developed. The following illustration shows this information. Notice the Report Design parameter under Internal Display on the Web Item parameters tab; it indicates that the custom Report Designer report is being used.

custom Report Designer report

The results of this custom Report Designer template and what the business user would see via a Portal are displayed in the following illustrations.

custom Report Designer template

custom Report Designer template

Now that we have reviewed the results of the template without data, we can go back into the Report Designer and make a copy of this template and insert a data provider into the template. The following illustration shows the process of Save As to make a copy of the template for your use.

process of Save As to make a copy of the template for your use

Choose the appropriate query to be a data provider, as shown in the following illustration, and once this is complete insert a data section into the copied template.

appropriate query to be a data provider

You have now created a copy of the template, inserted a query into the template and inserted a data section so that the query could be displayed in the Report Designer format. The final result of the configuration is shown in the following illustration. Save these changes by saving with a different technical name. This can be seen in the second illustration.

second illustration

second illustration

Now, stepping back into the WAD and using the Report Designer report in conjunction with the Report Web item as a support, we can complete this process. The following illustration shows the process of inserting the Report Designer report into the WAD.

process of inserting the Report Designer report into the WAD

The following illustration shows the final results, verifying that the header, footer (not shown in this illustration), and information are all available for reporting out of the WAD.

final results, verifying that the header, footer

Header and Footers
The option to use headers and footers in a report offers additional approaches to increase the professional look and feel of the report itself. In addition to inserting a header and footer, you can also insert an additional “section.” This allows you to add an additional data provider with other functionality, such as adding another query to the Report Designer format, or simply add additional text and fields.

When you use the header and footer options, the Field Catalog comes into play. The Field Catalog stores all the elements that you can drag and drop into the header and footer. In most cases, a report has some type of vital information that needs to be available to business users to enable them to understand what they are looking at and what types of data are available. All these different elements are available in the Field Catalog, including information such as the date of the data refresh, a reminder of the current filter values and variable values, the name of the data provider, and other fields. Although you can insert these elements into any of the available cells, they are more appropriately positioned either in the header or footer of the report. The best approach is to insert additional fields into the header and footer so that you can control the spacing and formatting on an individual basis for these cells and information. The following illustration shows the different sections available in the Field Catalog and the different elements available for use in the header and footer sections.

different sections available in the Field Catalog and the different elements

Query Fields
Another section of the Field Catalog is the query fields. The query fields represent all the different characteristics and key figures available in the data provider. This means the query fields provide access to every characteristic text, member id, and description as a field that can be used in a Report Designer report header or footer. In addition, the query fields provide access to every key figure for each member and also the totals of each key figure cumulated to every characteristic. Taking this a step further, you can present the total sales revenue amount in the header of the report before the results have even appeared. It is important to note that restrictions apply to the placement of the individual characteristic members. They can only be placed in a group level of the characteristic.

For example, if your report contains Month and Region, you can only place the query fields for Region in the group level for the region. You could not place this in the group level for the month and definitely not in the header and footer sections. The technical reason for this is that the characteristic member and its related key figures are only known at the time of row generation for the group level. All other query fields, such as the final result of the query, can be placed anywhere in the report, including within any group level.

Text Elements
Another component that is very important in the Field Catalog is the text element. Text elements represent a comprehensive range of informational parameters that can be used to provide the user with useful supporting information about the report. These include items such as date of data refresh, query name, author, and so on. If you review this list and there are other text elements that you would like to assign to the Report Designer report, you can add them to the report by using another Text Field insert but within this section of the Report Designer component these are the only standard text elements that are available. In terms of location on the report, there are no restrictions for the placement of the text elements.

Filter Fields
The filter fields, which can be any of the available characteristics, are used to remind the user of the current filter values applied to the report. This is especially useful if the filters are not obvious—they may be background filters. These are no restrictions for the placement of these filters fields in the report.

Variable Fields
Variable fields are just like filter fields except they are used to remind the user of the specific variable values chosen. There are no restrictions for the placement of these variable fields in the report.

Custom Fields
Custom fields are automatically generated whenever you create custom text in the report. Each unique custom text that is defined in the report’s cells is automatically added to the custom text fields list. These can be reused in any of the other cells in the report with a simple drag-and-drop process.

Sample Use of Parameters in the Field Catalog
As an example of some of the use of some of these functions, we will use the hierarchy report introduced earlier in the chapter and enhance it with these different options. The following illustration shows the hierarchy report in the Report Designer with the Page Header and Page Footer options. Choose the Page Header option and we will customize a field.

Sample Use of Parameters in the Field Catalog

Next, right-click the report and choose Insert Image on the context menu, as shown in the following illustration. Once the image item is assigned to the header field, double-click it, and the text “Image” appears. In the Properties tab of this item, all the different options for inserting the image are available.Next, right-click the report and choose Insert Image on the context menu, as shown in the following illustration. Once the image item is assigned to the header field, double-click it, and the text “Image” appears. In the Properties tab of this item, all the different options for inserting the image are available.

Sample Use of Parameters in the Field Catalog

Using the file path for the MIME Repository to review the location of the images and what is available shows where the images are stored. You can use this menu path to find the appropriate folder in which to save the images for use in the Report Designer. The following illustration shows the menu path to the folder holding the images for the Report Designer as well as for the WAD. Using the image’s technical name, available in the MIME Repository (sap_logo.gif), type that information into the filename so that the system can identify the appropriate image to use.

Sample Use of Parameters in the Field Catalog

Once you have inserted the image into your Report Designer field you will notice that the ability to adjust the size of the image is just below the field. You can choose the Keep Ratio check box to have the width and height stay in the same proportion throughout the process, or you can clear the check box and adjust the size of the image manually.

Next, we can turn our attention to the footer information in the report. Again, we can use the Insert menu to insert the footer into the report, as shown in the following illustration.

footer into the report, as shown

The footer will be divided by additional columns to get some space between the information displayed in the field, which in this case will be Created By, Last Refresh, and InfoProvider. The following illustration shows these objects inserted into the footer.

objects inserted into the footer

Finally, after all these changes, we can execute the report, the result of which is shown in the following illustration. Notice the image for the logo of SAP and also the enhanced text for the title of the report.

image for the logo of SAP and also the enhanced text for the title of the report

In addition, the footer has been inserted, as shown in the following illustration. Notice the use of additional columns to separate the information.

additional columns to separate the information

Multiple queries can be positioned in one Report Designer screen, but the standard view for multiple reports (data providers) is to display one under the other, with some formatting to make the overall picture consistent with the report requirements. It is possible to position two queries one next to the other, but the queries must be built with this specific requirement in mind. If you were to try to place two queries side by side within a Report Designer component, you would have a very difficult time. The two queries would need to be sized accordingly so that they can “fit” the Report Designer work area. If you switch the display of the Report Designer from regular tolandscape view, you will have a better chance to accomplish this.

In my opinion, it’s easier to just create two Report Designer reports with the two query definitions and then go to the WAD component and use the Report Web item in conjunction with a Table Web item (a table set up with two columns and one row). This allows the display of the two Report Designer reports side by side with very little difficulty. After this, you would just have to make some minor adjustments to the formatting and the separation between the two displays to accommodate the report requirements. So, regrettably, in this case, the easiest approach to be able to place these reports in a particular position requires working in the WAD rather than in the Report Designer. In the WAD, the result is easier to accomplish, faster to configure, and easier to maintain. Since we have all these different tools at our disposal, we may as well use the appropriate tool to get this right.



Face Book Twitter Google Plus Instagram Youtube Linkedin Myspace Pinterest Soundcloud Wikipedia

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

SAP BI Topics