CODEWITHSUNDEEP
sql-servert-sqlreporting-services
NewbieSQL_Germany
NewbieSQL_Germany

Reputation: 85

SSRS - Add year to month

In my report, i show the sales commission for 3 months from each sales person. I've also added the payout month. For a better overall look, i want to add the year to the payout month.

This is the month in my SQL-Query :

,[Monat] = convert(varchar(4),Year(Datum)) + right('00' + convert(varchar(2),Month(Datum)),2)

Right now, the expression for my payout month is this:

=MONTHNAME(IIF(RIGHT(Fields!Monat.Value,2)+2>=13,RIGHT(Fields!Monat.Value,2)-12+2,RIGHT(Fields!Monat.Value,2)+2))

The payout is always two months in the future. Commission of december 2020 will be payed in february 2021, january 2021 will be payed in march 2021 and so on.

enter image description here

I want the result to look like this: Februar 2021, März 2021 etc.

Is there a way to depict that in the SSRS expression? Thanks in advance.

Upvotes: 0

Views: 359

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

If you just need to display the month and year from a date field after you have added 2 months to it, then you can just do something like this.

=DATEADD(DateInterval.Month, 2, Fields!Datum.Value)

You can then format the textbox using MMMM yyyy to display it as you want.

Alternatively, you can do it all in a single step but this should be avoided if possible as you are then losing the date and converting to a string. If you exported to excel for example, you would not be able to do anything with the cell as it would just be a string.

If you really have to do this in all in a single expression then you can use

=FORMAT(DATEADD(DateInterval.Month, 2, Fields!Datum.Value), "MMMM yyyy")

Upvotes: 2

Related Questions