nav[aria-label="Primary Navigation"] { padding: 0; & ul { list-style: none; width: 100%; display: flex; flex-direction: row; justify-content: start; align-items: start; gap: 30px; padding: 0; & li { margin: 0; } & ul li { list-style: none; } } }

Java Scripting Question

dbmathis

HI All, I want to use the following code in my data set onFetch, but I am no Java expert and the code found at the following link will not work as is in the onFetch. Any guidance would be appreciated. <a data-ipb='nomediaparse' href='http://stackoverflow.com/questions/3228334/how-to-display-flat-data-structure-into-hierarchical-data-structure-java'>http://stackoverflow.com/questions/3228334/how-to-display-flat-data-structure-into-hierarchical-data-structure-java</a> I don't have the ability to add anything to the BIRT engine that runs the report. I must accomplish, with Java, exactly what's being done in the link, in the onFetch. Best Regards David

Find more posts tagged with

Comments

micajblock

What database are you using? Some databases support recursive SQL.

dbmathis

I have an XML data source and the flat structure is like so...<pre class="_prettyXprint">emp id, name, manager id</pre>

micajblock

So the hierarchy is jagged. So the number of columns is not determined before hand. That might be a problem. Can you post the XML file?

dbmathis

Let me scramble the data, and I will attach after that.

dbmathis

The XML is attached. It's<pre class="_prettyXprint">CompanyEmpId, name, MgrCompanyEmpId</pre>

dbmathis

Attachment

micajblock

Is it OK to assume a maximum number of levels? How do you want the data to appear? Just indented? Oracle would do it this way: <a data-ipb='nomediaparse' href='http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL'>http://en.wikipedia.org/wiki/Hierarchical_and_recursive_queries_in_SQL</a>

dbmathis

Hi mblock, First off, I really appreciate the time you are spending to help me. I have been working on this on and off for over a year. Now this monster has shown up again and I am on a deadline for tomorrow to have a working solution. I have been up all night trying to figure this out. While indentation is needed (precisely how the Oracle view demonstrates) If I have the proper sort, indentation should not be an issue because I have the depth in my data set already. I can pass the sorted by hierarchy data into a hashmap and output it all to a scripted data set. The max depth is 15 levels. However with my example, there is 3 levels.

micajblock

So what is it you need to do? In the data I see (which is what you would get from an Oracle query):level, employee id, employee name, manager id, manager name. What else do you need? Do you wan them in different columns?

micajblock

P.S. You are also missing a row for Marcia Mathis (the top level manager)

dbmathis

I don't have access Oracle. The data I work with is under strict control and I only have access to these dynamically generated XML data sources. These reports are then put into an online web app that feeds the dynamic XML to them based on underlying Oracle queries. What I am after is onFetch code (one row at a time) that will do a hierarchical sort on the data per the posted link above in my original post and that was seen in your Oracle example, which is awesome BTW. Then have the sorted output popped into a hashmap one record at a time.

dbmathis

So Bill Hill is the top for this data. These reports get ran for different places in the hierarchy. Every record has their manager listed unless they really are at the very top in which case they would have no manager.

micajblock

OK, now I understand, you want the data sorted by hierarchy. Back to my other question - Can I assume a maximum number of levels?

micajblock

Also it is OK if it is done in the report (and not on the data set)?

dbmathis

The max depth is15 levels. If you know of a better way then I am receptive to it being done in the report. Like you acknowledged, I just need the rows of data sorted hierarchically.

micajblock

See attached report design. I slightly changed the XML to add the missing top level (also attached). Let me know if you need explanation. Using BIRT 3.7.2

dbmathis

Thanks mblock, I think I am missing the themes jar file needed for this. Does this require the library? And if so would this require that I add a jar to the report engine that's running the report? Best Regards David

dbmathis

Nevermind. I think this approach will work, will just need to nest more tables correct? Which means if I know it will never go deeper than 15 levels I will be good (if I nested more tables). If I want an open ended depth I will still need a script right?

micajblock

You do not need the library (I am not using it). The easiest way to add more table is a simple copy and paste, I designed it so it will work this way. The table has an empty slot. So copy the last table and paste it in the empty detail row (as many times as you need).

dbmathis

Very good. I had thought of this approach at the very beginning, but for some reason kept thinking "write a script". I am going to put in in the live environment and have it run for 9 levels down against 4788 rows and see what the performance looks like. I am very thankful for the time you spent on this.

micajblock

Please let me know. Can I post this example in DevShare with your sample data?

dbmathis

Yes, pleased o share. I hope it can help others that are trying to do this.

micajblock

I also thinking of a way to recursively add the table. I will let you if I figure this out.

dbmathis

Oh, yes, please do, that would be the ultimate goal here.

dbmathis

Reporting back my ultimate findings for using your approach in a live environment with a full set of data 9 levels deep consisting of 5000 records.

I was not able to get the report to run below 6 levels. There seems to be some performance issues once it gets so many levels deep. This was one of my initial concerns with the nested approach, but I am not sure there would be any gain with a scripted approach. My intuition tells me that a well scripted approach may be better. Like I said, a data set onFetch that builds the hierarchical sorted data which I can dump into a scripted data set.

Some other issues that I had to solve with your approach. The second detail row did not always find a nested table (the cases where there were no children). I had to add a text element to the top of the report and do an initial loop through the data set, store a global variable for each patent and then add visibility logic to the second detail row which asks (parent global == null) ? true : false;. Without doing this, the excel output was adding an extra row below each node that had no children. There was also an issue with padding the cells of the second detail row (nested table container) which causes a misalignment of the actual data columns that I added out to the right in the real report. Instead of padding, I created a field which dynamically indents based on the level / depth field that's already in my data set.

Hopefully this is some helpful feedback.

I am still looking for a scripted solution if anyone knows how to accomplish what I requested in the initial question in this post.

micajblock

I was afraid of that. I avoid nested tables like the plague due to this. I think the best option is a scripted data source that implements the solution you described in the beginning of this post. Unfortunately this is a bit beyond my expertise and would require more time than I can afford right now.

dbmathis

It's a tough one. I am hoping that either someone is up for a challenge or perhaps they are so good that its not a challenge at all. It will help me and perhaps others out and would be a good learning experience.