Skip to main content

Notifications

Announcements

No record found.

Dynamics 365 Community / Forums / Finance forum / How to write an expres...
Finance forum
Suggested answer

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

Posted on by 643

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 643 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,828 Super User 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 643 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 643 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,828 Super User 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 643 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,828 Super User 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 643 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,828 Super User 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 643 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)

Helpful resources

Quick Links

Replay now available! Dynamics 365 Community Call (CRM Edition)

Catch up on the first D365 Community Call held on 7/10

Community Spotlight of the Month

Kudos to Saurav Dhyani!

Congratulations to the June Top 10 community leaders!

These stars go above and beyond . . .

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 287,995 Super User

#2
Martin Dráb Profile Picture

Martin Dráb 225,610 Super User

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans