Overview

In SAS Web Report Studio 4.2 and later, you can use prompted filters that have been created in OLAP-based information maps. This sample explains how to perform the following tasks:

• Use SAS^® Information Map Studio 4.2 (or later) to create a prompted filter for an OLAP member.
• Use the prompted filter in a report.

Note: In SAS Web Report Studio, you also can use non-prompted filters that are available in OLAP-based information maps.

As the input table for the cube, this sample uses the SASHELP.PRDSAL2 table. Click the Full Code tab for PROC OLAP code that you can use to build this cube.

Create the prompted filter

1. In SAS Information Map Studio, add all of the data item in the PRDSAL2 cube to the Information Map Contents pane.



2. Select Insert ► New Filter.
3. In the New Filter dialog box, complete these steps:
1. Name the filter.
2. Select a data item, such as Product Hier, and select to Prompt user for value(s). Only category data items are available.

   Note: An OLAP filter is applied only to the members of the cube that are available via the selected category data item. Before generating the result set for a query, the cube’s measures are reaggregated based on that subset of members.

3. For the Condition, select either Is equal to or Is not equal to, which are the only two conditions available for OLAP.
4. Click New to open the New Prompt dialog box.
4. On the General tab, complete these steps:
1. Name the prompt.
2. In the Display text box, enter instructions for users. A description is optional, especially if your display text is self-explanatory.

Here is what your prompt might look like at this point:



5. Click the Prompt Type and Values tab, and complete these steps:

Note: On this tab, the values for Method for populating prompt and Data source are preset.

1. Accept the only option for Prompt type, which is OLAP member.
2. For the Number of values, this sample uses Single value.
3. Select the data item to use in the filter. In this sample, Product Hier is selected.
4. (Optional) Provide a default value.

Here is what your prompt might look like at this point:



5. Click OK to return to the New Filter dialog box.
6. Click OK to exit the dialog box.
6. As a best practice, select Tools ► Run a Test Query and make sure that your prompt is working as expected.
7. Save the information map, and exit SAS Information Map Studio.

Use the prompt in SAS Web Report Studio

1. Log on to SAS Web Report Studio, select the information map that you just created, and insert a crosstabulation table into the layout.
2. The filter is not enabled by default. In the Section Data pane, select Options ► Section Filters, and then select the prompted filter.



3. Click OK, and then click View.

The prompt window will look similar to this, with the default value (if you specified one) selected. The display text appears above the prompt box.



4. (Optional) You can click the icon to browse for values at a specific level. You also can click the icon next to the prompt box to search for values. Here is an example of searching for values that start with specific characters.

View the results

For this sample, CHAIR is selected as the prompt value. Click View Report to see the following results.

Additional Documentation

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

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

For more information about PROC OLAP, see The OLAP Procedure.

---

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.

Before submitting this code, these tasks must be done:

• The PRDSAL2 table and its library (in this sample, SASHELP) must be registered in metadata.

Note: For information about registering libraries and tables, see Establishing Connectivity to a Library of SAS Data Sets.

• The LIBNAME, PROC OLAP, and METASVR statements must be modified to use the correct values for your environment.

---

