Hi everyone. I've been working with Node Hierarchy and Sheetnodes (+ Views) for a little while now and I've found good references for accessing external data, but I can't figure out how to fit it into my use case. I'm looking to cascade values up from children into parents (maybe several levels deep) sheetnodes. In the end, I'd like to have all the children brought to the highest parent and then perform summations/averages/etc. on the data that was in the children. I have no idea how to go about doing this so if someone could point me in the right direction I'd be very grateful.

tl;dr: How can I make a view that cascades child sheets into parent sheets?

Comments

infojunkie’s picture

infojunkie
Location Vancouver
commented
Category: support » feature

This is a feature that does not exist in Sheetnode today.

I'd be interested to know if its realization could be expressed in Excel terms, e.g. using external references or pivot tables. Such an implementation would simplify the development, since the spec would be clear.

Otherwise, I would need your helping specifying the feature. For example, how would the hierarchical organization among sheets expressed (e.g. using Relation module or node references) ? How would cells be instructed to perform a statistical function over their child sheets (e.g. using a custom spreadsheet function) ?

drillprp’s picture

drillprp commented

Sorry for the delayed response infojunkie. My current application is currently using Node Hierarchy which keeps IDs for ancestors (children) and parents. Looking around the nodehierarchy module it seems that the module provides several functions that take an nid and returns children or parents depending upon a function call. Here are two such function calls that I believe can be of use:

nodehierarchy_get_node_parent($nid) and nodehierarachy_get_node_ancentor_nids($nid)

I do think that the first step in making this sort of solution is to implement the Excel style external references, I.E. taking whole rows and columns and transplanting them into another sheet. Once that's in place integrating with nodehierarchy is a matter calling those functions and iterating over the nids. The biggest concern I see is how to specify to the sheetnode field that it is supposed to aggregate over all its children, or a subset of them. Perhaps you have an idea on how to manage the settings for that? I believe that it is safe to assume the user will know what row/columns they want to be aggregating from children.

As for performing a function over the child data I see it working no differently than if you were performing a function over just your own sheet. Take for example a parent p1 and children c1, c2. p1 has a template with column headers A,B,C and children have the matching column headers A,B,C and I want to aggregate that data into p1. I specify in my p1 template that I want the child rows to be aggregated (this comes back to my question at the end of the last paragraph) and then at the bottom of the p1 template under the child data I insert say an averaging function on column A. I've drawn a table to help visualize this.

A B C
c1:A c1:B c1:C
c2:A c2:B c2:C
=SUM(A) =AVG(B) =SUM(C)

This would cause all the rows from c1 column A,B,C to be placed below the headers, but above c2's data, then c2's data, and then the functions would be pushed down to the bottom.

Let me know if I've been unclear and if you're interested in adding this feature. I'm inexperienced in writing custom modules, but there is a chance that if you can implement the ability to transplant rows/columns from a given nid excel style that I could do the node hierarchy integration. Thanks again for your time!

peace2010’s picture

peace2010 commented

Hi,
I am faced with similar issue.. Is there a way to archive this.. I have created a content type with a sheetnode as one of the fields.. After data capture, I want to create a view that will aggregate the different sheetnode instances entered.. I am stuck aggregating those sheet nodes instances..

Thanks in advance..