web
You’re offline. This is a read only version of the page.
close
Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Finance | Project Operations, Human Resources, ... / How to write an expres...
Finance | Project Operations, Human Resources, ...
Suggested Answer

How to write an expression to show field in ssrs report?

(0) ShareShare
ReportReport
Posted on by Shooowtek 843

I have some parameters in a report based on which I would like to show and hide fields. As far as I know, standard expressions are constructed to hide a field.
Because I have multiple parameters, I would like the field to be visible for a certain configuration of parameters. In my opinion, this is better than writing out all the combinations when the field should be invisible (and there are many of these combinations)

Example: I would like field1, to be visible only when parm1="Yes", parm2="No", parm3="No" and parm4="Yes".

Could someone give me a hint on how to solve this problem?

I have the same question (0)
  • Suggested answer
    GirishS Profile Picture
    GirishS 27,827 Moderator on at

    Hi Shooowtek,

    You can use And operator to set visibility. Navigate to visibility property of the check box.

    IIF(parameter!parm1.Value="Yes" AND parameter!parm2.Value="Yes", False, True)

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 843 on at

    I observed a strange thing that I don't understand. I have a report with two tablixes. I have two "NoYes" parameters on the dialog.

    And now I have this situation in the report:
    1. show tablix 1 when parm1="Yes" and parm2 ="No".
    2. show tablix2 when parm1="Yes" and parm2="Yes"

    Tablix1 has expression for showing:

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm.Value = "No", false, true)

    Tablix2 contains such expressions for showing:

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm.Value = "Yes", false, true)m

    In this case, I can display the report with tablix1. On the other hand, if I select parm1="Yes" and parm2="Yes" on the dialog Im getting this error:

    The expression referenced a non-existing field in the fields collection.

    but when I change the expressions for hidden:

    tablix1:

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm.Value = "Yes", true, false)

    tablix2:

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm.Value = "No", true, false)

    I can display the report with tablix2, and again Im getting an error when I want to display the report with tablix1..

    Can anyone suggest me what I am doing wrong and how to solve it?

    Many thanks.

  • Shooowtek Profile Picture
    Shooowtek 843 on at

    Sorry, for the error in the expression. Always the second parameter is parm2.

    Tablix1 has expression for showing:

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm2.Value = "No", false, true)

    Tablix2 contains such expressions for showing:

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm2.Value = "Yes", false, true)

    The expressions for hidden:

    tablix1:

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm2.Value = "Yes", true, false)

    tablix2:

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm2.Value = "No", true, false)

  • GirishS Profile Picture
    GirishS 27,827 Moderator on at

    Try to restore the dataset and restart the SQL server reporting services.

    Also, on the expression window you will be able to see the parameter option on the left side. Make sure the "parm2" parameter is available for selection on the right side.

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 843 on at

    Yes, all parameters are available.

    I found on the Internet that I should use JOIN (or JOIN + InStr) in the expression. New expression:

    =IIf(Join(Parameters!DsMain_parm1.Value,",").Contains("Yes") and Join(Parameters!DsMain_parm2.Value,",").Contains("No"), false, true)

    Received error:

    The Hidden expression for the tablix 'Tablix1' contains an error: Overload resolution failed because no Public 'Join' can be called with these arguments: 'Public Shared Function Join(SourceArray As String(), [Delimiter As String = ]) As String': Argument matching parameter 'SourceArray' cannot convert from 'String' to 'String()'. 'Public Shared Function Join(SourceArray As System.Object(), [Delimiter As String = ]) As String': Argument matching parameter 'SourceArray' cannot convert from 'String' to 'Object()'.

  • GirishS Profile Picture
    GirishS 27,827 Moderator on at

    I guess join is used for multi select parameters. For the second Tablix try below expression.

    IIF(Instr(Parameterss!parm1.Value.Value = "Yes",Parameters!parm2.Value = "Yes"),false,true)

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 843 on at

    Hello GirishS, Thank you for your help.

    What do you mean by writing for the second tablix? - the expression for the first tablix what should it look like?

  • GirishS Profile Picture
    GirishS 27,827 Moderator on at

    You said the expression for 1st Tablix is working as expected.

    So, for the 2nd Tablix you said its throwing error when we set both parameters to "Yes".

    Thats why I tell you that expression.

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 843 on at

    The expression for tablix1 (is working OK):

    =IIf(Parameters!parm1.Value = "Yes" and Parameters!parm2.Value = "No", false, true)

    The expression for tablix2 (gets an error):

    =IIf(InStr(Parameters!parm1.Value = "Yes", Parameters!parm2.Value = "Yes"), false, true)

    error:

    The expression referenced a non-existing field in the fields collection.

    When I replace with an expression for hidden, then tablix2 is OK, and tablix1 gets these errors. I have no idea how to solve it anymore....

  • Shooowtek Profile Picture
    Shooowtek 843 on at

    I solved the problem by moving all parameter logic to DP class. As a result, the report always uses only one parameter. I don't know if this follows good practice, but it meets my expectations and works well.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Congratulations to the November Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
Martin Dráb Profile Picture

Martin Dráb 660 Most Valuable Professional

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 549 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 307 User Group Leader

Last 30 days Overall leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans