Using Syntax to Filter Dates in a Report

This article shows users how to use different syntaxes to filter a date in a Report.

Week Syntax

You can use the Week syntax to derive Week to Date or combination for your reporting. The command must be used with a Date Field.

1. This formula shows the Start of the Week to Now or the Current Date – One Day. The first part is the WeekStart and the second part is the Date. The colon separates the two formulas. 
   ‘{PeriodCalc.Now.WeekStart.AddMonths(0).AddDays(0).SqlDate}’:'{PeriodCalc.Now.AddMonths(0).AddDays(-1).SqlDate}’
   
2. Last Week to Date Minus 7 days from both sides of the formula.
   ‘{PeriodCalc.Now.WeekStart.AddMonths(0).AddDays(-7).SqlDate}’:'{PeriodCalc.Now.AddMonths(0).AddDays(-7).SqlDate}’
   
3. Last Year Current Week Minus 1 year from both sides of the formula.   
   ‘{PeriodCalc.Now.WeekStart.AddYears(-1).AddMonths(0).AddDays(0).SqlDate}’:'{PeriodCalc.Now.AddYears(-1).AddMonths(0).AddDays(-1).SqlDate}’

Month Syntax

You can use the Month syntax to derive Month to Date or combination for your reporting. The command must be used with a Date Field.

1. This formula shows the Month Start to Now or the Current Date – One Day. The first part is the MonthStart and the second part is the Date. The colon separates the two formulas.  
   ‘{PeriodCalc.Now.MonthStart.AddMonths(0).SqlDate}’:'{PeriodCalc.Now.AddMonths(-1).AddDays(-1).SqlDate}’
   
2. Last Month to Date Minus 1 Month from both sides of the formula.
   ‘{PeriodCalc.Now.MonthStart.AddMonths(-1).SqlDate}’:'{PeriodCalc.Now.AddMonths(-1).AddDays(-1).SqlDate}’
   
3. Last Year Minus 1 year from both sides of the formula.   
   ‘{PeriodCalc.Now.MonthStart.AddYears(-1).AddMonths(0).AddDays(0).SqlDate}’:'{PeriodCalc.Now.AddYears(-1).AddMonths(0).AddDays(-1).SqlDate}’

Year to Date Syntax

Here is the process to make a Period Syntax for two years prior YTD. Period Functions only have the prior year YTD so a YTD Prior Two Years must be created.

1. This formula shows the year to date minus two years. The (Period) is the name of the Parameter and (-2) is the number of years you want to go back. The first part is the YearStart and the second part is the Period. A colon separates the two formulas
   {PeriodCalc.GetPeriod(Period).AddYears(-2).YearStart}:{PeriodCalc.GetPeriod(Period).AddYears(-2)}