I need to create a BAQ of all purchased parts and list the approved suppliers. Simple, but what I’d like to do is put all the suppliers on one record
For Example:
Part Primary Supplier Lead Time App Supp1 App Supp 2 App Supp 3
ABC XYZ Supplier 10 GHI Supp XYZ Supp PDQ Supp
We don’t have more then 5 approved suppliers, so I can make it static and not worry about getting too fancy.
I was thinking doing it with a recursive subquery and number each approved vendor as it read through the aprvVend table for that part, then at the top level, display the info in a calculated field based on the number. Is this possible or am I going in the wrong direction?
I’m not having much luck with getting it to work recursively. Any help in the write direction would be appreciated.
I may be reading this a little incorrectly - perhaps some screenshots of a excel file of the result you are looking for would help!
Why do you need to recurse through this? I would think that you can get all suppliers numbered in order by having a simple subquery and then creating a calculated field which uses ROW_NUMBER() to label them 1,2,3,4 etc.
To cover having them all return in a single row - this may be a bit tricky in a BAQ though I am no SQL expert. What is your use case with this? This might be better done by calling your BAQ in an application, saving the BAQ response to a dataview, then passing this entire dataview into a function (unless we can call a baq directly from a function but I have not yet done this). From here, we can iterate through each row in the c# function and combine into a single row returned as a System.Data.Dataset which can then overwrite your original dataview in the application.
I might be completely off from what you are looking for here!
Thanks! I think you are right and I could use Row Numbers, I forgot about that. I don’t do a lot of BAQs and when I was working on my multi-level BOM BAQ someone talked about doing that. Let me try it. Thanks for the Input.
Sounds like you have what you need, but just want to throw STRING_AGG out there as an option (SQL 2017 and after). It will let you cram the list into a single cell. You might not want that.
So I’ve got row numbers working and putting the values into the columns based on the value.
The next step is to combine the same parts so that the results that currently look like this:
I tried using Grouping, but it’s causing the aggregate function to put everything in Supplier 1 because the row# is always 1
I thought making the reading of the aprvvend table a subquery where it pulls in the VendorID and VendorName so that I could group the results so it would return 1 line of data, but I’m getting the same results just 10 times slower.
Here is my RowNum Calculated field
case
when (AprvVend.PartNum is Null) then
0
else
Row_Number() over (partition by AprvVend.PartNum order by AprvVend.PartNum)
end
My other columns base the value to put into it from the RowNum.
AprvID1 calculated field
case
when RowNum = 1 then
STRING_AGG(Vendor.VendorID,‘’)
end
AprvName1 calculated field
case
when RowNum = 1 then
STRING_AGG(Vendor.Name,‘’)
end
There is something I’m missing that I feel is simple, but I’m not seeing it.
A simpler option would be to have two grids (parent & child). Grid 1 displays your parts with primary supplier info. When you click one of those parts, the approved suppliers display in a second grid below. Really easy to do with parent/child dataviews if you’re okay with not having it all in one grid.
I went with the Parent Child solution in the dashboard like you mentioned. Waiting back from end user to see if this will work for them.
I’ll see what they say. It was a simple solution, so I appreciate the suggestion.
