none
How to dynamical display the tablix's column

    Question

  • My customer want to display the column depending on the parameter he selected,could anyone help me out?

    Thanks very much.

    Tuesday, May 04, 2010 4:09 PM

Answers

  • Hi USA_RS,

    You gave the description “display the column depending on the parameter customer selected”, here you didn’t give the detailed information. There might be two understanding about your requirement:

    1.      Displaying the table grouped by the parameter customer selected

    2.      Dynamically display or hidden the column depending on the parameters selected

     

    §  Sample for No.1 understanding

    Here supposing you have two parameters ‘Category’ and ‘SubCategory’. If you select the ‘Category’ in the parameter drop-down list, the table will be displayed grouped by the Category, if you select the ‘SubCategory’ in the parameter drop-down list, the table will be displayed grouped by the SubCategory.

     

    >>Solution:

     

    1.      Right-click the whole table, then select the properties.

    2.      Switch to Groups tab and add a group to your table.

    3.      Type in the expression

    =Switch(Parameters!GroupBY.Value="Category",Fields!Category.Value,Parameters!GroupBY.Value="SubCategory",Fields!SubCategory.Value)

    4.      Drag the relevant datafields to the group row which is just below the header row.

    5.      Replace the words in the header with the expression   =IIF(Parameters!GroupBY.Value="Category","Category","SubCategory")

    6.      Replace the datafield with the expression =IIF(Parameters!GroupBY.Value="Category",Fields!Category.Value ,Fields!SubCategory.Value)

    7.      Delete the detail and footer row.

    8.      Preview the report you will see the report like these pictures:

                              

    §  Sample for No.2 understanding

    Here supposing you have two parameters ‘SalesAmount’ and ‘CostAmount’. If you select the ‘SalesAmount’ in the parameter drop-down list, the table will be displayed column SalesAmount, if you select the ‘CostAmount’ in the parameter drop-down list, the table will be displayed column CostAmount, If you select all the table will be displayed both of the columns .

     

    >>Solution one: Add a customer code to your report

     

    1.      In the "Parameters" dialog, add a new parameter called "ColumnsToDisplay", type some values and labels in, here is ‘SalesAmount’, ’CostAmount’ and select "Multi-value" checkbox. Make sure your parameter does not accept Nulls or Blanks

    2.      Add a function called "IsColumnSelected" using the following VB code that accepts the multi-value parameter and a column name and returns a boolean value

    Public Shared Function IsColumnSelected(ByRef objMultiValueParam() As Object, ByVal strColumnName As String) As Boolean
        Return 0 < InStr(vbNullChar & Join(objMultiValueParam, vbNullChar) & _
            vbNullChar, vbNullChar & strColumnName & vbNullChar)

    End Function

    3.      Switch to the layout tab, and select the whole column of SalesAmount ,then set its visibility using the expression =Not Code.IsColumnSelected(Parameters! ColumnsToDisplay.Value, "SalesAmount")

    4.      Select the whole column of CostAmount ,then set its visibility using the expression =Not Code.IsColumnSelected(Parameters! ColumnsToDisplay.Value, "CostAmount")

    5.      Preview the report ,select different parameter you will see different report like this picture:

                       

    >>Solution two: You can also achieve this using the Instr function in SSRS without VB code

    1.      Select the column ‘SalesAmount’ set its visibility using the expression =IIF(Instr(Join(Parameters! ColumnsToDisplay.Value),"SalesAmount")>0,false ,true)

    2.      Select the column ‘CostAmount’ set its visibility using the expression =IIF(Instr(Join(Parameters! ColumnsToDisplay.Value),"CostAmount")>0,false ,true)

    3.      Preview the report ,you can also get the report like the above picture.

     

    If you have any question about the detailed steps or further question ,please feel free to ask.

    Regards,

    Challen Fu

    • Marked as answer by USA_RS Monday, May 10, 2010 1:35 PM
    Monday, May 10, 2010 7:22 AM
    Moderator
  • You can create parameter with column names.

    Than in deatails field write down expression below

    =switch(Parameters!Parm.value="Column1",Fields!Column1.Value,
    Parameters!Parm.value="Column2",Fields!Column2.Value,
    Parameters!Parm.value="Column3",Fields!Column3.Value)

    And in Header of that column

    =switch(Parameters!Parm.value="Column1","Column1",
    Parameters!Parm.value="Column2","Column2",
    Parameters!Parm.value="Column3","Column3")

     

     


    Thanks, Kishan Mark it as Answer if this post helped you.
    • Marked as answer by USA_RS Monday, May 10, 2010 1:35 PM
    Tuesday, May 04, 2010 4:58 PM

All replies

  • You can create parameter with column names.

    Than in deatails field write down expression below

    =switch(Parameters!Parm.value="Column1",Fields!Column1.Value,
    Parameters!Parm.value="Column2",Fields!Column2.Value,
    Parameters!Parm.value="Column3",Fields!Column3.Value)

    And in Header of that column

    =switch(Parameters!Parm.value="Column1","Column1",
    Parameters!Parm.value="Column2","Column2",
    Parameters!Parm.value="Column3","Column3")

     

     


    Thanks, Kishan Mark it as Answer if this post helped you.
    • Marked as answer by USA_RS Monday, May 10, 2010 1:35 PM
    Tuesday, May 04, 2010 4:58 PM
  • Hi USA_RS,

    You gave the description “display the column depending on the parameter customer selected”, here you didn’t give the detailed information. There might be two understanding about your requirement:

    1.      Displaying the table grouped by the parameter customer selected

    2.      Dynamically display or hidden the column depending on the parameters selected

     

    §  Sample for No.1 understanding

    Here supposing you have two parameters ‘Category’ and ‘SubCategory’. If you select the ‘Category’ in the parameter drop-down list, the table will be displayed grouped by the Category, if you select the ‘SubCategory’ in the parameter drop-down list, the table will be displayed grouped by the SubCategory.

     

    >>Solution:

     

    1.      Right-click the whole table, then select the properties.

    2.      Switch to Groups tab and add a group to your table.

    3.      Type in the expression

    =Switch(Parameters!GroupBY.Value="Category",Fields!Category.Value,Parameters!GroupBY.Value="SubCategory",Fields!SubCategory.Value)

    4.      Drag the relevant datafields to the group row which is just below the header row.

    5.      Replace the words in the header with the expression   =IIF(Parameters!GroupBY.Value="Category","Category","SubCategory")

    6.      Replace the datafield with the expression =IIF(Parameters!GroupBY.Value="Category",Fields!Category.Value ,Fields!SubCategory.Value)

    7.      Delete the detail and footer row.

    8.      Preview the report you will see the report like these pictures:

                              

    §  Sample for No.2 understanding

    Here supposing you have two parameters ‘SalesAmount’ and ‘CostAmount’. If you select the ‘SalesAmount’ in the parameter drop-down list, the table will be displayed column SalesAmount, if you select the ‘CostAmount’ in the parameter drop-down list, the table will be displayed column CostAmount, If you select all the table will be displayed both of the columns .

     

    >>Solution one: Add a customer code to your report

     

    1.      In the "Parameters" dialog, add a new parameter called "ColumnsToDisplay", type some values and labels in, here is ‘SalesAmount’, ’CostAmount’ and select "Multi-value" checkbox. Make sure your parameter does not accept Nulls or Blanks

    2.      Add a function called "IsColumnSelected" using the following VB code that accepts the multi-value parameter and a column name and returns a boolean value

    Public Shared Function IsColumnSelected(ByRef objMultiValueParam() As Object, ByVal strColumnName As String) As Boolean
        Return 0 < InStr(vbNullChar & Join(objMultiValueParam, vbNullChar) & _
            vbNullChar, vbNullChar & strColumnName & vbNullChar)

    End Function

    3.      Switch to the layout tab, and select the whole column of SalesAmount ,then set its visibility using the expression =Not Code.IsColumnSelected(Parameters! ColumnsToDisplay.Value, "SalesAmount")

    4.      Select the whole column of CostAmount ,then set its visibility using the expression =Not Code.IsColumnSelected(Parameters! ColumnsToDisplay.Value, "CostAmount")

    5.      Preview the report ,select different parameter you will see different report like this picture:

                       

    >>Solution two: You can also achieve this using the Instr function in SSRS without VB code

    1.      Select the column ‘SalesAmount’ set its visibility using the expression =IIF(Instr(Join(Parameters! ColumnsToDisplay.Value),"SalesAmount")>0,false ,true)

    2.      Select the column ‘CostAmount’ set its visibility using the expression =IIF(Instr(Join(Parameters! ColumnsToDisplay.Value),"CostAmount")>0,false ,true)

    3.      Preview the report ,you can also get the report like the above picture.

     

    If you have any question about the detailed steps or further question ,please feel free to ask.

    Regards,

    Challen Fu

    • Marked as answer by USA_RS Monday, May 10, 2010 1:35 PM
    Monday, May 10, 2010 7:22 AM
    Moderator
  • Hi,

    You can use the visibility property of a column based on the Report parameter value you can hide or show the column.

    Thanks,

    Shobhit

    P.S : if the post is useful please mark as answer

    Monday, May 10, 2010 9:28 AM
  • Hi Challen thanks for your help , your No2 understanding is just what I need. I still learn some from your No1 understanding.
    Monday, May 10, 2010 1:34 PM
  • I have proposed a step by step solution here.
    Thursday, May 10, 2012 4:07 AM