SSRS Report - How to display a specific column value confditionally within a Row Group?

Question

Hi Folks,

My SSRS report Dataset (dataset 1) returns the data similar to below:

The report shows the output grouped by the Owner column as shown below. Please help with how can I display (what expression to use) the Oldest Open Case date value (where status = Open) by each team in the last column?

Answer 1

Hi @Sahil Monga ,
If you want to get the maximum value of the date, you can use only the Max or Min Function, combined with the IIF Function.

To get the earliest date with the status "Open", use the expression: =IIF(Fields!Case_Status.Value="Open",Min(Fields!Case_Created_On_Date.Value),Nothing)

To get the latest date with status "Open", use the expression: =IIF(Fields!Case_Status.Value="Open",Max(Fields!Case_Created_On_Date.Value),Nothing)

Here is the query data for my dataset:

Design:

Preview:

Best Regards,
Joy

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
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.

Answer 2

Hi,

Yes, statecodevalue=0 means Open and Createdon is a date field. I tweaked the expression a little and below worked:

=Min(iif(Fields!statecodeValue.Value=0,Fields!createdonValue.Value,nothing))

Actually, my dataset returns the name of teams and users both, to keep it simple I just showed the Team (ownerid) name in the snapshot so my grouping is - iif(Fields!owneridEntityName.Value="team", Fields!ownerid.Value, Fields!alias_team_name.Value)

Thanks so much for the help @Joyzhao-MSFT !

Answer 3

Hi @Joyzhao-MSFT ,

Thanks so much for your reply. I had actually tried the same before but it shows blank value in my case. Is there anything I am missing:

Report Design:

Expression:

Output: