question

SahilMonga avatar image
0 Votes"
SahilMonga asked SahilMonga answered

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

Hi Folks,

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

199894-dataset1.jpg


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?

199912-report-output.jpg


sql-server-reporting-services
dataset1.jpg (52.7 KiB)
report-output.jpg (46.1 KiB)
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 Joyzhao-MSFT edited

Hi @SahilMonga ,
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:

199988-image.png

Design:

200073-image.png

Preview:

199989-image.png

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.



image.png (24.1 KiB)
image.png (15.4 KiB)
image.png (14.7 KiB)
image.png (15.4 KiB)
· 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.

Set the Text Box Properties as follows:

200071-image.png

Preview:

200015-image.png


0 Votes 0 ·
image.png (20.0 KiB)
image.png (13.9 KiB)
SahilMonga avatar image
0 Votes"
SahilMonga answered Joyzhao-MSFT commented

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:
200363-image.png

Expression:
200373-image.png

Output:
200392-image.png



image.png (10.8 KiB)
image.png (20.7 KiB)
image.png (11.4 KiB)
· 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.

Does statecodevalue=0 mean that the case status is open?
I don't see this field in the dataset query you gave. Is the data type of the field [createdon] a date? Please let me know the grouping of your report and the usage of expressions.

0 Votes 0 ·
SahilMonga avatar image
1 Vote"
SahilMonga answered

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 !

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.