Is it possible if a report only have subtotal & grand total?

If you have questions or if you want to share your opinion about Aware IM post your message on this forum
Post Reply
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Is it possible if a report only have subtotal & grand total?

Post by cishpix »

Hi everyone,
I have a query that display some data (depend date, example from 1 March 2019 - 31 March 2019) and the query is group by material name and I want to design a report that only display subtotal of every material name and grand total.

Is it possible in AwareIM report?


Thanks
Regards,

Suwandy
-----------------
Kisaran - Indonesia
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Is it possible if a report only have subtotal & grand to

Post by tford »

Might be easiest to have a BO that maintains totals by Material Name via rules & easily display those totals on reports & queries.
Tom - V8.8 build 3137 - MySql / PostGres
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Re: Is it possible if a report only have subtotal & grand to

Post by cishpix »

tford wrote:Might be easiest to have a BO that maintains totals by Material Name via rules & easily display those totals on reports & queries.
Okay Tom, thanks for your respond.
I shall try follow your advice
Regards,

Suwandy
-----------------
Kisaran - Indonesia
kklosson
Posts: 1617
Joined: Sun Nov 23, 2008 3:19 pm
Location: Virginia

Re: Is it possible if a report only have subtotal & grand to

Post by kklosson »

Going from memory but I think I once accomplished that by setting the elements inside the details band to zero height.
V8.8
MySQL, AWS EC2, S3
PDFtk Toolkit
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Is it possible if a report only have subtotal & grand to

Post by Jaymer »

cishpix wrote:Hi everyone,
I have a query that display some data (depend date, example from 1 March 2019 - 31 March 2019) and the query is group by material name and I want to design a report that only display subtotal of every material name and grand total.

Is it possible in AwareIM report?


Thanks
Sure that HAS to be possible.
... or the report writer isn’t worth a crap.

You shouldn’t need to display a detail section. So you’re only going to show the group footer.
For testing, design it WITH the detail showing..once sub totals are verified, you can drop the detail band.
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Re: Is it possible if a report only have subtotal & grand to

Post by cishpix »

kklosson wrote:setting the elements inside the details band to zero height
You're right, I have follow your advice and it works. Thanks for you
Jaymer wrote:You shouldn’t need to display a detail section. So you’re only going to show the group footer.
Thank you Jaymer, your idea have sort out my problem but the "grand total" position always near end of the page although I have set it in "Column footer" section and the subtotal only 3 item/records.

Do you have any idea how to set the grand total NOT near end of the page?
Thank you.
Regards,

Suwandy
-----------------
Kisaran - Indonesia
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Is it possible if a report only have subtotal & grand to

Post by Jaymer »

you can see from the pics that this is achievable

but when you put your GRAND TOTALS in the column or page footer, they go to the bottom (as you said).

I have created a "ghost" section that doesn't really do anything except acts as a placeholder for GRAND TOTALS and it is "outside" the loop of each vendor. And has no grouping defined. This makes it display when the inner group (Vendor) is finished and thus the TOTALS appear in the proper place. (Mine was named "Section")

Since you have already created your group, you'll need to:
1) Click on Page Header band
2) Make a new Group, lets name it "Totals"
3) To be safe, click on Totals Header, then Make ANOTHER Material group. Now its "inside" Totals.
4) Copy/Paste the fields from existing Material Group to the new Material Group
5) Delete the OLD Material Group (From the Tree on the left.)
6) You'll need to redefine the group by property (In the SUM field) - I think it will throw an error if you try to save before doing this
7) Make sure you set the Totals Header and Detail Bands HEIGHTs TO ZERO (they are only set to 1 by dragging)

jaymer...
Attachments
Screen Shot 2019-04-12 at 10.11.13 AM.png
Screen Shot 2019-04-12 at 10.11.13 AM.png (47 KiB) Viewed 13478 times
Screen Shot 2019-04-12 at 10.09.58 AM.png
Screen Shot 2019-04-12 at 10.09.58 AM.png (62.93 KiB) Viewed 13478 times
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
tford
Posts: 4238
Joined: Sat Mar 10, 2007 6:44 pm

