Hi @Mayuran Thiru ,
At first, you need to obtain the next row value of the [From] field through the Lead Function when querying the dataset: SELCT LEAD([From]) OVER (ORDER BY Partner) AS NextValue
.
Since the SSRS dataset query does not support the "OVER" statement, the Common Table Expression (CTE) is referenced in the dataset query. The dataset query is as follows:
; WITH cte AS (
SELECT LEAD([From]) OVER (ORDER BY Partner) NextValue, *
FROM TableName)
SELECT * FROM cte
Through this query you will get an extra column [NextValue], which is the value of the next [From] field obtained after sorting according to the [Partner] field.
Then insert a new column [To] on the right side of the [From] field, using the following expression:
=IIF(
Month(Fields!From.Value)>=Month(Fields!NextValue.Value),
FORMAT(dateadd("d", -1, Fields!NextValue.Value),"MM-dd-yyyy"),
FORMAT(DateSerial(Year(Fields!From.Value), Month(Fields!From.Value), "1").AddMonths(1).AddDays(-1),"MM-dd-yyyy")
)
The expression means: when the month in the [From] field is equal to the value of the month in the [NextValue] field, the day before the [NextValue] field of the row is displayed; when the month in the [From] field is less than or greater than [NextValue] The value of the month in the field (in your case the [From] field will not be greater than the [NextValue] field), then the last day of the month in the [From] field is displayed.
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.