select
[Summary].[PartBin_PartNum] as [PartBin_PartNum],
[Summary].[Part_PartDescription] as [Part_PartDescription],
[Summary].[Calculated_Qty] as [Calculated_Qty],
(Replace(Replace(((select
(CAST(FilteredBins.PartBin2_BinNum AS VARCHAR) + ',') as [Calculated_Bins]
from (select
[PartBin2].[PartNum] as [PartBin2_PartNum],
[PartBin2].[BinNum] as [PartBin2_BinNum]
from Erp.PartBin as PartBin2
where (PartBin2.BinNum like '%402044' or PartBin2.BinNum like '%402047')) as FilteredBins
where FilteredBins. = Summary.PartBin_PartNum FOR XML PATH(''))) ,'</Calculated_Bins>',''),'<Calculated_Bins>','')) as [Calculated_Bins]
from (select
[PartBin].[PartNum] as [PartBin_PartNum],
[Part].[PartDescription] as [Part_PartDescription],
(sum(PartBin.OnhandQty)) as [Calculated_Qty]
from Erp.PartBin as PartBin
inner join Erp.Part as Part on
PartBin.Company = Part.Company
and PartBin.PartNum = Part.PartNum
group by [PartBin].[PartNum],
[Part].[PartDescription]) as Summary
Actually, it’s working. There were some missing filters so the nulls were valid. I re-added them, probably after being too click happy, and it’s working now. Thanks.
Has anyone experienced a character limitation when using this concept. What I mean is the results of creating a list into a single column, limits each element in the list to 30 characters. I am doing this to a UD field that is set for a string with a format of x(1000). Neither of my calcFields are set for x(30).
Do you see it right in the BAQ designer? Or truncated when used in dashboards and/or reports?
Is it possible that there is a new line at the end of every value being concatenated?
One last thing, there’s a weird bug where the first line of a Part description is always padded to 30 characters. Maybe thats related.
Hi @ckrusen, I think you misunderstood me. I don’t mean the size of the total field, but the size of each element within the list. So between the commas. Here is the results that I am getting.!
Hi @ckrusen, I followed along exactly like your Order example, but when I try to Analyze results I keep getting error “Severity: Error, Table: , Field: , RowID: , Text: No column name was specified for column 1 of ‘SubQuery2’.”
The fields are all exactly named like your example, so I’m at a loss. I get this error regardless of which tables I try this with, so maybe a bug in 10.0.700?
select
[Customer].[CustID] as [Customer_CustID],
[Customer].[Name] as [Customer_Name],
(REPLACE(REPLACE(((select
[OrderHed].[OrderNum] as [OrderHed_OrderNum],
(CAST(OrderHed.OrderNum AS VARCHAR) + ‘,’) as [Calculated_OrderNum]
from Erp.OrderHed as OrderHed
where OrderHed.CustNum = Customer.CustNum FOR XML PATH (’’))),’</Calculated_OrderNum>’,’’),’<Calculated_OrderNum>’,’’)) as [Calculated_Orders]
from Erp.Customer as Customer
You also need to add to the SubQuery Criteria for SubQuery2. Basically make the “joins” that you would have had if SubQuery2 was being used in SubQuery1
It might not be obvious, but in mine the Table column referes to the table in the second Sub-Query. (because my main subquuery alread has a table OrderDtl, the one inserted into SubQuery2 was named OrderDtl1. )
So I had to add criteria to connect the SubQ2 to the main one. Note that the tables in the expressions of the Filter Value column refere to OrderDtl And not OrderDtl1 (ignore that one line that does. It is to limit the results of the second SQ, and not used for linking to SQ1)
I’m not 100% positive, but I think that SQ Criteria (with the “… XML…”) just ends up inserting the required code in the Query Phrase to make the conactenation. I think you still need have criteria linking it to SQ1.
In my example, SQ Criteria:
Lines 1-3 link it to the main query. I think of it like the join to limit which records SQ2 uses to make that concatenated value.
*Lines 4-6 limit which records of SQ I’m going to use for the concat.
Line 7 is the one that builds the code in the Query Phrase
