Development and management of corporate reporting initiatives can be burdensome and result in a muddled array of report styles and data. Clearly streamlining the process for corporate branding through a reporting template gives direction to all users and ensures consistent results, as well as focused management of the development process.

While the drag and drop functionality of SAP BusinessObjects Web Intelligence (WebI) results in a powerful ad-hoc tool for corporate reporting, issues with formatting often cause end users to write off the tool as difficult to use, confusing, frustrating, or inaccurate. Correctly implementing a corporate standard and template mitigates these issues, resulting in greater user adoption and a cohesive corporate brand.

I take you through four steps of how to create a corporate reporting template, and also share best practices to consider.

Step 1. Locate Existing Reports to Use as a Starting Point

The first step in creating a template is to create a mock-up. This then functions as a roadmap, or recipe, for developing the template. The quickest way to get started is to locate an existing report or set of reports, whether in Microsoft Excel, Web Intelligence, or some other tool. The goal is to get something in front of the business as quickly as possible. The first report always takes the longest; everything else is often just a version of the first. If an experienced developer or administrator is available who is familiar with the installation and administration of SAP BusinessObjects, have them create the mock-up using Web Intelligence. Figures 1 and 2 show examples of what you may find in existing reports and what you work toward with a mock-up in Excel and Web Intelligence.

Primarily you are striving for visuals at this point in the process. However, data is an important part of that visual goal. While the validity of the data at this point is irrelevant as it is only present to determine business preferences, it is necessary for:

• Numerical formatting
• Case definition (upper, lower, or mixed)
• Identification of alert preferences for positive and negative values
• Alignment within cells
• Preferences for titles, subtitles, and indicators (e.g., date pages)
• Date formatting

You need to populate the  report with at least basic data (i.e., two to three pages worth) when working through the template mock-up so that everyone can see the way a report looks and feels, You also want to note the pagination properties so that page breaks are inserted at the appropriate points based on sections, tables, and charts.

Figure 1

A sample report in Excel

Figure 2

A sample report in Web Intelligence

Step 2. Use Corporate Branding Standards for Report Style Elements

Working with the mock-up, begin to identify and configure the visual preferences of the template, such as color scheme, number and date formatting, default fields (e.g., title, refresh date, disclaimers), and alignment. While these elements in the template are seemingly innocuous, carefully selecting these elements ensures the visual consistency of reports: Not only does each report have the same look and feel, but the end user also knows where to find data on a report.

For example, in Figure 2, data ranges and date processed are at the top of the report; page numbers and date of execution are at the bottom right corner of the page; all data elements are in the center with blue headers, with a slightly lighter blue for the footer and gray alternating rows. Without defining these seemingly trivial details, users are free to either select any combination of element placement and color they like, or the defaults are used. Either way, consistency across reports lags as the individual preferences of each developer takes light.

Figure 3 shows how such corporate branding elements incorporate into a template style, as well as key default fields.

Figure 3

A branded template with key fields

In the video demo below, I show you how to create the common elements of a corporate reporting template, using a Web Intelligence report as an example.

Step 3. Employ Free-Standing Template Cells and Add Common Elements

Drawing on existing functionality within Web Intelligence not only reduces the time to develop a template, but also provides the flexibility to meet a multitude of reporting needs. You can display elements — such as document name, last refresh date, filters, and page numbers — using built in functions. Using pre-defined cells — either built-in Web Intelligence elements or self-defined elements using a blank cell — allows the format of corporate reports to remain the same while also permitting a variety of customizations on individual reports. You can find these template cells in Web Intelligence by going to the Report Element tab and then navigating to the Cell sub-tab menu (Figure 4).

The menu on the left side of Figure 4 corresponds to the output on the right. For this example, if you add Document Name to the report, you get Pre-Defined Examples; if you add Last Refresh Date to the report, you get 11/7/12; if you add Drill Filter, you get None, and so on down the list.

Figure 4

Free-standing cells

Common elements include the following:

• Company logo
• Report title and subtitle
• Dates for which the report is displaying data
• Date and time of report execution
• Page numbers
• Filters applied to the report
• Confidentiality statement and disclaimer

Step 4. Define the Technical Details by Using a Checklist

Once you establish the general look and feel of the template, there are additional details to define. Following is an overview to help you:

Margins

Will the template be portrait or landscape? Creating two templates using each approach provides the most options with the least modifications for end users. Save time by developing one of them, and then copying it and modifying the details, such as margins and final element placement. While these vary across organizations, margins of ¼ inch allow for the most content per page, while still falling within the printable area. While wider margins create unneeded white space on a report, you can use ½- or 1-inch margins if reports contain little data.

Relative Positioning