Re: Is it possible if a report only have subtotal & grand to

Post by tford »

By the way, I wasn't trying to imply this couldn't be done in the report writer. Just saying that in the apps I have written where users needed to see totals for a product / region / etc, I've found that users often needed a way to see those on the screen as well so the approach I described earlier set the stage for both screen use & report use.
Tom - V8.8 build 3137 - MySql / PostGres
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Re: Is it possible if a report only have subtotal & grand to

Post by cishpix »

Jaymer wrote:I have created a "ghost" section that doesn't really do anything except acts as a placeholder for GRAND TOTALS and it is "outside" the loop of each vendor.
Many thanks for you and really appreciate for your help. This is the second time you sort out my problem again.

I'm still have other question but still related with grand total.
Ex, a material stock (we call it is bolt) have 2 measurement unit, box and pcs. Usually use box but sometimes it can have both measurement units.
So.. when I use SUM in grand total for pcs measurement unit, there is no any number if the total is 0 (there is no problem when the total is not 0). Do you still have other trick to solve this case?

UPDATE
I have try use "Attach Condition" but the condition cannot allow a condition like I use in rule or process (like the below)
SUM Material.stock WHERE (Material.MeasurementUnit='pcs')<>0

Thanks and Regards.
Regards,

Suwandy
-----------------
Kisaran - Indonesia
Jaymer
Posts: 2430
Joined: Tue Jan 13, 2015 10:58 am
Location: Tampa, FL
Contact:

Re: Is it possible if a report only have subtotal & grand to

Post by Jaymer »

cishpix wrote: Ex, a material stock (we call it is bolt) have 2 measurement unit, box and pcs. Usually use box but sometimes it can have both measurement units.
So.. when I use SUM in grand total for pcs measurement unit, there is no any number if the total is 0 (there is no problem when the total is not 0). Do you still have other trick to solve this case?

UPDATE
I have try use "Attach Condition" but the condition cannot allow a condition like I use in rule or process (like the below)
SUM Material.stock WHERE (Material.MeasurementUnit='pcs')<>0

Thanks and Regards.
I'm very happy to help - but LOGIC is very precise - your English is not very precise.
So its hard for me to understand EXACTLY what you want.

Is it possible for each Material to contain quantity for BOTH
Bulk Unit Count
Individual Count ?

--> "there is no any number if the total is 0"
Does that mean this: If the total is 0, the field displays blank?
So the issue is that instead of a blank, you want it to print "0" ?
(if this, then isn't it just a Formatting issue so that is does not suppress the 0 )

In each Material subtotal group, are you trying to print 2 numbers? :
Subtotal Box: xxx Subtotal Pcs: yyy
Click Here to see a collection of my tips & hacks on this forum. Or search for "JaymerTip" in the search bar at the top.

Jaymer
Aware Programming & Consulting - Tampa FL
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Re: Is it possible if a report only have subtotal & grand to

Post by cishpix »

Jaymer wrote:your English is not very precise.
So its hard for me to understand EXACTLY what you want.
Sorry and thanks for you, I know I need more practice my English again.
Jaymer wrote:Does that mean this: If the total is 0, the field displays blank?
So the issue is that instead of a blank, you want it to print "0"
(if this, then isn't it just a Formatting issue so that is does not suppress the 0 ) ?

In each Material subtotal group, are you trying to print 2 numbers? :
Subtotal Box: xxx Subtotal Pcs: yyy
Yes, that's what I want when the field displays blank so I want it can print "0". Is it possible?
Regards,

Suwandy
-----------------
Kisaran - Indonesia
cishpix
Posts: 183
Joined: Fri Nov 06, 2015 5:07 am
Location: Indonesia

Re: Is it possible if a report only have subtotal & grand to

Post by cishpix »

cishpix wrote: Yes, that's what I want when the field displays blank so I want it can print "0". Is it possible?
Looks like it's impossible, right?
Regards,

Suwandy
-----------------
Kisaran - Indonesia
Post Reply