locked
SSRS hide multiple column based on condition RRS feed

  • Question

  • User88744855 posted

    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.

    Wednesday, February 8, 2012 7:38 AM

Answers

  • User-830595639 posted

    Hi

    Write the following expression in the Hidden property of Row.

    =IIF(InStr(Parameters!JobID.Value,Fields!JID.Value)>0,False,True) and send the value in JobID parameter like "Value1,Value2,Value3".

    Thanks

    Nishant

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 10, 2012 2:32 AM

All replies

  • User-1756429883 posted

    If you want to use a parameter, you could create a dataset   with   :

    Select JOBID from "table"   where JOBID <> 02,07,11,19,21,29,31,40

     

    then assign that dataset to the parametar and those are the defaul values as well...

    the report should use that expresion to show all results but the ones taken out by the WHERE clause

    Wednesday, February 8, 2012 9:41 AM
  • User88744855 posted

    not at all clear...i never seen that dataset can be assign to parameter. if u can then discuss in detail.

    Thursday, February 9, 2012 1:13 AM
  • User-830595639 posted

    Hi

    Write the following expression in the Hidden property of Row.

    =IIF(InStr(Parameters!JobID.Value,Fields!JID.Value)>0,False,True) and send the value in JobID parameter like "Value1,Value2,Value3".

    Thanks

    Nishant

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, February 10, 2012 2:32 AM
  • User-1756429883 posted

    Ok easy Mou

    Create the dataset with the query I wrote before.  Then go to the report parameters and create one. Give it a name, a data type, and what the report will Prompt from the user.  If its going to be a hidden parameter (the user wont have to select anything) mark it , if you want the user to identify which can be selected then leave it uncheck.   on the Available values select FROM QUERY and select your newly created dataset... the same goes for default values and you are all set

     

     

     

    mark if this helps

    Monday, February 13, 2012 2:37 PM
  • User-492783255 posted

    1.Create custom function  under report/report properties/code

    2.Create parameters with check multiple values

    3. Create list with column name (available values)

    4. To each columns set visible criteria, where "1" change to correct column number:

        =Not Code.IsColumnSelected(Parameters!test.Value, "1")

    Public Function IsColumnSelected(ByRef test() As Object, ByVal tableName As String) As Boolean

    ' return whether the passed column name is in the multi-value
    ' parameter array
    Return 0 < InStr(vbNullChar & Join(test, vbNullChar) & _
    vbNullChar, vbNullChar & tableName & vbNullChar)

    End Function

    Tuesday, February 28, 2012 5:09 AM
  • User88744855 posted

    i do not understand fully what u tried to say.

    u said To each columns set visible criteria, where "1" change to correct column number....what is the meaning of 1 here?

    function IsColumnSelected what it does?

    u call IsColumnSelected with param value and hard coded 1 but the function signature is different. i hope the code is not proper. plzz check and get back.

    Tuesday, February 28, 2012 1:02 PM
  • User-492783255 posted

    1.Copy custom function  under report/report properties/code

    Public Function IsColumnSelected(ByRef test() As Object, ByVal tableName As String) As Boolean

    ' return whether the passed column name is in the multi-value
    ' parameter array
    Return 0 < InStr(vbNullChar & Join(test, vbNullChar) & _ 
    vbNullChar, vbNullChar & tableName & vbNullChar)

    End Function

    in this exsample parameter name is "test"

    2.Create parameters with check multiple values (parameter properties - general)

    3. Create list with column name (parameter properties - available values)

            Label             value
         column1              01

         column2              02

        etc

    4. To each columns in table set visible criteria, where "1" replace to correct column number  01 or 02 or 03 etc from value column :

        =Not Code.IsColumnSelected(Parameters!test.Value, "1")


    As result you got one additional filter, where you can check which columns show and which hide

    Wednesday, February 29, 2012 7:28 AM