Positioning is critical in the successful development of a template, yet it is the most-often-missed aspect by end users because users assume that if the items appear to be aligned correctly with whatever data they are currently viewing, that it will always be aligned. Users don’t necessarily think about the expanding and contracting of tables based on fluctuating datasets, which may result in the overlapping of elements and poor pagination.

When report elements are not positioned relative to each other on the report, they begin to overlap each other as the data set grows, resulting in elements that appear to be missing or floating in the middle of other elements. Each template should include a table for data and a chart (if charting is part of your organizational needs). A chart can always be deleted by a user who copies the template, but a chart’s inclusion allows you to set the relative position of that chart to the table. This ensures that regardless of how many rows of data the table contains, it does not overrun the chart, causing one or the other not to be visible.

Using relative positioning for elements such as titles, filter displays, and report dates in the headers ensures that all elements are evenly distributed and thereby more visually pleasing. Furthermore, setting the width of these elements ahead of time and disabling autofit options also safeguards data from being truncated and overrunning other elements.

To disable autoformat options, in Web Intelligence, follow menu path Format Cell > General > Autofit width and Autofit height checkboxes, and remove the marks from the boxes.

Refresh Settings

While largely driven by security and distribution requirements, refresh settings should be determined based on whether most reports need to contain the most up-to-date data based on user access privileges. In most cases, the Refresh on open option (under the Document Properties menu) is checked to ensure that the user gets the most recent data.

The exception to this is if the report is distributed via publication. Publications also send a refresh request to the server during execution. If the report is set to refresh on open as part of a publication, the system attempts a double refresh at the same time, causing the report to fail.

Prompt Settings

While not a part of the template itself, the corporate standard should note whether responses are modified by the following options:

• Prompt with list of values*. Uncheck this box to get a free-form text box for user defined values.
• Select only from list*. Uncheck this box to allow users to select from a list or enter a value in a free-form text box.
• Keep last values selected*. This option retains the last value entered. Uncheck this box for reports in which users have restricted data sets and only can select from a list of values to which they have access.
• Optional prompt. Uncheck this box to allow a report to run with no restriction from the user (i.e., a way to essentially select all).

By default, the starred items above are checked when creating a prompted filter. That said, if prompts are part of reporting, include these prompt responses in the template to inform users that the report may contain filtered data. While this can be accomplished using the Prompt Summary pre-defined field, a more useful way is to include the UserResponse() function in a blank cell positioned within the header.

Section and Block Breaking

When tables and sections are in reports, they can split data inopportunely across pages. Checking the boxes for a table or section to Avoid Page Breaks in Table/Section and Repeat Header on Every Page improves the readability of reports and eliminates a common complaint by users.

Other Considerations

Refer to the various element tabs in Web Intelligence 4.x. Define each property for a complete standard.

Designing for All Channels: Email, Mobile, Web

Reports today are consumed via a variety of sources, with mobile and embedded email becoming increasingly popular. Unfortunately, unless you develop your reports with these delivery methods in mind, you may end up with a report that displays less than optimally.

When a report is distributed via embedded email, all report elements align on the left in the order they were added to the report, regardless of their visual placement and positioning within the version in Web Intelligence. Therefore, when distributing reports in this way, it is important to remember to add every element to the report in the correct order, from top to bottom, as you would like it viewed in an email.

Figure 5 shows how elements can be jumbled when not added in the correct order. For example, the title of the report, Daily Sales, is below the filters and period displays near the top of the page. The tables should also be ordered positive, negative, positive, negative, yet both the WTD and PTD positive tables are displayed first, with their corresponding negative data elements pushed to the bottom.

Figure 5

A poor alignment of report elements

Conversely, Figure 6 shows how the elements appear in the correct order when added to the report in the proper sequence.

Furthermore, you can also see that the title and date run for fields in Figure 6 have been shifted so that they are centrally aligned above other report elements. You can accomplish this by setting the textbox in which element is contained to a size much larger than the text itself and then centering the text in the textbox. Even after being aligned to the left in an email, the elements still appear centered.

Figure 6

The proper alignment of report elements

Securing and Distributing Templates

A common question when creating a template is how to secure the data across users. When reporting is IT-managed and not editable by the end user, the developer can set up filters in the report itself. However, when securing reports globally — including a template — it is best to secure data at the universe or database level, thereby eliminating the need to set up security within a template, which a user could remove.

Regarding access and use, place the template in a public folder that end users have access to. Configure security settings to grant users only view and copy access. These settings enable users to copy the template to their favorites folder or another folder they have access to, and make modifications there instead of potentially breaking the settings within the main template.

Tips to Increase User Adoption

As with anything new, training users leads to greater adoption and acceptance. In the case of a Web Intelligence template, it is important to:

