Paul Kimmel's Blog

This blog post is in response to a customer query on our community support site at http://community.devexpress.com/forums/p/92876/319857.aspx. The customer wrote about an all too-often scenario. The customer had data from a second table containing data that was not correlated by keys; that is, there exists no natural relationship where a join could be established and a view created. (A preferable solution in a perfect world would be to have correlated data and use views, but I have run into this scenario hundreds of times.) 

The customer said: “I would like to append additional info at the end of each row”. The customer was kind enough to post a graphic of what he was trying to do and after a brief exchange the conversation migrated to code. As it is often easier to communicate with code then to stick to words the solution in this blog is my first attempt at getting him where he wants to be.

A core feature of the XtraPivotGrid is that you can add all kinds of aggregate data fields at the end (or beginning) of each data section. The XtraPivotGrid supports calculating things like the Sum, Min, and Max values. These aggregate behaviors will be automatically calculated. If you want to perform custom operations then you can select the SummaryType Custom and roll your own solution. This includes adding fields that contain unrelated or only quasi-related data. This is a slightly one-off solution—displaying uncorrelated data—which is why we need a little bit of code to get the job done. Figure 1 shows the end result. Then, we’ll walk through the steps to prepare the solution, followed by the code.

Figure 1: Note that the end of the group of data the summary data is followed by the extra, unrelated data.

Using data that is technically unrelated may not make technical sense but that doesn’t mean it won’t make sense to users or that customers won’t want such a solution.

In Figure 1 the Products table data is displayed with the Category ID and Product Name in the row area and the Unit Price in the data area. The total Unit Price is calculated by category and product. (Probably not a practical application—sum of unit prices—but it shows how summary information is automatically available in the XtraPivotGrid.) The three additional summary values could automatically be things like averages, minimum or maximum unit prices, or perhaps the number of items per category. For our scenario however we are adding some arbitrary data representing data that is not technically related but makes sense to a user. Here are the steps for adding additional custom totals followed by one solution for putting data in them.

Adding Custom Totals to a Pivot Grid

The following steps demonstrate how to add custom totals to an XtraPivotGrid and write code that puts programmer-defined values in the custom totals fields.

1. Create a WinForms project
2. Add an PivotGridControl and dock it to the Form
3. Use the PivotGridControl’s smart tags menu to configure the Northwind Products table to the pivot grid; this will add a northwindDataSet, productsBindingSource, and productsTableAdapter to the components tray in Visual Studio
4. Right-click on the pivot grid and select Run Designer
5. Close the designer
6. Click on the Properties Window and select the fieldCategory1 control
7. Click the Custom Totals property to open the designer-see Figure 2
8. In the PivotGridCustomTotal Collection Editor click Add three times to add three custom totals
9. For each of the three custom totals change the Tag properties to 1, 2, 3, respectively and the SummaryType to Custom
10. Click OK to close the Collection Editor
11. Back in the Properties window for the fieldCategoryID1 control—a PivotGridField object—change the TotalsVisibility to CustomTotals
12. Click on the PivotGridControl
13. Switch the Properties window view to the Events tab
14. Double click to implement a CustomCellValue event and a CustomDrawFieldValue event methods
15. Use CustomDrawFieldValue to change the custom summary captions, and use CustomCellValue to add the custom cell values for each custom summary field

Figure 2: Add three custom totals for the CategoryID field to the PivotGridCustomTotal Collection Editor .

The CustomCellValue event is used to do whatever it takes to correlate data where a natural correlation does not exist. In this example I contrived some arbitrary data and used the CategoryID’s to index the list of other data. Listing 1 contains the complete solution.

Listing 1: The custom events modify the custom total fields to contain data that is not ‘exactly’ aggregate values, but logically related to the records in some way—determined by a user scenario.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid;
using DevExpress.Data.PivotGrid;
using System.Reflection;

namespace S319857
{
  public partial class Form1 : Form
  {
    private List<Data> datum;
    public Form1()
    {
      InitializeComponent();

      datum = new List<Data>{
        new Data{ID=1, Cash="100", EftPos="Dummy", HealthFund="H1"},
        new Data{ID=2, Cash="200", EftPos="Dummy", HealthFund="H2"},
        new Data{ID=3, Cash="300", EftPos="Dummy", HealthFund="H3"},
        new Data{ID=4, Cash="400", EftPos="Dummy", HealthFund="H4"},
        new Data{ID=5, Cash="500", EftPos="Dummy", HealthFund="H5"},
        new Data{ID=6, Cash="600", EftPos="Dummy", HealthFund="H6"},
        new Data{ID=7, Cash="700", EftPos="Dummy", HealthFund="H7"},
        new Data{ID=8, Cash="800", EftPos="Dummy", HealthFund="H8"}};
    }

    private void Form1_Load(object sender, EventArgs e)
    {
      // TODO: This line of code loads data into the 
      // 'northwindDataSet.Products' table. You can move, or remove it, as needed.
      this.productsTableAdapter.Fill(this.northwindDataSet.Products);

    }

    private void pivotGridControl1_CustomCellValue(object sender, 
      PivotCellValueEventArgs e)
    {
      int ID = Convert.ToInt32(e.GetFieldValue(fieldCategoryID1));

      Data obj = datum.Find(d => d.ID == ID);

      if ((e.SummaryType == PivotSummaryType.Custom) 
        && (e.DataField == fieldUnitPrice1))
      {
        if (Convert.ToInt32(e.RowCustomTotal.Tag) == 1)
          e.Value = obj.Cash;
        else if (Convert.ToInt32(e.RowCustomTotal.Tag) == 2)
          e.Value = obj.EftPos;
        else if (Convert.ToInt32(e.RowCustomTotal.Tag) == 3)
          e.Value = obj.HealthFund;
      }
    }

    private void pivotGridControl1_CustomDrawFieldValue(object sender, 
      PivotCustomDrawFieldValueEventArgs e)
    {
      if (e.Info.Caption.Contains("Custom"))
      {
        if (Convert.ToInt32(e.CustomTotal.Tag) == 1)
          e.Info.Caption = "CASH";
        else if (Convert.ToInt32(e.CustomTotal.Tag) == 2)
          e.Info.Caption = "EFTPOS";
        else if (Convert.ToInt32(e.CustomTotal.Tag) == 3)
          e.Info.Caption = "Health Fund";      
      }
    }
  }


  public class Data
  {
    private int iD;
    public int ID
    {
      get { return iD; }
      set { iD = value; }
    }

    private string cash;
    public string Cash
    {
      get { return cash; }
      set { cash = value; }
    }

    private string eftPost;
    public string EftPos
    {
      get { return eftPost; }
      set { eftPost = value; }
    }

    private string healthFund;
    public string HealthFund
    {
      get { return healthFund; }
      set { healthFund = value; }
    }
  }

}

The CustomCellValue event uses the event argument’s GetFieldValue and the control who’s value you want to get as a parameter to get the current field value which is the CatorgoryID in this instance. datum.Find uses a Lambda expression to find the right Data object—representing arbitrary data. Finally, the if..conditional uses the predetermined Tag values to figure out which total’s fields get which data. CustomDrawField uses the Tags again to modify the custom total’s fields captions.

If you have naturally correlated data—disparate tables with primary and foreign keys—then you can use views and joins to populate the PivotGridControl. If you have a customers that have legacy data or just want to show something else then you can exploit the custom totals feature and events to get that data in the PivotGridControl.

Thanks for choosing Developer Express.

As a technophile who also happens to write I know how hard it can be when the documentation for a new technology is squiffy. Squiffy being you can’t figure it out from existing documentation and blogs because too many details are left out. This what happened when I started looking at EF4 and Model Defined Functions. A model “declared” function is one that you import from your storage provider, like a SQL Server stored procedure. A model defined function is one that is completely defined in your model. EF4 supports model defined functions but the documentation leaves out too many details that explain exactly how you get this to work. In this blog I will walk you through creating a model defined function in EF4 and then binding the results to our XtraPivotGrid.

An overview of the process requires some data you want to show in a Pivot Grid, an idea for tweaking the result set, and Entity Framework 4 model, and a custom query defined in the model only. The two basic steps are to add an XtraPivotGrid control to a WinForms project and add an ADO.NET Entity Data Model. (This example is based on Visual Studio 2010 and DevExpress controls—I used the a beta version of DevExpress controls for 10.2.) There are a lot of blogs and articles on EF Models but here is the nuts and bolts. (If you know how to do this step skip the next section.)

Adding an Entity Framework Model to Your WinForms Application

An Entity Framework Data Model is created from an applet in the Add New Items dialog. In its simplest form an EF model is simply some code generated classes that create entities based on some storage item like a database table. The basic idea is that once you have the data model interaction with the data store is easier. (We all refer to this as a data access layer in a general sense.)

To create the entity data model Follow these steps to create:

1. Create a WinForms project. (This will work in other kinds of projects too.)
2. Click Project|Add New Item
3. Select the ADO.NET Entity Data Model from the Add New Item dialog and name it NorthwindModel
4. Click Add
5. In the Entity Data Model Wizard (that starts up) select Generate from database and click Next
6. Choose an existing Northwind connection or click New Connection to add a connection
7. Use the defaults for everything else in this step and click Next
8. In the Choose Your Data Objects step expand Tables and select the Products table
9. Click Finish

That’s all there is to it. After you click Finish the .edmx file and designer will be open. An entity model is the important element you need.

Defining a Model Defined Function

A model defined function is one that exists only in your model. A model declared function is one you import from your server. We will be creating a model defined function. For our scenario we will add a model defined function that filters on ReorderLevel. It is worth stating that you could add such a trivial filter right in the LINQ query, but we will create the model defined function and use such a simple example to focus on the mechanics of model functions. As a general rule I would only add a model defined function if it gave my solution some extra leverage that might be hard for my developers to do themselves with LINQ like joins.

The key to adding a model defined function is to modify the model XML, but a lot of the blogs don’t tell you which XML to modify and leave out a few steps. You should be able to reproduce this process because I am not going to leave any steps out. To create the model defined function you need to modify the SSDL in the EDMX file and then import the function and call it with your LINQ query, binding the results to the PivotGrid. Here they are:

1. Right click on the .EDMX file in the solution explorer and select Open With
2. Choose the XML (Text) Editor—yep, we gotta modify the EDMX file directly
3. The EDMX file will look like Listing 1. Find the SSDL section
4. At the end of the SSDL section just before the closing </Schema> tag add a <Function> tag
5. Add a Name attribute Name=”GetProductsByReorderLevel”
6. Add an IsComposable=”false” attribute
7. In the <Function> tag add a <CommandText> tag and write your vanilla SQL inside of the <CommandText></CommandText> tag; I used select * from Products where ReorderLevel > @p1
8. Make sure you use an @ in the query itself or the framework will think the right hand side operand is a column
9. After the <CommandText>—parameters are expected to follow—add a <Parameter> tag with a Name=”p1”, Mode=”In”, and Type=”int” attributes. (Make sure you don’t use @ in the parameter Name attribute; doing so will kill the code generation.)

