TODATE function's possible error

I have observed todate function error couple of days back situation is like this.

One of our report consist dashboard prompt on Quarter and other columns, default quarter is set to current quarter using repository variable, report does not have any date dimension columns to display, we have one measure in this report as QTD_Amount related column in RPD defined as “ifnull(ToDate(sum(ifnull(Order_Total , 0)), [Level Quarter]) , 0)” in this situation report works as expected.

Even if I chose some other quarter using Dropdown works fine or Dash Board prompt changed to Multi Select control on Quarter and choosing “All Choices” works fine, but when I select “all choices” in dropdown then it gives following error.

Odbc driver returned an error (SQLExecDirectW).

Error Details Error Codes: OPR4ONWY:U9IM8TAC:OI2DL65P

State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 22036] Level in TODATE function (Quarter) must be a rollup of (or equivalent to) the measure level (Year). (HY000)

Report expects quarter column( level) or in other terms sql-group by expects quarter. To overcome this error we need to add level column of todate to report and hide that column if you don’t want to display.

Hope it helped.