Resources

SAS® AppDev Studio™ 3.0 Developer's Site

Traffic Lighting Example   About It See It Build It  

In the following example, you will create an XRANGE for the PRDMDDB multidimensional database that will enable traffic lighting and highlight key sales figures for Furniture and Office Supplies in Canada, Germany, and the USA. This will help you to determine where to focus your business efforts in the future based on the profitability of the various areas.

The key sales figures that you are interested in are those with sums below $100,000, sums above $145,000, minimums of $5, and minimums of $10.

Preparing the XRANGE:

  1. Start SAS.

  2. Create a new attribute dictionary by following these steps:

    1. Enter reposmgr on the SAS command line to open the Repository Manager window.

    2. In the Repository Manager window, click Attribute Dictionary to open the Dictionary Registration window.

    3. Click New to open the Register Dictionary (New) window.

    4. Enter the following information for the new attribute dictionary:

      Location Information Value
      Dictionary Name NEWATTR
      Library SASUSER
      Description SASUSER MDDB Example Sales Data

      Click OK to close the Register Dictionary (New) window.

    5. Click Close to close the Dictionary Registration window, and then click Exit to close the Repository Manager window.

  3. Start SAS/EIS by entering EIS on the SAS command line.

  4. In the EIS Main Menu, double-click Setup to open the Setup window.

  5. In the Setup window, select Search path (under Environment) to open the Search Path dialog box. In the Search Path dialog box, select Attribute search path to open the Attribute Dictionary Search Path window.

  6. In the Attribute Dictionary Search Path window, make sure that the path for your SASUSER appears in the Path field. If not, select the down arrow beside the Path field and select SASUSER.

    In the Available list box, select (by double-clicking) the SASUSER MDDB Example Sales Data attribute dictionary that you just created to move it to the Selected list box. Make sure that the Use search path check box is selected. Click OK to close the Attribute Dictionary Search Path window.

  7. Click Goback to close the Search Path dialog box. Click Goback to close the Setup window and return to the EIS Main Menu.

  8. In the EIS Main Menu, double-click Metabase to open the Metabase window.

  9. In the Metabase window, make sure that SASHELP appears in the Repository field. If it does not, select the down arrow beside the field and select SASHELP. In the Table list box, select MDDB Example Sales Data. Click Copy below the Tables list box to open the Copy a Metabase Registration window.

  10. In the Copy a Metabase Registration window, select the down arrow next to the Repository field and select SASUSER. Next, enter SASHELP.PRDMDDB in the Name field and SASUSER MDDB Example Sales Data in the Description field.

    Note: This name must be the same as the original being copied or webEIS will not recognize the copied registration.

    Click OK to copy the registration and return to the Metabase window. In the Metabase window, select the down arrow next to the Repository field and then select SASUSER. You should see your copied registration listed in the Tables list box.

Creating the XRANGE variable:

  1. While in the Metabase window, select Edit [arrow] Attribute Dictionary from the SAS/EIS menu bar to open the Attribute Dictionary window.

  2. In the Attribute Dictionary window, select the down arrow beside the Attribute Dictionary field and then select the NEWATTR attribute dictionary you just created.

  3. If you do not have an XRANGE listed in the Attributes list box, click Add to open the Properties window. Enter the following values:

    Field Name Value
    Name XRANGE
    Applies to VARIABLN
    Description SASUSER XRANGE Variable
    Interactive method SASHELP.CLEATTR.XRANGE.FRAME

    Leave the remaining fields blank. Click OK to close the Properties window. Click Close to close the Attribute Dictionary window.

  4. In the Metabase window, make sure that SASUSER appears in the Repository field. Select SASUSER MDDB Example Sales Data, which is the table registration that you copied, in the Tables list box. Then click Columns below the Tables list box to open the Columns window.

  5. In the Columns window, select Actual Sales, if not already selected in the Columns list box. Click Add below the Attributes list box to open the Select Attributes window. Select XRANGE in the Attributes list box, and then click OK to open the Column Extended Range Entry dialog box.

  6. In the Column Extended Range Entry dialog box, enter SASUSER.XRANGE.MYXRANGE.SLIST in the empty field. Then click Edit.

    Note: The XRANGE must be an SLIST entry type.

    If the new XRANGE does not exist, you will see a SAS dialog box with this message: "Entry SASUSER.XRANGE.MYXRANGE.SLIST does not exist or could not be read." You can ignore this message. Click OK to continue and open the Extended Range Entries dialog box.

  7. In the Extended Range Entries dialog box, select SUM in the Statistics list box. Click Edit to open the Edit window. Enter the following values for Segment 1 of your XRANGE:

    Field Name Value
    Lower Value 0
    Upper Value 100000
    Foreground Color Blue
    Background Color Yellow
    Font Default
    Font Style Bold Italic
    Font Size 10
    Attribute None

  8. Use the scroll bar to page down to Segment 2, and enter the following values:

    Field Name Value
    Lower Value 145000
    Upper Value 500000
    Foreground Color Red
    Background Color Gray
    Font Default
    Font Style None
    Font Size 18
    Attribute None

    Click OK to close the Edit window.

  9. In the Extended Range Entries dialog box, select MIN in the Statistics list box. Click Edit to open the Edit window. Enter the following values for Segment 1 of your XRANGE:

    Field Name Value
    Lower Value 0
    Upper Value 5
    Foreground Color Magenta
    Background Color Cyan
    Font Default
    Font Style None
    Font Size 10
    Attribute None

  10. Use the scroll bar to page down to Segment 2, and enter the following values:

    Field Name Value
    Lower Value 10
    Upper Value 100
    Foreground Color Green
    Background Color White
    Font Default
    Font Style None
    Font Size 12
    Attribute None

    Click OK to close the Edit window.

  11. Click OK to close the Extended Range Entries dialog box. Click OK to close the Column Extended Range Entry dialog box. Click Close to close the Columns window. Click Close to close the Metabase window. Select File [arrow] Exit from the menu bar to close SAS.

