question

Geronimo-3335 avatar image
0 Votes"
Geronimo-3335 asked Joyzhao-MSFT edited

SSRS Dataset values with IFF conditions

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
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Geronimo-3335 commented

Hi @Geronimo-3335
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.


· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Joy,

Thanks for your response. Actually i have a dataset with 2 columns: Names, Roles and this dataset cannot be edited because it forms part of predefined list of reports for an application. By the way, thanks for the note about IFF, it was changed accordingly, but same issue.

I need 2 columns to separate the Names, so if you are a team leader your name appear in one of the column and if not they appear in another column. I can do it using filter but this require 2 different tablix of one column arranged side to side. But with this setup, one of the rows expands while the other stays the same size. If there is a possibility to freeze both columns together so that the both row resize together, it will be enough.

Best Regards,
Geronimo

0 Votes 0 ·
Joyzhao-MSFT avatar image
0 Votes"
Joyzhao-MSFT answered Joyzhao-MSFT edited

Hi Geronimo,
@Geronimo-3335 ,
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.


01.jpg (64.9 KiB)
02.jpg (40.2 KiB)
03.jpg (43.7 KiB)
· 5
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Joy,

I get what you are saying. I used ur expression for visibility. The information is correctly retrieved. However, indentation is not correct. It seems to me that an additional blank row is added due to the boolean conditions. In my second column the data start on the next row. Unfortunately i cannot send u a screenshot as i have an issue with the print screen button on my keyboard.

At any point in time, both columns need to have information. i hope you get my point.

Any idea how can this be solved?

Thanks & Regards,
Girish

0 Votes 0 ·

Hi Girish,
I’m sorry I can’t imagine how the blank page appears and what your report looks like at the moment.
Although I am willing to help you resolve this issue, I am really at a loss.
Regards,
Joy

0 Votes 0 ·

Hi Joy,

I managed to click a picture of that tablix after i used the visibility formula you provided. The rows are misplaced as you would see, i would like to see them aligned . In fact i want both rows to be of the same saze, that is they expand with respect to the other. Have a look at the screenshot i uploaded.

Thanks,
Geronimo94695-20210507-104531.jpg


0 Votes 0 ·
20210507-104531.jpg (231.2 KiB)

Hi Roy,

I have figured another way to achieve that by creating 2 separate tablix and used filters with expressions to display data i require. I used a condition to reset roles to blank for all those whose position are not Team Leader.

Thanks for your help by the way.

Regards,
Geronimo

0 Votes 0 ·

Hi Geronimo,
I'm glad to hear that you have resolved this issue. You could mark a useful answer as "Accept Answer" to help more people see it.
Best Regards,
Joy

0 Votes 0 ·