Hello there,

I have a report that looks at the balances of each GL Account by GL Year and Period.

What I'm struggling with is having the report look at current year and current period.. Currently it has a parameter where I enter the year and the period.. I want it to automatically look at current year and current period.


Table name is V_GLYEAR_PERIODS


The fields that I think could help are:

FISCALYR_START (date/time) <-- this one is ALWAYS January first of each year: 2019/01/01 00:00:00.00
FISCALYR_END (date/time) <-- this one is ALWAYS December 31st of each year: 2019/12/31 00:00:00.00



Now the Periods:

I have 3 fields, PERIOD, PERIOD_START_DATE, and PERIOD_END_DATE
I believe the field PERIOD is the easiest, since it ALWAYS has values from 1-12 (each number represents the month of the year).


Can someone please help me write the selection formula so I can get rid of the parameter and have the report automatically pull current year/period?



Thank you and best regards,
Bas

It keeps telling me "Bad Date Format string"
because the value that's in FISCALYR_START is 2019/01/01 00:00:00.00

so I tried to put it in a formula that would convert it to a date field, and it still doesn't work

I tried these:
DateValue({V_GLYEAR_PERIODS.FISCALYR_START})

cDate(ToText(cDate({V_GLYEAR_PERIODS.FISCALYR_START}),"yyyyMMdd"))

and they both still give me a "Bad date format string"


how can i convert this 2019/01/01 00:00:00.00 string to a date?

Here is what I did and worked:

Created a formula called it {@FiscalStart} and it contains:
stringvar MyDateStr:= {V_GLACCT_YBB_ALL_PERIODS_CUMM.GLYEAR_START_DATE};
datetimevar MyDate;
MyDate:= cdatetime(val(mid(MyDateStr,1,4)),val(mid(MyDateStr,6,2)),val(mid(MyDateStr,9,2))
,val(mid(MyDateStr,12,2)),val(mid(MyDateStr,15,2)),val(mid(MyDateStr,18,2)));
MyDate

then created formula called it {@FiscalStart2} and it contains:
date({@FiscalStart})


Then in the selection formula, I used the formula you provided previously:
(year(currentdate)=year({@FiscalStart 2})
and month(currentdate)={V_GLYEAR_PERIODS.PERIOD})



Thank you so much!!!