Adhoc reporting - Subtotal of grouped subtotal + grand total query

[david.thompson]

Hi,  we've got a fairly standard report built in Adhoc (table view)  that just lists a bunch of nominal codes with values, grouped by the nominal and the period.

The output is great, its exactly what is wanted and displays the nominal description (grouped) as a sub heading, with a column for the value and lines under the nom description showing the period (June, July, August etc) with that periods value to the right of it.   We are using 'Totals Data' and we get a grand total of all of these noms at the bottom.

The customer has come back to us and advised that the Noms used are actually 2 groups, and they want what we have already + a subtotal on both groups with a grand total at the end.

I've tried creating a calculated measure and a calculatedfield that we can use to groups the 2 sets of Noms together, but can't get it to work.  Is it possible in Adhoc to do this?

[Thomas Tibcken]

Please look at this link below - that might be the solution for you:

https://community.jaspersoft.com/questions/1211566/jasperreports-ad-hoc-get-totals-multiple-sessions

Regards TT

[david.thompson]

TT, I'd already used the grouping to get the data how we thought it was wanted, and it would be perfect if we didn't need more grouping on top of that. 

I can't post a pic of it atm, but this is a short version of our current output , which is grouped by Nominal description and Period Description (months as shown below).

 

*Nominal 1*                 - Fin Value

August Totals                        10.00

July Totals                             10.00

June Totals                             10.00

Nominal 1 Totals                   30.00

---

*Nominal 2*                 - Fin Value

August Totals                        10.00

July Totals                             20.00

June Totals                             10.00

Nominal 2 Totals                   40.00

---

*Nominal 3*                 - Fin Value

August Totals                        10.00

July Totals                             10.00

June Totals                             10.00

Nominal 3 Totals                   30.00

-----------

Grand Total                           100.00

The full report has 11 nominals in it, 5 are 'income' and 6 are 'expense' Noms, so the customer has asked for us to sub total the 2 groups and then grand total that (so in the example above it would be a group of Nom 1 and a group of Nom2 & 3). 

I've been told I can create a calculated field that does the grouping, but I've not been able to get it to work.  

 

[Thomas Tibcken]

Hi,

to create a virtual Field with IF-THEN-ELSE logic you can use this example with multiple IF conditions:

IF("Customer Country" == 'Germany', 'DE', IF("Customer Country" == 'France', 'FR', 'Other'))
 

This should let you create a Group with your Values and with Totals.

That should be what your customer is looking for. 

Please let me know if this will be the solution. 

THX and Regards 

TT