Here is the added XML block and Listing 1 shows the complete EDMX listing.

<Function Name="GetProductsByReorderLevel" IsComposable="false">
  <CommandText>
   select * FROM Products
   where ReorderLevel > @p1;
  </CommandText>
  <Parameter Name="p1" Mode="In" Type="int" MaxLength="75" />
</Function>

Listing 1: The complete .EDMX listing with the SSDL model defined function.

<?xml version="1.0" encoding="utf-8"?>
<edmx:Edmx Version="2.0" xmlns:edmx="http://schemas.microsoft.com/ado/2008/10/edmx">
  <!-- EF Runtime content -->
  <edmx:Runtime>
    <!-- SSDL content -->
    <edmx:StorageModels>
      <Schema Namespace="NorthwindModel.Store" Alias="Self" Provider="System.Data.SqlClient" ProviderManifestToken="2008" xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
        <EntityContainer Name="NorthwindModelStoreContainer">
          <EntitySet Name="Products" EntityType="NorthwindModel.Store.Products" store:Type="Tables" Schema="dbo" />
        </EntityContainer>
        <EntityType Name="Products">
          <Key>
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="ProductID" Type="int" Nullable="false" StoreGeneratedPattern="Identity" />
          <Property Name="ProductName" Type="nvarchar" Nullable="false" MaxLength="40" />
          <Property Name="SupplierID" Type="int" />
          <Property Name="CategoryID" Type="int" />
          <Property Name="QuantityPerUnit" Type="nvarchar" MaxLength="20" />
          <Property Name="UnitPrice" Type="money" />
          <Property Name="UnitsInStock" Type="smallint" />
          <Property Name="UnitsOnOrder" Type="smallint" />
          <Property Name="ReorderLevel" Type="smallint" />
          <Property Name="Discontinued" Type="bit" Nullable="false" />
        </EntityType>
        <Function Name="GetProductsByReorderLevel" IsComposable="false">
          <CommandText>
            select * FROM Products
            where ReorderLevel > @p1;
          </CommandText>
          <Parameter Name="p1" Mode="In" Type="int" MaxLength="75" />
        </Function>
      </Schema>
    </edmx:StorageModels>
    <!-- CSDL content -->
    <edmx:ConceptualModels>
      <Schema Namespace="NorthwindModel" Alias="Self"
      xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"
      xmlns="http://schemas.microsoft.com/ado/2008/09/edm">
        <EntityContainer Name="NorthwindEntities" annotation:LazyLoadingEnabled="true">
          <EntitySet Name="Products" EntityType="NorthwindModel.Product" />
        </EntityContainer>
        <EntityType Name="Product">
          <Key>
            <PropertyRef Name="ProductID" />
          </Key>
          <Property Name="ProductID" Type="Int32" Nullable="false"
          annotation:StoreGeneratedPattern="Identity" />
          <Property Name="ProductName" Type="String" Nullable="false"
          MaxLength="40" Unicode="true" FixedLength="false" />
          <Property Name="SupplierID" Type="Int32" />
          <Property Name="CategoryID" Type="Int32" />
          <Property Name="QuantityPerUnit" Type="String" MaxLength="20"
          Unicode="true" FixedLength="false" />
          <Property Name="UnitPrice" Type="Decimal" Precision="19" Scale="4" />
          <Property Name="UnitsInStock" Type="Int16" />
          <Property Name="UnitsOnOrder" Type="Int16" />
          <Property Name="ReorderLevel" Type="Int16" />
          <Property Name="Discontinued" Type="Boolean" Nullable="false" />
        </EntityType>
      </Schema>
    </edmx:ConceptualModels>
    <!-- C-S mapping content -->
    <edmx:Mappings>
      <Mapping Space="C-S" xmlns="http://schemas.microsoft.com/ado/2008/09/mapping/cs">
        <EntityContainerMapping StorageEntityContainer="NorthwindModelStoreContainer"
        CdmEntityContainer="NorthwindEntities">
          <EntitySetMapping Name="Products"><EntityTypeMapping
          TypeName="NorthwindModel.Product"><MappingFragment StoreEntitySet="Products">
            <ScalarProperty Name="ProductID" ColumnName="ProductID" />
            <ScalarProperty Name="ProductName" ColumnName="ProductName" />
            <ScalarProperty Name="SupplierID" ColumnName="SupplierID" />
            <ScalarProperty Name="CategoryID" ColumnName="CategoryID" />
            <ScalarProperty Name="QuantityPerUnit" ColumnName="QuantityPerUnit" />
            <ScalarProperty Name="UnitPrice" ColumnName="UnitPrice" />
            <ScalarProperty Name="UnitsInStock" ColumnName="UnitsInStock" />
            <ScalarProperty Name="UnitsOnOrder" ColumnName="UnitsOnOrder" />
            <ScalarProperty Name="ReorderLevel" ColumnName="ReorderLevel" />
            <ScalarProperty Name="Discontinued" ColumnName="Discontinued" />
          </MappingFragment></EntityTypeMapping></EntitySetMapping>
        </EntityContainerMapping>
      </Mapping>
    </edmx:Mappings>
  </edmx:Runtime>
  <!-- EF Designer content (DO NOT EDIT MANUALLY BELOW HERE) -->
  <Designer xmlns="http://schemas.microsoft.com/ado/2008/10/edmx">
    <Connection>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="MetadataArtifactProcessing" Value="EmbedInOutputAssembly" />
      </DesignerInfoPropertySet>
    </Connection>
    <Options>
      <DesignerInfoPropertySet>
        <DesignerProperty Name="ValidateOnBuild" Value="true" />
        <DesignerProperty Name="EnablePluralization" Value="True" />
        <DesignerProperty Name="IncludeForeignKeysInModel" Value="True" />
      </DesignerInfoPropertySet>
    </Options>
    <!-- Diagram content (shape and connector positions) -->
    <Diagrams>
      <Diagram Name="NorthwindModel">
        <EntityTypeShape EntityType="NorthwindModel.Product" Width="1.5" PointX="0.75"
        PointY="0.75" Height="2.9417936197916665" IsExpanded="true" />
      </Diagram>
    </Diagrams>
  </Designer>
</edmx:Edmx>

I intentionally did not use code formatting and re-paginated the XML to fit the page, but don’t do that. Just add the <Function> block in bold in Listing 1.

One might think that is all there is too it, but the Entity Framework maps at the the conceptual storage level, CSDL, and we added the model function at the storage level. You need to map the function at the conceptual level next.

Before you continue close the .edmx file. Make sure you save it. Then, double-click the .edmx file to open the designer, right click, and click Validate to make sure your XML is good.

Mapping the Model Defined Function at the Conceptual Level

Your model-defined function at the SSDL should now be available. To view the model select View|Other Windows|Entity Data Model Browser. If everything is going well so far your function should be available in the Model Browser under Northwind.Store|Stored Procedures—see Figure 1.

Figure 1: The model-defined function at the storage level.

The next step is to import the function, which in essence adds an entry to the CSDL level and let’s you call it. To add the function at the conceptual level right click on the NorthwindModel.edmx file and follow these steps:

1. Click Add|Function Imports
2. Using Figure 2 as a visual guide, enter a function name. I used GetProductsByReorderLevel, but if you have funky Sproc names then you can make them more code friendly here.
3. Select the Stored Procedure Name
4. Change the Returns a Collection of to Entities and select the Product entity type
5. Click OK

Now if you check the Model Browser you will see that the Function Imports section contains a usable CSDL function—see Figure 3.

Figure 2: Add Function Import to add the function to the CSDL-conceptual level—so you can call it with LINQ to Entities.

Figure 3: The function GetProductsByReorderLevel can now be called from LINQ to Entities.

Binding the PivotGrid to the Model Defined Function

Unfortunately the data source wizard in Visual Studio 2010 doesn’t know about model defined functions so we can’t use the wizard to get the data from the model defined function. However, since the model-defined function is mapped to the Product entity you can use the Data Source Configuration Wizard to bind to an Object. Follow these steps to configure the PivotGrid with Product entity data.

1. Click the PivotGridControl’s smart tags menu, click Choose Data Source|Add Project Data Source
2. In the Data Source Configuration Wizard choose Object
3. Click Next
4. Expand the project’s node
5. Expand the first child node and check Product
6. Click Finish

Figure 4: Let the Data Source Configuration Wizard configure the XtraPivotGrid’s fields for you.

In the code behind you need just a couple of lines in the code behind and you are off to the races.

Listing 2 initializes the NorthwindEntities object, calls the model-defined function, and updates the productBindingSource.DataSource to point to the new data. It takes some work to get here but most of the effort was completed by wizards with just a tiny bit of XML editing and a very little bit of code. Since code is generally the most time consuming thing and the most expensive this is actually a beautiful thing.

Listing 2: Once you get your entities right it takes just a little bit of code to use them.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace ModelDefinedFunctionAndPivotGrid
{
  public partial class Form1 : Form
  {
    NorthwindEntities entities = new NorthwindEntities();
    public Form1()
    {
      InitializeComponent();

      var results = from product in entities.GetProductsByReorderLevel(10)
                    select product;
      productBindingSource.DataSource  = results;
    }
  }
}

I’d love to see an SSDL designer for model defined functions. I’d also like to eliminate the leap from SSDL to CSDL. It doesn’t make a lot of sense that one would add a function they didn’t want to use from LINQ, but EF4 is a big improvement over earlier versions. Figure 5 shows the running application after I have moved some fields around at runtime.

Figure 4: The XtraPivotGrid with data from a model-defined function and a single LINQ query.

I manage a TODO list with Microsoft Outlook. If it weren’t for Outlook and Calendar items synched on my iPhone I would forgot a lot of detailed tasks. Right now there are so many new features for 10.2 that I want to cover that my task list has gotten pretty long and is growing and projecting out for several weeks to come. It is long enough right now that I wonder if I am going to get some sleep this weekend or get to that 30 foot trunk of a tree that the wind just knocked over into my yard. The last week or so sleep has been at the bottom of the TODO list—although I did wheeze through 30 minutes of basketball at the gym last night.

The feature on the menu today is a single feature, the CustomFilterPopupItems event for the XtraPivotGrid. The XtraPivotGrid is a composable, searchable, sortable, filterable grid that supports data mining type tasks for your end users. The CustomFilterPopupItems event is new in 10.2 and is fired when the user displays the filter list for a field. Figure 1 shows the demo application with the filter list visible. A user brings up the filter list by clicking on the little filter button associated with a given field.

Figure 1: Pivot Grid Control with the filter items visible for the Category Name field. 

This demo handles the CustomFilterPopupItems event for the XtraPivotGrid control. Normally when you display the filter and deselect items those elements are removed from the result set—the data area in the grid and the row area. For example, if you expand the Category Name filter and uncheck Show All and check Beverages then the default behavior is that the products that are beverages are the only products shown .

By default the non-visible data items are available in the filter still. This sample demonstrates how to hide the checked filter items that aren’t visible in the grid. For example, in our scenario by checking the Beverages Category only beverages are visible in the grid, and with the sample code for CustomFilterPopupItems in Listing 1 the invisible items are also removed from the filter list. For instance, if you drop the Category Name filter in our scenario then the event code removes items that for all intents and purposes won’t be displayed anyway.

Figure 2 shows the Beverages scenario without the event code, and Figure 3 shows the scenario with the event code.

  
Figure 2: Notice that even though Beverages is the only Category checked all products are visible in the Product Name filter.

Figure 3: With the CustomFilterPopupItems event (as written in Listing 1) invisible items are removed from the popup filter list.

Listing 1: Using the CustomFilterPopupItems event to hide filter items that aren’t being displayed in the data grid based on other filter selections.

using System;

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using DevExpress.XtraPivotGrid.Data;

namespace EmptyWinApp {
    public partial class Form1 : Form {
        public Form1() {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e) {
            // TODO: This line of code loads data into the 'productReports._ProductReports' 
            // table. You can move, or remove it, as needed.
            this.productReportsTableAdapter.Fill(this.productReports._ProductReports);
        }

        private void pivotGridControl1_CustomFilterPopupItems(object sender, 
        DevExpress.XtraPivotGrid.PivotCustomFilterPopupItemsEventArgs e) 
        {
            List<object> values = e.Field.GetVisibleValues();
            values.Sort();
            for(int i = e.Items.Count - 1; i >= 0; i--) {
                if(e.Items[i].IsChecked == true && values.BinarySearch(e.Items[i].Value) < 0)
                    e.Items.RemoveAt(i);
            }
        }
    }
}

We love customers at DevExpress, so when one of you guys writes me from the trenches I drop what I am doing and try to get a response out as soon as possible.

Today, while working on a blog post for the ASP.NET Pivot Grid Control’s (XtraPivotGrid) CustomFilterPopupItems event for 10.2 a customer sent me a message through the newsgroups. He wanted to know if it was possible to use LINQ not as a datasource for an XtraGrid but to manipulate data in an XtraGrid. As is customary I said if you send me a trimmed down demo then I will tweak it for you. This approach is mostly to ensure that I understand what the customer is trying to do. In this case I had a pretty good idea of what he was trying to accomplish so I whipped up a solution and some explanatory text that gives one an idea of how to approach a solution using LINQ.

LINQ uses deferred execution. This means LINQ won’t actually touch the data in the query until you start accessing it. A LINQ query itself runs when the data is accessed not at the time (or line) where the query exists unless you touch it in the query, like calling an extension method like First, or Take on the tail end of the query. It is also worth noting that LINQ is for querying not updating data necessarily, so updating something else in a LINQ query requires using side effects.

For the solution I demonstrated the side effects approach in a compiler directive #if and a preferable, more reader friendly solution in the #else condition. Both approaches are explained along with some sidebar discussions about the code in general—see Listing 1.

Listing 1: Touching data in an XtraGrid with a LINQ query.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace QueryXtraGridWithLINQ
{
  public partial class Form1 : Form
  {
    public Form1()
    {
      InitializeComponent();
    }

    private void Form1_Load(object sender, EventArgs e)
    {
      // TODO: This line of code loads data into the 'northwindDataSet.Orders' table. 
      // You can move, or remove it, as needed.
      this.ordersTableAdapter.Fill(this.northwindDataSet.Orders);

    }

    private void gridControl1_Click(object sender, EventArgs e)
    {
#if   TOGGLE
      // do something with LINQ on side effects but not a good idea
      Func<DataRow, DateTime> SideEffects = row => 
      {
        DateTime newDateTime = Convert.ToDateTime(row["OrderDate"]).AddYears(1);
        object[] data = row.ItemArray;
        data[3] = newDateTime;
        northwindDataSet.Orders.LoadDataRow(data, false);
        return newDateTime;
      };
      
      // you could try this but because of deferred execution side effects 
      // won't actually happen until you touch the data
      var orderDates = from DataRow row in northwindDataSet.Orders.Rows
                       select new {OrderID=row["OrderID"], OrderDate=SideEffects(row)};

      // this is mumbo jumbo - esoterrorism
      orderDates.ToArray();
      int rowsUpdated = ordersTableAdapter.Update(northwindDataSet.Orders);
      gridControl1.Refresh();
      MessageBox.Show(string.Format("rows {0} updated", rowsUpdated));
#else
      
      // a better solution
      var orderDates = from DataRow row in northwindDataSet.Orders.Rows
                       select new {OrderID=row["OrderID"], 
                       OrderDate=Convert.ToDateTime(row["OrderDate"]).AddYears(1), 
                       RowData=row};

      foreach(var r in orderDates)
      {
        object[] data = r.RowData.ItemArray;
        // get desired data element to change - this is the least reliable way
        data[3] = r.OrderDate;
        northwindDataSet.Orders.LoadDataRow(data, false);
      }

      Validate();
      ordersBindingSource.EndEdit();
      int rowsUpdated = ordersTableAdapter.Update(northwindDataSet.Orders);
      gridControl1.Refresh();
      MessageBox.Show(string.Format("rows {0} updated", rowsUpdated));
#endif
    }
  }
}

In the #if TOGGLE part of the code a generic delegate (Func) and a Lambda Expressions are used to update an arbitrary column’s value, in this instance OrderDate. The DataRow comes in, the new data is set, and data is updated with LoadDataRow. The Lambda Expression returns the new data and time value, so it can be assigned to the anonymous type’s projection value OrderDate in the select clause of the LINQ query. Using data[3] is risky so in general a better approach to identifying the correct column and table should be used. But this approach has bigger problems.

Func SideEffects has side effects, that is, it changes data. Doing unexpected things is generally considered a no no. Worse still the LINQ query (because LINQ uses deferred execution) won’t even run unless we touch it. In this case touching it is accomplished by calling orderDates.ToArray. ToArray actually causes the query to execute. This approach while functional is esoteric to say the least. So, it is possible to use LINQ directly to change data, but I wouldn’t use this approach.

The #else block contains a better approach.

In the #else block LINQ is used to get the data to be changed, but a for each is still used to actually effect the change. This is a better solution because we need to touch the data to execute the query anyway, and in the LINQ query itself updates were not intended. of course, as is shown in the projection—select new—you can update the results in the new, projected data.

Next, the foreach statement iterates over all of the data and updates the OrderDate value. Storing the DataRow in the projection and using LoadDataRow is a nice, convenient way to update the desired rows. If you wanted to update just some rows then use a where clause in the LINQ query to just get the data you wanted. TableAdapter.Update and Refresh will ensure that the database and the GridControl has the new values.

Using the smart tags menu for the ordersBindingSource an Update method was added to the XSD for the solution. Refer to the code attachment for the complete solution.

Believe it or not customers do write DevExpress and evangelists directly and we love to help them. Its fun to play in the puzzle palace—someone needs help, maybe it is tricky for that person, maybe it is tricky for me too and I get to help them figure out a solution. Recently someone from the ‘hood (East Lansing) asked me if it was possible to dynamically change the data source of an ASPxGridView on an AJAX call, a callback. The challenge is that the ASPxGridView is going to have one set of columns on a page load for the current datasource and then the callback will try to bind an alternate dataset. You could just use a postback. You could use multiple grids and multiple datasources and play control hide and seek, but this customer stated that he had ten possible datasets and didn’t want to manage ten pairs of datasources and ASPxGridViews. (We want what we want and we need what we need.)

By default the ASPxGridView’s AutoGenerateColumns is set to true. My solution reads the schema of the new datasource, dynamically adds the columns to the ASPxGridView, indicates which field is the primary key, and then binds the datasource. So, if you want an arbitrary number of datasources displayed on your web pages then you can try this solution. Session is used to manage the current datasource, and the Page_Load relads the current datasource, so updates should work too.

The solution—the code behind is shown in Listing 1—uses a ScriptManager, two buttons, a single ASPxGridView, and two AccessDataSources. Click one or the other of the buttons and the datasource is changed.

Listing 1: The code-behind for the solution.

Imports System.Data.OleDb
Imports System.Data
Imports DevExpress.Web.ASPxGridView


Partial Class Default2
    Inherits System.Web.UI.Page

  Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
    DataSource = AccessDataSource1
    BuildGridView()

  End Sub

  Protected Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
    DataSource = AccessDataSource2
    BuildGridView()
  End Sub


  Private Sub BuildGridView()
    ASPxGridView1.Columns.Clear()
    ASPxGridView1.AutoGenerateColumns = False

    Dim sql As String = DataSource.SelectCommand
    Dim table As DataTable = New DataTable()

    Using connection As OleDbConnection = New OleDbConnection(DataSource.ConnectionString)
      connection.Open()
      Dim command As OleDbCommand = New OleDbCommand(DataSource.SelectCommand, connection)
      Dim Adapter As OleDbDataAdapter = New OleDbDataAdapter(command)
      Adapter.FillSchema(table, SchemaType.Source)
    End Using

    For Each column As DataColumn In table.Columns
      Dim co As GridViewDataColumn = New GridViewDataColumn()
      co.FieldName = column.ColumnName
      ASPxGridView1.Columns.Add(co)

    Next

    If (table.PrimaryKey.Count > 0) Then
      ASPxGridView1.KeyFieldName = table.PrimaryKey(0).ColumnName
    End If

    ASPxGridView1.DataSource = DataSource
    ASPxGridView1.DataBind()
  End Sub

  Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    If (DataSource Is Nothing) Then Return
    BuildGridView()
  End Sub

  Private _dataSource As AccessDataSource
  Private KEY As String = "DATASOURCE"
  Public Property DataSource As AccessDataSource
    Get
      If (Session(KEY) Is Nothing) Then Return Nothing
      Return Session(KEY)
    End Get
    Set(ByVal Value As AccessDataSource)
      Session(KEY) = Value
    End Set
  End Property
End Class

The solution I uploaded for the demo uses the beta version 10.2. You can switch all references to the newer version with find and replace, but it is more fun to download a trial version of DXperience. The two buttons are used for the AJAX triggers.

I love new software and new features. It is like the best birthday present. Many of you have used our ASP.NET Pivot Grid-Table know that the ASPxPivotGrid control is highly composable at design time and runtime. You as well as your end users can move field data around and and change the way the data appears; rows can be move to columns, columns to rows, fields to filter and data areas, and by doing so the ASPxPivotGrid will display your data in ways that is meaningful to your end users.

Now the DevExpress ASP.NET Pivot Grid—in 10.2—can be paired with the ASPxPivotCustomizationControl—see Figure 1—and the user can manage column, data, row, filter, and hidden fields from a central control.  Check Defer Layout Update and the user can make several changes and apply the updates when all desired changes have been made.

To use use the ASPxPivotCustomizationControl place it and an ASPxPivotGrid control on a web form. Set the ASPxPivotCustomizationControl’s MasterControlID to point at the ASPxPivotGrid and you are off to the races.