LIBNAME sashelp BASE "your-sas-path\core\sashelp" ;

 PROC OLAP
           Data=sashelp.PRDSAL2
           DrillThrough_Table=sashelp.PRDSAL2
           cube=PRDSAL2
           Path="your-path"
           Workpath="your-path"
           Description="PRDSAL2"
          ;
 
 METASVR host="your-host-name" port=8561 protocol=bridge userid="your-userid"
    pw="your-password"
    repository="Foundation"
    olap_schema="SASApp - OLAP Schema";

 DIMENSION Prod hierarchies=(Prod ) 
          CAPTION='Product Dimension'
          SORT_ORDER=ASCENDING ;

          HIERARCHY Prod ALL_MEMBER='All Prod' 
          levels=( PRODTYPE PRODUCT ) 
          CAPTION='Product Hier'
          DEFAULT
          ;

          LEVEL PRODUCT 
          CAPTION='Product' 
          SORT_ORDER=ASCENDING
          ;

          LEVEL PRODTYPE 
          CAPTION='Product Type' 
          SORT_ORDER=ASCENDING
          ;


 DIMENSION Geo hierarchies=(Geo ) 
          CAPTION='Geo'
          TYPE=GEO SORT_ORDER=ASCENDING ;

          HIERARCHY Geo ALL_MEMBER='All Geo' 
          levels=( COUNTRY COUNTY STATE ) 
          CAPTION='Geo'
          DEFAULT
          ;

          LEVEL STATE 
          CAPTION='State/Province' 
          SORT_ORDER=ASCENDING
          ;

          LEVEL COUNTY 
          CAPTION='County' 
          SORT_ORDER=ASCENDING
          ;

          LEVEL COUNTRY 
          CAPTION='Country' 
          SORT_ORDER=ASCENDING
          ;


 DIMENSION Time hierarchies=(Time ) 
          CAPTION='Time'
          TYPE=TIME SORT_ORDER=ASCENDING ;

          HIERARCHY Time ALL_MEMBER='All Time' 
          levels=( YEAR QUARTER MONTH ) 
          CAPTION='Time'
          DEFAULT
          ;

          LEVEL MONTH 
          CAPTION='Month' 
          SORT_ORDER=ASCENDING
          ;

          LEVEL QUARTER 
          CAPTION='Quarter' 
          SORT_ORDER=ASCENDING
          ;

          LEVEL YEAR 
          CAPTION='Year' 
          SORT_ORDER=ASCENDING
          ;

 MEASURE PREDICTAVG
          STAT=AVG
          COLUMN=PREDICT
          CAPTION='Average PREDICT'
          FORMAT=DOLLAR12.2
          DEFAULT
          ;

 MEASURE ACTUALAVG
          STAT=AVG
          COLUMN=ACTUAL
          CAPTION='Average ACTUAL'
          FORMAT=DOLLAR12.2
          ;

 MEASURE ACTUALSUM
          STAT=SUM
          COLUMN=ACTUAL
          CAPTION='Sum of ACTUAL'
          FORMAT=DOLLAR12.2
          ;

 MEASURE PREDICTSUM
          STAT=SUM
          COLUMN=PREDICT
          CAPTION='Sum of PREDICT'
          FORMAT=DOLLAR12.2
          ;

 AGGREGATION PRODUCT
             PRODTYPE
             STATE
             COUNTY
             COUNTRY
             MONTH
             QUARTER
             YEAR
             / NAME='DEFAULT'
             ;
 
 FORMAT PRODUCT $CHAR10.;
 FORMAT PRODTYPE $CHAR10.;
 FORMAT STATE $CHAR22.;
 FORMAT COUNTY $CHAR20.;
 FORMAT COUNTRY $CHAR10.;
 FORMAT MONTH MONNAME3.;
 FORMAT QUARTER 8.;
 FORMAT YEAR 4.;

 RUN;

---

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 shows you how to use SAS® Information Map Studio to create a prompted filter for an OLAP member that can then be used in SAS® Web Report Studio.

Type:	Sample
Topic:	Data Management ==> Data Sources ==> Cubes Query and Reporting ==> OLAP (Online Analytical Processing) Query and Reporting

Date Modified:	2010-06-03 12:31:59
Date Created:	2010-06-03 10:15:13

Operating System and Release Information

Product Family	Product	Host	Product Release		SAS Release
			Starting	Ending	Starting	Ending
SAS System	SAS Information Map Studio	Windows Vista for x64	4.2		9.2 TS2M0
		Windows Vista	4.2		9.2 TS2M0
		Microsoft Windows XP Professional	4.2		9.2 TS2M0
		Microsoft Windows Server 2008 for x64	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 for x64	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Standard Edition	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Enterprise Edition	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Datacenter Edition	4.2		9.2 TS2M0
		Microsoft® Windows® for x64	4.2		9.2 TS2M0