Become an Expert Part 2: Adding Features to the Sales Management Application

Part 2 Overview

Microsoft Visual Studio contains a number of standard controls. ComponentOne Studio Enterprise provides easy-to-use controls with enhanced features which can be used in Visual Studio along with standard controls. In this series, we make use of these controls to create a Sales Management Application. In Part 1; we started building a simple application using C1FlexGrid to display and edit sales data. In this part, we'll power up our application by using features provided by C1FlexGrid.

Add Product Category

You may remember that in the previous section, "Proceeds" and "Payments" were added to the Sales Management Application based on dates. A real life organization, however, sells a number of products and might want to look at aggregate sales figures for each product category. For this, a category item needs to be added so that not just dates but category wise sales figures can also be represented.

First, let's add a column "Product Category" in the grid. Open the "C1FlexGrid Column Editor" by selecting Designer... from the C1FlexGrid's smart tag to add/delete rows at any location in the grid. We will put this new column between the "Date" and "Proceeds" columns. Name it ProductCategory and set AllowEditing to False.

Setting the Value of Product Category

We have a separate CategoryMaster table where the data values are translated into display values using DataMap (use CategoryMaster script included in the sample zip to create the CategoryMaster table, and the Sales script to recreate the Sales table adding a CategoryCode data field). Here the CategoryCode value is specified and the corresponding CategoryName is displayed on the grid. Values in C1FlexGrid have been made non-editable in our application, but DataMaps can also be used where editing is possible in the grid and values are entered in a drop-down list.

ListDictionary dictionary = new ListDictionary(); dictionary.Add("CategoryCode", "CategoryName"); this.c1FlexGrid1.Cols[1].DataType = typeof(int); this.c1FlexGrid1.Cols[1].DataMap = dictionary;

CategoryCode is added as the key and CategoryName is added as the display value in the ListDictionary. With this, we have added a "ProductCategory" column in the grid and completed the settings for the DataMap.

Add a Window to Enter Data

Next, let's change the way we enter data in the grid. In Part 1, we added/modified data directly through C1FlexGrid, but this is very inconvenient in a real application. So we'll create a new input window to enter data. When data is to be entered, a new window will pop up and for simple data input.

Pop up window to enter sales data

First, we will add a Form to the project. This form will contain controls where data related to "Date", "Category", "Proceeds" and "Payments" can be entered.

public object CategoryCode { get { return this.comboBox1.SelectedValue; } set { this.comboBox1.SelectedValue = value; } }

Use the code below to get and set the entered value in the main window.

  • When new data is entered

this.c1FlexGrid1.AddItem(new object[] { "Date", ???????????? ????"CategoryCode", ????????????? ? ??"Proceeds", ?????????????? ?? "Payment" });

  • When existing data is updated

this.c1FlexGrid1["rowNum", 0] = "Date"; this.c1FlexGrid1["rowNum ", 1] = "CategoryCode"; this.c1FlexGrid1["rowNum ", 2] = "Proceeds"; this.c1FlexGrid1["rowNum ", 3] = "Payments";

When the popup window is closed, the value entered will be retrieved automatically and reflected in C1FlexGrid. Numeric values can also be entered easily through this window.

Adding Subtotals/Grand Total

Up untill now we have been making changes at the record level, but in a Sales Management Application we would need information about total sales. So let's add a totals row to calculate sales for a month. If there are multiple data rows for the same date, we'll calculate subtotals for that date also.

In order to add Subtotals and Grand Total rows, we need to simply add up the values of each column and add a new row to display the result. But here we'll use the data aggregation feature in C1FlexGrid to display values.

  • Use the Subtotal method to calculate aggregate value

// Clear subtotal. this.c1FlexGrid1.Subtotal(AggregateEnum.Clear); // Sort the contents of the grid. this.c1FlexGrid1.Sort(SortFlags.Ascending, 0, 1); // Calculate the grandtotal. this.c1FlexGrid1.Subtotal(AggregateEnum.Sum, -1, -1, 2, "Total"); // Calculate the subtotal. this.c1FlexGrid1.Subtotal(AggregateEnum.Sum, 0, 0, 2, "{0:MM/dd} Subtotals");

First let's remove the existing aggregate row. The data in the grid is sorted on two fields, first being date and then classified as per the product category. The first parameter in the SubTotal method, used to calculate the total and subtotal values, is the aggregate function type (function type is Sum in this case). Parameters 2 ~ 4 are outline level, grouping columns and aggregate columns. Instead of a specific column, -1 is used as the grouping column of the GrandTotal row. For subtotals column, it is set to 0 (Date column). The aggregate column in both cases (GrandTotal and Subtotal) is column 2 (Proceeds) in the code above. Finally, set "Total" in the caption of the totals row and a date placeholder in the caption of the subtotals row. That's all we need to do to set aggregate rows in C1FlexGrid!

For the GrandTotal row we will specify the average of the Gross Margin Rage column. Please note that the Gross Margin Rate of the SubTotals row is not calculated.

Let's modify the display style for aggregate rows so that they are highlighted in the grid.

CellStyle cellStyle = c1FlexGrid1.Styles[CellStyleEnum.GrandTotal]; cellStyle.BackColor = Color.Blue; cellStyle.ForeColor = Color.White; cellStyle.Font = new Font(Font, FontStyle.Bold); cellStyle = this.c1FlexGrid1.Styles[CellStyleEnum.Subtotal0]; cellStyle.BackColor = Color.Orchid; cellStyle.ForeColor = Color.White; cellStyle.Font = new Font(Font, FontStyle.Bold);

Here, we specify the cell style to change the display. In this application we are setting the cell background of the totals row to blue and the values to white. These are in bold. Cell styles for subtotals are also set in the same way.

Now let's run the application. Notice the Totals row at the bottom of C1FlexGrid. Values for a particular date will be displayed in the Subtotals rows. We've now demonstrated how it's simple to add aggregate rows to the grid by using C1FlexGrid methods.

Visual Display for Gross Margin Rate

Let's make some more changes. Just like the aggregate rows, we'll change the color of the GrossMarginRate column. We want to show visually where the GrossMarginRate is higher and fill the cell proportionally according to the value in the gross margin rate, sort of like a Progress Bar.

Let's change the DrawMode property of C1FlexGrid from Normal to OwnerDraw. Now the OwnerDrawCell event will be fired when the cell is rendered.

The code for the OwnerDrawCell event is as follows:

// Calculate horizontal width. Rectangle rc = this.c1FlexGrid1.GetCellRect(e.Row, "Column Index"); rc.Width = (int)(this.c1FlexGrid1.Cols[e.Col].WidthDisplay * "Gross Margin Rate"); // Draw the background of the cell. e.DrawCell(DrawCellFlags.Background | DrawCellFlags.Border); // Display bar graph. rc.Inflate(-2, -2); e.Graphics.FillRectangle(Brushes.LimeGreen, rc); rc.Inflate(-1, -1); e.Graphics.FillRectangle(Brushes.PaleGreen, rc); // Draw the contents of the cell. e.DrawCell(DrawCellFlags.Content);

First, we get the size of the cell (cell that displays the GrossMarginRate value). Then, calculate the horizontal width of the cell proportional to the actual Gross Margin Rate. Render the background, border, and so on based on the calculated value and set the colors for border and the fill color of the rectangle to display a horizontal bar graph. Finally, display the cell value (GrossMarginRate).

Set the placeholders "{0}" and "{1}" to display page number and total page numbers respectively.

Conclusion

In Part 2, we used the C1FlexGrid features to add aggregate rows, draw cells, and print the grid data. All this was done by simply calling a few methods. In the Part 3, we'll enhance our application's capabilities further by adding a few more controls to it along with C1FlexGrid and exporting data from the grid to a PDF file.

Download Sample Project

Acknowledgements

Greg Lutz

comments powered by Disqus