Xtract - Data Cube - Optimized version
Posted: Tue Aug 25, 2009 3:26 pm
Hi friends,
For the past few weeks, I was developing a small but powerful utility for a HMG Grid Control. Let me explain the functionality.
If you use electronic spreadsheets like MS Excel/Openoffice Calc, you might have come across Pivot Table/Data Pilot. I wondered many times, why such a Database utility is not available in xBase on the fly. In deed, I utilize this Data Pilot utility a lot. I have to export the data in a grid to spreadsheet via csv files and then do Data Pilot every time. It was time consuming and involving two software.
I wished to generalize and implement this in HMG Grid control, so that we can do Data Pilot on any Grid Data.
For those, who are not familiar with Data Pilot, I explain some basics:
Now, consider the following table. It consists of 5 columns viz., Region, Product, Salesman, Quantity and Discount which are self explanatory. This is a HMG grid which contains many rows.
To make this data to be useful and to take managerial decisions, we may have to answer some of the questions like:
1. What are all the regions, products, who are all the sales people.
2. What is the total quantity sold
3. What is the total number of transactions salesman-wise/product-wise/region-wise
4. What is the total quantity sold region-wise, product-wise, salesman-wise, region-product-wise, so on and so forth in various combination
5. What is the maximum/minimum discount allowed in total, region-wise, product-wise, salesman-wise and in various combination
Now, this is actually seeing the data in various perspectives or in 3 Dimensional!
These things are possible now with my small utility.
I proceed to explain how it works. Once, we click 'Xtract', we will get the window like below. There are 5 areas, viz., Available Columns, Data Operations, Row Grouping, Column Grouping, Selected Data Operations.
Available Columns of the grid are listed in the first area. As of now, we have four operations namely Sum, Maximum, Minimum, Count. You can select any available column to move to either Row Grouping or Column Grouping or Data Operations. If you select a column for data operation, you can select the nature of operation also from the next listbox. For, Sum, Maximum and Minimum, the column shall contain numeric values. Otherwise, the data operations would return 0 as the result. If you want to remove any row/col grouping or data operation, you can do so by pressing the "Del" button near the respective area.
Now we can get a simple report:
What is the total quantity sold product-wise?
First select "Product" from the available columns press "Row" button to select the column for Row Grouping. Then, select "Quantity" from available Columns and select Sum from the data operations listbox, press "Data" button.
You can see that the "Product" column is moved to Row Grouping and Quantity with Sum, is moved to Data Operations. Now press "Report" You will get the following report!
Suppose we add Salesman in Column Grouping, what happens?
Suppose we add Region also in Row Grouping and Maximum Discount to Data operations, what happens?
So, we can group the data as we like.
I am so happy to share this (premature) utility for your testing and valuable suggestions. I request the forum members to give a suitable name for this utility.
I have the following in process:
1. Grand summary as in Pivot Tables/Data Pilots
2. Optimization of logic (I use much of arrays. If the number of rows and number of unique values increases, the program is very much slow. So, please don't check with a huge database with so many products.)
For the past few weeks, I was developing a small but powerful utility for a HMG Grid Control. Let me explain the functionality.
If you use electronic spreadsheets like MS Excel/Openoffice Calc, you might have come across Pivot Table/Data Pilot. I wondered many times, why such a Database utility is not available in xBase on the fly. In deed, I utilize this Data Pilot utility a lot. I have to export the data in a grid to spreadsheet via csv files and then do Data Pilot every time. It was time consuming and involving two software.
I wished to generalize and implement this in HMG Grid control, so that we can do Data Pilot on any Grid Data.
For those, who are not familiar with Data Pilot, I explain some basics:
Now, consider the following table. It consists of 5 columns viz., Region, Product, Salesman, Quantity and Discount which are self explanatory. This is a HMG grid which contains many rows.
To make this data to be useful and to take managerial decisions, we may have to answer some of the questions like:
1. What are all the regions, products, who are all the sales people.
2. What is the total quantity sold
3. What is the total number of transactions salesman-wise/product-wise/region-wise
4. What is the total quantity sold region-wise, product-wise, salesman-wise, region-product-wise, so on and so forth in various combination
5. What is the maximum/minimum discount allowed in total, region-wise, product-wise, salesman-wise and in various combination
Now, this is actually seeing the data in various perspectives or in 3 Dimensional!
These things are possible now with my small utility.
I proceed to explain how it works. Once, we click 'Xtract', we will get the window like below. There are 5 areas, viz., Available Columns, Data Operations, Row Grouping, Column Grouping, Selected Data Operations.
Available Columns of the grid are listed in the first area. As of now, we have four operations namely Sum, Maximum, Minimum, Count. You can select any available column to move to either Row Grouping or Column Grouping or Data Operations. If you select a column for data operation, you can select the nature of operation also from the next listbox. For, Sum, Maximum and Minimum, the column shall contain numeric values. Otherwise, the data operations would return 0 as the result. If you want to remove any row/col grouping or data operation, you can do so by pressing the "Del" button near the respective area.
Now we can get a simple report:
What is the total quantity sold product-wise?
First select "Product" from the available columns press "Row" button to select the column for Row Grouping. Then, select "Quantity" from available Columns and select Sum from the data operations listbox, press "Data" button.
You can see that the "Product" column is moved to Row Grouping and Quantity with Sum, is moved to Data Operations. Now press "Report" You will get the following report!
Suppose we add Salesman in Column Grouping, what happens?
Suppose we add Region also in Row Grouping and Maximum Discount to Data operations, what happens?
So, we can group the data as we like.
I am so happy to share this (premature) utility for your testing and valuable suggestions. I request the forum members to give a suitable name for this utility.
I have the following in process:
1. Grand summary as in Pivot Tables/Data Pilots
2. Optimization of logic (I use much of arrays. If the number of rows and number of unique values increases, the program is very much slow. So, please don't check with a huge database with so many products.)