In the next few days I will post some sample applications that demonstrate how to use the ASPxPivotCustomizationControl and some of the other new features of 10.2.

Figure 1: The ASPxPivotCustomizationControl let’s you manage the ASPxPivotGrid from a central location, sort of like a control panel for the ASPxPivotGrid.

I was working on a project for Lucent Technologies in 1998. I was reading Jim McCarthy’s “Dynamics of Software Development” at the time came across the phrase “Don’t Flip the Bozo Bit”, which basically means don’t write off people quickly, everyone may have something to offer. The phrase resonated at the time because we had a guy on the team dubbed Whizzy Whizardo; everything was a UML/design problem for this guy except he didn’t really know UML. (I am not sure anyone knew the UML that well in 1998.) Whizzy Whizardo got his bozo bit flipped big time and our variation on the phrase was “Don’t Bite the Flip Bozo”. Whizzy got sacked—I had nothing to do with it, I liked the guy—the project was a huge success and ten years later the clown-fear phenomena seems to be growing. There is a lot of clown fear in the world.

I work out of Michigan but am in LA this week. When in LA I pester everyone at the home office about what is knew, and what do they think is good blog fodder. it is a shortcut in a sense, but the point is getting information out to you faster.

The first thing is that we have a new dot-release in beta, 10.2. I am working with Ivan—a developer to plumb some of the new features for 10.2—and today I came across the regular expression feature in the RichEditControl. My first thought was that is pretty cool. My next thought was how many end users are going to know how to write a regular expression. Not many. (I wonder how many developers can write regular expressions well.)

Let’s talk about first fruits, first. The regular expression feature is tucked out of the way in the Find and Replace dialog, effectively making it an innocuous feature—one that can be ignored by most users. And, based on my experience regular expressions are great if you have complicated editing tasks and big documents, but they aren’t needed that often. However, if you are writing tools for power users then they will appreciate the Regex capabilities of the XtraRichEdit control and will take then time to use them. Users somewhere in between may need some coaching mechanisms, sample expressions, or help documentation for your product built on our product, so knowing your audience will help you decide if Regex for rich text editing is an issue or a non-issue.

To that end let’s break it down into two groups: what to do with the Regex feature of the XtraRichEdit control for end users who are not power users and everybody else—inspired mid-level users and power users. (Remember developers are by implication power users and they will take the initiative to figure our Regexes if they need them and use regular expressions when they want to.)

Regular Expressions in the XtraRichEdit Control for End Users

As the XtraRichEdit control adds new features like HTML table support and support for regular expressions it becomes clearer that this control pushing the boundaries of capabilities. By adding these features you will be able to build more powerful applications that use rich text editing. So, the objective is remove barriers by adding capabilities without raising the bar to entry.

The regular expression language has a very terse syntax but is a powerful shorthand notation for performing very complicated tasks like pattern matching. The challenge for users and developers alike is the terseness. I suspect for this reason a regular expression capability might be overlooked by a casual end user and its placement—see Figure 1—in the Find And Replace dialog is unobtrusive.

Figure 1: Regular expressions are supported in the XtraRichEdit control in 10.2.

The casual observer may not even notice the Regular expression checkbox. That’s fine because the casual observer may have a hard time figuring out how to write a regular expression. It is good that regular expression support (which can be managed programmatically too) is tucked away, but there is a lot of power in that one little checkbox, assuming you need a lot of power.

I’d like to see an option in the properties window to disable this feature but have it on by default. Since 10.2 is in beta we’ll see how that shakes out.

Regular Expressions for Power Users and Developers

Writing controls for consumption by programmers is sort of a balancing act. The trick is to create something that can be consumed and tweaked by all without having too many limiting factors. Many programmers wanted HTML table support and it ended up in the product. Programmers building power tools or advanced editors may want or really need regular expression support. Visual Studio’s find and replace feature supports regular expressions, but MS-Word’s find and replace does not.

If you are writing applications with RTF support for developers then they will likely pick up on the regex feature in the XtraRichEdit control and have an idea how to use it. These users at a minimum will know how to Google for it and know what to look for. If you are writing applications with RTF support for normal human beings but have some power users and want to promote the regular expression features—as a selling point—then you may need to give them an idea how to use this feature. Also, if you want to use regular expression support behind the scenes then you can. Let’s split the next two sub-sections into RTF regex support for developers and RTF regex support for power users.

Using Regular Expressions with the XtraRichEdit Control in the Code Behind

Calls to regular expressions can be called programmatically through the Document API, a sub-property/feature of the RichEditControl. The example in Listing 1 loads a text document (with no special formatting), permits you to enter some text or a regular expression and then highlights all matches. The sample uses RichEditControl.Document.FindAll which accepts a regular expression, ChangeFontBackColorCommand to change the background color of the matching items, and StartOfDocumentCommand to return the cursor to the start of the document.

Listing 1: Using DevExpress.XtraRichEdit.Commands and Regular Expressions to implement a Highlight All Search Feature for the XtraRichEdit control.

Imports System.Runtime.InteropServices
Imports System.Reflection
Imports System.IO
Imports System.Text.RegularExpressions
Imports DevExpress.XtraRichEdit.API.Native
Imports DevExpress.XtraRichEdit.Commands
Imports DevExpress.Utils.Commands


Public Class Form1

  Private Sub Form1_Load(ByVal sender As System.Object,
  ByVal e As System.EventArgs) Handles MyBase.Load

    RichEditControl1.Text = File.ReadAllText("..\..\RandomText.txt")

  End Sub

  Private Sub SimpleButton1_Click(ByVal sender As System.Object,
  ByVal e As System.EventArgs) Handles SimpleButton1.Click

    Dim regex As Regex = New Regex(MruEdit1.Text)
    Dim range() As DocumentRange = RichEditControl1.Document.FindAll(regex)

    For Each item In range

          RichEditControl1.Document.Selection = item

          Dim command As ChangeFontBackColorCommand = New ChangeFontBackColorCommand(RichEditControl1)
          Dim state As ICommandUIState = command.CreateDefaultCommandUIState
          state.EditValue = Color.Yellow
          command.ForceExecute(state)

    Next

    Dim startOfDocCommand As StartOfDocumentCommand = New StartOfDocumentCommand(RichEditControl1)
    startOfDocCommand.Execute()

    'DocumentRange[] FindAll(Regex regex);
    'DocumentRange[] FindAll(Regex regex, DocumentRange range);
    'int ReplaceAll(Regex regex, string replaceWith);
    'int ReplaceAll(Regex regex, string replaceWith, DocumentRange range);
  End Sub
End Class

The Form_Load event loads a random text document. This could just as easily be an RTF or HTML document. The SimpleButton click event uses the MruEdit control’s text property as a regular expression. Remember a regular expression can be plain text too. Then, RichEditControl1.Document.FindAll returns an array of DocumentRange objects reflecting the found matches. The for each loop updates the current selection with each DocumentRange and uses ChangeFontBackColorCommand to highlight that section of the document. After all selections are made the caret is moved to the start of the document. Refer to Figure 2 for a visualization of the running application.

 
Figure 2: In the demo the regular expression \w+at looks for strings of letters preceding the letters ‘a’ and ‘t’.

The commented text at the end of the function show some other Document API methods that you can use with regular expressions.

Defining a Regular Expression in the Find and Replace Dialog

The biggest part about regular expressions in the XtraRichEdit control is that you may have to support the occasional power user of your product. If you are using DevExpress tools to build developer tools then your users will probably know how to use regular expressions or at least Google for help. An office or desktop power user may think it is cool to have regular expressions but not know how to get started. For those users you may want to add something to your product’s documentation, give the power user a copy of Dan Appleman’s  Regular Expressions with .NET, pre-define some common regular expressions or sit with the power users and give them a little training. Which way you decide to go depends on how you write the customer agreement.

The picklist for regular expressions in the RichEditControl’s Find and Replace dialog has a few regex commands, but there are an infinite combination of regular expressions so such a list is not going to get a regex neophyte very far. (Of course, 10.2 is still in beta so we’ll see what the final form of this feature is.) Ultimately possible refinements to the beta implementation may be to permit turning regexes off and pre-canning regular expressions in the MruEdit control for the find dialog. (My next blog gives you a hack for stuffing the Find and Replace MruEdit control and hiding the regex checkbox.)

 
Figure 3: The picklist for regular expressions in the RichEditControl’s Find and Replace dialog.

I will be the first to admit that the DevExpress controls have a lot of properties, a lot of designers, and there is a lot to learn. To say that the DevExpress controls are feature rich in short is am understatement. I know this because I work every day trying to learn them all. I work on learning them, acquiring knowledge of them as extemporaneous knowledge so that when I run into customers at conferences I can offer assistance in a timely manner. This also explains why more frequently I see want ads that ask for Developer Express experience specifically. (For example, I just got an email from Domino in Ann Arbor; Dominos is look for just such a candidate. Contact Todd Bohlen at todd.bohlen@dominos.com if you are interested.)

I don’t mean to say that using our controls is hard. for the most part the default settings will get you up and running without a lot of tweaking, and the LookAndFeel feature will help you get a professional looking skin with one property setting. However, if you want to be an expert then you have some work ahead of you. To help DevExpress is cranking out new features all the time and to keep our customers going we are cranking out videos, blogs, tweets, and attending conferences all the time too. The way this works is the product group evangelists imagineer something or we get a custom query and write a blog in conjunction with the support answer to make sure the information gets as broad an audience as possible. Of course, we encourage our customers to post queries to support to help us make the products better, but I also tell people at conferences if you need help feel free to contact me directly. In response to a customer query one of our developers suggest I explore some of the features of exporting data to Excel from an XtraPivotGrid, and they had an example already to go.

MS-Excel is widely used by office workers and developers at the behest of customers. I know this because I wrote a revised edition of Excel 2003 VBA Programmer’s Reference in 2003 and it is still selling several hundred copies every quarter.

The example forwarded to me demonstrates hwo to manually create some data, but any data will do. The example also shows how to programmatically set a bunch of properties to tweak the WYSIWYG view of the data in Excel. For blogs screen shots are good and code works well because you can read the code, copy it, or download it. (For videos, I generally prefer to use designers because they play better in a video format.)

The customer query in question asked about getting rid of some of the header data for the Excel export, so that is included in the code sample. The data is just some make believe data, but collectively the code gives you an idea of just a few of the property-features that are available. When you see a property chain like pivotGridControl1.OptionsPrint.PrintFilterHeaders what you are seeing are nested properties. In this instance it means that you should look in the PivotGridControl property list for OptionsPrint, expand that and find PrintFilterHeaders. In feature rich controls like ours there are going to a lot of nested properties; this is done to help organize by property groups.

Listing 1 contains the code sample that produces the basic WinFoms GUI shown in Figure 1, and when the data is exported the results will look like Figure 2. An overview of the code is provided after Listing 1.

Figure 1: The GUI with the data added by the PopulateTable method.

