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

    Unanswered: Urgent : Sub Report Calculations - A Bit Critical For Me

    Hi Gents,

    I know this is a bit lengthy and I might not be capable to explain here clearly but I will try my best to put in detail to make you understand my situation.

    I am trying to calculate some fields from the report. My Main report contains 2 sub reports they are as follows:

    QR_TotalCashCashSales (query based Main report)

    R_TotalMaint (query based Sub report 1)
    R_SalesRet (query based Sub report 2)

    Sum of main reports at footer is not a problem as usual. The main thing I am looking for is to add sum of calculated fields that coming from sub-report to main report’s field.

    The detail section of the main report having some data bound fields. The field called SumOfAmount is providing amount of the line in detail section. At the report footer I place a unbound text box named GrandSales in which I set as =Sum([SumOfAmount]) to have final total. This is ok.

    Let me put the story of my sub reports from which I need to get some calculations.

    Sub-report R_SalesRet contains following bound data fields in detail section.

    SalesRetAmt, ReturnQty, AvgPurPrice.

    I have placed some unbound text boxes in this sub report for further calculations.

    First Unbound Text Box:

    To get running sum (if there will be more than one record), I placed one unbound textbox named TxtLostProfit in which I placed following expression.

    =([SalesRetAmt])-([ReturnQty]*[AvgPurPrice]). Here the Running Sum is set to No.

    Second Unbound Text Box:

    Another text box I placed named TxtNetLostProfit and set its Running Sum to “Over All” which is giving me desired result. And this is also I place in detail section of the sub report called R_SalesRet so when records are more than one, it displays cumulative sum of the expression.

    Third Unbound Text Box:

    This last unbound text box called Text68 I placed at footer of this sub report in which it displays last cumulative figure (=TxtNetLostProfit)

    Now my question is: How I can add this cumulative figure of Text68 of sub report to main report’s text box.

    Please note that 2 sub reports I placed in Report Footer of my main report.


    What I played around to get the desired result is like this.

    In one unbound text box called TxtGrandSales I need put expression as follows

    =[GrandSales]. This is ok.

    Another one last unbound text box I placed in main report footer called Text75 which holds following expression because my planning is to add cumulative result of in Text75 with GrandSales of main report so it became =[GrandSales]+[Text75]

    The text box Text75 contains this.

    =IIf(R_TotalSalesRet.Report.HasData=True, R_TotalSalesRet.Report.Text68),0)

    But this displays only value of first record (Not cumulative that I want).

    Let us say if first records result value is 10 and second records result value is 13 so it should count 23 as cumulative in Text75 but it presents 10 only.

    So thought I should make sum of its previous field TxtNetLostProfit from detail section of sub report and then set as follows.

    =IIf(R_TotalSalesRet.Report.HasData=True,Sum(R_Tot alSalesRet.Report.TxtNetLostProfit),0)

    But no use….

    Can somebody among you help me?
    With kind regards,
    Ashfaque

  2. #2
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Uh .. No.

    You cannot (as far as I know) propagate sub-report calculations to a main report ...

    But there is another way ...
    Back to Access ... ADO is not the way to go for speed ...

  3. #3
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Totals using sub reports

    You can show the total on the main report.

    As an example
    GrandSales is the cumulative total on the main report.
    main_report is the name of your main report
    sub_report_1 is the name of first sub report
    sub_report_2 is the name of your second sub report
    sub_report_1_total is the final total on sub report 1
    sub_report_2_total is the final total on sub report 2
    You will need to change these names to your report field names

    In your unbound box for the total put the following

    =[GrandSales]+Reports!main_report!sub_report_1!sub_report_1_tot al+Reports!main_report!sub_report_2!sub_report_2_t otal

    When using sub reports you must show the reference to the main report.

    HTH

  4. #4
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by Poppa Smurf
    You can show the total on the main report.

    As an example
    GrandSales is the cumulative total on the main report.
    main_report is the name of your main report
    sub_report_1 is the name of first sub report
    sub_report_2 is the name of your second sub report
    sub_report_1_total is the final total on sub report 1
    sub_report_2_total is the final total on sub report 2
    You will need to change these names to your report field names

    In your unbound box for the total put the following

    =[GrandSales]+Reports!main_report!sub_report_1!sub_report_1_tot al+Reports!main_report!sub_report_2!sub_report_2_t otal

    When using sub reports you must show the reference to the main report.

    HTH
    Nifty. I didn't think of it like that ... Good one.
    Back to Access ... ADO is not the way to go for speed ...

  5. #5
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Thank Poppa,

    I will implement your method and let you know soon.

    Thanks again for the prompt reply.
    With kind regards,
    Ashfaque

  6. #6
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Sub Report

    Thank you.

    I use the same format but have a main form with no data in the details section,but with three sub reports in the report footer and the final totals are shown in the footer of the main form. Each sub report has their own totals shown in the respective sub report footer.

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

    Here is the pic I attached....still same..
    Attached Thumbnails Attached Thumbnails Sub Rpt Cal.JPG  
    With kind regards,
    Ashfaque

  8. #8
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Could you please post a copy of your database with no confidential data etc. I will use your last post as a guide to help you solve your problem.

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Quote Originally Posted by M Owen
    Nifty. I didn't think of it like that ... Good one.
    Welllllll ... I might have been premature ...

    How I do these types of reports is to add a series of columns to the query/table the report runs on which holds the values to be calculated on for the grand totals with a grouping column for subtotal calcs ...
    Back to Access ... ADO is not the way to go for speed ...

  10. #10
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Sub Report Calculations

    Here is a very basic database that totals the staffing for three work areas with the total displayed on the main report.

    In the real world I would have a number of queries to give me the source data for each sub report etc. and there would be more than one row of data etc.

    rpt_staffing is the main report.
    Attached Files Attached Files

  11. #11
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Sorry for my late reply as I was out of office.

    Thanks Poppa for your help.

    Here is my db I attached to examine.

    Please have a look and let me know the best way to deal with the problem.

    Thanks again.
    Attached Files Attached Files
    Last edited by Ashfaque; 06-30-07 at 10:42.
    With kind regards,
    Ashfaque

  12. #12
    Join Date
    Jan 2005
    Location
    Nanded, India
    Posts
    397
    Poppa, your method is not working with my db I tried. I think because of RunningSum. Can you take a look at my test db?
    With kind regards,
    Ashfaque

  13. #13
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388

    Sub Report Calculations

    Ashfaque

    I have made some changes to your database. Here is a copy of test_01

    1. Removed the running totals
    2. Have a good look at the report R_TotalSalesRet and query QR_TotalSalesRet. In the query I have added another column called net_profit and used it for the control source for TxtNetLostProfit. Text68 control source =sum([net_profit]) which is the calculated field in the query.
    3. I changed the formula for your Net Profit to be =([GrandMaint]+[TxtTotProfit])-R_TotalSalesRet.Report!Text68. You were missing R_TotalSalesRet.Report!Text68.

    Let me know if any of the calculations are incorrect
    Attached Files Attached Files

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

    Excellent ! ! !

    This is what I was looking for...

    I know now instead of calculating on report itself, a bettter idea is to calculate it in query itself so we could get desired result.

    Thank you very much for your time.
    With kind regards,
    Ashfaque

  15. #15
    Join Date
    Jun 2007
    Location
    Maitland NSW,Australia
    Posts
    388
    Your Welcome.

    Contact me if your require more assistance etc.

Posting Permissions

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