I am in the process of evaluating jasperserver pro. The software is currently installed on a mac laptop and accessing a Postgres database within my corporate network.
I have created a number of domains and ad hoc views using data from one of my data sources.
I am exploring the ad hoc editor. am having difficulty understanding how to create calculated measures such as row totals for groups within a domain with multiple tables and their joins. I did not come across an example of a calculated measure using the countAll funcrtion.
Here's simple description of what I would like to understand better:
Table A ... LEFT JOIN Table B.....LEFT JOIN Table C
I would like to produce a tabular report that looks like this:
A.Field1, A.Field2, <Total number of B.Field3>, <Total number of C.Field4>
I tried creating 2 calculated measures - one for countAll(B.PrimaryKey) and one for countAll(C.PrimaryKey). I have tried a variety of the requisite levels. However, when I drag these fields onto the row scrtion, I get a row count of 1 for every row of ABC, rather than the totals of B and/or C for every A.
I notice that, when the gear is set to Details, the generated SQL is pretty straightforward. When I select Totals, I see select .... sum() group by etc.
Please advise.
Thanks...
