Woody Pewitt - DevExpress Analytics Blog

Using the data from my first post in this series Getting started with Pivot Grid (Shaping Data) I will create a simple ASP.NET application to display that data.

This video will walk you through the steps in this post.

1. Create a new ASP.NET Web Site project
2. Open the Default.aspx page in Design view
3. Add a ASPxPivotGrid control on the the design surface
4. In the ASPxPivotGrid Tasks menu or the DataSourceID property window select “<New data source>”
5. Select SQL Database
6. Connect to an installed instance of the NorthWind database
7. Select “Specify a custom SQL statement or stored procedure”
8. Use the SQL from the “Shaping Data” blog post
   

   SELECT DISTINCT
         Categories.CategoryName,
         Products.ProductName,
         ([Order Details].UnitPrice * [Order Details].Quantity) * (1 - [Order Details].Discount) / 100 * 100 AS ProductSales,
         Orders.ShippedDate
   FROM Products INNER JOIN
         Categories ON Products.CategoryID = Categories.CategoryID INNER JOIN
         [Order Details] ON Products.ProductID = [Order Details].ProductID INNER JOIN
         Orders ON [Order Details].OrderID = Orders.OrderID
   WHERE (Orders.ShippedDate IS NOT NULL)

At this point you have the PivotGrid aware of the data set you created but it dose not know what to show. We need to add PivotGrid fields for each field from the data set we want to display. This can be done in code or by using the Fields Editor in the “Tasks” menu, some ways you can do it in code are:

The RetrieveFields method is a helper that will create PivotGridField objects for all the fields in the bound data source.

You can also use the Add method of the Fields collection.

Note: The use of IsPostBack, the page will persist the pivot fields between post backs, if you add duplicate fields during a post back the user can get unexpected results.

While both above methods work by far the easiest way to do this is the Fields Editor I mentioned.

1. Open the “ASPxPivotGrid Fields Editor”
   • Available from the control task menu
   • Or at the Fields link at the bottom of the Properties list for the control
2. Click the “Retrieved fields” in the toolbar
3. Click OK and run the project

The web page will come up and all the fields will be in the Filter area where a user can move them to the other parts of the grid. While this is good functionality most user will want a default report so we will potion the fields in some default areas.

1. Go back to Visual Studio
2. Open the Fields Editor like we earlier
3. Select the “CategoryName” field
4. Change the caption to “Category”
5. Change “Area” to “RowArea”
6. Select the “ProductName” field
7. Change Caption to “Product”
8. Change Area to “RowArea”
   • Note that AreaIndex value changed to “1” this controls the order of the fields in a given area
9. Select the “ProductSales” field
10. Change the Caption to “Product Sales”
11. Change Area to “DataArea”
12. Select the “ShippedDate” field
13. Change the Caption to “Product Sales”
14. Change Area to “ColumnArea”

At this point we have a default layout for the pivot grid when we run the project at this point you will see the fields in the expected area. The displayed data is quite sparse because we are looking each day in the data where many products only sell a few a week. To make this look better we want show years and months of sales not each day.

1. If you have closed the Fields Editor then open it like we earlier
2. Select the “ShippedDate” field
3. Change the Caption to “Shipped Year”
4. Change the GroupInterval to “DateYear”
5. Add a new field with the “Add an Item” button on the far left of the toolbar
6. Change the Caption to “Shipped Month”
7. Change Area to “ColumnArea”
8. Change the GroupInterval to “DateMonth”
9. Select “ShippedDate” for FieldName (this property has a drop down list of the data fields)

Now we will get the year and each years months displayed when the application is run. The user can collapse & filter on year and month as they like. Also note the other options we had in the GroupInterval property you will see we could have used quarters and may other options.

At this point the markup for the pivot grid looks like:

Now this is looking like what we want (click for animation):

The last thing I want to do is make the Year Shipped and Month Shipped fields a grouped set. This can be useful when you want one or more fields to be associated together, end-users will not be able to separate the fields at runtime.

1. Open the Group Editor click on “Groups” link
   • Available from the control task menu
   • Or at the Fields link at the bottom of the Properties list for the control
2. Click on the “Add an item” in the on the top left of the Group Editor
3. Select your new group “Group 0” and click “Edit” on the left end of the tool bar
4. Move “Shipped Year” and “Shipped Month” from the “Ungrouped Fields” list to the “Grouped Fields”
5. Close the Group’s Fields Editor dialog and the Group Editor dialog
6. Run the project

You now have a simple ASP.NET application using the ASPxPivotGrid, I hope this quick little walk though will help you get started. The best way to learn what you can do with this control is to start doing.

Please let me know if you have any questions!