Using expressions for a value in Paramaters

Question

I have a report that returns various products depending on which product group you select. Most of these products all have similar product codes that allow me to use the LIKE operator to get the required results. However, for one particular product group, I have the following problem:

VSAMPLES    
VSAMPLES2016    
VSAMPLES2016DD  
VSAMPLESADD 
VSAMPLESET  
VSAMPLESLARGE   
VSAMPLESLARGEADD
VSAMPLESNEW

I only need the top two products to be listed. But using 'VSAMPLES% as a parameter value will return all of these products.

Can i write an expression for the parameter value that will use 'VSAMPLES% and 'VSAMPLES2016% to only return these two products?

EDIT

The query is:

SELECT STRC_CODE, STRC_DESC FROM DeFactoUser.F_ST_Products
WHERE STRC_CODE LIKE @ProductCode

I am using LIKE so I don't have to specify dozens of products for each group. For one Parameter value I am using 'PA.A% This works perfectly because every product starting with PA.A is needed. In the case of VSAMPLES this isn't the case.

Parameter Values are as follows:

So, can I not add a value to the Aspire tab that will return only those two products?

Would that not mean having to specify each product for all the other groups? Would be dozens. — R_Avery_17
@NealS dozens or the top 2? How about the middle 2? Or 3? You don't need expressions in parameters, even if that made sense. You need to fix the query. SSRS supports parameters with multiple values. You can write WHERE STR_CODE IN @codes and have the @codes parameter receive values eg from a dropdown, selection or query — Panagiotis Kanavos
@NealS check the docs on adding a multi-value parameter to a report — Panagiotis Kanavos
@PanagiotisKanavos I don't want to be able to select multiple values. I have about 20 product groups, that when each is selected return the relevant products. I have no use in returning 2 or more product groups at a time. — R_Avery_17

R_Avery_17 R_Avery_17 · Accepted Answer · 2017-09-19T11:52:24

OK, What i was asking might not have been possible. i fixed the issue by altering my query.

SELECT STRC_CODE, STRC_STATUS, STRC_DESC FROM DeFactoUser.F_ST_Products
WHERE STRC_CODE LIKE @ProductCode AND STRC_CODE NOT IN ('VSAMPLES2016DD', 
'VSAMPLESADD', 'VSAMPLESET', 'VSAMPLESLARGE', 'VSAMPLESLARGEADD', 
'VSAMPLESNEW')

This results in only the two products I needed being returned when i use VSAMPLES% as a value.

Much simpler then I thought.

Thanks for the input into the question I asked.

Using expressions for a value in Paramaters

1 Answers