Overview

SAS Web Report Studio does not have a built-in feature that subsets all sections of a report based on prompt values that are entered only once (sometimes called global prompting). For example, if you enter a value for a date prompt in section 1, that value is not used to automatically subset all other sections in the report.

As a solution, this sample presents two small stored processes that you can use to seamlessly pass prompt values to all sections of your report. In this way, all sections are automatically subset by the same criteria. The stored process can even be used to subset other reports.

Prerequisites

In addition to knowing how to create a report in SAS Web Report Studio, this sample assumes that you know how to submit SAS code, register a stored process in metadata, and create an information map.

This sample is intended for advanced users.

About the sample

To illustrate how the stored processes work, this sample explains how to create a prompted report with two sections. The prompts are for year and month. After the year and month are entered, both sections of the report are subset by the specified values.

About the stored processes

This solution uses two stored processes:

• One stored process is used to store the prompt values in a SAS table that contains only one row (so performance is fast). The table is stored in the WRSTEMP library, and it is unique for each user because each user's ID is concatenated to the table name.
• The second stored process retrieves the values from the SAS table.

About the sample data

As the input table for the information map, this sample uses the PRDSALE table, which is one of the sample tables included with SAS. For information about registering libraries and tables, see the SAS 9.2 Intelligence Platform: Data Administration Guide.

Considerations

• This sample only works in conjunction with prompts on the stored process.
• Although you can use report linking to pass values from one section to another, this sample has the following advantages: it does not require you to set up linking, each section does not need to have a prompt, and users do not have to click a link to pass prompt values.
• If users bypass the prompted section, the selected section will show results based the last entered prompt values.

Save and register the SetDate stored process

1. Save the following code as SetDate.sas to your SAS server and note the location. You must know the location in order to register the stored process.

/* ****************************************************************************\ * Program: SetDate.sas * * Description: This program prompts the user for year and date and then saves the * values into wrstemp.<yourid>date table. Macro, GetDates is used to retrieve * the values from the table. WRSTEMP was chosen just as an example. Any * library that all users have access to could be used. * * Input: Prompt #1 year * Prompt #2 month * * ******************************************************************************* */ *ProcessBody; %stpbegin; %global year month; options symbolgen; %let _metauser = %scan(%sysfunc(getoption(METAUSER)),1,'@'); data wrstemp.&_metauser.date; length year month 8; year = &year; month = &month; run; %stpend;

2. In SAS Management Console, click the Folders tab.
3. Select SAS Folders ► Shared Data ► StoredProcesses (or another shared location).
4. Right-click on StoredProcesses and select New Stored Process to launch the New Stored Process wizard.
5. Name the stored process and enter any optional description and keywords. Click Next.
6. On the Execution page, provide the following information:
1. For the SAS Server, select the SAS Workspace Server on which to run the stored process.
2. For the Source code repository, select from the existing list, or click Manage to select the physical location where you stored the sample stored process (e.g., c:/storedprocesses).
3. For the Source file, enter the full name of the stored process. In this sample, the stored process is named SetDate.sas.
4. Do not select any Results type. Click Next.
7. On the Parameters page, create prompts for Year and Month.
• For the Year prompt, set the following properties: Numeric, User selects values from a static list, and Single Value. Click the Add button to add 1993 and 1994 as the values in the selection list. Select 1993 as the default value.



• For the Month prompt, set the same properties, and then click Add to add, for sample purposes, the months Jan, Feb, and March as the formatted values, and 1, 2, and 3 as the unformatted values. Select Jan as the default value.



Here is what the Parameters tab might look like at this point.



8. Click Next and then Finish to exit the wizard.

Note: If you are creating the stored process in SAS 9.3, then, in SAS Management Console, right-click on the stored process and select Make Compatible. Using the Make Compatible option is the easiest way to ensure that stored processes created with SAS 9.3 can be used with information maps.

Save and register the GetDate stored process

1. Save the following code as GetDate.sas to your SAS server and note the location. You must know the location in order to register the stored process.

/* **************************************************************************** * Program: GetDate.sas * * Description: This program grabs the year and date from wrstemp.<userid>date table and * sets them into two macro variables: year and month. * * ******************************************************************************* */ *ProcessBody; %stpbegin; %global year month; %let _metauser = %scan(%sysfunc(getoption(METAUSER)),1,'@'); data _NULL_; set wrstemp.&_metauser.date; call symput("year",year); call symput("month",month); run; data _NULL_; put "Year= &year"; put "month= &month"; run; %stpend;

2. To register GetDate.sas, follow steps 2 through 8 in the previous section, skipping step 7. This stored process does not include parameters.

Note: If you are creating the stored process in SAS 9.3, then, in SAS Management Console, right-click on the stored process and select Make Compatible. Using the Make Compatible option is the easiest way to ensure that stored processes created with SAS 9.3 can be used with information maps.

Create the prompted information map that uses the SetDate stored process

1. In SAS Information Map Studio, from the Resources - Application Servers pane, insert PRDSALE into the information map, and add the columns to the Information Map Contents pane.
2. Still on the Resources - Application Servers pane, change the Show selection list to be Stored processes, and then select the SetDate stored process.
3. Open the properties for the Year data item, and change it from a measure to a category.
4. Open the properties for the Month data item, and edit the expression so that it looks like this: month(<<prdsale.month>>). Then, on the Classifications, Aggregations, Formats tab, change the Format Type to Numeric.
5. Select Insert ► New Filter and name the filter Month Year Filter.
6. From the Data item list, select Year. From the Value(s) list, select Use an expression, and then click Create Expression. Enter the following into the Expression text box: &year. Click OK to acknowledge the warning message that appears.
7. Back on the filter Definition tab, click Combinations and then Add.
8. From the Data item list, select Month. From the Value(s) list, select Use an expression, and then click Create Expression. Enter the following into the Expression text box: &month. Click OK to acknowledge the warning message that appears.
9. Back on the filter Definition tab, click Combinations and then Add.

This is what your filter should look like at this point.



10. To make sure the data is subset correctly, run a test query on the information map that includes the filter.
11. Save the information map as Prdsale with Year Month Prompt.
12. Select Edit ► Properties ► Information Map, and click the General Prefilter tab. Assign Month Year Filter to the PRDSALE table. This setting ensures that the filter is always applied when the information map is used.
13. Save the information map again but do not close the information map.

Create the information map that uses the GetDate stored process

1. From within the Prdsale with Year Month Prompt information map, select Save As and name the new information map Prdsale Map.
2. Replace the SetDate stored process with the GetDate stored process.
3. Save the information map.

Creating the multi-section report

1. Login to SAS Web Report Studio with a user who is authorized to create new reports, and select New report.
2. In the Section Data pane, select Options ► Select Data.
3. Click Change Source and select the Prdsale with Year Month Prompt Map.
4. Insert a list table into the report.
5. Select Insert ► New Section, enter a name, and accept the default of using new data.
6. In the new section, in the Section Data pane, select Options ► Select Data.
7. Click Change Source and select the Prdsale Map.
8. Insert a list table into the report.
9. Save the report as Prdsale Report.

Testing the report

From the Table of Contents, select Section1, and then click the View tab. When you are prompted for a year and month, accept the defaults or enter new values, then click View Report. Results in both report sections should be subset by the values that you entered.

Figure 1. Stored process prompts appear

Figure 2. Section1 displays results for March 1993

Figure 3. Section2 is automatically subset by March 1993

Additional Documentation

For more information about stored processes, see SAS 9.2 Stored Processes: Developer's Guide.

For more information about SAS Management Console, see the SAS Management Console documentation page.

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 presents two small stored processes that you can use to seamlessly pass prompt values to all sections of a SAS Web Report Studio report. In this way, all sections are automatically subset by the same criteria. The stored process can even be used to subset other reports.

Type:	Sample
Topic:	Query and Reporting ==> Interactive Querying

Date Modified:	2010-09-07 14:09:08
Date Created:	2010-09-01 11:34:35

Operating System and Release Information

Product Family	Product	Host	Product Release		SAS Release
			Starting	Ending	Starting	Ending
SAS System	SAS Web Report Studio	Microsoft® Windows® for x64	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Datacenter Edition	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Enterprise Edition	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 Standard Edition	4.2		9.2 TS2M0
		Microsoft Windows Server 2003 for x64	4.2		9.2 TS2M0
		Microsoft Windows Server 2008 for x64	4.2		9.2 TS2M0
		Microsoft Windows XP Professional	4.2		9.2 TS2M0
		Windows Vista	4.2		9.2 TS2M0
		Windows Vista for x64	4.2		9.2 TS2M0
		64-bit Enabled AIX	4.2		9.2 TS2M0
		64-bit Enabled Solaris	4.2		9.2 TS2M0
		HP-UX IPF	4.2		9.2 TS2M0
		Linux for x64	4.2		9.2 TS2M0
		Solaris for x64	4.2		9.2 TS2M0