Sometimes the user may wish to reorganise and summarise data in a stream set to get a desired report. The pivot view functionality allows the user to use a stream view attribute's values as the rows against which all other data is reported.

All examples on this page are based on the stream data shown in Figure 1.

Figure 1: Raw stream set data

Table of Contents

Example 1: Basic Pivot View

These instructions describe how to configure a view to look like Figure 2. The view shows the total 'Random_Number' value per 'Random_Date' and 'Status' value.

Figure 2: 007_Pivot_View1

Step-by-step guide

1. Open the stream configuration.
2. Go to the Views tab.
3. Create a new view. Choose the 'Pivot' option.
4. Give the view a name. In this example, the view has been named 007_Pivot_View1.
5. Leave the Pivot Column Source as 'View Data'.
6. Drag the 'Random Date' attribute from the left hand list into the Row Attribute field.
7. Drag the 'Status' attribute from the left hand list into the Column Attribute field.

8. Drag the 'Random Number' attribute from the left hand list into the Value Attribute field. It will set the aggregate attribute to Sum() by default.

   

   Figure 3: View configuration for 007_Pivot_View1

9. Save the view.
10. If you now open the view, it should look like Figure 2.

Example 2: Adding a Total Column to the Basic Pivot View

These instructions describe how to configure a view to look like Figure 4. The view shows the total 'Random_Number' value per 'Random_Date' and 'Status' value. It also shows the total 'Random_Number' value per 'Random_Date'.

Figure 4: 007_Pivot_View2

Step-by-step guide

1. Open the stream configuration.
2. Go to the Views tab.
3. Create a new view. Choose the 'Pivot' option.
4. Give the view a name. In this example, the view has been named 007_Pivot_View2.
5. Leave the Pivot Column Source as 'View Data'.
6. Drag the 'Random Date' attribute from the left hand list into the Row Attribute field.
7. Drag the 'Status' attribute from the left hand list into the Column Attribute field.

8. Drag the 'Random Number' attribute from the left hand list into the Value Attribute field. It will set the aggregate attribute to Sum() by default.

9. Drag the 'Random Number' attribute from the left hand list into the Total Attribute field. It will set the aggregate attribute to Sum() by default.
   
   

   Figure 5: View configuration for 007_Pivot_View2

10. Save the view.
11. If you now open the view, it should look like Figure 4.

Example 3: Multiple row attributes

These instructions describe how to configure a view to look like Figure 6 . The view shows the total 'Random_Number' value per 'Random_Date' per 'Status' and 'Even_CV_ID' value. It also shows the total 'Random_Number' value per 'Status' and 'Even_CV-ID' value.

Figure 6: 007_Pivot_View3

Step-by-step guide

1. Open the stream configuration.
2. Go to the Views tab.
3. Create a new view. Choose the 'Pivot' option.
4. Give the view a name. In this example, the view has been named 007_Pivot_View3.

5. Leave the Pivot Column Source as 'View Data'.

6. Drag the 'Status' attribute from the left hand list into the Row Attribute field.
7. Drag the 'Even_CV_ID' attribute from the left hand list into the Row Attribute field.
8. Drag the 'Random Date' attribute from the left hand list into the Column Attribute field.

9. Drag the 'Random Number' attribute from the left hand list into the Value Attribute field. It will set the aggregate attribute to Sum() by default.

10. Drag the 'Random Number' attribute from the left hand list into the Total Attribute field. It will set the aggregate attribute to Sum() by default.

    

    Figure 7: View configuration for 007_Pivot_View3

11. Save the view.

12. If you now open the view, it should look like Figure 6.
    
    

Example 4: Using different aggregate functions and formatting fields

These instructions describe how to configure a view to look like Figure 8 . The view shows the maximum 'Random_Number' value per 'Status' and  'Random_Date' value. It also shows the maximum 'Random_Number' value per 'Status'. The format of 'Random_Date' has been amended from the original stream set format.

Figure 8: 007_Pivot_View4

Step-by-step guide

1. Open the stream configuration.
2. Go to the Views tab.
3. Create a new view. Choose the 'Pivot' option.
4. Give the view a name. In this example, the view has been named 007_Pivot_View4.
5. Leave the Pivot Column Source as 'View Data'.
6. Drag the 'Status' attribute from the left hand list into the Row Attribute field.
7. Drag the 'Random Date' attribute from the left hand list into the Column Attribute field.

8. Drag the 'Random Number' attribute from the left hand list into the Value Attribute field. It will set the aggregate attribute to Sum() by default.

9. Double click Sum(Random_Number) in the Value Attribute field.
10. Change the aggregate function to Maximum.

11. Drag the 'Random Number' attribute from the left hand list into the Total Attribute field. It will set the aggregate attribute to Sum() by default.

12. Double click Sum(Random_Number)->Total in the Total Attribute field.

13. Change the aggregate function to Maximum. 

     

    Figure 9: View configuration for 007_Pivot_View4

14. Double click on Random_Date in the Column Attribute field.

15. Change the Date/Time Format to yy-MMM-dd.
     

    

    Figure 10: View Attribute Configuration for Random_Date

16. Save the view.

17. If you now open the view, it should look like Figure 8.

Example 5: Using the maximum pivot columns field

Step-by-step guide

1. Open the stream configuration.

Model Ref: 007

Related articles