Group by function when query includes an embedded Select statement
This is more of a SQL question than specific to BI Publisher. I am trying to summarize cost by month to use in a pivot table. I'm using the following query:
select "PROJECT"."NAME" as "PROJNAME",
"ACTIVITYEXPENSE"."EXPENSEITEM" as "EXPENSE",
(select to_char("UDFVALUE"."UDFDATE", 'YYYY-MM') as "MONTH"
from "PXRPTUSER"."UDFVALUE" "UDFVALUE"
where "ACTIVITYEXPENSE"."OBJECTID" = "UDFVALUE"."FOREIGNOBJECTID" ) as "EXPYYYYMM",
sum ("ACTIVITYEXPENSE"."PLANNEDCOST") as "PLANNED"
from "PXRPTUSER"."ACTIVITYEXPENSE" "ACTIVITYEXPENSE",
"PXRPTUSER"."PROJECT" "PROJECT"
where "PROJECT"."OBJECTID"="ACTIVITYEXPENSE"."PROJECTOBJECTID"
group by "PROJECT"."NAME", "ACTIVITYEXPENSE"."EXPENSEITEM", "?????"
The ????? at the end is where my question begins. Can I group by the "EXPYYYYMM" field created by the select to_char function?