Section Total

[Former Member]

Is there a way to create a Total for a Section?

Let's say this is the original table:
Year__Fruits__Costs
2011__Apples__$10
2011__Oranges__$10
2012__Apples__$15
2012__Oranges__$15

This is what happens when I set the Year as the section:
2011
Fruits__Costs
Apples__$10
Oranges__$10

2011
Fruits__Costs
Apples__$15
Oranges__$15

What I want to include as part of the Years section is a Grand Totals like so:
2011
Fruits__Costs
Apples__$10
Oranges__$10

2011
Fruits__Costs
Apples__$15
Oranges__$15

Grand Total
Fruits__Costs
Apples__$25
Oranges__$25

And when I look at the Section tab, I want it to show each section accordingly:
-2011
-2012
-Grand Total

The closest I can get it is to have 2 tables as section with Year being sectioned and another separate table acting as a Grand Total section.  However, when I look at the Sections tab, it's splits up both tables into 2 parent sections.  So for example, if I was in the 2012 section, I would need to back out to the parent view, then select the section that has Grand Total.  Here's the steps for this example:  2012 -> Sections-> section B -> Grand Total.  Ideally 2011, 2012, and Grand Total would be on the same level in the Sections tab.

section A->

2011

2012

section B ->

Total

I would want to see:

2011

2012

Grand Total

Is this achievable?  My goal for the sections tab is allowing users to select a critiera whether it be Year, Month, Vehicles, etc and see the indivdual breakdown for that critiera but also be able to select and see the Grand Total for that critiera.

[Former Member]

Hi,

An alternative way is to combine your query with a "Grand Total" query in your data source.

You need to design an object with a fake value for Grand Total, e.g. object [Grand Total] with "Grand Total" value.

You combine the queries having:

[Year], etc...

UNION

[Grand Total], etc...

The 2nd query should sum data for all years.

In the report you will see the "Grand Total" as a year.