A Simpler, Cleaner Approach to Trend Reporting in BW Using Variables

  • by Robert Oliver, Investment Banking IT
  • May 15, 2003
When building reports that show past and projected results, query designers might start by creating a large column structure. This approach might prove cumbersome. The author shows a more flexible method using structures and a little user input via simple variables.


Your company wants a report that shows all sales (value or volume) over a range of years, maybe for a product, a brand, or a market. This report shows actual sales trends for comparison against forecast and plan, enabling the business to spot growth areas or where products are stagnating. The report might look something like this:

  • First column starts from early history (10 years ago to last year)
  • Second column shows the current year (year to date, plus plan or forecast for remainder of year)
  • Third column shows the short-term plan for next year
  • The last few columns show strategic planning information for two to five years into the future

A query designer might start building this report by thinking about a large column structure. This would work, but it could result in changes every year as another year of history means adding columns. Alternatively, you could use a structure requiring complex user entry of variables to add flexibility, but thereby reducing user friendliness.

There is another way. I will demonstrate an approach that uses a combination of structures and characteristics in a column, providing a flexible report that is easy to develop. It also requires minimal user input via simple variables.

Robert Oliver

Robert Oliver is an SAP BI developer working for an investment bank in London. He has been working with SAP R/3 since 1994 and SAP NetWeaver BW since 1999. Since 2005 he has been working with the planning and consolidation systems provided by SAP. Prior to his current role he was an end user and a consultant.

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

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.