SSRS Dataset values with IFF conditions

Geronimo 1 Reputation point
2021-05-05T07:12:47.657+00:00

Hi Everyone,

I am using a predefined dataset which contains staff names and roles. What i want to achieve is to display only names with respect to a role in a column. So example Team Leader need to appear in one column and the rest should appear in another column next to it. ( Same Tablix ).

I modified 2 separate tablix and placed it side by side. Filter was used to retrieve names based on the Roles. The issue i get is that if on column contains more than one name, the size of the row will grow, but the row next to it stays the same. I would like them to render with the same row size, that is a possibility to join the two separate tablix columns in one row.

The other option to try is to create one tablix and use conditional expression to display data based on the roles. But this is tricky and it does not work. My code below:

=IFF(First(Fields!StaffRole.Value, "AuditReportStaff")="Team Leader",First(Fields!StaffName.Value, "AuditReportStaff"))

Any idea as to why this code does not work or is giving error. ( "The Value expression for the textrun ‘StaffName.Paragraphs[0].TextRuns[0]’ contains an error: [BC30451] 'IFF' is not declared.)"

What do you suggest ?

appreciate your help on the above.

Thanks & Regards

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
2,727 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Joyzhao-MSFT 15,561 Reputation points
    2021-05-05T08:47:20.367+00:00

    Hi @Geronimo
    Do you mean IIF function? Instead of IFF.
    The best way to resolve this type of issue is to show the framework of your tablix and give some test data. Usually this is more intuitive than imagining what your report looks like.
    Best Regards,
    Joy


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Joyzhao-MSFT 15,561 Reputation points
    2021-05-05T10:19:43.413+00:00

    Hi Geronimo,
    @Geronimo ,
    I will show you cases that I think are similar:
    In the following table, when Rank>=5, the Sales1 column is displayed, and when Rank<5, the Sales2 column is displayed. (Similar to: when the employee role is'Team Leader', the name is displayed in column 1, when the employee role is not'Team Leader', it is displayed in column 2)
    93858-02.jpg
    I used the following expressions in the "Visibility" option of the Tax Box properties of the two columns:
    93955-03.jpg

    =IIf(Fields!Rank.Value>=5,False,True)  
    =IIf(Fields!Rank.Value<5,False,True)  
    

    After previewing, it is displayed as follows:
    93961-01.jpg
    According to your situation, I guess you may need to set the visibility expression of staff names1 as:

    =IIf(Fields!StaffRole.Value="Team Leader",False,True)  
    

    The visibility expression of andstaff names2 is set to:

    =IIf(Fields!StaffRole.Value="Team Leader",True,False)  
    

    The above expression is based on your StaffRole and Roles fields are the same Dataset. The First function returns the first value of a set of data after applying all sorts and filters in the specified scope. I don't understand why you need First Function, you could add a description here.
    If I misunderstand what you mean, please feel free to correct me. To be honest, I really hope you can give some simple data for testing.
    Best Regards,
    Joy


    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.