I have a parameter in a BAQ using a drop down list where each displayed text has multiple values. I’m trying to add table criteria to filter IN against the parameter but can’t get it to work.
can you share your BAQ definition?
Unless you choose ‘NPC’ for your company, you won’t get any results.
This is the problem, You can not chose the IN statement with the specified parameter filter value
If you have the operation set to IN the only parameter filter value is specified list paramter.
Is @Mfg a string or a list parameter? Can we get a screenshot of that definition? You can only use a list parameter for the IN criteria.
If the operation is set to = then you can choose specified parameter, if the operation is set to IN the filter value is narrowed to 3 options.
specified constant list / specified list parameter / field from specified sub-query.
I don’t understand how this (Erp.Part.ClassID IN (@Mfg)) works in SQL but not a BAQ.
The company is NPC, I was just trying a work around to get the IN statement included in the BAQ.
I’m pretty sure that when you use a parameter, it’s being used as a single value. A single string with commas in it.
WHERE column_name IN (‘ABC’, ‘DEF’, ‘XYZ’)
isn’t the same as
WHERE column_name IN (@Param)
When @Param = ‘ABC,DEF,XYZ’
What might work is to make a calc field using
CHARINDEX(Company, @Mfg) which will return a non-zero value if Company is in @Mfg. Then add sub-query criteria for that calc field <> 0
Edit
The above has potential issues if one company ID is entirely in another. Like if there was ‘EP1’ and ‘EP10’.
2 Likes
Are you wanting to have the users select from the list before the BQ is run? That is what the BAQ parameter is used for.
Otherwise you can just create the list within in the BAQ (specified constant list). Which the other folks here are describing as you don’t need the extra step of creating a parameter.
Forget the company I took that out. It was just a trick to try insert the IN statement.
I am trying to use parameters. I’ve created the drop down list parameter, but the BAQ will not allow the use of an IN statement against a drop down list parameter.
Apparently it does allow it. It’s just not very useful.
Using the IN statement against a item list parameter works but not against a drop down list. They both have multiple values so I don’t understand why the IN statement doesn’t work for both.
The value of the dropdown, isn’t a list of multiple elements to be checked against. Its a single value. No different than:
WHERE Company IN('EP1')
That is essentially the same as WHERE Company = 'EP1'
So WHERE Company IN('EP1,EP2,EP3') is the same as WHERE Company = 'EP1,EP2,EP3'
2 Likes
We have several ssrs reports that I am trying to convert to BAQ reports. These reports all have a drop down parameter that allows the user to pick several classid’s that are associated to a manufacturer. For example John Deere has a value of JDE,ENAS,JDRG. The previous crystal and ssrs version of the report used an IN statement to included records where the classid was one of those values.
For example here is the drop down parameter in the ssrs report.
And this is the Query from that same report.
SELECT Erp.Part.PartNum, Erp.Part.ClassID, Erp.PartTran.PackNum, Erp.PartTran.PackLine, Erp.PartTran.PONum, Erp.PartTran.TranDate, Erp.PartTran.TranNum, Erp.PartTran.TranQty, Erp.PartTran.TranType
FROM Erp.Part INNER JOIN
Erp.PartTran ON Erp.Part.Company = Erp.PartTran.Company AND Erp.Part.PartNum = Erp.PartTran.PartNum
WHERE (Erp.PartTran.TranDate BETWEEN @StartDate AND @EndDate) AND (Erp.Part.ClassID IN (@Mfg)) AND (Erp.PartTran.TranType IN (@TranType))
So I am trying to recreate this as a BAQ report.
1 Like
Unless I’m missing something, it has always been like this. Even if it does let you use it, ‘ENAS, JDE, JDRG’ is still just one string. It doesn’t matter if you have multiple values separated by commas, Epicor will still see it as just one string. There is nothing there (on Epicor or SSRS side) that splits that string by commas into separate values.
You can have a calculated field there in the BAQ that does that for you, something like:
calcCompany = case when Company in (‘ENAS’,‘JDE’,‘JDRG’) then ‘John Deere’ when …
Your @Mfg parameter will have the same values and display texts like John Deere, get rid of the values with commas.
Finally, you will filter the BAQ by that calculated field calcCompany and not the Part_Company like calcCompany = @Mfg parameter
I’m not quit following you. I currently have a parameter named Manufacturing. The first drop down has a display text of John Deere and a value of JDE,ENAS,JDRG. Those 3 values are all part class id’s. Then I have 2 more drop downs each with a display text and multiple values.
So Epicor is unable to use the IN statement because sees the parameter as a single value. I’m not sure how you would separate those values in a calculated field and if you did how would you use that to set the criteria?
Are you trying to do a contains type thing? Like if you pick a drop down, then the field needs to have something in the field that matches the dropdown? Generally in SQL you would use a like (’%filter%’) for that. Just make the field a like field, then either add the % in the drop down values of the parameter, or do an expression to add them into the criteria.
I mean use a calculated field like below in the BAQ and filter against that. The formula for it would be something like:
calcField = case when PartClass in (‘JDE’,‘ENAS’,‘JDRG’) then ‘John Deere’ when PartClass in (‘KHRE’,‘EKAS’) then ‘Kohler’ when PartClass in (‘YNMR’,‘EYAS’) then ‘Yanmar’ else ‘’ end
You then use that calculated field to filter the BAQ. Use the SubQuery criterias - it will let you use calculated fields.