Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Unanswered: How to display GroupTotal in Report Header?

    I know to display GroupTotal in footer....

    How to display the same GroupTotal in report Header other than using DSum function?

    I tried various ways like running sum in data tab etc..

    Any idea?
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Have you tried just doing a =sum([MyFieldToTotal]) in for the Control Source? If that doesn't work, you could try putting In your report footer on the Control Source =sum([MyFieldToTotal]) and put some name on that text box (i.e. TotalOfPrice) and then in the ReportHeader set the control source =([TotalOfPrice]). I usually use the dsum function in a form verses a report.
    Last edited by pkstormy; 06-09-07 at 12:11.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  3. #3
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thanks Paul,

    I tried this way at first. But it calculating twice and producing big amont figure. I even tried to keep Running Sum as Over All, Over Group and No option at data tab. But useless.

    With kind regards,
    Ashfaque
    With kind regards,
    Ashfaque

  4. #4
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Here is db to know what I did with...

    I neet sum of GrandTotal in OrdersDetail section of report at the top of the report. Although it is done using Dsum but need to know another method. This issue troubled me before also.

    With kind regards,
    Ashfaque
    Attached Files Attached Files
    With kind regards,
    Ashfaque

  5. #5
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ahhh... I see your situation now. I'll see if I can figure anything out.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ashfaque,

    Remove the Control Source for Text102 i.e. =DLookUp("[Department]","Departments") & " (Report Total: " & Format(DSum("GrandTotal","Orders",Report.Filter)," $#,###.00") & ")" in your Report Header and put this code in the OnFormat event for the Report Footer (make sure it's in the Report Footer and not the Report Header!):

    Reports!ItemInvoiceAll!Text102 = DLookup("[Department]", "Departments") & " (Report Total: " & Format(Reports!ItemInvoiceAll!Text143, "$#,###.00") & ")"

    Also, expand your Text102 box wider so you see the entire text when the report is viewed.
    Last edited by pkstormy; 06-10-07 at 03:06.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Ashfaque,

    I also noticed that if I click on the Record Source of the report, the query grabs all records. If I then close the Record Source query, the filter no longer applies and you get all the records (and the large GrandTotal value of $107,241.40). You might want to set criteria in the query (i.e. Orders.Deleted = 0 AND Orders.Department ='Production' AND Year(Orders.OrderDate) = '2007' AND Orders.RequestedBy = 'Bill Thompson) criteria is in the query itself.

    You could have a form where the user selects a Department, Year, and RequestedBy and set the criteria in the query equal to...
    For Department field....=Forms!MyFormName!FilterDepartmentField (where FilterDepartmentField is a combobox dropdown or textbox on the form)
    For OrderDate field....=Forms!MyFormName!FilterOrderDateYear (....)
    For RequestedBy field...=Forms!MyFormName!FilterRequestedByField (....)

    But I'm not sure if you're already doing that. I didn't see any forms in the sample you supplied, just the Filter in the Report property.
    Last edited by pkstormy; 06-10-07 at 13:43.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397

    Thumbs up

    Excellent !!!

    Thank you very much again Paul for your valuable help....

    Did not set the criteria it is just a sample db to place on forum. I wil apply your trick at real envornment...

    One again thanks for your help...

    With kind regards,
    Ashfaque
    With kind regards,
    Ashfaque

  9. #9
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    One question....from where you set Text143 ?
    With kind regards,
    Ashfaque

  10. #10
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Sorry, got it..
    With kind regards,
    Ashfaque

  11. #11
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Text143 was your total textbox in the footer.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Just goes to show that naming your textboxes with logical/meaningful names is just plain stoopid!
    George
    Home | Blog

  13. #13
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Yes - I fully agree Georgev!
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •