This section contains information on joint use of OLAP ModelKit and Chart ModelKit. We will create a simple application that will help you understand how to use this function.
First of all we need to define data that will be used. A small database is created in Access (named it work.mdb). Database schema is displayed in the image below.
There are three tables: goods (Goods), customers (Customer), and orders (CustomerOrder). All keys are of int32 type; the rest of fields except of Count and Cost are strings.
We will create a pivot table that will display sum spent for every type of goods by each order. Then we will create a chart based on this pivot table.
Let’s create application.
First of all we need to think about structures that will be used as data sources. DataSet will be used for this purpose. Create a table (CustomerOrder) in the DataSet. A list of its fields is displayed in the picture below.
Cust_name, country and name fields are strings, count and cost are of Double type, and id_order is of int32 type.
It is necessary to fill the DataSet in the OnLoad event using our database. Let’s write the following code:
private void Form1_Load(object sender, EventArgs e)
{
OleDbConnection Connection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=\"..\\..\\Work.mdb\"");
OleDbDataAdapter adapter2 = new OleDbDataAdapter("SELECT id_order, count, cost, cust_name, country, name FROM (CustomerOrder INNER JOIN Customer ON CustomerOrder.id_customer = Customer.id_customer) INNER JOIN goods on CustomerOrder.id_goods = Goods.id_goods", Connection);
adapter2.Fill(customerOrder);
Connection.Close();
}
DataSet is ready for use. Let’s add BindingSource and specify the created DatSet as a data source. Don’t forget to select the CustomerOrder table as DatMember property value.
Add two components onto the form: DataCube and DataCubeGrid.
Set the created BindingSource as a datasource for DataCube (the DataSource property) and specify the following parameters for creating a pivot table:
Set this DataCube as the DataCubeGrid.Source property value.
Thus, we get pivot table. Its columns contain orders ID and rows contain description of goods. The crosses contain sum by Ошибка! Не указано имя файла.
We need to create a chart based on this table and which X axis enumerates order ID and Y axis displays sum by order. We need to do it so that filtering total data by columns and rows makes chart regenerate.
Step1: Add the DataCubeView component onto the form. Set a DataCube from which the data will be taken (the DataCube property) and Ошибка! Не указано имя файла.
Now we need to set the Argument and Value properties. There are two variants. In the first variant it is possible to set GenericCaption as argument and GenericFact as Value. GenericCaption is taken from the axis specified in the DataCubeView and GenericFact is total value by this GenericCaption. In this case GenericCaption is id_order and GenericFact is sum by the order with id_order ID.
Note: You can set field names as argument and value, thouth if you modify X-Dimension, Y-Dimension or Fact values, you will need to modify chart as well. This is not very convenient, that is why we strongly advise you to use GenericCaption and GenericFact.
Now we only need to add text to the handler of the change event of the DataCubeView (the ListChanged event). This event performs when the pivot table is recalculated including tha case of filtering data. So, by writing in it text to refresh chart we will be able to regenerate chart every time data in the table is changed. Moreover, it is necessary to set method for recalculating DataCube when the application is launched. Add it to the OnLoad form handler.
Thus, we get its final variant.
private void Form1_Load(object sender, EventArgs e)
{
OleDbConnection Connection = new
OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=\"..\\..\\Work.mdb\"");
OleDbDataAdapter adapter2 = new OleDbDataAdapter("SELECT id_order, count, cost,
cust_name, country, name FROM (CustomerOrder INNER JOIN Customer ON
CustomerOrder.id_customer = Customer.id_customer) INNER JOIN goods on
CustomerOrder.id_goods = Goods.id_goods", Connection);
adapter2.Fill(customerOrder);
Connection.Close();
dataCube1.Recalculate();
}
Here is a sample code for DataCubeView refresh handler.
delegate void DataChangedDelegate();
private void dataCubeView1_ListChanged(object sender, ListChangedEventArgs e)
{
DataChangedDelegate dataChangedDelegate = chartViewer1.Chart.DataChanged;
chartViewer1.BeginInvoke(dataChangedDelegate);
}
Ошибка! Не указано имя файла.