But isn’t that where the recursion link is happening. T22’s Parent is T2’s AssemblySeq.
How does this work:
INNER JOIN JACTE T2 ON T22.Company = T2.JobAsmbl_Company
Is that joining both the JACTE AND T2 at the same time? Or is that statement saying T2 is within JACTE?
Just never seen a double-reference like that.
Try this in the union select.
WHERE T22.AssemblySeq > T2.JobAsmbl_AssemblySeq
The Line you highlighted T2 is the alias for JACTE is could be written as for more readability.
INNER JOIN JACTE AS T2 ON T22.Company = T2.JobAsmbl_Company
1 Like
I did notice that you are not using any of the CTE fields in your final select statement. Did you add those?
Same result.
I was going to add them in once the report actually started working again. Was trying to limit the variables.
One things I WAS worried about is that the CTE query initially has T2. as AssemblySeq = 0. Then it does the recursion which becomes the resulting dataset JACTE (which is everything EXCEPT AssemblySeq 0).
But I DO need to get to get the Top Level Assy 0 back in there. So wouldn’t I still want T2 amongst the main join statements?
I think the current approach is more or less replacing T2 (JobAsmbl) with a CTE query.
But don’t I need the CTE “in addition to”?
We are close.
This is doing the recursion you are looking for.
I just tweaked the first select.
;WITH JACTE AS
(
SELECT
T2.Company AS JobAsmbl_Company,
T2.JobNum AS JobAsmbl_JobNum,
T2.AssemblySeq AS JobAsmbl_AssemblySeq,
T2.Parent AS JobAsmbl_Parent,
CAST(FORMAT(T2.AssemblySeq, '000') AS NVARCHAR(1000)) AS Calculated_AssySeqPath,
1 AS Calculated_AssyLevel
FROM JobAsmbl T2
WHERE T2.AssemblySeq = 0 and T2.Parent = 0
UNION ALL
SELECT
T22.Company AS JobAsmbl1_Company,
T22.JobNum AS JobAsmbl1_JobNum,
T22.AssemblySeq AS JobAsmbl1_AssemblySeq,
T22.Parent AS JobAsmbl1_Parent,
CAST(T2.Calculated_AssySeqPath + '.' + FORMAT(T22.AssemblySeq, '000') AS NVARCHAR(1000)) AS Calculated_AssySeqPath2,
T2.Calculated_AssyLevel + 1 AS Calculated_AssyLevel2
FROM JobAsmbl T22
INNER JOIN JACTE AS T2 ON T22.Company = T2.JobAsmbl_Company
AND T22.JobNum = T2.JobAsmbl_JobNum
AND T22.Parent = T2.JobAsmbl_AssemblySeq
WHERE T22.AssemblySeq > T2.JobAsmbl_AssemblySeq
)
SELECT
*
FROM JACTE
2 Likes
So… can I just use this… join JACTE to my main query and STILL also query T2 like the base query did. This would give me my standard T2 results, and (hopefully) an assembly lineage to sort by.
The top level assy is in the first select. So you should be ok. the CTE will have the top level and all of the children.
The T2 alias is messing with you.
Here is your CTE without the T2
;WITH JACTE AS
(
SELECT
Parent.Company AS JobAsmbl_Company,
Parent.JobNum AS JobAsmbl_JobNum,
Parent.AssemblySeq AS JobAsmbl_AssemblySeq,
Parent.Parent AS JobAsmbl_Parent,
CAST(FORMAT(Parent.AssemblySeq, '000') AS NVARCHAR(1000)) AS Calculated_AssySeqPath,
1 AS Calculated_AssyLevel
FROM JobAsmbl AS Parent
WHERE Parent.AssemblySeq = 0 and Parent.Parent = 0
UNION ALL
SELECT
Child.Company AS JobAsmbl1_Company,
Child.JobNum AS JobAsmbl1_JobNum,
Child.AssemblySeq AS JobAsmbl1_AssemblySeq,
Child.Parent AS JobAsmbl1_Parent,
CAST(TopAss.Calculated_AssySeqPath + '.' + FORMAT(Child.AssemblySeq, '000') AS NVARCHAR(1000)) AS Calculated_AssySeqPath2,
TopAss.Calculated_AssyLevel + 1 AS Calculated_AssyLevel2
FROM JobAsmbl as Child
INNER JOIN JACTE AS TopAss ON Child.Company = TopAss.JobAsmbl_Company
AND Child.JobNum = TopAss.JobAsmbl_JobNum
AND Child.Parent = TopAss.JobAsmbl_AssemblySeq
WHERE Child.AssemblySeq > TopAss.JobAsmbl_AssemblySeq
)
SELECT
*
FROM JACTE
1 Like
I’m with ya. Thank you!
Sorry, meeting and lunch got in the way… had to tweak the JobAsmbl_AssemblySeq to just AssemblySeq because that was the original syntax in the base query which was referenced in various fields and subreports, etc. Once I did that everything seems to be working.
The only thing I’m noticing (so far) in some preliminary testing is getting some repeating rows in the Operations Detail subreport, which I wasn’t getting before. I need to look at the data getting passed to that report and probably pin that down. But otherwise…
1 Like
If you want to post the query, we can take a look.
GAH! Had to restart my computer and when I opened my query back up it looks like this:
… and people wonder what @josecgomez is complaining about with Report Builder. THIS is a prime example. 
Anyway… I have a couple question which maybe you can shed some light on.
The RDD for ProdDtl includes the LaborDtl table, but it is not included in any of the relationships.
How does this impact the overall query? Is the RDD pulling in the entire LaborDtl table (which seems like it would be HUGE) and then finally filtering it down via the parameters passed from the Main Report to the Sub-Report?
Also, second question…
With now using the JACTE in my query joins:
FROM JobHead_" + Parameters!TableGuid.Value + " T1
LEFT OUTER JOIN JACTE ON T1.Company = JACTE.JobAsmbl_Company
AND T1.JobNum = JACTE.JobAsmbl_JobNum
LEFT OUTER JOIN JobOper_" + Parameters!TableGuid.Value + " T3 ON JACTE.JobAsmbl_Company = T3.Company
AND JACTE.JobAsmbl_JobNum = T3.JobNum
AND JACTE.AssemblySeq = T3.AssemblySeq
LEFT OUTER JOIN JobOpDtl_" + Parameters!TableGuid.Value + " T4 ON T3.Company = T4.Company
AND T3.JobNum = T4.JobNum
AND T3.AssemblySeq = T4.AssemblySeq
AND T3.OprSeq = T4.OprSeq
… this no longer matches up with the RDD. What impact does this have? I’m joining JobAsmbl in the RDD relationships to JobOper. But in the RDL I’m joining JACTE to JobOper. I suppose this doesn’t have to be consistent. We’re just using the RDD to pull the data and then the RDL to bend to our will? Is that the gist?
2 Likes
#Preach
it is just awful!
2 Likes
Did you format it? If you did, copy and paste it to notebook and it will still be formatted. The query viewer just does not hold any format when viewing.
1 Like
It doesn’t help that I woke up last night at 3AM to the sound of my dog throwing up on my bedroom floor. The SOUND that goes with your gif is fresh in my memory.
4 Likes
That sound should be recorded and added into our phone alarm as the only sound it can make. Nobody will snooze again, nothing will get a person out of bed faster than that sound.
2 Likes
I had that EXACT same thought last night (while trying to finally fall back to sleep after a carpet scrubbing session). I think my kids could come in yelling “FIRE” and it would take me longer to react.
That sound is an instant trigger!
Ugh… the query change also messed with some of the calculations in the report.
This definitely isn’t mathing correctly.
… all this just to change the sort order, haha.
This is where you need to be careful as I have gotten burned more than once before I finally figured it out. Even though there is no relationship, that does not mean that there is not one hardcoded in the background. I finally figured this out when I wanted data from one of those tables and created the relationship myself. When I looked at the table in the report database, there was 2 of every row. This is where having access to the report database really helps ( Access to query SSRS report database in Cloud | Epicor Ideas Portal (aha.io)). The whole table is not loaded, only the records that are needed.
Does not matter. Remember, the RDD is to extract the data out of Epicor and into the SSRS Reports db. Once they are in the database, they are separate tables that you can do anything you want with.
2 Likes