Step 1: Select Data Source Origin

The first step is to insert a new pivot widget into the sheet.

To do that:

1. Click on the New Widget toolbar item

2. From the sub menu, select Pivot Widget

Alternative way:

1. Right click over the canvas
2. From the popup menu, select Insert Widget menu item
3. From the sub menu, select Pivot widget

Step 2: Fill Widget with data

The second step is to populate the widget with data. We wish to view the Sales & Cost measures (Measures dimension) for each of the available product members (Product dimension)

To do that:

1. Drag the Product dimension from the Data Browser and drop it over the Rows Area inside the Widgets Data Layout Panel.

2. Drag the Measures dimension from the Data Browser and drop it over the Columns Area inside the Widgets Data Layout Panel.

3. Click on the Update button, inside the Widgets Data Layout Panel.

Explanation: Dragging the actual dimension (and not specific members/measures) will display all available members from that dimension inside the widget. In our case, the pivot will display all available products and all available measures.

Tip:

To select and use specific members of a given dimension from the Data Browser, expand (press the plus sign) the dimension and use the mouse + keyboard keys to select specific members

Examples on how to select specific members:

Left mouse click + [Ctrl] - adds a member into the selection

Left mouse click + [Shift] - adds a range of members into the selection

Step 3: Add Sub Totals to Pivot

The third step is to add Sub Totals to the products dimension.

To do that:

1 .Right click on the Product dimension inside the pivot

2. From the pop menu, select Sub Totals, then Sum

Note:

Sub Totals can be applied in the same manner on column dimensions

Tip:

You can have multiple Sub Totals of different aggregations (Sum, Avg, Min, Max, Count) per dimension

Step 4: Format Pivot

This step is about formatting the pivot. Let's start by formatting the headers of the pivot.

To do that:

1. Select all headers in the pivot by selecting the header cells (made up from the product field and the Sales & Cost measures),just like you do with spreadsheet cells.

2. Now you can apply formatting attributes to the currently selected cells in the pivot by clicking on the appropriate toolbar formatting buttons .

3. Sometimes, we wish to format long lists of members and we don't want to select all cells to do it. For that, we can use the pivot's span selection feature that allows you to select rows/columns and groups with a single click.

For example, to select all members in the product dimension (on rows) do the following:

• Move cursor over the upper edge of the product cell until you see an arrow sign that indicates a column selection.
• Click it and all members in the Product dimension will be selected.

Tip:

The Pivot widget supports other types of span selection such as rows, columns or groups selection.

For example, if you wish to select all members in the product dimension without the header, move & click the cursor between the member cells (Acer 2400, Acer 2500, ) in the product dimension until the cursor sign changes to span selection,

To discover over span selection options, just move cursor over various cells and click when the cursor changes to:

Span select members:

Span select values column:

Span select complete rows/partial rows of specific members:

Tip:

Use the Theme button, (located inside your formatting toolbar) to quickly give your Widgets a quick formatted and professional appearance. For example, the following images display the same pivot widget, with varying theme colors selected.

Step 5: Add Time members to Pivot

This step is about extending the pivot with months from the Calendar dimension.

To do that:

1.From the Data Browser, expand the Purchase Date (Calendar) dimension.

2.Drag the Months node from the Data Browser and drop it before the product item, inside rows area, inside the Widgets Data Layout Panel.

Explanation: As we can see, the month's level now aggregates every product over each of the available month members. If we scroll down the pivot, we will see the available products for each month, and their corresponding Sales and Cost measures. In addition, the previously added sub-totals (step 3) are now calculated over each month.

Tip:

You can also drag & drop the Month node directly on the pivot widget, before or after an existing dimension.

Step 6: Filter Pivot

This step filters for specific members in Product that have Sales > 200,000 over normal work days (Monday to Friday)

To do that:

1. Right click on the Product dimension cell inside the pivot

2 .From the pop menu, select Filter, then Apply NewFilter.

3. Inside the Filter dimension dialog, we wish to get all products with Sales > 200,000 over all working days combined.

To accomplish that, we must assign a specific scope to our measure. Instead of asking (Sales) > 200,000, we need to ask (Sales, [Working Days]) > 200,000, where [Working Days] = (Mon, Tue, Wed, Thu, Fri)

To do that:

3.1 Click on the click to define a scope area under the Sales measure.

3.1 Click on the Purchase date (Weekdays) dimension.

3.2 Right click on the Product dimension cell inside the

3.3 Inside the popup, expand the All Purchase date (Weekdays) member.

3.4 Select Monday till Friday by clicking on each member node.

3.5 Change the equal sign to the greater than(>) sign in the criteria box.

3.6 Change the default zero value to 200,000.

3.7 Click on the OK button in the Select input from Purchase date(Weekdays) popup menu.

3.8 Click on the OK button in the Assign Scope For Sales popup window.

Note:

To cancel the current selection in the popup, click on the clear button , or anywhere outside the popup window

Explanation: We can see that our pivot changed its products content. Notice that since we have months before the products, the filter will automatically calculate weekdays per month. If we had quarters or years before the products, then the filter would automatically take that into consideration and would apply sales of weekdays per quarter, or per year. This kind of filtering inside Prism is called context sensitive filtering, and it saves you lots of time when creating and using filters because as a filter author, you don't need to worry about personalizing the filter for each user, or each specific scenario.

Note:

If we had customers before the products, then the filter would return products that had 200,000 weekday sales per customer

Step 7: Rename fields and members

Step seven shows how to rename fields and members inside the pivot.

To rename a field:

1. Double click on field that says Filter by Criteria

2. From the sub menu, select Pivot Widget

3. Press [Enter]

Note:

Members are renamed in the same way, just double click on the member's cell, rename the caption and press [Enter]

Step 8: Pivot Sorting

This step involves the sorting of products by their corresponding Sales figures.

To do that:

1. Right click on the Sales measure inside the pivot

2. From the pop menu, select Sort, then select Largest to Smallest

This will sort (grouped by months) all Sales figures, from largest to smallest. We can easily see which products count and contribute the most per month.