Figure 2: The data output to MS Excel; notice that the selected cell A1 (or that general area) doesn’t include the data fields represented by the ellipses in Figure 1.

The design time view has an XtraPivotGrid, a button, and a DataSet on it. Follow these steps to create the DataSet’s DataTable structure.

1. Select the DataSet and display the Properties window
2. Open the DataSet’s Tables Collection Editor—see Figure 3
3. Click Add to add a DataTable
4. Change the TableName property to Data
5. Click on the Table’s Column property to open the Columns Collection Editor—see Figure 4
6. Click the Add button to Add three members, naming them Name, Date, Value, respectively
7. Change Name’s DataType to System.String
8. Change Date’s DataType to System.DateTime
9. Change Value’s DataType to System.Decimal
10. Close the Column’s Collection Editor
11. Close the Tables Collection Editor
12. Assign DataSet1 to the PivotGridControl’s DataSource property
13. Assign the Data table to the PivotGridControl’s DataMember property

Figure 3: Use the Tables Collection Editor to manually add tables to a DataSet.

Figure 4: Use the Columns Collection Editor to manually add columns to a DataTable.

After completing steps 1 through 13 above right-click on the PivotGridControl and select Run designer. Using Figure 5 as a visual guide drag and drop the Dame field once, the Date field twice, and the Value field three times to the PivotGrid Fields list. The field duplicates will be used for things like Sum, Count, and Average.

  
Figure 5: Add three more PivotGrid fields using the Add button.

Follow these steps to configure each of the fields:

1. Set fieldName Area property to RowArea
2. Set the fieldDate Area to ColumnArea and change the Caption to Year and the GroupInterval property to DateYear
3. Set the fieldValue Area to DataArea, Caption to Sum
4. Set fieldValue1 Area to DataArea, Caption to Count, and SummaryType to Count
5. Set fieldValue2 Area to DataArea, Caption to Average, and SummaryType to Average
6. Set fieldDate1 Area to ColumnArea, Caption to Date
7. Close the designer

The design time view should look like Figure 6. The code is provided in Listing 1. (It is worth noting that if you use an actual datasource, say from a database, a lot of these steps are performed for you. It is also worth nothing that you can drag and drop fields at design time and runtime rather than changing the Area property of each field.)

Figure 6: The design time view after adding additional fields to the PivotGridControl.

Listing 1: The code-behind that adds data and performs the export to Excel operation.

using System;
usingSystem.Collections.Generic;
usingSystem.ComponentModel;
usingSystem.Data;
usingSystem.Drawing;
usingSystem.Text;
usingSystem.Windows.Forms;
usingDevExpress.XtraPrinting;
usingDevExpress.XtraPivotGrid;

namespaceWindowsApplication53
{
    public partial classForm1: Form
   {
        publicForm1()
        {
            InitializeComponent();
        }
        private voidForm1_Load(objectsender, EventArgs e)
        {
            PopulateTable();
            pivotGridControl1.RefreshData();
            pivotGridControl1.BestFit();
        }
        private voidPopulateTable()
        {
            DataTable myTable = dataSet1.Tables["Data"];
            myTable.Rows.Add(new object[] {"Aaa", DateTime.Today, 7});
            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddDays(1), 4 });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today, 12 });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddDays(1), 14 });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today, 11 });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddDays(1), 10 });

            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddYears(1), 4 });
            myTable.Rows.Add(new object[] { "Aaa", DateTime.Today.AddYears(1).AddDays(1), 2 });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddYears(1), 3 });
            myTable.Rows.Add(new object[] { "Bbb", DateTime.Today.AddDays(1).AddYears(1), 1 });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddYears(1), 8 });
            myTable.Rows.Add(new object[] { "Ccc", DateTime.Today.AddDays(1).AddYears(1), 22 });
        }

        stringfileName = "pivot.xls";
        private voidbutton1_Click(objectsender, EventArgs e)
        {
            pivotGridControl1.BeginUpdate();
            pivotGridControl1.OptionsPrint.PrintColumnHeaders =
              DevExpress.Utils.DefaultBoolean.False;
            pivotGridControl1.OptionsPrint.PrintDataHeaders =
              DevExpress.Utils.DefaultBoolean.False;
            pivotGridControl1.OptionsPrint.PrintFilterHeaders =
            DevExpress.Utils.DefaultBoolean.False;

            PivotGridField fieldExportHeader = pivotGridControl1.Fields.Add();
            fieldExportHeader.Caption = "Export";
            fieldExportHeader.Name = "fieldExportHeader";
            fieldExportHeader.Area = DevExpress.XtraPivotGrid.PivotArea.ColumnArea;
            fieldExportHeader.Visible = true;
            fieldExportHeader.AreaIndex = 0;
            fieldExportHeader.TotalsVisibility = PivotTotalsVisibility.None;
            pivotGridControl1.OptionsView.ShowGrandTotalsForSingleValues = true;
            pivotGridControl1.EndUpdate();

            try
           {
                pivotGridControl1.ExportToXls(fileName);
                System.Diagnostics.Process.Start(fileName);
            }
            catch(System.Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
           {
                pivotGridControl1.Fields.Remove(fieldExportHeader);
            }
        }

        private voidpivotGridControl1_CustomExportFieldValue(objectsender,
        DevExpress.XtraPivotGrid.CustomExportFieldValueEventArgs e)
        {
            TextBrick brick = e.Brick as TextBrick;
            if(e.Brick == null) return;

            if(e.Field != null&& e.Field.Name == "fieldExportHeader"
           && e.ValueType == PivotGridValueType.Value )
            {
                brick.Text = GetLayoutDiscription(sender as PivotGridControl, e.Field );
            }
        }

        private voidpivotGridControl1_FieldValueDisplayText(objectsender,
        PivotFieldDisplayTextEventArgs e)
        {
            if(e.Field != null&& e.Field.Name == "fieldExportHeader"
           && e.ValueType == PivotGridValueType.Value)
                e.DisplayText = "Export in progress, please wait...";
        }


        private stringGetLayoutDiscription(PivotGridControl pivot,
        PivotGridField unnecessaryField)
        {
            string text = "ColumnFields: ";
            List<PivotGridField> fields = pivot.GetFieldsByArea(PivotArea.ColumnArea);
            for(int i = 0; i < fields.Count ; i++)
            {
                if(object.ReferenceEquals(fields[i], unnecessaryField)) continue;
                text = text + fields[i].ToString();
                if(i < fields.Count - 1)
                    text = text + " -> ";
            }
            return text;
        }
    }
}

Tip: In Visual Studio select the PivotGridControl and use the Events view to add a CustomExportFieldValue and FieldValueDisplayText events for the PivotGridControl.

Form_Load calls PopulateTable that gives you some data to work with. If you use a database table then you will likely skip this step. pivotGridControl1.RefreshData does just what it says—you added data now we need to refresh the grid. The BestFit method automatically adjusts field columns to the minimum width needed to display the contents of each field.

PopulateTable simply adds some rows of data to the DataTable defined in the designer.

button1_Click contains the export code. BeginUpdate prevents the grid from updating while a bunch of changes are made. Call EndUpdate when all tweaks are made. The OptionsPrint features are set to hide the PrintColumnHeaders, PrintDataHeaders, and PrintFilterHeaders. A new PivotGridField is added to the ColumnArea with the caption “Export”, and the OptionsView.ShowGrandTotals property—to show the grand totals for the data fields—is set to true. ExportToXls actually exports the data to an MS-Excel file, and System.Diagnostics.Process.Start with a filename uses the operating system’s knowledge of file types to figure out which process to start, in this case MS-Excel due to the .xls extension. (You can use Automation to control Excel directly, but this approach isn’t necessary.) Finally, after the export the new PivotGridField is removed.

When the ExportToXls method is called CustomExportFieldValue is called for the PivotGridField added by the code. This event handler calls GetLayoutDescription which performs a tweak on the added field’s display text. FieldValueDisplayText displays an “exporting” message on the grid during the export.

There is a lot of tweaks going on with this code. Extra PivotGridFields are added and removed. Redundant field headers are hidden. (These are the dragable buttons the user has access to on the forms—to move fields between data, row, and column areas—that aren’t really needed in an Excel worksheet.) And, some tweaking of the text output is done.

The .NET Framework has literally thousands (maybe tens of thousands) of hidden gems. In the dark ages programmers learned a language’s grammar and a few dozen keywords and used—don’t say it—books to learn about a language’s function library. Literally, you could pick up a new language relatively quickly—except for C++—but it was still hard to use because one had to read analog books; Kindle didn’t even exist. Now if you want to learn a new language or framework you can still pick up the keywords and grammar pretty quickly, but the frameworks are massive. If one had to search a book to figure out how to use the .NET Framework it would be an impossible task. Thankfully, with Google, Wikipedia, blogs, and searchable articles if a developer can state a problem in plain English then there is probably some sample code that demonstrates a solution. I would venture to say that with content being added daily a developer can find a solution to almost any programming problem. This blog is my homage to the Web eventually covering everything.

.NET 4.0 added the BigInteger. BigInteger theoretically has no upper or lower limit, which is why it has no MinValue or MaxValue properties like other numeric types. You can assign a BigInteger a number as big as your computer’s memory will permit. For example, I computed Fibonacci numbers—by adding the previous two numbers 1,1,2,3,5,8,13,21,34,etc—and assigned them to BigInteger; the numbers were very huge very quickly with no end in sight.

You can use BigInteger much as you would an other integer type in .NET, but you can’t use the Math class with BigInteger. The Math class so far hasn’t been retrofitted for BigInteger. However, BigInteger has its own methods and overloaded operators that provide many of the same capabilities as other integer types have, except square root, which we will come back to. BigIntegers can be initialized with string, byte arrays, and smaller integer types. If you want to initialize a BigInteger with a byte array then it is worth noting that BigInteger stores data in little-endian order—lower order bytes precede higher-order bytes in the array. Positive values are interpreted by the most significant bit of the last byte; that is, the last bit of the last byte in the array is the sign bit. If you want to create a positive BigInteger with a byte array then place a 0 as the last element of the byte array. For example,

new BigInteger(new byte[]{255});

creates a BigInteger with the value –1 and

new BigInteger(new byte[]{255, 0});

creates a BigInteger with the value of 255.

The other conspicuously missing method for BigInteger is square root. I am not sure why it is missing, but calculating the square root of a number is useful in a large number of mathematics equations. For instance, the quadratic formula is determined using a square root, and the Sieve of Eratosthenes is used to determine prime numbers which are used in public key encryption. I have grabbed and tweaked two methods for calculating prime numbers for BigIntegers. The first is a simple method based on doubles and has an upper limit of Double.MaxValue, and the second is taken from Michael Wein’s post based on the (Isaac) Newton-Raiphson method—see Listing 1 and 2.

Listing 1: Calculating square roots up to Double.MaxValue based on

  or

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Numerics;
using System.Timers;

