breaking out a tree into levels

[John1Chr]

I am really struggling with the oracle tree that we are working with. Everything is one member and working with different levels is difficult. I like to breakout levels from this "tree" so that working with will be easier to sort/link etc. I am using CUBE like reporting in excel to pull the data.


I have text in a column that I would like to separate the levels. Each level is indented 5 spaces. The highest level is below the lower levels and has 5 spaces in front of it and the lowest level has 25 spaces. The lowest level is always on top and the highest is on the bottom.

25 spaces APUnit
25 spaces APUnit
20 spaces Bud Act
15 spaces Bud Prog
10 spacesAgncy
5 spacesDept

This can cycle through a few times till it hits ENTITYWIDE with No spaces. There can be many APUnits within a Budget Activity. There can be many budget activities within a budget program. There can be many Agncies within a Dept. There is only one EntityWide.

Can I get the columns to read up and put in column A the dept, B the agency, C Bud Prog, D Bud Act, E AP unit and have it fill in the columns so that all of the AP units have a tree so that I can sort in excel.

Right now I can not sort or do anything with the info even though the hiearchy is obvious. Has anyone done something similiar with code? Any ideas?

 

[SkipVought]

Hi,

The structure that you have described is for VISUAL effect. The data that supports the kind if REPORT has parent and child relationship, so bud act is parent of apunit and bud prog is parent of bud act etc., etc......


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[John1Chr]

Yes, wish there was a similiar scenerio out there that had code that could be modified. I would like to use this data to link information to data from another system and grab other dimensions (ie fund) I like to sort / subtotal using the various parent/children. I feel limited.

 

[SkipVought]

What application?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

It is not clear where you are pulling the data from. But hierarchical data is stored like this
ItemID ItemName ... other item fields, ParentID

Each item (node) has an ID, and a Parent ID. Any other data representation such as separate columns for levels is useless.
From this data structure you can put it into tree views, sort, find branch totals, etc... Even put in separate columns for display. However, working with data in separate columns would not be very useful or flexible.

So your data would be stored like this

[/tt]
ItemID, Item Description, ParentID
233   spaces APUnit 2           111
222   spaces APUnit 1           111
111   spaces Bud Act abc        789
789   spaces Bud Prog 123       456
456   spacesAgncy X             123
123   spacesDep A 
[/tt]

But it would display like

[/tt]
spacesDep A 
   spacesAgncy X             
      spaces Bud Prog 123      
         spaces Bud Act abc        
               spaces APUnit 1          
               spaces APUnit 2         
[/tt]

I like to use a tree view control to view and manage the data, and then write it back to the data structure. With a tree view control it is easy to add, sort, delete and move nodes and branches. Writing the code to do this is a little advanced, and you need to be pretty proficient in vba. If you are proficient in vb.net this is actually a lot easier to work with the tree view control in .net vice vba.

 

[John1Chr]

Application is Oracle Hyperion Smartview for Office Fusion Ed. V11.1.2.2.000 (essbase).

So maybe, try Vb.net if I want related data in a row - separate columns. Otherwise I need access to the production table.(security clearance would be an issue)

I'm new to the tool and like the tree for presentation but struggle if I need to add new dimensions (by linking to get the names vs. the code name and any other related info and resort and subtotal, etc. I'm used to working with data warehouse info that is organized in that manner.(row with levels)

 

[SkipVought]

You need the source data else you will need to reconstruct the item/parent relationships.

The forum is forum707. If the application you are working IN is Oracle, then you should post in an Oracle forum like forum1177

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[John1Chr]

I will try the oracle forum..thanks for your time.