SSRS Reporting multi value parameters

0
votes

I have a ssrs report, that gives me multiple product's price. My Parameter is not drill down, I have to type in the parameters(since I have large range of product number).

Now my questions is, how can i get the last entered product ( parameter) always appear at the bottom of the report ?. This would help me where to look the latest product in the report.For example I have product numbers like:
abc-234,
abc-570,
ght-908,

Now what I want is that the latest entered product number which is ght-908 to appear at the bottom of the ssrs reports. Right now it gives me the report for the multiple product, but its all over the place and i have to squint my eyes and try to find out where my most recent entered product numbers (parameters) is. I have also tried to stop the parameters to being refreshed everytime i add a product number.

3
reporting-servicesreporting
So just to confirm - user types in comma-separated list of parameters and you need to display last parameter at the bottom of every page below the data? - Yuriy Galanter
yes! that's exactly what i need to do. I am using a store procedure that joins a tvf delimitedsplit8k, which allows me to have a multiple parameters seperated by comma. I can keep on adding the parameters. I want the last added parameters to appear at the bottom of the reports. Thank you much appreciated for the time and effort.. - starry_night

3 Answers

0
votes

Assuming your parameter name is MyParameter, in report designer (BIDS) just drop a textbox onto report below the data (e.g. Table) and put following expression into its value's formula:

=Parameters!MyParameter.Value.Split(",")(Parameters!MyParameter.Value.Split(",").Length - 1)

it will split the parameter list and grab the last value

Update: here is the screenshot with steps:

enter image description here

And here is the runtime result

enter image description here

0
votes

This expression works for me:

=Trim(Right(Parameters!Product_Number.Value
  , InStr(StrReverse(Parameters!Product_Number.Value), ",") - 1))

Trim might not be strictly necessary but is useful as it will work if the values are split with spaces as well as commas, or not.

For example:

enter image description here

enter image description here

0
votes

It sounds like you want to order the results of the stored procedure by the order of the product codes as they are typed into the report parameter (which is a comma separated list).

You can return the index (order) of each product code in the parameter by using the Array.IndexOf and Split functions, e.g.

If you have a report parameter called "ProductNumber" and you also have a field called "ProductNumber" returned in your dataset, the following code will return the zero-based index of the Product Number as entered into the parameter list:

=Array.IndexOf(
     Split(Parameters!ProductNumber.Value.ToString(), ",")
   , Fields!ProductNumber.Value
 )

So if abc-234 is the first product number in the parameter list then this code will return 0. If abc-570 is the second product number in the parameter list then this code will return 1, etc.

Assuming the products are listed in a tablix, then I would set the tablix sort expression to the above, which should sort the products into the order specified in the report parameter.

enter image description here