Use Excel Workbooks for Attractive, Easily Maintained Reporting Front Ends

  • by Simon Ferres, BW Team, Barclays Capital
  • October 1, 2004
Sometimes, the standard BW options for supplying your business with reporting tools just aren't enough. Additional reporting power is available from Excel worksheets, which when used in conjunction with BW queries result in graphically impressive reporting front ends that are simple to maintain.
Key Concept
Solutions workbooks are Excel spreadsheets linked to BEx Analyzer queries via Visual Basic that are used for a front end. Via BEx Analyzer, solution workbooks provide for reporting that combines data from multiple ad hoc queries from the same (or different) InfoProviders in a solution that end users can maintain themselves. They also can be employed for ad hoc reporting that combines BW data sources with offline or other non-BW sources of data, which don’t adhere to the master data and metadata stored in BW.

BW offers a substantial array of features in its so-called “presentation layer” so you can create versatile, intelligent, and graphically impressive Web and Business Explorer (BEx) front-end tools. Sometimes, however, your reports need more than what the BEx Analyzer alone can provide. A simple remedy to the dilemma is solution workbooks, which combine BEx queries with Microsoft Excel worksheets for business intelligence reporting.

The solution workbook uses BEx Analyzer queries linked via Visual Basic functions to an Excel worksheet used as a front sheet. Its simple design and construction enable the BW developer and more technically able end users to link multiple BEx queries and other sources of data within Excel for BW reporting.

I will help you decide whether BW solution workbooks are right for you and your end users. Then, I’ll walk you through the development process for solution workbooks. In a future issue of BW Expert, I’ll show you how to maintain your solution workbooks after you’ve set them up for your business reporting.

Note that while developing solution workbooks is not complicated, it requires basic knowledge of Visual Basic. In addition, you need to know basic Excel functionality such as using logic to add formulas to a workbook.

Simon Ferres

Simon Ferres is a manager on the BW team at Barclays Capital, London, where he has worked since 1999. He is jointly responsible for the ongoing development of the BW installation and providing the bank's BI solution for a number of core business areas including finance, human resources, sales, and investment banking. Prior to working at Barclays Capital, Simon worked as a graduate trainee at Gillette UK.

See more by this author


No comments have been submitted on this article. 

Please log in to post a comment.

To learn more about subscription access to premium content, click here.