• Explain how to find the template. You must consistently name templates (e.g., Sample Template – Portrait Layout) and ideally store them in a public folder in which users can easily find what they need.
• Explain how to copy the template and save it to create a new report. If this process is confusing or difficult, users are more likely to avoid it. Explaining the basic functionality of a template, including how to save it to a folder for which they have edit rights, is critical. The Save As function is the most straightforward way to accomplish this.
• Explain how to add a new query and add query results to the template. In Web Intelligence 4.x, a default query connecting to a data source is no longer necessary for creating a template. As a result, you need to tell users how to add a new data source to the existing template, or add a query for their own data if a query is already included to support the database-driven global element updates  I discussed earlier. Furthermore, when a new query is added, the system can ask users if they want to:
  • Insert a table in a new report
  • Insert a table in a current report
  • Include result objects in a document without generating a table

Users creating a report from a template should select the third option above in order to allow them to drag elements onto the table and charts in the template instead of creating new tables and reports. Either of the first two options disregards the intention of the template.

• Explain how to remove elements from the template if necessary. Not all reports require sections, tables, or charts. Show users how to delete these elements without affecting the placements of the other report elements.

Supporting Changes and Future Reporting Needs

Template changes are required over time. The definition of a corporate reporting standard and construction of a corporate reporting template provides a starting point for pushing those changes out to the organization, while also allowing you to apply some changes retroactively.

Use the Save As Function

All changes made to the template itself must be incorporated into new reports going forward. If you make changes to a copy of the template, and the existing template is then over-written, it is important to use the Save As method of replacement instead of copying and pasting the material and then deleting the original.

Using Save As ensures that the underlying cluster unique identifier remains the same for the template and that shortcuts created by users are not broken.

Create Objects for Date-Sensitive Elements

To keep your template flexible, don’t hard-code evolving items, such as copyright years that become outdated in a short time. Instead, create objects either in a database table or in a universe from which they can then be inserted in the template. This allows changed items, such as copyrights, disclaimers, and logos, to apply to all reports — existing and new — by simply changing the associated object.

To use objects for date-sensitive items, take the following steps:

1. Create objects in the database and universe to contain the data required
2. Add a query to the template that contains the new objects
3. Run the query and use the objects in place of variables, constants, or Web Intelligence pre-defined template elements within the template report

Possibly Modify Web Intelligence Configuration

Lastly, you can make global changes through another avenue: Modify the Web Intelligence configuration file to set default fonts and color schemes to your corporate preferences. This ensures that if a user does not utilize the report template, certain aspects of your corporate reporting standard are still included in every report.

However, due to the core application effects of these changes, enlist the help of an administrator with significant experience in SAP BusinessObjects configuration.

This option does not include template variables, such as positioning and timestamps; it only addresses defaults, such as font, colors, and background. The template is still necessary to standardize elements such as spacing, dates, and page numbers.

Also note that if a skin — akin to a background image or watermark — is created containing items such as the company logo and disclaimers, and then the skin is set as the default for all reports, no user can remove these elements from their individual reports. Such an approach is useful at companies in which all correspondence and reporting must bear the skin. However, the approach does not work well if there are multiple logos or subsidiaries that require the ability to modify placement or style of these elements.

To modify Web Intelligence configuration, take the following steps:

1. Navigate to your SAP BusinessObjects 4.x install files:

• <install drive>:Program Files (x86)SAP BusinessObjectsTomcat6webappsBOEWEB-INFeclipsepluginswebpath.AnalyticalReportingwebWEB-INFclassesdefaultConfig.xml
• <install drive>:Program Files (x86)SAP BusinessObjectsSAP BusinessObjects Enterprise XI 4.0imagesWebIDefaultStyleSheet.css

1. Copy these files to create a backup and store it in a separate location
2. Open the defaultConfig.xml file
3. Modify the various report configuration elements listed within the file
4. Save and close.

See Figures 7 and 8 for pointers about where the values to be changed are located in the code.

Figure 7

Element coding within the defaultConfig.xml file

Figure 8

Element coding within the WebIDefaultStyleSheet.css file

Jennifer McKune

Jennifer McKune is a senior BI consultant with full life cycle data warehouse and business process experience across many industry sectors. She speaks globally and mentors on data warehousing, ETL, and business process and reporting best practices. She has more than eight years of IT experience in the following areas: data warehouse design; ETL; architecture and development; business process re-engineering; universes dashboards, and report design and implementation; security and business analysis and administration; technical training and mentoring; and project management.

If you have comments about this article or would like to submit an article idea, please contact the BI editor.

You may contact the author at Jennifer.McKune@decisionfirst.com.

If you have comments about this article or publication, or would like to submit an article idea, please contact the editor.