nested iif statement not working

Question

nested iif statement not working

Systems Administration 1

Hello,
If I have this table with the values below but for the "chng Date" and "ss State" I just want one row.
I have tried this SSRS iif Statment and it is not giving me one row:
=IIf(Fields!ss_State.Value = "TimeCarded" OR Fields!ss_State.Value = "Approved", Fields!ChngDate.Value,
IIf(Fields!ss_State.Value = "Posted", Fields!ChngDate.Value ,
Fields!ChngDate.Value ) )
Please advise.
ed EE ID Dateworked Chng Date ss State
22833 May 19, 2021 3-5-2021 12:00:00 AM Posted
5-20-2021 12:00:00 AM Timecard
May 20, 2021 3-5-2021 12:00:00 AM Posted
5-20-2021 12:00:00 AM Timecard
May 23, 2021 3-5-2021 12:00:00 AM Posted
6-1-2021 12:00:00 AM Approved
May 24, 2021 3-5-2021 12:00:00 AM Posted
6-1-2021 12:00:00 AM Approved

4 answers

Your answer

Answer 1

You're using the same expression Fields!ChngDate.Value for both the true and false cases so this if statement isn't doing anything. Note that if your dataset has 4 rows then the resulting table in SSRS is going to have 4 rows as well. You cannot use expressions to combine rows like that which it seems is what you're wanting to do. If you want to combine rows together then you need to use grouping instead. Grouping will allow you to combine related rows together into a single row. But given that your example if statement is using the same value for all combinations of true and false it is unclear how to group. At a first pass I'd say group by ss_State to get all the rows for a particular state together but I'm just guessing based upon the limited data provided.

For SSRS also note that you can tell it to put some fields in rows, some in columns and some in the details. Therefore you can use this to "combine" rows as well in some cases. For example you might specify the state as the rows and the dates as the columns leaving the details to show only the fields that are remaining. But it is really dependent upon the data and what you want to show.

Ultimately you might do better to go back to your dataset in the designer and adjust the query until you get the data you need. In general it is better, and easier, to use SSMS/Azure Data Studio to get a working query and then use that for your dataset rather than blindly loading data from the tables and then using SSRS expressions to manipulate the data. So personally I would recommend you start with fixing your query to return the data organized the way you want.

Systems Administration 1 Reputation point

2021-06-08T14:00:16.12+00:00

Hello,

Thank you for the response, I did try this with the dataset query but it did not work. Now , I am trying this in SSRS.
My chngdate is in a group and in my group properties I placed the iif statment in the expression area. My goal is to retrieve one row of the latest chng date with the state field too.
Michael Taylor 60,161 Reputation points

2021-06-08T14:53:42.79+00:00

If you are trying to take a row and determine a value given that row then consider using switch instead of Iif.

=Switch(Fields!ss_State.Value == "TimeCarded", Value1, Fields!ss_State.Value == "Approved", Value2, Fields!ss_State.Value == "Posted", Value3)

Answer 2

Systems Administration 1

If I use a switch in the chng date group expression then I get empty cells for chng date. This is the expression I used. Should I remove the detail level?

Michael Taylor 60,161 Reputation points

2021-06-08T16:07:36.31+00:00
The Switch expression won't put anything in the UI if the values don't match so add a final condition and value that is true.

=Switch(...,, True, "Unknown")

Then make sure it shows up. If it does then this tells you the expressions you're using aren't valid. Note that equality is case sensitive so you might consider using StrComp to compare the strings instead to eliminate this issue.

Answer 3

ZoeHui-MSFT 41,491

Hi @Systems Administration ,

for the "chng Date" and "ss State" I just want one row

Not really clear about your need.

Could you please describe it with more details? (screenshot is welcome.)

It seems the original data is like below, what's the result you want?

Regards，

Zoe

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.
Hot issues October

Systems Administration 1 Reputation point

2021-06-09T10:14:54.36+00:00

the result I am trying to achieve :

ed_ee_id Dateworked ChngDate ss_State
22833 May 23, 2021 June 1, 2021 Approve

The max date for the chgDate and the coincide ss_State which in the example is "Approve".
The result should only give one row for the chgDate and the ss_state. The order of the ss_State as follows: post, approve, Timecarded. An example from above

ed_ee_id Dateworked ChngDate ss_State
22833 May 19, 2021 May 20, 2021 TimeCarded
ZoeHui-MSFT 41,491 Reputation points

2021-06-10T06:48:06.337+00:00

Hi @Systems Administration ,

I fully understand your needs now.

It's a little hard to meet your needs from SSRS side, we'd suggest that you may deal with the data with t-sql before design the report.

You may post a new question with the tag of sql-server-transact-sql, there are several experts to help deal with your issue.

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

Furthermore, it helps to have a short description of the business rules which explains why you want that result.

Answer 4

Systems Administration 1

Could we not retrieve my goal by having the chng date in a desc order then have the "first" function row selected for the group of chng dates?

Share via

nested iif statement not working

4 answers

Your answer