Need an SQL query with YEAR and MONTH functions in SAP B1 using Service Layer

I'm having issues trying to apply a query in SAP B1 (HANA) through the service layer, so that it can be read from Power BI or Excel. The original query involves grouping costs by equipment, year, and month. Here is the SQL query:

SELECT
    "OcrCode4" as "Equipo",
    YEAR(t0."DocDate") as "Anio",
    MONTH(t0."DocDate") as "Mes",   
    SUM("DocTotal") as "Total" 
FROM OPCH t0 
INNER JOIN PCH1 t10 ON t0."DocEntry" = t10."DocEntry" 
WHERE t0."CANCELED" = 'N' and "OcrCode4">'A0000000' 
GROUP BY "OcrCode4",
    YEAR(t0."DocDate"),
    MONTH(t0."DocDate") 
ORDER BY "OcrCode4",
    "Anio",
    "Mes"

However, Service Layer does not support the YEAR and MONTH functions, so I attempted to modify the query and create it in service layer as follows:

    POST https://**:50003/b1s/v1/SQLQueries
    
    {
      "SqlCode": "sql21",
      "SqlName": "QueryOnItem21",
      "SqlText": "SELECT t10.OcrCode4 as equipo, t0.DocDate as fecha, SUM(t0.DocTotal) as total FROM OPCH t0 INNER JOIN PCH1 t10 ON t0.DocEntry = t10.DocEntry WHERE t0.CANCELED = 'N' AND t10.OcrCode4 > 'A0000000' GROUP BY t10.OcrCode4 ORDER BY t10.OcrCode4, t0.DocDate"
    }

I intended to use the following URL to aggregate the results:

https://**:50003/b1s/v1/SQLQueries('sql21')/List?$apply=groupby((year(fecha), month(fecha)), aggregate(total with sum as TotalSales))

However, this approach is not working as expected, and I'm unable to use the YEAR or MONTH functions in the $apply clause. I am aware that creating custom views, importing them as models in SAP, etc., is a possible solution, but I am curious if there's an alternative approach. It seems unusual that such a common requirement cannot be achieved more straightforwardly.

If anyone has experience or insights into resolving this issue, I would greatly appreciate your assistance. Thank you!