I got a requirement to show previous year and current year in a SSRS report.
Ex: If you are in 2019 . Need to show 2018 and 2019
If we are in 2020. Need to show 2019 and 2020
To achieve this I just need to get previous year start, end dates and current year start and end dates.
I used the below code to get the result.
Variable declaration
-------------------------
int currentYear = year(systemDateGet());
int previousYear = currentYear - 1; // previous year
int nextYear = currentYear + 1; // Next year
// I have another way to get previous year and next year. But, this will match the exact date in the previous year and next year.
date prvyear = prevYr(systemDateGet());
date nextyear = nextYr(systemDateGet());
str CurrentYearStart = "01"+"/"+"01"+"/"+int2Str(currentYear);
str CurrentYearEnd = "12"+"/"+"31"+"/"+int2Str(currentYear);
str previousYearStart = "01"+"/"+"01"+"/"+int2Str(previousYear);
str previousYearEnd = "12"+"/"+"31"+"/"+int2Str(previousYear);
Coding
----------
//Invoiceamount previous year
select sum(InvoiceAmount) from custInvoiceJourPrvYear where custInvoiceJourPrvYear.InvoiceDate >= str2Date(previousYearStart, 213)
&& custInvoiceJourPrvYear.InvoiceDate <= str2Date(previousYearEnd, 213)
&& custInvoiceJourPrvYear.InvoiceAccount == salesTable.InvoiceAccount
&& custInvoiceJourPrvYear.InvoiceAmount > 0.00;
SalesActivityReportTmp.PreviousYearTotal = custInvoiceJourPrvYear.InvoiceAmount;
//Invoiceamount current year
select sum(InvoiceAmount) from custInvoiceJourCurrentYear where custInvoiceJourCurrentYear.InvoiceDate >= str2Date(CurrentYearStart, 213)
&& custInvoiceJourCurrentYear.InvoiceDate <= str2Date(CurrentYearEnd, 213)
&& custInvoiceJourCurrentYear.InvoiceAccount == salesTable.InvoiceAccount
&& custInvoiceJourCurrentYear.InvoiceAmount > 0.00;
SalesActivityReportTmp.CurrentYearTotal = custInvoiceJourCurrentYear.InvoiceAmount;
// SSRS changes
--------------------
To add previous and current year in the report header/column header I added the below logic.
-> In the header/column "Expression" and added the below logic for previous year
= today.AddYears(-1).year & " Total Sales"
-> In the header/column "Expression" and added the below logic for current year
= today.Year & " YTD Total Sales"
-> In the same way you can add for next year as well.
= today.AddYears(+1).year & " Total Sales"
@Rahul
Ex: If you are in 2019 . Need to show 2018 and 2019
If we are in 2020. Need to show 2019 and 2020
To achieve this I just need to get previous year start, end dates and current year start and end dates.
I used the below code to get the result.
Variable declaration
-------------------------
int currentYear = year(systemDateGet());
int previousYear = currentYear - 1; // previous year
int nextYear = currentYear + 1; // Next year
// I have another way to get previous year and next year. But, this will match the exact date in the previous year and next year.
date prvyear = prevYr(systemDateGet());
date nextyear = nextYr(systemDateGet());
str CurrentYearStart = "01"+"/"+"01"+"/"+int2Str(currentYear);
str CurrentYearEnd = "12"+"/"+"31"+"/"+int2Str(currentYear);
str previousYearStart = "01"+"/"+"01"+"/"+int2Str(previousYear);
str previousYearEnd = "12"+"/"+"31"+"/"+int2Str(previousYear);
Coding
----------
//Invoiceamount previous year
select sum(InvoiceAmount) from custInvoiceJourPrvYear where custInvoiceJourPrvYear.InvoiceDate >= str2Date(previousYearStart, 213)
&& custInvoiceJourPrvYear.InvoiceDate <= str2Date(previousYearEnd, 213)
&& custInvoiceJourPrvYear.InvoiceAccount == salesTable.InvoiceAccount
&& custInvoiceJourPrvYear.InvoiceAmount > 0.00;
SalesActivityReportTmp.PreviousYearTotal = custInvoiceJourPrvYear.InvoiceAmount;
//Invoiceamount current year
select sum(InvoiceAmount) from custInvoiceJourCurrentYear where custInvoiceJourCurrentYear.InvoiceDate >= str2Date(CurrentYearStart, 213)
&& custInvoiceJourCurrentYear.InvoiceDate <= str2Date(CurrentYearEnd, 213)
&& custInvoiceJourCurrentYear.InvoiceAccount == salesTable.InvoiceAccount
&& custInvoiceJourCurrentYear.InvoiceAmount > 0.00;
SalesActivityReportTmp.CurrentYearTotal = custInvoiceJourCurrentYear.InvoiceAmount;
// SSRS changes
--------------------
To add previous and current year in the report header/column header I added the below logic.
-> In the header/column "Expression" and added the below logic for previous year
= today.AddYears(-1).year & " Total Sales"
-> In the header/column "Expression" and added the below logic for current year
= today.Year & " YTD Total Sales"
-> In the same way you can add for next year as well.
= today.AddYears(+1).year & " Total Sales"
@Rahul
Chiba Capital Financial Group
ReplyDelete