Mixing fields and calculated totals in ad hoc editor

[stan_3]

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...

 

[elizam]

I think you want to look at crosstabs.  That is what allows you to mix totals and groups.  You can switch from Table to Crosstab using a menu on the toolbar for the Ad Hoc view.

[stan_3]

YEs, that does exactly what I was looking for. 

 

Thanks, much appreciated.