Overview

Users often create calculated members using existing members of a cube, and, typically, they want to display both stored and the calculated members in the same crosstabulation table in SAS Web Report Studio. However, this functionality is not readily available because the query can contain only one category or hierarchy data item from each dimension. As a workaround for this limitation, this sample explains how to use the Hierarchize function to create a new data item that uses the stored members of the cube and the calculated member. This sample uses a cube based on the SASHELP.PRDSALE data set and uses a calculated member for North America, which includes Canada and U.S.A.

Add the calculated data item to the information map

After you have created your calculated member and it is available in metadata (in SAS^® 9.1.3, you must refresh the cube to make the member available), you can create or update an information map that uses the member. In this sample, you create a new data item that uses the stored members of the cube and the calculated member North America.

Complete the following tasks in SAS Information Map Studio.

1. Select Insert ► New Data Item to open the Data Item Properties dialog box.
2. Click Edit to enter the expression for the new data item. Make sure to change the Type to OLAP Category. Select Set Functions under Categories to select the Hierarchize function.

   

3. After you insert the Hierarchize function into the Expression Text box, you can use the Data Sources tab to insert individual stored members of the cube into the expression. For this sample, the expression includes the dimension name (GeoDim) and the children function to include all members that are stored in the dimension.

   

4. Use the Data Sources tab to insert the calculated member (North America). Separate each member in the expression with a comma and enclose everything inside of the parentheses within curly braces { }.

   

5. Save the information map.

Use the new data item in SAS Web Report Studio

In SAS Web Report Studio, select the information map that includes the new data item (Geography and North America Member). You cannot include any other data items that reference the same dimension. So, in this sample, you cannot include the Geography Hierarchy along with the data item Geography and North America Member.

Insert a crosstabulation table, and display the results, which include both the stored members and your calculated data item (North America) in the same table.

Tip: If you need your calculated member to display in a different order than the bottom (which is default), you can create a filter to control the order in which the members are displayed. The order in which you include the members in the filter definition controls the order in which they are displayed in the table. In this example, Canada was added first, then U.S.A., then North America, then Germany.

Additional Documentation

For more information about calculated members in SAS 9.2, see Specifying Calculated Members and Measures.

For more information about SAS Information Map Studio, see the SAS Information Map Studio documentation page.

For more information about SAS Web Report Studio, see the SAS Web Report Studio documentation page.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

---

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

This sample explains how to use the HIERARCHIZE function to display stored members and calculated members from the same dimension in the same crosstabulation table.

Type:	Sample
Topic:	Query and Reporting ==> OLAP (Online Analytical Processing)

Date Modified:	2010-06-18 16:03:55
Date Created:	2010-06-07 14:38:15

Operating System and Release Information

Product Family	Product	Host	SAS Release
			Starting	Ending
SAS System	SAS Web Report Studio	Windows 7 Ultimate x64
		Windows Millennium Edition (Me)
		Windows 7 Ultimate 32 bit
		Windows 7 Professional x64
		Windows 7 Professional 32 bit
		Windows 7 Home Premium x64
		Windows 7 Home Premium 32 bit
		Windows 7 Enterprise x64
		Windows 7 Enterprise 32 bit
		Microsoft Windows XP Professional
		Microsoft Windows Server 2008 for x64
		Microsoft Windows Server 2008
		Microsoft Windows Server 2003 for x64
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows NT Workstation
		Microsoft Windows 2000 Professional
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 95/98
		Microsoft® Windows® for x64
		z/OS
		Windows Vista
		Windows Vista for x64
		64-bit Enabled AIX
		64-bit Enabled Solaris
		HP-UX IPF
		Linux
		Linux for x64
		Solaris for x64