namespace BigIntegerDemo
{
  class Program
  {
    static void Main(string[] args)
    {
      while (true)
      {
        Console.Write("Enter a number: ");
        string input = Console.ReadLine();
        if (input == "q") return;
        BigInteger number = new BigInteger(Convert.ToDecimal(input));
        double result = Math.Exp(BigInteger.Log(number) / 2);
        if (result != Double.PositiveInfinity)
          Console.WriteLine("Square root of {0} is {1}", number, result);
        else
          Console.WriteLine("something went wrong");
      }
    }
  }
}

double result = Math.Exp(BigInteger.Log(number) / 2) takes the Log of the BigInteger value, it is divided by 2 and then uses the result as the exponent of the value e, Euler’s number. The drawback with this approach is that the result is a double and if it exceeds Double.MaxValue then Math.Exp will return Double.PositiveInfinity, requiring another solution for very large BigIntegers.

Listing 2 uses an implementation of the Newton-Raiphson method for calculating the square root. Since this algorithm uses basic mathematical capabilities the entire algorithm works with BigIntegers (too) and is capable of returning the square root of a BigInteger, a very big integer.

Listing 2: Calculate the square root of a BigInteger using an implementation of the Newton-Raiphson method posted by Michael Wein.

  static  BigInteger Sqrt(BigInteger number)
  {
    // problem with lower numbers to to right bit shift
    int bitLength = number.ToByteArray().Length * 8 + 1;
    BigInteger G = number >> bitLength / 2;
    BigInteger LastG = BigInteger.Zero;
    BigInteger One = new BigInteger(1);
    while (true)
    {
      LastG = G;
      G = (number / G + G) >> 1;
      int i = G.CompareTo(LastG);
      if (i == 0) return G;
      if (i < 0)
      {
        if ((LastG - G).CompareTo(One) == 0)
          if ( (G * G).CompareTo(number) < 0 && 
            (LastG * LastG).CompareTo(number) > 0) return G;
      }
      else
      {
        if ((G-LastG).CompareTo(One) == 0)
          if((LastG * LastG).CompareTo(number) < 0 && 
            ((G*G).CompareTo(number) > 0)) return LastG;
      }
    }
  }

The original post was listed as a response at http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/c13d3fec-21d9-4f74-92de-a6132d5e9915/. In that post a revision was added—if (i==0) return G;—that returns G when right-shifting a number yields 0. This statement avoids a division by zero error. Also, I searched and there is no bitLength method for BigInteger, so I used ToByteArray().Length * 8 to calculate the bit length. Ananda Vardhana posted a while loop calculation that determines the bit length too. My solution is faster and a little simpler.

Conferences like DevLink are about learning and community. DevLink 2010 was at Lipscomb University in Nashville. On Friday, August 6th I met retired Chief Justice Judge Alston, his wife Kiyoko, and Judge Alston’s dog Pecos. Judge Alston was the only blind Judge in Michigan when he retired. Judge Alston told me that he wanted to build software as a service to make it easier for Michiganders to work with the Michigan courts. Judge Alston has one additional challenge, albeit I suspect a minor one, he is blind. Pecos is his seeing eye dog.

Judge Alston told me that a big challenge for him was designing GUIs. We talked about how great controls and components can help any developer create professional, compelling GUIs, which is of course part of my job as a Technical Evangelist, and of course it is true. I also told Judge Alston about GiveCamps. A GiveCamp is where developers get together for a weekend, volunteering their time, jump starting development for non-profit organizations. Judge Alston may not exactly represent a non-profit entity, but I suggested perhaps he could sign up to participate in a GiveCamp and get some help with developing the GUIs for his software. GiveCamps don’t have anything directly to do with DevExpress although DevExpress routinely sponsors them, and telling people about GiveCamps is not necessarily part of my job. However, I think of the opportunity to participate in community events as an opportunity to learn, to help build community, and to help create awareness about DevExpress’ products. This is the reason I present at these events—although seldom on DevExpress products directly---and this is why I like manning the DevExpress booth.

Other Party Software

Another attendee walked up on Friday and said: “I stop by your booth every year and ask you tell me why I need your products”. (I love cocky developers.) My first unspoken thought was well you have gotten by this long without our tools, clearly you think you don’t need tools. Of course, that response wouldn’t help anyone. Instead I gave my response based on my beliefs—although I doubt I convinced this particular individual.

My belief about tools are based on a quote attributed to the geometer Archimedes and a second lesson from my father. Archimedes is attributed with “give me a lever and a place to stand and I shall move the earth”, and my father said “a craftsman is known by his tools”. Collectively my belief is tools can move mountains and with quality tools you can move them in style and adroitly. From over 20 years experience I know that there is no way any developer with no tools can compete with a developer with a set of good tools. Even a less experienced developer is likely to outpace a great developer if the former has good tools. Sure, a really smart guy can build anything, eventually, but building everything from scratch is impossible. For example, if you are using the .NET framework you are already using about 2 million lines of pre-existing code.

Finally, there is no such thing as third party code. There is code each of us writes and code written by others, other party code. Most of the code you and I use is other party code already. There is a single canonical rule expressed in two ways that supports the idea of using other party code. One version of the canon is using existing code is always cheaper than writing code from scratch, and the other version is that intellectual property means more than just writing code. When one writes his or her own code intellectual property has been created and must be owned, and ownership exceeds the cost of initial creation. The code has to be designed, coded, debugged, and updated over time. So, intellectual property is like getting married and having babies, the cost lasts beyond conception; the cost spans a lifetime.

So, when someone asks me why do I need your (DevExpress’) tools, I say you don’t need our tools, but you do need tools. Ours just happen to be the best available. The reason DevExpress’ tools are the best available choice for developers is that the tools are great, the results speak for themselves, and DevExpress provides excellent help, online videos and blogs, and we send evangelists all over the world talking to developers who help us make them better, all the time. With Developer Express you get scores of professional controls and tools for Windows, the Web, Silverlight, WPF, Delphi, and CodeRush and Refactor Pro! The cost to an individual developer is a few hundred bucks. No royalties, no complicated distribution fees, no hidden costs, and no marriage to home grown intellectual property.

Comparing LINQ to XML to DynamicObects for XML in VB10

My session at DevLink was Saturday at 10:30 am. The session was Comparing LINQ to XML to Dynamic Objects for XML in VB2010. This is a little like comparing apples to oranges because LINQ to XML is for XML and Dynamic Objects for XML is just one scenario for Dynamic Objects; Dynamic Objects are not just for XML. The session went pretty well, even thought my session had a modest turn out. There were 150 sessions to choose from, it was Saturday—the end of the conference—and these things happen.

We had a drawing during my session and one lucky person—Kim Styles—is the winner of DXperience Enterprise and Visual Studio 2010 Ultimate with MSDN.

Thanks to John Kellar and company for coordinating a great event, and thanks to all of the attendees that stopped by the DX booth. A special shout out to Kevin Pilch-Bisson the Dev Lead for C# for the unsolicited comments about DevExpress’ CodeRush.

UPDATE: Please check out our recent post regarding enhancements to pivot grid and charts integration

The XtraPivotGrid is a great control for end-user composability. You can associate an XtraPivotGrid with a datasource and let the user figure out how the data should be organized. The XtraPivotGrid let’s you (the developer) and the user to pick which fields are row fields, which are columns, and which are displayed in the data area. You can create hierarchies of columns and rows, for example, if you have categories of products and products then you could place both of the categories and the products along the rows-side of the XtraPivotGrid and all of the products would be associated with it’s category.

The XtraPivotGrid works sort of like a spreadsheet. At the end of the day an XtraPivotGrid is going to display text and numeric data and is capable of display totals—a default option. And, text and numeric data is great but users often have to examine the numbers to derive meaning. If you combine a chart with the numbers—a pie or bar chart—then the chart ala a picture is a thousand words quickly conveys information, like biggest and smallest. For example, if you display product sales in the XtraPivotGrid then you could combine a chart with the grid to create a visualization of the data, and users can quickly figure out things like top sellers. The best part is that you can use an XtraPivotGrid as the data source for an XtraChart, then whatever the user does in the grid is automagically reflected in the chart—select specific rows, sort, filter, re-organize the data and the chart reflects the numeric and text data the user sees.

You can create a form with an XtraPivotGrid and XtraChart pairing without writing a stitch of code. The following steps will walk you through creating a WinForms application that uses the Northwind Product Sales view. The XtraPivotGrid gets its data from the view, and the XtraChart gets its data from the XtraPivotGrid. Move fields around in the XtraPivotGrid and the XtraChart is updated to reflect the data it “sees” in the XtraPivotGrid. There are oodles of settings for the XtraPivotGrid and the XtraChart, but we’ll take a fairly direct route to get the result shown in Figure 1.

 
Figure 1: An XtraPivotGrid and XtraChart; the XtraPivotGrid is the data source for the XtraChart.

1. Add an XtraForm to your project so you can use the LookAndFeel features of the DevExpress suite
2. Drop an XtraPivotGrid on the left side of the XtraForm
3. Set the PivotGridControl’s Anchor property to Top, Bottom, and Left
4. Drop an XtraChart control on the form; the ChartControl’s designer will be displayed; pick the bar chart type—see Figure 2—and click Finish
5. Position the ChartControl to be to the right of the PivotGridControl and set the ChartControl’s Anchor property to Top, Bottom, Left, Right
6. Click on the PivotGridControl’s smart tag and click Choose Data Source|Add Project Data Source
7. Use the Data Source Configuration Wizard and select the Northwind database and the Product Sales for 1997 view
8. This will set the PivotGridControl’s DataSource to the productSalesFor1997BindingSource
9. Run the PivotGridControl’s designer from the grid’s context menu—accessed by right-clicking over the grid
10. In the PivotGridControl’s designer (Property Editor) click the Show Field List button >>
11. Drag and drop the ProductName and ProductSales fields from the Field List to the PivotGrid Fields list-see Figure 3
12. Close the Property Editor
13. In the PivotGridControl drag and drop the ProductName field (represented by a button-looking control) to the location that reads Drop Row Fields Here
14. Drag the ProductSales field to the location on the PivotGridControl that reads Drop Data Items—see Figure 4
15. Switch to the Properties window for the PivotGridControl, expand OptionsChartDataSource and set ShowRowGrandTotals to False
16. Click on the ChartControl; change the ChartControl’s DataSource property to pivotGridControl1 by expanding the dropdown, clicking Other Data Sources, Form1 List Instances, pivotGridControl1—see Figure 5
17. Set the ChartControl’s DataAdapter property to (none)
18. Scroll down to the ChartControl’s SeriesDataMember and from the drop down list select Series
19. Scroll down to the ChartControl’s SeriesTemplate property and set the sub-property ArgumentDataMember to Arguments and ValueDataMembers Value sub-property to Values
20. Expand the SeriesTemplate PointOptions sub-property; Expand the nested ValueNumericOptions and change the Format property to Currency
21. Scroll up to the ChartControl’s Legend property and set the Visible sub-property to False
22. Click on the bar chart part of the ChartControl to select the DevExpressXtraCharts.XYDiagram (part) of the ChartControl; change the Rotated property to True
23. Click on the X-Axis which is now along the vertical edge of the chart to select the DevExpress.XtraCharts.AxisX part of the control; set the Reverse property to True in the Properties window
24. Click on the Y-Axis of the ChartControl which is now along the horizontal axis to focus the DevExpress.XtraCharts.AxisY part of the control; expand the Label property and change the Angle sub-property to –45 to make the Y-Axis text diagonal
25. Change the Label’s Antialiasing sub-property to True to smooth out the appearance of the axis text
26. Expand the NumericOptions property for the AxisY and change the Format sub-property to Currency and Precision sub-property to 0
27. In the Properties window select the XtraForm and change the LookAndFeel UseDefaultLookAndFeel to False and SkinName to Blue
28. Run the demo

