Rank Columns / Decision Support System

Rank Columns are used to construct a Decision Support System query and to report the results. A rank column reports percentages. The Manifold Decision Support System (DSS) provides a way to select records using flexible criteria with queries. DSS results are reported as percentage values from 0 to 100 percent within a rank column in a table. Creating a rank column creates a DSS query, the results of which are reported in the column.


In this example we will open the Order Details table and find all "luxury" goods. We shall define luxury goods to mean those goods that are both "expensive" and "rare". Expensive goods, in turn, are defined to be those goods for which the unit price is "high" and rare goods are those goods for which quantity is "low."


Step 1: Import and open the table


Import the Order Details table from the Nwind.mdb sample database.




In this example we show only the Unit Price and Quantity fields for brevity.


Step 2: Add a rank column




Choose Table - Add - Rank Column to add a rank column.




This command launches the Add Rank Column dialog, which is used to add criteria and to create the query that will generate this rank column. Begin by changing the name to Luxury. This will be the name of the rank column as it will appear in the table.


Step 3: Add criteria


We will add two criteria.


images\btn_new_thing.gif Click on the New button to add the first criterion.




In the Add Criterion dialog, enter Expensive as the name of this criterion. Choose Unit Price for the column and High for the Type of criterion. The pane on the right shows the criterion curve that is automatically generated by DSS based on the data in the table. In essence, we've defined a criterion to find expensive goods by finding unit prices that are high. Press OK. (See the Add Rank Column Dialog topic for detailed instructions on the Add Criterion dialog.)




Back in the Add Rank Column dialog, the result is a new criterion that is saved in the hierarchical diagram under the name of the column (field) it uses, Unit Price. If we were to expand the Unit Price level we would see a criterion called Expensive underneath it.


images\btn_new_thing.gif Click on the New button once more to add another criterion.




For this criterion we will use the name Rare. We choose Quantity for the field (column) to use and Low for the type. The pane on the right shows the criterion curve generated by DSS for records that have low quantity values. This is a way of finding "rare" goods in that their quantities are low. Press OK.




Back in the Add Rank Column dialog we now have two fields in use with for criteria, the Quantity and the Unit Price fields.


Step 5: Drag and drop criteria to create a rank query


We create rank queries by dragging and dropping criteria from the upper pane in the Add Rank Column dialog to the lower pane. We begin by expanding the levels in the upper pane so we can see the criteria from which we can choose.




We drag and drop the Rare criterion from the upper pane to the lower pane.




The Rare criterion appears in the rank query pane under Criteria. So far, we've built a query that selects all records that are "rare", that is, for which quantity is "low". To make this a more interesting query we will drag and drop the Expensive criterion from the upper pane into the lower rank query pane.




The Expensive criterion now joins the Rare criterion in the rank query pane. By default, new criteria are added using Boolean OR to create a combined query. So far, our query will find all goods that are either rare or are expensive. We would like to amend this query so it finds those goods that are both rare and expensive.




To do so, we double click into the Junction field and change it to And.




The new rank query is now equivalent to "Select all records that are Rare and Expensive." So far, so good; however, we would like to find only those goods that are very expensive. We can "nudge" a criterion to accentuate it or de-emphasize it by using the Hedge column.




Double clicking into the Hedge column for the Expensive criterion we can choose very as the hedge.




The result is a query that finds all records that are Rare and very Expensive. [Note: The Not field allows us to prefix each criterion with a Not Boolean modifier. So, we could find records that are Rare and Not very Expensive if desired.]


Press OK to add the rank column using this query to the Order Details table.


Step 6: Admire result


A new column called Luxury appears in the Order Details table. It is a rank column and formatted by default to show percentages with two digits of accuracy after the decimal point. A gray background indicates it is a special column.




We can click on the column head to sort the tables by the values in the Luxury column. If we do so, we can see that those records given a 100% ranking all have very high prices and low quantities. Further down in the table are those records with not quite so very expensive prices or perhaps slightly greater quantities. At the very end of the table are those products that are cheap and abundant.