Question
I created an Ad-Hoc view by using one calculation measure with Average() function and one dimension using sample supermart domain.
When I observed the query generated I do not see an AVG function in the query.
Could you please let me know the steps to validate the function?
Explanation
Some of functions available in Calculated fields / measures are 'translated' and included into generated SQL while others are applied to returned dataset in memory.
Function Average() is the one applied in memory, that is why you do not see it in generated SQL.
You can debug the underlying behaviour by enabling a few debug parameters:
- Ad Hoc WorkingDataSet
- SQL query executer
- Groovy code generation for memory datasets (including Ad Hoc dynamic filters)
If you repeat the steps and review WEB-INFlogsjasperserver.log file, you can see something like (from my sample ad-hoc view, 'New Measure is calc measure used Absolute function'):
DomainDataStrategy,http-apr-8630-exec-10:449 - complex filter in query: ((public_orders.shipcountry in ('Canada', 'Austria', 'USA')) and (NewMeasure in (10251, 10253, 10254, 10275, 10276, 10297, 10310, 10313, 10317, 10326, 10330)))
2017-01-16 12:49:28,587 DEBUG CommonDomainDataStrategy,http-apr-8630-exec-10:489 -
2017-01-16 12:49:28,587 DEBUG CommonDomainDataStrategy,http-apr-8630-exec-10:490 - hybrid calc DomainFilter
2017-01-16 12:49:28,588 DEBUG CommonDomainDataStrategy,http-apr-8630-exec-10:498 - mem field public_orders.shipcountry: expr = shipcountry, type = java.lang.String
2017-01-16 12:49:28,588 DEBUG CommonDomainDataStrategy,http-apr-8630-exec-10:498 - mem field NewMeasure: expr = null, type = java.lang.Integer
2017-01-16 12:49:28,588 DEBUG CommonDomainDataStrategy,http-apr-8630-exec-10:510 - mem filter: ((shipcountry in ('Canada', 'Austria', 'USA')) and (Absolute(orderid) in (10251, 10253, 10254, 10275, 10276, 10297, 10310, 10313, 10317, 10326, 10330)))
......
Native query: select Sum("freight") as "Sum_freight",
"orderid",
"shipcountry",
"shipname"
from "public"."orders"
group by "orderid", "shipcountry", "shipname"
order by "orderid", "shipcountry", "shipname"
limit 200001
'Mem filter' means that filter applied in memory to result set returned by the query without WHERE clause..
Ref. Case 01460258
Recommended Comments
There are no comments to display.