Good afternoon,
I have a rather large BAQ built to populate our job control sheets. These are proprietary sheets that list all the relevant details for a job. One section of this report has always given me trouble. It is a list of the upcoming releases and their remaining amounts. The list should be 12 or fewer records, and should be sorted ascending by req date.
In the past I made this work with FOR XML PATH. But I have since switched to using stringagg. Here are the relevant parts of my BAQ. Let me know if you want to see the whole thing, it is a mess!
First there is subquery7 to pull all the releases for open jobs, list them with their remaining amounts to be shipped, and sort them by date. Since it is a subquery I cant use order by. Unless I also specify a TOP clause. This seemed perfect for me because I only want the top 12 records. However, after building the BAQ, I am not getting any results in my string agg, the field is entirely blank. There definitely is data there to merge, as the report works fine without turning on the orderby in the subquery.
I take the values listed in the subquery7, and pull them into a single field in another subquery like this:
This formula pulls the quantity and the date from the release, and spaces them all out with line breaks.
My question is, how can I sort the values returned in subquery7, so that they appear in the correct order when I merge them into a single value with string agg?
As I mentioned above, I tried turning on orderby and the Top clause in SQ7. I limited it to 11 or 12 records. This stops the BAQ from throwing an error, but it returns a blank value instead of an aggregated string.
Thank you for your time!
Nate
Pad with zeroes for logical sorting: eg
Date : 20220525 or 220525
Also I would do this Date then Qty as you know the date will be given set of columns you should also pad your qty to account for the greatest possible amount.
This way, you know all your items end up with a columnar sort.
Example would be by Qty amt on date if you pad and reorient your fields.
Good idea, but the problem is that the BAQ will not let me use the orderby in a subquery. I need the subquery to return sorted results into the stringagg formula.
Here is the final location on the job control sheet. You can see a sort of table, but its just for show. The data exists in a single field and is just placed over the job control sheet template to look like a small table. This data comes from a field that looks like this:
FWIW I have attached the whole ugly BAQ. It likely wouldn’t work on any other company, as our job and sales order numbers are intimately linked. NRS-JCS_1.baq (128.9 KB)
So my suggestion would be an intermediate result set based on your info provided.
If you need by Qty as string then by date would need to pad out your int counts like this:
Substring('0000'+convert(varchar, cast(SubQuery7.Calculated_RemainQty as int)),1+Len(convert(varchar, cast(SubQuery7.Calculated_RemainQty as int))),4) + ' ' + convert(varchar, SubQuery7.OrderRel1_ReqDate,1)+char(10)
Then your data is more easliy parsable & sortable.
This will not really be sortable on output looking at what you have though, as you are building out what looks to be Char(10) delimited lines. You would need to break these into separate lines to make them sortable, or presort them in a subquery {best option}.
I did something similar in a Quantity Usage report where each month usage was aggregated into a monthly total. Where each field contains a YYMM for the PartTran data to calculate usage.
There was (I haven’t tried it in either a BAQ or SQL recently) a trick where you can enter the subquery result set as TOP 100 PERCENT and it will allow you to sort the results of the subquery.
Thank you Tyler! I didn’t know about that trick. It worked! I had to add some extra grouping, and I had to move my date field to the beginning of my subquery, but after that I could turn on order by req date, and everything is sorted perfectly! Thanks!!!
I am trying to get this working again in another BAQ. I have a list of operations in a subquery, and I want to sort the list so that I can do a string agg to get the list of operations (resource groups).
I have set the subquery as Result Set Rows = Top, In Percent 100. I am using JobOper, JobOperDtl, and Capability tables. I have JobOper.OprSeq, Company, JobNum, AssemblySeq, and my calcualted field to pull the resource group. All of these are grouped fields.
I have set the sort for this subquery to just look at JobOper.OprSeq. I use a second subquery to pull the (hopefully sorted) op seq values into a string_agg.
The second subquery is grouped by JobOper.Company, JobNum, and AssemblySeq.
I can’t seem to get my string agg to be sorted in the opseq order. What am I missing?
Interestingly I created a sample to show this and it works just fine. So, it must be some other part of my larger query that is stopping the sorting from sticking. testesnew.baq (29.5 KB)
This is the larger BAQ where the sorting is not working. The BAQ is not complete yet. I have to get this sort working to move on. The top level joins all the hours remaining by department to the list of jobs. Then it also adds on the subquery to show the resource groups. We call the workcenters. The AllWCs list calculated field should show the operation list in op sequence order. I think I have everything setup as I did in my example post above, but it still won’t sort. DeptRemain.baq (298.7 KB)
No worries. I spent a lot of time a while back getting that to work, when I saw your post I had to search thru my StringAgg calculated fields to find it. Glad I could help. I have found all kinds of uses for StringAgg.