Viewing your data using the XRANGE:

  1. Start webEIS.

  2. Make sure that the SAS IOM Spawner is running. If it is not running, select Start [arrow] Programs [arrow] SAS AppDev Studio [arrow] Services [arrow] SAS V9.1 [arrow] Start SAS V9.1 IOM Spawner.

  3. Enter Traffic Lighting in the Name field and Data in the First Section Name field. Click OK to create a new document.

  4. Connect to your local machine. Select the down arrow next to the Server Connection field, and then select your machine. In the Data Source field, select SASUSER MDDB Example Sales Data (SASUSER SASHELP.PRDMDDB). This is the MDDB that references the data that you created in the SAS/EIS session. Click OK to populate the Data tab of the Navigator (located on the left pane of the window).

  5. Drag and drop the visual component and arrange it as shown on the See It tab:

    Component Icon
    Multidimensional Table Table graphic

  6. Specify the data that you want to display in your table. Drag the Geographic hierarchy and drop it on the Rows item. Drag the Product Line hierarchy and drop it on the Columns item. Drag the Actual Sales analysis variable and drop it on the Measures item. Drag the Sum and Minimum statistics and drop them on the Actual Sales analysis variable. Select View [arrow] Refresh to load the data into the table.

    Notice how the cells are different colors, which correspond to the cell value. For Sum, values between 0 and 100,000 are highlighted in blue and yellow and values between 145,000 and 500,000 are highlighted in red and gray. Values between 100,000 and 145,000 are not highlighted because colors were not specified for that range of values. Save the document.

  7. Select View [arrow] Preview Mode to switch to Preview mode.

  8. Right-click in the table over Canada, Germany or USA to access the drill options (Expand, Collapse, Drilldown, and so on) from the pop-up menu. Try the different drill options and notice how the cells change colors as the values change.

  9. Save the document.

  10. Using the Data tab of the Navigator (located on the left pane of the window), drag Sales Lag and drop it on the Measures item, and then select View [arrow] Refresh. Notice that the cells for Sales Lag do not have XRANGE colors. This is because Actual Sales is the only analysis variable that was assigned an XRANGE.

Additional Practice:

Suppose that you want to look at the values for Sales Lag. Negative values for Sales Lag mean that sales did not do as well as predicted. You would like to highlight negative values in red. Any positive values for Sales Lag above 40 denote sales that went beyond the predicted values. You would like to highlight the values above 40 in green. Follow these instructions to create your XRANGE for Sales Lag.

  1. Close webEIS.

    Note: When you close the traffic lighting document, avoid saving it in order to preserve the original document.

  2. Start SAS. Then start SAS/EIS by entering EIS on the SAS command line.

  3. In the EIS Main Menu, double-click Metabase to open the Metabase window.

  4. In the Metabase window, make sure that SASUSER appears in the Repository field. Select SASUSER MDDB Example Sales Data, which is the table registration that you copied earlier in this example, in the Tables list box. Then click Columns under the Tables list box to open the Columns window.

  5. In the Columns window, select Sales Lag from the Columns list box. Then click Add under the Attributes list box to open the Select Attributes window. Select XRANGE and then click OK to open the Column Extended Range Entry dialog box.

  6. In the Column Extended Range Entry dialog box, enter SASUSER.XRANGE.MYXRANGE2.SLIST and click Edit.

  7. In the Extended Range Entries dialog box, select SUM in the Statistic list box. Click Edit to open the Edit window. Enter the following values for Segment 1 of your XRANGE:

    Field Name Value
    Lower Value -1000
    Upper Value 0
    Foreground Color White
    Background Color Red
    Font Default
    Font Style Bold
    Font Size 10
    Attribute None

  8. Use the scroll bar to page down to Segment 2, and enter the following values:

    Field Name Value
    Lower Value 40
    Upper Value 100000
    Foreground Color White
    Background Color Green
    Font Default
    Font Style None
    Font Size 10
    Attribute None

    Click OK to close the Edit window.

  9. Click OK to close the Extended Range Entries dialog box. Click OK to close the Column Extended Range Entry dialog box. Click Close to close the Columns window. Click Close to close the Metabase window. Select File [arrow] Exit from the menu bar to close SAS.

  10. Start webEIS.

  11. Open the Traffic Lighting document that you created in the first half of this example. Click OK.

    Use the Data tab of the Navigator (located on the left pane of the window) to drag Sales Lag and drop it on the Measures data item and drag Sum and drop it on Sales Lag. Select View [arrow] Refresh. Notice that the Sales Lag cells now use the XRANGE formatting that you set in SAS.