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 753

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?

  • Shooowtek Profile Picture
    Shooowtek 753 on at
    RE: How to write an expression to show field in ssrs report?

    Thank you GirishS for your help and efforts.

  • GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: How to write an expression to show field in ssrs report?

    Great!

    Instead of that you can create two separate designs. Based on the parameter selection you can set the design name in controller class.

    Thanks,

    Girish S.

  • Shooowtek Profile Picture
    Shooowtek 753 on at
    RE: How to write an expression to show field in ssrs report?

    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.

  • Shooowtek Profile Picture
    Shooowtek 753 on at
    RE: How to write an expression to show field in ssrs report?

    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....

  • GirishS Profile Picture
    GirishS 27,823 Moderator on at
    RE: How to write an expression to show field in ssrs report?

    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 753 on at
    RE: How to write an expression to show field in ssrs report?

    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,823 Moderator on at
    RE: How to write an expression to show field in ssrs report?

    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 753 on at
    RE: How to write an expression to show field in ssrs report?

    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,823 Moderator on at
    RE: How to write an expression to show field in ssrs report?

    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 753 on at
    RE: How to write an expression to show field in ssrs report?

    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)

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

Daivat Vartak – Community Spotlight

We are honored to recognize Daivat Vartak as our March 2025 Community…

Announcing Our 2025 Season 1 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Kudos to the February Top 10 Community Stars!

Thanks for all your good work in the Community!

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 293,202 Super User 2025 Season 1

#2
Martin Dráb Profile Picture

Martin Dráb 231,923 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,156 Moderator

Leaderboard

Featured topics

End-of-life Announcement for Microsoft Dynamics GP

Product updates

Dynamics 365 release plans