locked
It is possibe to only list fields which has values in a SSRS free form report? RRS feed

  • Question

  • I want to build a SSRS free form repot to display employee's payroll information within a work group. There are total 40 fields in a record for each employee but some of these fields don't have values. Is it possible to only list valued fields in the textboxes in the order with field names dynamically?
    Thursday, April 19, 2018 3:58 PM

All replies

  • Hi zhao790

    According to your description, you want just show the field which is not null in report, right?

    Could you tell me which one you want to hide , the cell with null or blank value or the column with all null and blank value?


    Former :aaaa

    Latter: ccccc

    Generally , it would accomplish in report by design using column visibility expression to control the visibility for the whole 40 fields, unfortunately ,you have to set the expression for 40 fields

    If it is former , you could add your table into list and use column visibility like this :

    =Iif(Fields!number.Value Is Nothing, True, False)

    Ps: list must group on the field with unique check ,and you can set the table border coincide with list border in order to combine several tables in list together ,in appearance.

    If it is latter, the expression is also worked, but no need to add table into list.

    Also ,you could use query to return the query set of field which is not all null or blank value.

    You could ask T-SQL team for more professional help.

    Hope it can help you.

    Best Regards,

    Eric Liu


    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 20, 2018 8:09 AM
  • its not possible to bind the values dynamically

    But you can always set static mapping for all the 40 columns and conditionally hide them based on whether they've values or not

    The expression would just look like

    =IIF(Len(Fields!ColumnName.Value) > 0, False,True)

    for hidden expression of each textbox where ColumnValue would be the displayed value


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Friday, April 20, 2018 10:14 AM
  • Thank you so much for you help, Eric.

    I was required to build a SSRS sub report for employee payroll deduction information like this format (Sample from two employees for comparison). 

    Each row shows 4 fields. If a data field is null or blank it is not displayed and next valued field will replace the position to appear dynamically.

    Such as in Data1 the field CREDIT UNI doesn't appear and FICA appears at second position comparing with Data2. In Data 2 LIFE INSUR, MUNICIPAL, PENSION-LO, TRANSITBEN, TDA LOAN appear but no in Data1

    Is it possible to build this report? Thanks in advance.

    Data1:

    DED: CITY TAX 35.11     FICA    80.40     FEDERAL TA 123.51  HEALTH 2.52

         MEDICARE 18.81     PENSION 38.98     STATE TAX 50.61    TAX-DEFFER 25.21

         UNION DEDU 33.60

    Data2:

     DED: CITY TAX 61.87     CREDIT UNI 345.52  FICA 125.25      FEDERAL TA 18.06

          HEALTH .00        LIFE INSUR 46.60   MEDICARE 29.29   MUNICIPAL 1.00

          PENSION-LO 136.33 STATE TAX 91.44    TRANSITBEN 60.50 TAX-DEFFER 101.00

          TDA LOAN 94.85    UNION DEDU 33.60

    Saturday, April 21, 2018 8:03 PM
  • Thanks, Visakh16. See what I was required for the assignment.
    Saturday, April 21, 2018 8:22 PM
  • Are these contents from single column or multiple columns in your report?

    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Sunday, April 22, 2018 8:40 AM
  • Data is from one record for one employee with multiple columns.
    Monday, April 23, 2018 11:52 AM
  • Hi  zhao790

    I test on my machine ,and it seems impossible to do it as far as i know.
    First ,you don’t know the column count number in each row. May be 10 in first row, 6 in second ..
    Second , you can’t determine the field name in each column of table . such as in the first column in table of report , you may get the field lik: city , life and other fields.
    you need to control the filed name in each row ,and it seems impossible obviously.

    So, it Is better to show it in table with blank in cell which have null value.

    Thanks for understanding.
    Best Regards,
    Eric Liu

    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, April 27, 2018 10:09 AM
  • Thanks Eric.
    Friday, April 27, 2018 6:08 PM
  • Hi  zhao790

    If you have any other question about this issue, please feel free to ask. 

    And , if you think the reply solved your issue, please mark as answer.

    Best Regards.

    Eric Liu 



    Best Regards, Eric Liu MSDN Community Support Please remember to click Mark as Answer if the responses that resolved your issue, and to click Unmark as Answer if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, May 1, 2018 7:01 AM
  • Thanks Eric.

    Is it ok if you display all these available data in concatenated format inside a single report column?

    It will be similar to the format you showed above

    If that is ok, then you can get it easily using a SSRS expression


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Tuesday, May 1, 2018 7:04 AM