Tree structure in Reporting

[Rauf]

Please see the details
Tables involved : F4301, F4311
Module : Proccurement
Related Application : Document Tracer

Tree Structure
OR (75)
|_OQ (89)
|_OF (94)
| |_OV (15)
| |_OV (51)
|_OG (74)
| |_OV (88)

This is related with proccurement of an item.
The created OR(Requisition Order) is split into number of documents.
One of my application gives me the above structure.
The tree is formed because the data is stored as parent/child.

I need to create a report with the following format

Or No. Child1 Child 2 Child3
------------------------------------------------------
OR(75) OQ(89) OF(94) OV(15)
OR(75) OQ(89) OF(94) OV(51)
OR(75) OQ(89) OG(74) OV(88)

Is it doable in JDE Reporting.
Can you please share some logic behind the same.
I think the 'Looping' will work here.

 

[Rauf]

OR is there any System Functions are avilable to traverse a tree ?

 

[Larry_Jones]

JDE's ER language on its own is not capable of 'N'-level traversals of Parent-Child relationships (such as a Bill of Material) without execessive duplicate coding for every possible level. This is why JDE's architecture is forced to revert to 'C' functions to handle explosions/where used.

You "can" do a limited explosion in ER, but without variable arrays it would get really ugly.

 

[vbojan]

Or, if one prefers SQL over C, one can create Virtual table based on recursive SQL query and then make JDE application or report over that table.

Hope this helps,
Bojan

 

[Rauf]

I created a demo excel file. Can you please let me know if it is possible in JDE.

 

[JMR]

I'm going to get philosophical about your "is it possible..." question. If you know your source data and can conceive how that source data should appear as output, then, yes, it's possible.

As for your specific task: It appears you could have any number of columns. If that is correct, report design pdf output is not a very handy tool for reports needing a variable number of columns. I'd look to produce csv output via business functions (open a text file, write to it (build a string with comma-separated values), close it).

If you know you will only EVER have x-number of columns, then pdf output would work. Your biggest obstacle seems to be how to get your source data into the format you want.

I'd say, no, it's not very likely you can do what you want with a bsvw over the source table. Be a programmer here. Look at your desired output. What if you had a table that had that output data in it? Consider a 2-stage process: Build a worktable that meets your needs and use it to produce your output.

 

[Rauf]

I am new to JDE

, that is why I confused a little bit.
Sorry if I ask unnecessary questions.
Can you please have a look at the Tree structure and expected output of the report.
My doubt is,
I have only one information in my hand, OR and its id 75.
I can get the first OQ(89) by using .Select and then .FetchNext table I/O operations.
Now how I can traverse the tree to know how much 'leaf' it have. Here it is three.
If I get the number, I think, I can use a 'while' loop to print the three rows.
Am I correct ?

 

[JMR]

I understand you're new to JDE...that's OK.
Your task requires a recursive solution. There is no magic button or system function within the JDE toolset to provide an automatic tree traversal. What that means is this problem just became a generic recursion problem.

So now you may be asking how can you handle recursion in JDE. Well, there is always the BSFN. I have never tried this, but you could try to recursively call a report section...don't know if works.

You still didn't answer the question of: Do you know if there is a limit to the number of layers (order branching) you will need (ie how many columns will the report need? Is it possible that it could require ANY number of columns?)

 

[Rauf]

I think, it can be done using <font color="blue"> Subsections </font> or <font color="blue">Conditional Sections</font>

 

[Rauf]

<font color="red"> Do you know if there is a limit to the number of ... </font> <font color="green"> Yes. There will be only fixed columns </font>

And one more thing,the depth of the tree will always be less than 5.

<font class="small">Code:</font><hr /><pre>
OR (75)
|__OQ (89)
|__OF (94)
|__OV (15)
</pre><hr />

In the above tree, it is just 4.

 

[DBohner-(db)]

Another option is to use the power of SQL, SQL Views and the use of a Virtual Table.

Link a table to itself, parent to child, as many rows deep as you feel are necessary. - SQL View!
- Being sure to rename the subsequent columns as you go
- Create new Data Dictionary Items for the Subsequent (renamed) columns
- Create a Virtual Table over the SQL VIEW (do not generate)
- Create the BSVW over the Virtual Table
- REMEMBER That SQL Views are Distinct

Now, you can report on the 'tree' structure

(db)