Page 1 of 3

Xtract - Data Cube - Optimized version

Posted: Tue Aug 25, 2009 3:26 pm
by Rathinagiri
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.

Image

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.

Image

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.

Image

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! :)

Image

Suppose we add Salesman in Column Grouping, what happens?

Image

Image

Suppose we add Region also in Row Grouping and Maximum Discount to Data operations, what happens?

Image

Image

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. :)
xtract.zip
(8.24 KiB) Downloaded 501 times
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.)

Re: Another Grid Utility

Posted: Tue Aug 25, 2009 3:51 pm
by esgici
Thanks Rathi for sharing.

Could you send your sample data too please ? ( may be .txt or .csv ) format )

Regards

--

Esgici

Re: Another Grid Utility

Posted: Tue Aug 25, 2009 4:48 pm
by Rathinagiri
Sample data is already preloaded in the prg file itself Esgici. However, you can import any dbf too.

Re: Another Grid Utility

Posted: Tue Aug 25, 2009 5:08 pm
by esgici
Thanks Rathi

Regards

--

Esgici

Re: Another Grid Utility

Posted: Tue Aug 25, 2009 9:20 pm
by Vanguarda
Hi friends,

WOW rathi... very nice this function. Thank for sharing it with us.

with best regards,

Re: Another Grid Utility

Posted: Wed Aug 26, 2009 12:42 am
by luisvasquezcl
Hi Rathi,.
great job... i'm very impresive.
regards,
Luis Vasquez

Re: Another Grid Utility

Posted: Wed Aug 26, 2009 2:07 am
by apais
This is known as a Multidimentional Data Cube or simply Data Cube.
OLAP tools uses it for data analysis.

Congratulations ! You have multiplied 10X the value of our software.

Re: Another Grid Utility

Posted: Wed Aug 26, 2009 2:22 am
by fchirico
rathinagiri wrote: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.............
Honestly is one of the best tools available at HMG, I mean really.
It would be good if in the window "xTractReport" add 2 buttons which are "Export to Grid2csv" and "Export to Grid2print"

The name could be "DataCube" .

Congratulations on your creativity!

Greetings and eternally grateful, Fernando Chirico

Re: Another Grid Utility

Posted: Wed Aug 26, 2009 2:28 am
by sudip
Hello Rathi,
Thanks a lot. :)
I downloaded it. I shall confirm after testing. :D
This will be gr8 helpful to me, as I mainly create software for Sales Accounting and Inventory System.
With best regards.
Sudip

Re: Another Grid Utility

Posted: Wed Aug 26, 2009 2:55 am
by sudip
Hello Rathi,
I tested it. It's an excellent utility. Truly speaking I "was" not comfortable with pivot table ;) But, using your tutorial, now I can understand at least basics of pivot table :D Besides an "Inventor", you are a very good teacher also :D (remembering MySql, Sterio Image, Grid2Print and many more)
Yes, this will be very much helpful to all programmers.
Thanks a lot.

One wishlist:-
Is it possible to Print/Preview final result using your Grid2Print or Roberto's Report Generator :)

Regarding Name, I vote for Xtract. It means "Extract data" or "eXTRA aCcounT" :)

With best regards.

Sudip