Reporting Services - hide table row based upon report parameter value

0
votes

i am showing jobid in my report. i want that if job ID are this 02,07.11,19,21,29,31,40 etc then those report rows will not be shown. i know how to hide rows writing expression but i just need to know if there any short cut way to say that my job is are 02,07.11,19,21,29,31,40.

still i am doing like =IIF(Fields!JID.Value = 02 or Fields!JID.Value = 07 or Fields!JID.Value = 11 or Fields!JID.Value = 19, True, False)

is there any way like =IIF(Fields!JID.Value in (02,07.11,19,21,29,31,40), True, False)

if i send job id as a parameter value send from calling environment like "02,07.11,19,21,29,31,40" then how to di it.

please let me inform.

1
reporting-services

1 Answers

0
votes

Will it work for your purposes to handle this in SQL? You have a couple options...

  1. Option 1

    Set up a multi-valued, string parameter in your report called @JIDValues. SQL sprocs do not accept arrays of values, so you will need to pass this as a string array to your sproc. In order to do this, you will need to join your values together in your report code that executes the stored procedure.

    JOIN(Parameters!SiteGroupList.Value, ",") & "'"

    Inside the sproc (or the report depending on your preference) you will need to append and prepend commas to your string so that the following will work for your values on the ends as well as in the middle of the string. The following WHERE clause will be able to determine if a specific Job ID is contained in the comma delimited string you passed to the sproc.

    WHERE @JIDValues LIKE '%,' + LTRIM(RTRIM(STR(tableA.JID))) + ',%'

  2. Option 2

    Alternatively, if this is a static list, you could hard-code your sproc to exclude them.