The SUM function in the Datamart query below is not working as needed.
// Get datamart context def dmContext = api.getDatamartContext() // Create datamart query def dmQueryInvoice = dmContext.newQuery(dmContext.getDatamart("Transactions"), false) dmQueryInvoice. select("INVOICE_NUMBER"). select("ACCOUNT_NAME"). select("SUM(FINAL_GIVEN_NET_PRICE)"). where(Filter.equal("INVOICE_DATEYear","2018"), Filter.equal("BILLING_TYPE","F2")) |
You need to add a variable name to the SUM statement to get the sum value from the query result:
select("SUM(FINAL_GIVEN_NET_PRICE)","SUM_FINAL_GIVEN_NET_PRICE")
Also, you should add separate WHERE clauses for each filter.
.where(Filter.equal("INVOICE_DATEYear","2018"))
.where(Filter.equal("BILLING_TYPE","F2"))
Also make sure that all DM fields used in SELECT statements are marked as dimension (in their underlying Data Source).
The second parameter of the method newQuery​(Table table, boolean rollup)
is important - if its value is FALSE, then there will be no rollup/aggregation happening.
So when you set the rollup param to TRUE, the rollup will happen (i.e., the SUM function will work).