Hi everyone!
I have created a production report for a single part. The report pulls data from mainly the Job and Order tables. The report lists all open releases for any open jobs. It also lists the materials needed for the sub-assembly, along with the operation list for that sub assembly.
My goal is to have this report accurately reflect the parent/child relationships that exist in parts with many levels of sub-assemblies. Since I don’t know how many levels of sub-assemblies there could be for a given part, is there a way to setup a recursive report to show these various levels of dependents?
My first appraoch was to duplicate the “Jobs” subquery in my BAQ, then reattach it at the top level. Then I figured I would have to do this for every child level. That’s when I came here.
I have searched, and there are a lot of threads mentioning recursion. I can’t seem to glean the trick from them. Can you point to a thread you think would be most helpful to nudge me in the right direction?
(EDIT: Whoops! I just tried to post this reply to the wrong thread!)
I just went through the ICE Users Guide and applied the CTE example to my BAQ. I got all the errors to go away, but I am not quite getting the results I had hoped for.
I think the problem lies in the way sub-assemblies are related in the parent/child hierarchy. There are two fields in JobAsmbl, Parent and Child. The fields represent the assembly sequence number for that part’s parent or child parts.
The CTE example links based on parts and material parts. How can I convert this to fit the assembly parent/child relationships? (BAQ attached to exacerbate things.)
You’ll use those parent/child fields in your union all join. There’s another common example with menu hierarchy that does something similar, might be a better example:
Thanks for the examples!
I think I have a working version of my BAQ. Now, I need to get rdl report file to reflect the hierarchy correctly.
What is the best way to indent a variable number of children levels within a rdl file? I have at least 4 levels of children within some of my assemblies.
Thanks!
Do I absolutely need a recursive BAQ (CTE query) if all my data already exists in the BAQ? Can I just use parent/child assembly sequence to setup the hierarchy in the rdl report file?
My existing BAQ does show all the asm parts for a job. I can then display the asm child and parent fields. How can I use these together to indent the tablix report field? I see that you can use the Level() function to offset the padding for a textbox, based on the recursion level.
All of this is a bit confusing, even with the variety of examples.
Blockquote
To build a recursive hierarchy in a tablix data region, you must set the group expression to the field that specifies the child data and the Parent property of the group to the field that specifies the parent data.
Based on that requirement, I believe that my group expression (child data) should be JobAsmbl.AssemblySeq. And my Parent group property should be JobAsmbl.Parent.
However, this isn’t working at all. I can’t get the Level() to show anything other than 0, so I know my recursive grouping is off somehow. The recursive version of my BAQ report kept crashing, so I reverted to my BAQ before I added recursion. The BAQ does have all the data I want in the report, I just need the report to show it correctly.
At this point I am just stabbing blindly. I change a grouping, field, property, or expression, then reupload, and rerun the report. What am I missing?
For a CTE Query, My anchor CTE, or my base query, has multiple tables with outer joins. When I create the UnionAll query, do I recreate the entire anchor CTE inside the UnionAll?
I think I have a solution for my report. I did not end up using a CTE Query. I have all the data I needed in my original BAQ, I just had to get the layout right in the report form.
I ended up grouping on AssmSeq, and parenting on AssmParent. Once I have the while report done, I’ll post it here for reference.
I believe this is my final solution. I have attached the BAQ and the Report RDL files for your reference.
This report lists all of the sub-assemblies for a given part number. The production information for each assembly part is included. Each sub-assembly is correctly parented, and the report format looks alright.
Note, you will need to setup the single part number filter in the BAQ Report, otherwise the BAQ will try to return all parts!