Alright, I got the errors to go away, but the BAQ won’t iterate. It just gives me the initial value listed. Could someone give me a crash course on how to iterate through something? I’ve gotten this to work in the past with an indented BOM. Now what I’m looking to do is show my demand against production and iterate the difference between those against the current on-hand qty of each part. This should show me whether or not my build plan is on track for the year or not.
Dylan, I have wanted to try this again after I got the BOM listing going.
I haven’t used recursion as much in SQL aside from that. I need to practice more with it.
Sorry I can’t help at this time.
Looking forward to any help that is posted here so that I may learn too.
I have used a lot of microsoft links and other forums (stack) as well that have helped me get further on this.
I recommend making your recursive result a CTE and then trying to do the running total using the CTE.
Hate to pry, but any chance you have some visuals/instructions on how to get this working?
Correct me if I am wrong, but I believe you said you had the recursion working and the aggregation was not?
If that is correct, take the aggregation out of the recursive query. Make your Top Level a CTE and then you can use those results in a new query and just aggregate against the results.
You are right. So in this case I have the following listing as the results:
Company: Year/Month: Part: Change: = Production - Demand
If I change this top-level to a CTE that means that this will be my anchor point, right? The way I was trying to do it before was to have the current on-hand qty first and try to iterate by the change shown for each month.
Currently, I have the CTE set the way you said and I have linked the current on-hand qty as the union. Unfortunately, this doesn’t quite seem to work. I have been taking notes from the following post. The new version of BAQ listed:
AGDB_BI_SpplyDmnd02Rec2.baq (235.7 KB)
Unfortunately I am not on Kinetic yet, so I can’t use your BAQ. Can you post the “SQL” from the general tab?
Sure thing! Don’t make too much fun of my poor BAQ building
/*
* Disclaimer!!!
* This is not a real query being executed, but a simplified version for general vision.
* Executing it with any other tool may produce a different result.
*/
with [UnionGrab] as
(select
(Case
when SOByMonth1.OrderRel_Company is not null then SOByMonth1.OrderRel_Company
when ForeQty.Forecast_Company is not null then ForeQty.Forecast_Company
when MPSQty.MasProd_Company is not null then MPSQty.MasProd_Company
when FirmJobQty.JobHead_Company is not null then FirmJobQty.JobHead_Company
when UnfirmJobQty.JobHead1_Company is not null then UnfirmJobQty.JobHead1_Company
when OHQty2.PartBin_Company is not null then OHQty2.PartBin_Company
end) as [Calculated_Company],
(Case
when SOByMonth1.Calculated_MonthYear is not null then SOByMonth1.Calculated_MonthYear
when ForeQty.Calculated_MonthYear is not null then ForeQty.Calculated_MonthYear
when MPSQty.Calculated_YearMonth is not null then MPSQty.Calculated_YearMonth
when FirmJobQty.Calculated_YearMonth is not null then FirmJobQty.Calculated_YearMonth
when UnfirmJobQty.Calculated_YearMonth is not null then UnfirmJobQty.Calculated_YearMonth
when OHQty2.Calculated_CurrentYearMonth is not null then OHQty2.Calculated_CurrentYearMonth
end) as [Calculated_YearMonth03],
(case
when SOByMonth1.OrderRel_PartNum is not null then SOByMonth1.OrderRel_PartNum
when ForeQty.Forecast_PartNum is not null then ForeQty.Forecast_PartNum
when MPSQty.MasProd_PartNum is not null then MPSQty.MasProd_PartNum
when FirmJobQty.JobHead_PartNum is not null then FirmJobQty.JobHead_PartNum
when UnfirmJobQty.JobHead1_PartNum is not null then UnfirmJobQty.JobHead1_PartNum
when OHQty2.PartBin_PartNum is not null then OHQty2.PartBin_PartNum
end) as [Calculated_Part],
(case
when SOByMonth1.Calculated_OpenQty is null then 0.00 else SOByMonth1.Calculated_OpenQty
end) as [Calculated_OpenSOQty],
(case
when ForeQty.Calculated_QtyOpen is null then 0.00 else ForeQty.Calculated_QtyOpen
end) as [Calculated_OpenForeQTY],
(case
when MPSQty.Calculated_MPSQty is null then 0.00 else MPSQty.Calculated_MPSQty
end) as [Calculated_OpenMPSQty],
(case
when FirmJobQty.Calculated_JobQty is null then 0.00 else FirmJobQty.Calculated_JobQty
end) as [Calculated_OpenFirmJobQty],
(case
when UnfirmJobQty.Calculated_UnfirmJobQty is null then 0.00 else UnfirmJobQty.Calculated_UnfirmJobQty
end) as [Calculated_OpenUnfirmJobQty],
(case
when OHQty2.Calculated_OHQty is null then 0.00 else OHQty2.Calculated_OHQty
end) as [Calculated_OpenOHQty],
(OpenFirmJobQty+ OpenUnfirmJobQty) as [Calculated_Production],
(OpenSOQty+ OpenForeQTY) as [Calculated_Demand],
(Production- Demand) as [Calculated_Change],
(OpenOHQty) as [Calculated_Total]
from (select
[OrderRel].[Company] as [OrderRel_Company],
[OrderRel].[PartNum] as [OrderRel_PartNum],
(sum((OrderRel.OurJobQty- OrderRel.OurJobShippedQty)+ (OrderRel.OurStockQty- OrderRel.OurStockShippedQty))) as [Calculated_OpenQty],
(convert(varchar, year(OrderRel.ReqDate), 101)+ '-'+ convert(varchar, month(OrderRel.ReqDate), 101)) as [Calculated_MonthYear],
('SOByMonth') as [Calculated_Catagory]
from Erp.OrderRel as OrderRel
inner join Erp.OrderDtl as OrderDtl on
OrderRel.Company = OrderDtl.Company
and OrderRel.OrderNum = OrderDtl.OrderNum
and OrderRel.OrderLine = OrderDtl.OrderLine
and ( OrderDtl.OpenLine = 1 )
inner join Erp.OrderHed as OrderHed on
OrderDtl.Company = OrderHed.Company
and OrderDtl.OrderNum = OrderHed.OrderNum
and ( OrderHed.OpenOrder = 1 )
where (OrderRel.OpenRelease = 1)
group by [OrderRel].[Company],
[OrderRel].[PartNum],
(convert(varchar, year(OrderRel.ReqDate), 101)+ '-'+ convert(varchar, month(OrderRel.ReqDate), 101))) as SOByMonth1
full outer join (select
[Forecast].[Company] as [Forecast_Company],
[Forecast].[PartNum] as [Forecast_PartNum],
(sum(Forecast.ForeQty- Forecast.ConsumedQty)) as [Calculated_QtyOpen],
(convert(varchar, year(Forecast.ForeDate), 101)+ '-'+ convert(varchar, month(Forecast.ForeDate), 101)) as [Calculated_MonthYear],
('ForeQty') as [Calculated_Catagory]
from Erp.Forecast as Forecast
where (Forecast.Inactive = 0 and Forecast.ForeQty > Forecast.ConsumedQty)
group by [Forecast].[Company],
[Forecast].[PartNum],
(convert(varchar, year(Forecast.ForeDate), 101)+ '-'+ convert(varchar, month(Forecast.ForeDate), 101))) as ForeQty on
ForeQty.Forecast_Company = SOByMonth1.OrderRel_Company
and ForeQty.Forecast_PartNum = SOByMonth1.OrderRel_PartNum
and ForeQty.Calculated_MonthYear = SOByMonth1.Calculated_MonthYear
full outer join (select
[MasProd].[Company] as [MasProd_Company],
[MasProd].[PartNum] as [MasProd_PartNum],
(sum(MasProd.ProdQty)) as [Calculated_MPSQty],
(convert(varchar, year(MasProd.DueDate), 101)+ '-'+ convert(varchar, month(MasProd.DueDate), 101)) as [Calculated_YearMonth],
('MPSQty') as [Calculated_Catagory]
from Erp.MasProd as MasProd
group by [MasProd].[Company],
[MasProd].[PartNum],
(convert(varchar, year(MasProd.DueDate), 101)+ '-'+ convert(varchar, month(MasProd.DueDate), 101))) as MPSQty on
MPSQty.MasProd_Company = ForeQty.Forecast_Company
and MPSQty.MasProd_PartNum = ForeQty.Forecast_PartNum
and MPSQty.Calculated_YearMonth = ForeQty.Calculated_MonthYear
full outer join (select
[JobHead].[Company] as [JobHead_Company],
[JobHead].[PartNum] as [JobHead_PartNum],
(sum(JobHead.ProdQty)) as [Calculated_JobQty],
(convert(varchar, year(JobHead.DueDate), 101)+ '-'+ convert(varchar, month(JobHead.DueDate), 101)) as [Calculated_YearMonth],
('FirmJobQty') as [Calculated_Catagory]
from Erp.JobHead as JobHead
where (JobHead.JobClosed = 0 and JobHead.JobComplete = 0 and JobHead.JobFirm = 1)
group by [JobHead].[Company],
[JobHead].[PartNum],
(convert(varchar, year(JobHead.DueDate), 101)+ '-'+ convert(varchar, month(JobHead.DueDate), 101))) as FirmJobQty on
SOByMonth1.OrderRel_Company = FirmJobQty.JobHead_Company
and SOByMonth1.OrderRel_PartNum = FirmJobQty.JobHead_PartNum
and SOByMonth1.Calculated_MonthYear = FirmJobQty.Calculated_YearMonth
full outer join (select
[JobHead1].[Company] as [JobHead1_Company],
[JobHead1].[PartNum] as [JobHead1_PartNum],
(sum(JobHead1.ProdQty)) as [Calculated_UnfirmJobQty],
(convert(varchar, year(JobHead1.DueDate), 101)+ '-'+ convert(varchar, month(JobHead1.DueDate), 101)) as [Calculated_YearMonth],
('UnfirmJobQty') as [Calculated_Catagory]
from Erp.JobHead as JobHead1
where (JobHead1.JobClosed = 0 and JobHead1.JobComplete = 0 and JobHead1.JobFirm = 0)
group by [JobHead1].[Company],
[JobHead1].[PartNum],
(convert(varchar, year(JobHead1.DueDate), 101)+ '-'+ convert(varchar, month(JobHead1.DueDate), 101))) as UnfirmJobQty on
FirmJobQty.JobHead_Company = UnfirmJobQty.JobHead1_Company
and FirmJobQty.JobHead_PartNum = UnfirmJobQty.JobHead1_PartNum
and FirmJobQty.Calculated_YearMonth = UnfirmJobQty.Calculated_YearMonth
full outer join (select
[PartBin].[Company] as [PartBin_Company],
[PartBin].[PartNum] as [PartBin_PartNum],
(sum(PartBin.OnhandQty)) as [Calculated_OHQty],
(FORMAT (Constants.Today, 'yyyy-MM')) as [Calculated_CurrentYearMonth],
('OHQty') as [Calculated_Catagory]
from Erp.PartBin as PartBin
inner join Erp.WhseBin as WhseBin on
PartBin.Company = WhseBin.Company
and PartBin.WarehouseCode = WhseBin.WarehouseCode
and PartBin.BinNum = WhseBin.BinNum
and ( WhseBin.NonNettable = 0 )
where (PartBin.OnhandQty > 0)
group by [PartBin].[Company],
[PartBin].[PartNum]) as OHQty2 on
UnfirmJobQty.JobHead1_Company = OHQty2.PartBin_Company
and UnfirmJobQty.JobHead1_PartNum = OHQty2.PartBin_PartNum
and UnfirmJobQty.Calculated_YearMonth = OHQty2.Calculated_CurrentYearMonth
union all
select
[UnionGrab1].[Calculated_Company] as [Calculated_Company],
[UnionGrab1].[Calculated_YearMonth03] as [Calculated_YearMonth03],
[UnionGrab1].[Calculated_Part] as [Calculated_Part],
[UnionGrab1].[Calculated_OpenSOQty] as [Calculated_OpenSOQty],
[UnionGrab1].[Calculated_OpenForeQTY] as [Calculated_OpenForeQTY],
[UnionGrab1].[Calculated_OpenMPSQty] as [Calculated_OpenMPSQty],
[UnionGrab1].[Calculated_OpenFirmJobQty] as [Calculated_OpenFirmJobQty],
[UnionGrab1].[Calculated_OpenUnfirmJobQty] as [Calculated_OpenUnfirmJobQty],
[UnionGrab1].[Calculated_OpenOHQty] as [Calculated_OpenOHQty],
[UnionGrab1].[Calculated_Production] as [Calculated_Production],
[UnionGrab1].[Calculated_Demand] as [Calculated_Demand],
[UnionGrab1].[Calculated_Change] as [Calculated_Change],
(sum(UnionGrab1.Calculated_Change) OVER (PARTITION BY UnionGrab1.Calculated_Part ORDER BY UnionGrab1.Calculated_YearMonth03)) as [Calculated_Total]
from UnionGrab as UnionGrab1)
select
[UnionGrab3].[Calculated_Company] as [Calculated_Company],
[UnionGrab3].[Calculated_YearMonth03] as [Calculated_YearMonth03],
[UnionGrab3].[Calculated_Part] as [Calculated_Part],
[UnionGrab3].[Calculated_OpenSOQty] as [Calculated_OpenSOQty],
[UnionGrab3].[Calculated_OpenForeQTY] as [Calculated_OpenForeQTY],
[UnionGrab3].[Calculated_OpenMPSQty] as [Calculated_OpenMPSQty],
[UnionGrab3].[Calculated_OpenFirmJobQty] as [Calculated_OpenFirmJobQty],
[UnionGrab3].[Calculated_OpenUnfirmJobQty] as [Calculated_OpenUnfirmJobQty],
[UnionGrab3].[Calculated_OpenOHQty] as [Calculated_OpenOHQty],
[UnionGrab3].[Calculated_Production] as [Calculated_Production],
[UnionGrab3].[Calculated_Demand] as [Calculated_Demand],
[UnionGrab3].[Calculated_Change] as [Calculated_Change],
[UnionGrab3].[Calculated_Total] as [Calculated_Total]
from UnionGrab as UnionGrab3
First off, your first few Case statements do not have an Else clause. That might be causing an issue. You could also think of doing COALESCE instead if all you are doing is checking for Null values.
If I were to do this (and I am definitely not an expert!!) I would do it as 6 CTE queries and then use those CTEs as the tables for a seventh query to bring all the data together.
Start with SubQuery1, rename it SOByMonth, change the type to CTE and the generated SQL should look like
select
[OrderRel].[Company] as [OrderRel_Company],
[OrderRel].[PartNum] as [OrderRel_PartNum],
(sum((OrderRel.OurJobQty- OrderRel.OurJobShippedQty)+ (OrderRel.OurStockQty- OrderRel.OurStockShippedQty))) as [Calculated_OpenQty],
(convert(varchar, year(OrderRel.ReqDate), 101)+ '-'+ convert(varchar, month(OrderRel.ReqDate), 101)) as [Calculated_MonthYear],
from Erp.OrderRel as OrderRel
where (OrderRel.OpenRelease = 1)
group by [OrderRel].[Company],
[OrderRel].[PartNum],
(convert(varchar, year(OrderRel.ReqDate), 101)+ '-'+ convert(varchar, month(OrderRel.ReqDate), 101)))
You can take out the OrderHed and OrderDtl as if the line is closed all releases are automatically closed and if the order is closed all lines and releases are automatically closed.
Add a new SubQuery as a CTE and rename to ForeQty. Create that query and keep on doing this until all 6 are in their own CTE.
Finally, add a new SubQuery as Top Level and use the previous CTEs as the tables. Join them all together and do your calculations.
Isn’t that pretty much what I did only instead of storing them as CTE’s I stored them as InnerSubQueries?
Yes, but at the end of what I am suggesting, there is no Union.
True, but then how do we get a rolling total for each part by month?
Rolling total = Current On hand number + (production for the month - demand for the month)
Currently, I only see that for each month it will look at what I have on hand, right now, for each month instead of looking at what the rolling total was at the end of each month.
I took a quick look at your query then noped out because it’s way more complicated that I have time for. But if you are looking for a rolling total you can use windowing functions.
Sum(table.Field) over (partition by Separator1Table.SeparatorField1 , Separator2Table.SeparatorField2 etc. order by Table.FieldtoSortBy)
This will give you a running sum without have to try and use a CTE.
Not sure if that’s what you are looking for, but it seems simpler than whatever you are trying to do.
Sorry, missed the rolling part.
@Banderson thanks, I was just looking up the syntax, now I don’t have to.
I would use the Rows window.
Sum(table.Field) over (partition by Separator1Table.SeparatorField1 order by Table.FieldtoSortBy ROWS BETWEEN CURRENT ROW AND 1 PRECEDING)
@Banderson and @jkane,
Thank you both!! Just now getting into the data. I now need to figure out how to get my initial on hand qty to only show up on the current month and I believe that will get it to where this thing is finally counting right. Thank you both for the assist!!
RunningValue in SSRS if that helps =) My scenario is different but maybe these notes will help spark some ideas.
=RunningValue(Fields!Calc_Quantity.Value, SUM , “Dataset”) + Fields!OnhandQty.Value
[Calc_Quantity]
=IIF(Fields!RequirementFlag.Value = 0, Fields!Quantity.Value, (-1 * Fields!Quantity.Value))
@Banderson and @jkane,
When you guys say (partition by Separator1Table.SeparatorField1, 2) what do you think I should use? I’m trying to get company, year/month, and partnumber. If those are good enough can I skip the order by, or should I still use it for the same fields?
I believe you want
SUM(Calculated_Total) OVER(PARTITION BY YearMonth ORDER BY YearMonth ROWS BETWEEN CURRENT ROW AND 1 PRECEDING)
If you skip the orderby then you won’t get a running total. It will just be the total in that partition you defined.