[Solved] Subtotaling in Group Footer

[nathan3553]

Attached database.
Want to update report to show subtotaling by Fund.
Can we insert a second query into the group footer or add a summary somehow?

Example output:
(Group Header)
FamilyID
(Detail)
10/1/2013 Fund1 $100
10/2/2013 Fund1 $50
10/3/2014 Fund2 $100
(Group Footer)
Fund1 $150
Fund2 $100
Total $250

[Sliderule]

You said / asked:

Can we insert a second query into the group footer or add a summary somehow?

No, you canNOT insert a second query.

Yes, to accomplish your task, you define a function, to 'calculate' what you need, in the Oracle Report Builder.

Please see the link below, with a demo database / report you can download, and, investigate as an example. This assumes, you are using the Oracle Report Builder extension, as your tool to create reports in Base.

viewtopic.php?f=42&t=70217&p=313938

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[nathan3553]

Your answer is close, but not really what i'm looking for...so if you look at the example database you posted and review Report: WoldAreaReport - By Area.

The data is like:
Country Continent Area Sq KM
Georgia Asia 69700

What I'm looking to do is then quantify in the same report footer:
Asia Subtotal
Africa Subtotal
Ideally I'd write a query like Select Continent, Sum(AreaSqKM) from Table group by continent. I didn't seem to find this particular scenario in the sample db.
And it seems my sample database did not upload.

[Sliderule]

I have no idea what you are talking about . . . therefore . . . I cannot help.

[nathan3553]

here is a copy of the database where I have attempted it in the Report. I think the report is self explanatory, but the numbers in the subtotals are wrong. That's what i'm attempting to get corrected.

[RPG]

Hello

Maybe this function works for you? I have only add amount once in the first function or
placed amount in front of the function.

Code: Select all

IF([FUND]="Donation";[AMOUNT]+[TO_SUBTOTAL];[AMOUNT]+IF([TO_SUBTOTAL]>0;[TO_SUBTOTAL];0))

[AMOUNT] + IF([FUND]="Donation";[TO_SUBTOTAL];IF([TO_SUBTOTAL]>0;[TO_SUBTOTAL];0))

Romke

[Sliderule]

Try the following attached database file . . . with a different Query, additional functions ( to calculates totals, once by GROUP, another for the report grand totals ), and, Report Name.

Contribution.odb

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[nathan3553]

I still seem to be having some issues. I see that you have it working in the sample.
In my actual database, I'm using a View to produce the report--I'd still suspect this to work as desired. I copied the functions you created but still get 0.00 for the group subtotals. Did you happen to change anything else?
For instance, I saw one discrepancy between mine and yours like: "Print Group When Change" = "Yes".

What specifically did you change in the query other than adding full table names, full field names, and moving the joins to the Where clause? -- how might this make an impact?--sorry if this is a stupid question, just wanting to understand.

I see that you used a Query and did an order by. However when I use a query with order by I receive an error message when running the report?--"Cannot be in ORDER BY clause...."

[Sliderule]

In the Query, I allowed the Analyze command ( Base Parser ). Other than that, and, NOT using alias names for the tables ( since the Base Parser does not like that ) . . . nothing different.

And, yes, the functions in the report . . . were double, that is, one set, to calculate the values at the group level ( for each unique group ) and another set, for the Total of the report.

Yes, also, I changed, and you might want to modify them, the IF statements ( both at the Group level, and, at the Total Report level ). That is, I could not really determine what you wanted to do differently, if anything, for NEGATIVE numbers.

One more comment . . . I elected NOT to change the way you have it displayed, in your Query . . . you could change the BOOLEAN field to a TEXT field . . . but . . . please keep in mind . . . there are THREE possible values for a BOOLEAN field, so, I coded, just in case ( word pun intended ) for each possible BOOLEAN return value:

1. TRUE
2. FALSE
3. NULL

So, in the Query, I would suggest something like this ( of course, change what is between the single quotes to whatever you prefer ):

Code: Select all

CASE WHEN "CONTRIBUTION"."CASH" = TRUE THEN '[X]' WHEN "CONTRIBUTION"."CASH" = FALSE THEN '[ ]' ELSE '[?]' END as "ALIAS_NAME"

I do NOT know why your Query is giving an error message, since, I cannot see it, but, yes, I am sure you figure it out.
Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[nathan3553]

Sliderule,
I want to say that I appreciate all your help. I have spent the night tinkering with it but I'm still not getting it. So I spent the rest of my evening cleaning up data so that I could post my database online. I've attached it here. Notice that the subtotaling of "TITHES AND OFFERINGS" doesn't seem to work in Giving Statement 2014.

I've also added the query with order by, where the report1 fails to open.(If I should open a second post about this topic I can, since it strays away from the original post).

[Sliderule]

I've also added the query with order by, where the report1 fails to open.(If I should open a second post about this topic I can, since it strays away from the original post).

I did not bother to look at that, since, I thought ( warning . . . Sliderule thinking, or, making it look like thinking can be dangerous to your health ) . . . if we could first 'agree' on the other report . . . that is, the values and report content returned . . . is what you need.

Contribution1.odb

These are the changes ( I think I have all listed ) I made between Report Giving Statement 2014 as you wrote it ( I did NOT change this at all ) and, Giving Statement 2014_Sliderule:

1. Added, because I could not help myself, a Grand Total Label field in the Report Footer, with a background colour, and, I gave the data function =[TOTAL_ALL] a format with currency, field separator.
2. I changed the Detail field . . . from . . . Cash . . . to . . . CASH . . . so it would pick up the asterisk for the boolean field. Note this is to match exactly, including CASE: UPPER, Mixed, lower for the field name in the View.
3. The problem / solution is easy, when someone looks at it from afar, and, yes, it is totally understandable. Your View, V_CONTRIBUTIONS_2014 , you named a few COLUMNS with Mixed Case, "Amount" and "Fund" . . . therefore . . . in the functions, you have to use those Mixed Case field/column names, rather than the UPPER CASE names I found. Please let me add, this is an understandable error, and, why I always like to keep database table and view COLUMN/FIELD names as UPPER CASE withOUT any spaces, for consistency.
4. I did have to delete the report: Report1 from the attached file, so, it would be small enough ( bytes ) to be included in this forum as an attachment.

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[nathan3553]

Thank you! The case changes made the difference!