It has to have multiple values. Each line of the drop down has multiple part classes. For example if the user want info for John Deere all the transaction records for class ID JDE, ENAS, and JDRG need to be retrieved
If they chose Yanmar then there are different class id’s that need to be retreived.
Got it. Epicor doesn’t have a way to define a scalar variable like the list that you created in SQL. You’ll probably need to make a subquery to make your list and filter it that way. I was looking to see if you could put the hardcoded list in the BAQ calculated field, but it’s not working the way I want it to.
Don’t know if this is something you thought of doing or not.
You could create a UD field for the part class to be the customer name. Then write your BAQ to select the customer and their part class fields from the part class items.
Granted if there is a many to many relationship between customer and part class that wouldn’t work. You would have to create a parent/child relationship for that to work. If that is the case you could create UD code types for each customer and then create a code for each part class.
This way you aren’t having to update the SSRS report each time a new customer/part class field is added.
I Just tried this out in the latest version of Kinetic with the new BAQ UI… and IT WORKS. I originally thought it didn’t work, but then I realized that I missed a field in the definition of the parameter. I had to tell it that it was the editor type of “Item LIst” and then I could assign it to my condition.
In my example, I put “A” and “C” into the list, and i only got back those two records from the BAQ results:
E10 has that too. But it’s not what the OP wants. He wants the user to choose from drop-down, with each drop down value actually being a list and not a string that looks like a list.
As he pointed out, SSRS actually does treat the string as a list.
@NSP - can you select the IN operator and then enter an expression like
STRING_SPLIT(@Mfg,',')
DropDownList was never supposed to work with IN operator. It just tells smart client what control should be shown to return 1 exact value - selected in combobox. So IN is not supposed to be used with that value.
You must use Item List in old or new BAQ designer as Tim showed above.
Thank you all for the great suggestions. I came up with a work around.
1.) Created a drop down list parameter and assigned each item in the drop down a number 1, 2, 3 etc…
2.) Created a sub query with a calculated field that assigned the calculated field a number corresponding to the drop down number 1, 2 3, etc.
3.) In the top level query I created 3 additional table criteria grouped in parenthesis and separated by OR statement.
4.) Each new criteria started as Calculated_Mfg = specified expression.
5.) The first expression = ‘1’ AND ((SubQuery1.Part_ClassID = ‘JDE’) OR (SubQuery1.Part_ClassID = ‘ENAS’) OR (SubQuery1.Part_ClassID = ‘JDRG’)).
JDE / ENAS / JDRG are the values that I originally had in the value filed of the drop down.
6.) the next 2 expressions were as the first but with different classID values.
1 Like
I’m a little late to the party, but just to throw in another method…
I have a use case where I want to query some information for a short but arbitrary list of parts without users doing data entry work, and I don’t want to return the entire item master. So, I have a single string parameter which receives a comma separated list of part numbers, and a recursive CTE that splits the string into a set I can join on the part table. If your part numbers contain commas, you can switch out the comma character for whatever other character that’s supported by nvarchar. So, without dropping our whole production BAQ here, here’s what the text looks like as returned by the BAQ application:
with [PartNumSplitter] as
(select
(case
when len(replace(@PartNum, ',', '')) < len(@PartNum) then substring(@PartNum, 1, charindex(',', @PartNum) - 1)
else @PartNum
end) as [Calculated_PartNum],
(right(@PartNum, len(@PartNum) - charindex(',', @PartNum))) as [Calculated_remainder]
union all
select
(case
when len(replace(PartNumSplitter.Calculated_remainder, ',', '')) < len(PartNumSplitter.Calculated_remainder) then substring(PartNumSplitter.Calculated_remainder, 1, charindex(',', PartNumSplitter.Calculated_remainder) - 1)
else PartNumSplitter.Calculated_remainder
end) as [Calculated_PartNum],
(right(PartNumSplitter.Calculated_remainder, len(PartNumSplitter.Calculated_remainder) - charindex(',', PartNumSplitter.Calculated_remainder))) as [Calculated_remainder]
from PartNumSplitter as PartNumSplitter
where
(PartNumSplitter.Calculated_PartNum = case when ',' + right(@PartNum, len(PartNumSplitter.Calculated_PartNum)) = ',' + PartNumSplitter.Calculated_PartNum then null
else PartNumSplitter.Calculated_PartNum end
and PartNumSplitter.Calculated_PartNum <> ''
and PartNumSplitter.Calculated_PartNum is not null))
I got it figured out, thanks.