Pivot Table ideas

Hi Hiran,

>>  For the Pivot Table, I think the biggest problem
will be figuring out
>>  how to take the data structures of Items and
Attributes and transform
>>  that data into something that makes sense to
display in a Pivot Table.
>>  The first problem isn't so much a programming
problem as a conceptual
>>  problem, or perhaps a UI design problem...
>>
>>  Pivot Tables work best when there are a small
number of axes that can be
>>  pivoted (maybe 2 to 6), and where each axis has a
small number of
>>  possible buckets (maybe 2 to 20).
> 
> I agree. I think a drop down should be provided for the
user to select 
> chart attributes. It is better to have an upper bound
for the number of 
> axes(E.g.: 6). But there could be attributes which are
not suitable for 
> the pivot table ( E.g.: Attributes with a unique
value). The plugin can 
> either exclude such attributes from the drop down or
display a warning 
> that the table may be too big to display if that
attribute is selected. 
> In order to determine suitable attributes, I thought of
comparing the 
> item count with distinct attribute value count and
decide based on a 
> standard ( E.g.: If the item count is 50 and the an
attribute has 15 
> distinct values, the attribute is a suitable attribute
for the chart). 
> There could also be an upper bound for the number of
buckets (E.g.: 20).
> 
> Please tell me what you think about it.

That sounds like a good approach.


>>  In OpenRecord, Items have Attributes with values,
and those values can
>>  be strings, dates, numbers, or references to other
items.  It seems to
>>  me that there might be more than one approach for
mapping the ideas of
>>  Items/Attributes/values to the ideas of
Axes/Buckets/cells...
>>
>>  Here's one approach... To display a Pivot Table
with 3 axes and 108
>>  cells, you might populate the table from a set of
108 OpenRecord Items.
>>    So, for a table like the Gas Tax table, you
might have 108 Items all
>>  in the Category "Donation", where each
Donation Item has a 3 attributes
>>  called "Year", "Form", and
"Charity", plus an additional attribute
>>  called "Amount".  An individual
Donation Item might look like this:
>>     Category: Donation
>>     Year:     2001
>>     Form:     Cash
>>     Charity:  Greenbelt Alliance
>>     Amount:   $61.46
>>
>>  That first approach will only work well if the
number of Donation Items
>>  is the same as the number of cells in the Pivot
Table.
>>
>>  A better approach might be to allow for there to
be many more Donation
>>  Items, and then have the Pivot Table automatically
calculate the totals.
>>    So in this second approach, there could be 5
different Donation Items
>>  that are all donations of Cash in 2001 to the
Greenbelt Alliance, and
>>  the Pivot Table will add all 5 of the Amount
values and show that total
>>  in the corresponding cell of the Pivot Table.
>>
>>  That second approach will work smoothly if all of
the Items in the
>>  category Donation all have values for the 4
Attributes Year, Form,
>>  Charity, and Amount.  I'm not sure what the Pivot
Table code should do
>>  if some of the Items are missing some Attributes,
or if some of the
>>  Items have Amount values that are not Numbers, or
if one of the Items
>>  has a multi-valued Amount Attribute.  This second
approach also only
>>  works well if the values that the user wants to
see in the cell are
>>  things that can be totaled up, like numbers.  In
some other scenarios,
>>  the user might not want to see the sum of a set of
Attribute values, but
>>  might instead want to see an average of the
values, or see a count of
>>  the number of Items that fall into that cell.
> 
> I thought of going for the second approach, which is
complete and user 
> friendly. 

Okay, sounds good.


> For missing information, some logic can be used to
compare the 
> attributes values and if all nonempty attribute values
are matched, the 
> missing information can be predicted. 

I don't think I understand that idea.  It sounds like a
feature that you 
would add once other things are already working, so maybe we
don't need 
to discuss it yet, but when the time comes maybe you can
tell me more 
about the idea or give an example.


> Handling multiple items with same data needs to be
figured out. The 
> plugin can provide a drop down list specifying the
treatment of the 
> multiple items. For example, the drop down can have
options for numeric 
> addition, average calculation, and string addition. But
this approach 
> would not be effective if the user wants average for
one axis and 
> addition for another. I'll let you know if I find a
more effective 
> method to do this.

I like your idea of using a drop down to let the user pick
different 
ways of combining values (numeric addition, average, string 
concatenation).  You could have just one drop down for the
whole pivot 
table, so that the user could not pick "average"
for one axis and 
"addition" for another.

 Brian


____________________________________________________________
_______
Copyright rights relinquished. This work is in the Public
Domain.
For details see: htt
p://creativecommons.org/licenses/publicdomain/

openrecord-dev mailing list
openrecord-devlists.berlios.de
http://bat.berlios.de/mailman/listinfo/openrecord-dev