When the demo is running drag the Product Name field to the Rows area and the Product Sales field to the data area. Experiment with the context menus for the fields and sort or filter the data, change the number of selected rows, and consider adding the Category Name to the Rows area to see how the chart is effected.

 
Figure 2: Choose the Bar chart type in the Chart Wizard.

  
Figure 3: Move the fields you want from the Field List to the PivotGrid Fields list.

 
Figure 4: Drop the fields in the row, columns or filter area to create the XtraPivotGrid view.

 
Figure 5: Select the XtraPivotGrid control for the XtraChart’s DataSource.

If you add the CategoryName field to the Rows area then a hierarchy of products by category is created. To select all of the products for a given category just click on the category. See Figure 6 for an updated view of the form with the category added to the Rows area and the Beverages category selected.

Figure 6: Add the Category Name to the Row area and select the Beverage category to update the chart.

About 1 in every 3 applications I write there is a legacy data requirement. Some data in the application comes from a text file, across and FTP connection, or is exported to one of these formats. Another common request is to zip up these files for consumption. I am sure some if you have those requirements too. For that reason I implemented this little hack that let’s you quickly and easily create a zip folder and put data in it. Fundamentally I don’t have a problem with hacks as long as it is acknowledged that they can be prone to fragility and they are well-documented.

This solution depends on knowing that the header information for a zipped folder in Windows contains 21 bytes with the first four being the values 80, 75, 5, and which is basically the characters PK♣♠. If you write 80,75,5,6 followed by 17 zeros to a file it magically becomes a zip folder. then, by using Shell32.dll and the Shell32.Folder.MoveHere function you can move files easily into this zip folder. If you are curious how things—zip folders, undeleting files, keyboard hooks, and that sort of thing—then you might have fun with this code. If you actually have a compression requirement then this little bit of code might be useful for you. To implement the solution add a reference to Interop.Shell32.dll and try the code in Listing 1.

Listing 1: This code creates a Windows zip folder and demonstrates how to move files into it, implicitly compressing those files.

Imports System.IO.Compression
Imports System.IO
Imports System.Security.AccessControl
Imports Shell32


Module Module1

    Sub Main()
      'GZipStream()

      Const folder As String = "..\..\..\..\test.zip"
      Dim bytes(21) As Byte
      bytes(0) = 80
      bytes(1) = 75
      bytes(2) = 5
      bytes(3) = 6

      File.WriteAllBytes(folder, bytes)

      Dim sh As New Shell
      Dim dest = sh.NameSpace(System.IO.Path.GetFullPath(folder))

      ' source
      Dim filename As String = "..\..\..\..\data.txt"
      File.WriteAllText(filename, New String("w", 1000000))
      dest.MoveHere(System.IO.Path.GetFullPath(filename))

      Dim fsw As FileSystemWatcher = 
        New FileSystemWatcher(Path.GetDirectoryName(System.IO.Path.GetFullPath(filename)))
      Dim result As WaitForChangedResult = fsw.WaitForChanged(WatcherChangeTypes.All)
      

      For Each item In dest.Items
        Console.WriteLine(item.name)
      Next

      Console.ReadLine()
    End Sub

End Module

