Expand +



Excel 2007 PivotTables provide enhanced interactive reporting and analytics options

by The Tip Doctor

June 22, 2010

Microsoft has made it much easier to design a PivotTable and Pivot Chart using SAP NetWeaver BW and Xcelsius. The screen shot below highlights a number of improvements in Excel 2007 PivotTables. The numbers in the list correspond to the numbers in the image.

  1.  Making a connection to an InfoCube, MultiProvider, or BEx query InfoCube automatically populates the Field List. You can see that multiple hierarchies for Calendar Year/Month are displayed, as is an external Product Hierarchy. One of the most significant improvements in Excel 2007 PivotTables is that ability to display multiple dimensions in the Fields List.
  2. Instead of dragging and dropping fields onto a grid, as in Excel 2003, users can drag and drop them from the fields list to the rows, columns, filters, and values section shown on the right of the PivotTable Field List dialog box.
  3. Every time you drag a new field into the rows, columns, filters, or value fields, Excel 2007 executes an MDX query to the SAP NetWeaver BW system. You can turn this off by checking the Defer Layout Update check box at the bottom of the Field List. When you finish selecting fields, click the Update button to produce the PivotTable. You have to uncheck the Defer Layout Update check box if you want to make changes to the pivot table.
  4. With the Product Hierarchy on the rows and the YEAQUAMON hierarchy on the columns, a user has the option to drill down and drill up from the levels of the hierarchy, shown with the plus (+) and minus (-) indicators.
  5. Excel 2007 automatically provides grand totals for the columns — you can turn this feature on and off.
  6. Populating the Report Filter section of the Field List places the selected dimensions, Region Code, and Distribution Channel as drop-down boxes.

This excerpt was taken from Larry Sackett’s, “SAP NetWeaver BW and XcelsiusExcel 2007 PivotTables Provide Enhanced Interactive Reporting and Analytics Options” (BI Expert, Update 1, 2010). 

html_removed html_removed html_removed html_removed html_removed// bw + bsl && x + aw - ah / 2 - cw >= bsl ) { = x + aw - ah / 2 - cw; } else { = x + ah / 2; } if (y + ch + ah / 2 > bh + bst && y + ah / 2 - ch >= bst ) { = y + ah / 2 - ch; } else { = y + ah / 2; } = "visible"; } } } function msoCommentHide(com_id) { if(msoBrowserCheck()) { c = document.all(com_id); if (null != c && null == c.length) { = "hidden"; = -1000; = -1000; } } } function msoBrowserCheck() { ms = navigator.appVersion.i ndexOf("MSIE"); vers = navigator.appVersion.substring(ms + 5, ms + 6); ie4 = (ms > 0) && (parseInt(vers) >= 4); return ie4; } if (msoBrowserCheck()) { document.styleSheets.dynCom.addRule(".msocomanchor","background: infobackground"); document.styleSheets.dynCom.addRule(".msocomoff","..."); document.styleSheets.dynCom.addRule(".msocomtxt","..."); document.styleSheets.dynCom.addRule(".msocomtxt","position: absolute"); document.styleSheets.dynCom.addRule(".msocomtxt","top: -1000"); document.styleSheets.dynCom.addRule(".msocomtxt","left: -1000"); document.styleSheets.dynCom.addRule(".msocomtxt","width: 33%"); document.styleSheets.dynCom.addRule(".msocomtxt","background: infobackground"); document.styleSheets.dynCom.addRule(".msocomtxt","color: infotext"); document.styleSheets.dynCom.addRule(".msocomtxt","border-top: 1pt solid threedlightshadow"); document.styleSheets.dynCom.addRule(".msocomtxt","border-right: 2pt solid threedshadow"); document.styleSheets.dynCom.addRule(".msocomtxt","border-bottom: 2pt solid threedshadow"); document.styleSheets.dynCom.addRule(".msocomtxt","border-left: 1pt solid threedlightshadow"); document.styleSheets.dynCom.addRule(".msocomtxt","padding: 3pt 3pt 3pt 3pt"); document.styleSheets.dynCom.addRule(".msocomtxt","z-index: 100"); } // ]]>html_removed


by Larry Sackett

*** There are two tip option that I picked out of this article, I wasn’t sure if the first one was too short. –LC ***



The Simba ODBO provider is part of your SAP NetWeaver BW license. There is no additional cost for using MS Excel 2007 with SAP NetWeaver BW. You only have to make sure you have the latest Simba ODBO drivers installed on the client machine (available via a BI add-on for the SAP GUI) and that you have the most recent SAP GUI front-end patches and SAP NetWeaver BW service packs. You need SAP GUI 7.10 or higher.

..."msoCommentShow('_anchor_1','_com_1')" onmou seout="msoCommentHide('_com_1')" href="#_msocom_1" language="..." name="_msoanchor_1">[JMH1] 


*** or ***..."msoCommentShow('_anchor_2','_com_2')" onmouseout="msoCommentHide('_com_2')" href="#_msocom_2" language="..." name="_msoanchor_2">[JMH2] 


Microsoft has made it much easier to design a PivotTable and Pivot Chart using SAP NetWeaver BW and Xcelsius. Figure 1 highlights a number of improvements in Excel 2007 PivotTables. The numbers in the list correspond to the numbers in Figure 1.

..."msoCommentShow('_anchor_1','_com_1')" onmouseout="msoCommentHide('_com_1')">

 [JMH1]I think might be a bit short for a tip.

..."msoCommentShow('_anchor_2','_com_2')" onmouseout="msoCommentHide('_com_2')">

 [JMH2]Do we need introductory text? If so, I’ve added some from the article J

An email has been sent to:

More from SAPinsider


Please log in to post a comment.

No comments have been submitted on this article. Be the first to comment!