The Const folder defines a application-relative file folder. the byte array of 12 characters is used to create the zip folder. The first four bytes are 80, 75, 5, and 6, and the remaining bytes are 0s. File.WriteAllBytes actually creates a Windows zip folder. The next statement creates an instance of the Shell object, followed by a call to the Namespace method which creates a Folder COM object. The next two lines create a simple large file—this is where you should create your output file—with a lot of highly compressible data. The call to dest.MoveHere moves the large text file to the zip folder. The interesting predicament here is that because this is a Console application it appears the Shell32 and COM stuff can be cleaned up before the move actually happens. By using a FileSystemWatcher and WaitForChanged you can force the application to wait until the text file is moved to the compressed folder. (The remaining code just dumps the files in the compressed folder.

It is worth noting that the System.IO.Compression namespace in .NET supports compression and decompression via the GZiPStream. You can experiment with this class to create (write/read) .gz files that use a lossless compression algorithm. Lossless compression algorithms are designed to create exact replicas of the original data, which is useful for data content like database tables or text files. Another general compression algorithm is lossy, which offers greater compression but some data might be lost. Lossy algorithms are used with things like images where a little fidelity is fungible. listing 2 contains an example of similar code using GZipStream.

Listing 2: Compressing a text file using System.IO.Compression.GZipStream

Imports System.IO.Compression
Imports System.IO
Imports System.Security.AccessControl
Imports Shell32


Module Module1

    Sub Main()
      Dim filename As String = "..\..\..\..\data"
      File.WriteAllText(filename + ".txt", New String("w", 1000000))

      Using inStream As New FileStream(filename + ".txt", FileMode.Open)
        Using outStream As FileStream = File.Create(filename + ".gz")
          Using compress As GZipStream = New GZipStream(outStream, CompressionMode.Compress)
            inStream.CopyTo(compress)
            Console.WriteLine("Compressed from {0} from {1} to {2} bytes",
              inStream.Name, inStream.Length, outStream.Length)
          End Using
        End Using
      End Using

      Console.ReadLine()
    End Sub
End Module

When I compared the results from the two samples, the code in Listing 1 indicates that the hack compressed the text files to 1K of data and the GZipStream solution compressed the same file to about 8K. Good to know. Sometimes hacks are used because other solutions don’t exist. Sometimes hacks are used because they produced desired results like more compression. If you decide to use a Hack add it to the Task List by using the ‘Hack: comment here feature of Visual Studio.

The XtraPivotGrid is one of my favorite controls. The reason is simple: With a minimal effort you can implement a grid bound to data, the user can move data to rows, columns, and the data area and ad hoc reports can be printed or the data can be exported to multiple formats with a minimal amount of effort. Power and ease.

Historically, users can find it challenging to get data on a form in just the right way. Often these discussions take much longer than it takes to actually design the form. With the XtraPivotGrid you can bind data to the PivotGridControl and then let the user move data around, apply filters, and decide for themselves how the data should be laid out and what that data means. These means power and flexibility without protracted gerrymandering by developers. Additionally, the PivotGridControl has built in printing/reporting and exporting. This means the data can be printed as hard copy reports or exported to other formats like Excel of HTML for further manipulation or sharing across the Web.

The message I want to convey here is that using the XtraPivotGrid as an data exporting tool is easy, it is not labor intensive. Consequently the demo is straight forward and easy to reproduce. The power is built into the tool without a lot of struggling on our part. To recreate the demo—shown at design time in Figure 1—follow these steps:

1. Place a RibbonControl on a Windows Form (VB 2010 was used for this demo)
2. Place a PivotGridControl on the form and set Dock to Full
3. Using the XtraPivotGrid’s smart tags menu select Choose Data Source and walk through the wizard binding the grid to the Northwind Products table
4. Run the XtraPivotGrid’s designer and click Retrieve Fields—see Figure 2
5. Right click on the RibbonControl and select Run Designer
6. Add two BarButtonItems and assign them to the default RibbonPageGroup—see Figure 3
7. Change the RibbonPage’s Text property to Export
8. Change the RibbonPageGroup’s Text property to Export
9. And, change one BarButtonItem’s Caption to Export to Excel and the other one to Export to HTML
10. For both of the BarButtonItems (in the designer) switch to the Events view and implement an ItemClick event handler
11. Switch to the code behind view and add a reference to Microsoft.Office.Interop.Excel—for the demo I used Microsoft Office 2010, which is version 14.0 of the Interop DLL
12. The code-behind in listing 1 uses the built-in feature of the PivotGridControl to export to Excel and Excel interop to open Excel and the built-in export to HTML feature to export to HTML and then run IE (or your default browser) to show the exported HTML

Figure 1: Bind data to an XtraPivotGrid and let the user  decide where to locate the data elements.

Figure 2: Bind to a data source and retrieve the data fields and you have a functioning XtraPivotGrid form.

Figure 3: Add two BarButtonItems; these will be used to implement two exporting behaviors.

Listing 1: The code-behind for the demo.

Imports Microsoft.Office.Interop.Excel
Imports System.IO


Public Class Form1

Private Sub Form1_Load(ByVal sender As System.Object,
ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'NorthwindDataSet.Products' table. 
'You can move, or remove it, as needed.
Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)

End Sub

Private app As Application
Private Sub BarButtonItem1_ItemClick(ByVal sender As System.Object,
ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick
  ' export to excel

  Const filename As String = "..\..\data.xlsx"

  If (File.Exists(filename)) Then
    File.Delete(filename)
  End If

  PivotGridControl1.ExportToXlsx(filename)

  app = New Application()
  app.Visible = True


  app.Workbooks.Open(System.IO.Path.GetFullPath(filename))

End Sub

Private Sub BarButtonItem2_ItemClick(ByVal sender As System.Object,
ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem2.ItemClick
  ' export to HTML
  Dim filename As String = "..\..\data.html"
  If (File.Exists(filename)) Then
    File.Delete(filename)
  End If

  PivotGridControl1.ExportToHtml(filename)
  Process.Start(System.IO.Path.GetFullPath(filename))

End Sub
End Class

The first ItemClick event cleans up existing .xlsx files, uses the PivotGridControl to export the data, start an instance of MS-Excel and opens the output file. GetFullPath is used to convert the relative application folder to a complete path because Excel will unlikely be running in your application’s directory. the second ItemClick event cleans up existing output .html instances, gain uses the PivotGridControl to export to HTML and then uses Process.Start to open the .html file. Because Windows maps file types to applications, starting a process with a .html file should run your default browser, that is the default application for .html files.

Exporting to CSV (comma-separated values), .xls, .xlsx, ,html, .Mht, .Pdf, .Rtf, and .txt are all supported by the XtraPivotGrid. the best part is that you get all of this behavior and the formatted output looks great—see Figures 4 and 5.

Figure 4: A function Excel worksheet with the exported data.

 
Figure 5: XtraPivotGrid data exported to HTML and displayed in IE8

I hope when you read this blog you think “man that is easy!” It is easy for a couple of reasons: first, I knew what I wanted to accomplish, had the DevExpress controls that did the heavy lifting almost automatically, and secondly I was aware of the Interop capabilities and that opening an HTML file would open my browser. A little knowledge and powerful tools go a long way toward making heavy lifting a whole lot easier.

To create the Windows Form that produced the data outputs shown I simply dragged the Category ID and Product Name fields to the row area in the XtraPivotGrid, and dragged and dropped the Units In Stock, Units On Order, Quantity Per Unit, and Unit price fields to the data area. You could just as easily create this default layout for your users, again without writing any code.

Why is all of this default capability compelling to me? Well, I have been writing software a long time now and have had to create applications with these kinds of capabilities from scratch—all manually constructed with VBA, DDE, COM, OLE Automation—and now just how much work is being done for me by the DevExpress controls. Many users need some flexibility in the layout of a control, which the XtraPivotGrid provides, and some rudimentary exporting or printing capabilities and DevExpress controls provide all of this functionality out of the box.

I like the XtraPivotGrid. The XtraPivotGrid lets users create crosstab views of data that permit alternative ways to look at the same data. More than that I like the XtraPivotGrid for its end user composability. It is one of those controls that naturally lend itself to end user customization post delivery. By dragging and dropping data elements to row, column, data or filter areas a user can completely change the layout of the data and look at the data from any perspective that makes sense to the user. For the developer the XtraPivotGrid means that the amount of time spent at design time can be reduced; bind the XtraPivotGrid to a data source (or OLAP) source and let the user tinker with the appearance.

If you combine the end user composability factor with built in printing capabilities a user can take a code-light XtraPivotGrid form and turn it into an ad hoc reporting engine. All of this capability is built into the XtraPivotGrid. As a developer you just need to obtain your copy of DXperience (or the XtraPivotGrid) an you get these features and your customers can have access to the power and flexibility without necessitating writing a ton of code on your part. the following steps walk you through creating a WinForms application with an XtraPivotGrid and turn it into an ad hoc reporting tool with a minimum of effort.

To create the demo (shown in Figure 1) follow these steps:

1. Create a WinForms application in Visual Studio 2010

2. Drag a DevExpress RibbonControl onto the form. (This will be used to initiate printing)

3. Use the RibbonControl designer to add a BarButtonItem to a RibbonPageGroup (see Figure 2)

4. Switch to the BarButtonItem’s event’s view and implement the ItemClick event. (See Listing 1 for the code for the ItemClick event)

5. Drag a PivotGridControl to the form and change the Dock style to Fill.

6. From the PivotGridControl’s smart tags menu select Choose Data Source, and click Add Project Data Source

7. Configure the Northwind database’s Products table

8. Add the code to the ItemClick event as show in Listing 1

9. Run the demo

Figure 1 shows the runtime view and Figure 3 shows the design time view. Notice that in this demo we didn’t even set the columns to any of teh areas in the PivotGridControl. We will let the end user decide where to display the data elements at runtime.

figure 1: The runtime view prior to setting the data elements to the desired areas of the XtraPivotGrid.

Figure 2: Add a BarButtonItem to the RibbonControl and implement the ItemClick event for the print action.

Figure 3: The design time view of the demo.

Listing 1: the code-behind for the ItemClick event.

Public Class Form1

  Private Sub Form1_Load(ByVal sender As System.Object,
    ByVal e As System.EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'NorthwindDataSet.Products' table. 
    'You can move, or remove it, as needed.
    Me.ProductsTableAdapter.Fill(Me.NorthwindDataSet.Products)
  End Sub

  Private Sub BarButtonItem1_ItemClick(ByVal sender As System.Object,
    ByVal e As DevExpress.XtraBars.ItemClickEventArgs) Handles BarButtonItem1.ItemClick

    PivotGridControl1.OptionsPrint.VerticalContentSplitting =
      DevExpress.XtraPrinting.VerticalContentSplitting.Smart
    PivotGridControl1.OptionsPrint.PageSettings.Landscape = True
    PivotGridControl1.Print()

  End Sub
End Class

Clearly the user will want to move data elements around and configure a layout that tells them a user story about the data. Follow these steps to configure one view of the data.

1. In this order drag the CategoryID to the area that reads Drop Row Fields Here

2. Drag the SupplierID next to the CategoryID

3. Drag the ProductName next to the SupplierID

4. Drag and drop the Units In Stock, Unit Price and Units On Order fields to the area that reads Drop Data Items

5. Collapse a few of the categories so you can see the totals and grand totals row—see Figure 4

6. Click the print button

Figure 5 shows the first page of the printed report. For an ad hoc report this conveys the data desired in the layout desired and it looks pretty good for a no-code solution. If you want more control over the “report” then consider switching to XtraReports. However, a lot of time users need ad hoc reports as often as they need pre-determined reports, and the built in printing capabilities make ad hoc reporting a reality with little or no effort from you.

Figure 4: The view after following steps 1 through 6 above.

Figure 5: An ad hoc report fully created by the end user.

If you haven’t used the XtraPivotGrid before there are a few features I’d like to point out before we wrap up. Click any of the field buttons and a filter tag menu pops up. This feature let’s the user pick the data results for that field, including all or individual fields. Click field buttons in the row area to change the sort order of the that row. Right-click the a field button to display a context menu. the context menu let’s the user Reload data, hide, change the order, show the field list (to retrieve hidden field’s), and show a PivotGrid Prefilter dialog that enables the end user to build custom data filters visually (see Figure 6).

Figure 6: Use the PivotGrid Prefilter at runtime to define custom data filters. (In the example  only categories greater than 5 are shown).

After you apply a filter the filter is shown at the bottom of the XtraPivotGrid and it can be disabled and re-enabled or deleted by the end user (see Figure 7).

Figure 7: Enable, disable, or delete prefilters defined by the end user here.

There are a ton of blog posts on covariance and contravariance. These subjects are based on math principles of the same name and applied in an analogous way to .NET 4.0 programming. The rub is that a lot of the blog posts and the help files seem to do a good job of explaining assignment compatibility more than covariance and contravariance. the most common definition goes something like this: suppose you have a parent class Person and child classes Employee and Customer that inherit from Person. A Person could be a Customer too, so it is a wider type relative to Employee and Customer and Employee and Customer are narrower types. It makes sense that you can declare a Person and assign it an instance of a Customer or Employee. This underpins how polymorphism is supported but describes assignment compatibility.

A second part of the definition is that covariance preserves assignment compatibility and contravariance is the opposite of regular child to parent assignment. This is a little better but not entirely or tangibly concrete. To make these subjects a little more concrete and hopefully less abstruse I will use an exemplar for both topics, illustrating what one might intuitively like to do but couldn’t prior to .NET 4.0. Using the class diagram in Figure 1 and generic delegates and Lambda Expressions (for simplicity) let’s examine the before and after support for variance.

Figure 1: A simple class diagram showing inheritance relationships.

In pre .NET 4.0 you could define a delegate with a generic parameter, specify the parameter type. Suppose that parameter type was a child type—Customer in our example. It seems intuitive that the same delegate could be assigned to a second delegate that accepted a parent type. Unfortunately this didn’t work—see Listing 1.

Listing 1: Pre .NET 4.0 this seemingly intuitive assignment didn’t work.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace VarianceDemo
{
  class Program
  {

    delegate T MyFunc<T>();

    static void Main(string[] args)
    {
      // attempt at covariance
      MyFunc<Customer> getCustomer = ()=>new Customer();
      MyFunc<Person> getPerson = getCustomer;

      Console.ReadLine();

    }
  }

  class Person{}
  class Customer : Person{}
  class Employee: Person{}
  class Manager: Employee{}
}

It really seems like a function that returns a a more derived type—Customer—should be compatible with a function that uses a less derived type, a parent class. As is this code doesn’t work and didn’t work prior to .NET 4.0. However, if you change the generic parameter T to an out parameter in .NET 4.0 then assignment compatibility via covariance is supported. Listing 2 contains the very simply revision.

Listing 2: Change the generic parameter to an out parameter to support covariance—narrower type to wider type assignment.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace VarianceDemo
{
  class Program
  {

    delegate T MyFunc<out T>(); // use out modifier

    static void Main(string[] args)
    {
      // covariance
      MyFunc<Customer> getCustomer = ()=>new Customer();
      MyFunc<Person> getPerson = getCustomer;

      Console.ReadLine();

    }
  }

  class Person{}
  class Customer : Person{}
  class Employee: Person{}
  class Manager: Employee{}

}

The only change to the code is the use of the out modifier in the delegate definition. (The use of the Lambda expression is for convenience only.) Now getPerson will return a Customer which is after all a kind of Person.

Contravariance supports the opposite direction of assignment. For instance, you might like to define a delegate that performs an action on a Person and assign that to a delegate that performs that action on a Customer. Intuit9ively, you might write the code in Listing 3.

Listing 3: An attempt at contravariance—assigning a wider type to a narrower type.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace VarianceDemo
{
  class Program
  {

    delegate void MyAction<T>(T t);

    static void Main(string[] args)
    {

      // attempt at contravariance
      MyAction<Person> printPerson = (person)=>Console.WriteLine(person);
      MyAction<Customer> printCustomer = printPerson;


      Console.ReadLine();

    }
  }

  class Person{}
  class Customer : Person{}
  class Employee: Person{}
  class Manager: Employee{}
}

This seems intuitive too. Why not? A Customer is a kind of Person and it is likely that you might want to perform the same kinds of actions on Customers as you would Persons. Prior to .NET 4.0 this didn’t work, but if you change the generic parameter to an in parameter then assignment compatibility in the form of contravariance is supported—see Listing 3.

Listing 3: With the in modifier on delegate parameter assignment from a wider type to a narrower type works too.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace VarianceDemo
{
  class Program
  {

    delegate void MyAction<in T>(T t);

    static void Main(string[] args)
    {
      // contravariance
      MyAction<Person> printPerson = (person)=>Console.WriteLine(person);
      MyAction<Customer> printCustomer = printPerson;

      Console.ReadLine();
    }
  }

  class Person{}
  class Customer : Person{}
  class Employee: Person{}
  class Manager: Employee{}

}

In the revision printCustomer can piggyback on the printPerson behavior. The compiler also checks scenarios at compile time to make sure that the contravariant assignment makes sense. For example, the compiler will catch this operation and a compiler error will occur:

MyAction<Employee> printEmployee = (employee) => Console.WriteLine(employee);
MyAction<Customer> printCustomer = printEmployee;

An Employee is not a Customer.

There is a lot of information, but the best source of information including some of the math behind variance can be found on Charlie Calvert’s and Eric Lippert’s blogs. Dozens of other posts talk about assignment compatibility for the most part—but if they all said just use the out modifier for covariance and the in modifier for contravariance then they’d be a lot farther ahead.

Variance comes into play in delegates, collections, interfaces, and generics. For the most part I think of the subject as better, more intuitive support for assigning child types to parent types and vice versa and it is covariance and contravariance that extend and preserve assignment compatibility in ways that seem more intuitive.