Skip to Content
J I

Add a specific value to the largest value in a group

Hi,

We want to add a formula to our crystal report where in a group if a specific value exists then add that amount to the largest amount in that group.

As in below example our invoice group by the vehicle registration number.

In second group we want to add the Administration Fee to the Largest Fee in that group, like I want to sum both administration fee (20 + 20) to the largest amount (285) and also hide both administration fees.

Can some one help me to achieve this?

I shall be thankful to you.

capture.png (15.7 kB)
Add comment
10|10000 characters needed characters exceeded

  • So in the above example you're trying to add 40 to 285 and show the subtotal as 325 or where do you need to show that value? also could you share more about the report structure/sections?


    This might be achievable using a running total or a variable to sum on specific conditions.

  • Hi Ashraf,

    Yes I want to show sub total as 325.

    Below find the report structure.

    So my Details section is the group section.

    I tried different ways but none of them working:

    shared numberVar x; 
    if ({T_AC_INV_ITEMS_PRINT.F_ITM_NARR_7} = 'Adminstration Fee') then jib = {T_AC_INVOICE_ITEMS.F_ITM_NET_TOTAL} + MAXIMUM({T_AC_INVOICE_ITEMS.F_ITM_NET_TOTAL},{T_AC_INV_ITEMS_PRINT.F_ITM_NARR_3}) else x =({T_AC_INVOICE_ITEMS.F_ITM_NET_TOTAL})

    This might be achievable using a running total or a variable to sum on specific conditions?

    Can you please help me to achieve this?

    Thanks in advance

    Regards

    J

  • Since everything comes from the net field, try a running total(SUM) that resets on each group and evaluates records when net = maximum(net) OR Type = Administration Fee.

  • Get RSS Feed

2 Answers

  • Best Answer
    Jul 16, 2018 at 08:28 AM

    Hi,

    Here's what you need to do:

    1. Create a formula field called "AdminFee" with this code and place it on the "Details" section:

    If {Table.Action} = 'Administration Fee' then {Table.Net}; 

    2. Right-click this Formula Field > Format Field > Common tab > Check "Suppress".

    3. Create a formula called @Net and replace the Original "Net" field with this formula field on "Details" section:

    shared numbervar net; 
    shared numbervar gnet; 
    If {Table.Net} = Maximum({Table.Net}, {Vehicle Reg Number field}) then 
    net := {Table.Net} + Sum({@AdminFee}, {Vehicle Reg Number field}) 
    Else 
    net := {Table.Net}; 
    gnet := gnet + net; 
    net; 
    
    //Replace {Table.Net} and {Vehicle Reg Number field} with the correct database fields for Net and Vehicle Regn Number.

    4. Create a formula called @Vat and replace the Original "Vat" field with this formula field:

    shared numbervar net; 
    net * 0.2; 

    5. Create a formula to Reset the variables and place this on the Group Header:

    shared numbervar net := 0; 
    shared numbervar gnet := 0; 

    6. Create a formula to Display the subtotal for "Net" and place this on the Group Footer:

    shared numbervar gnet; 

    7. Create a formula to Display the subtotal for "Vat" and place this on the Group Footer beside "Net":

    shared numbervar gnet; 
    gnet * 0.2;

    Hope this helps.

    -Abhilash

    Add comment
    10|10000 characters needed characters exceeded

    • I realized there are some unnecessary formulas in my previous reply.

      1. Modify the @Net formula to:

      shared numbervar net;
      If {Table.Net} = Maximum({Table.Net},{Vehicle Reg Numberfield}) then 
      net := {Table.Net} + Sum({@AdminFee},{Vehicle Reg Numberfield}) Else 
      net := {Table.Net};

      2. Remove the formula fields from the Group Footer. Instead, use the original summaries you had on the footer.

      Simply insert a summary for Net and a summary for VAT.

      3. Go to the Section Expert > Highlight the Details section > click the formula button beside "Suppress" and use this code:

      {Table.Action} = 'Administration Fee'

      -Abhilash

  • Jul 16, 2018 at 08:11 AM

    Conditionally suppress the Admin fees and add a sort to your data so that Admin fees are at the top of each day

    Create 2 formula

    @reset //Place this in your date group header, header can be suppressed

    Whileprintingrecords;

    Global numbervar admin:=0;

    @display// use your relevant fields in formula place this in position of current net value

    Whileprintingrecords;

    Global numbervar admin;

    If Action = Admin fee then Admin:=Admin+netamount;

    If NetValue = Max(NetValue, DateGroup, "daily") then NetValue+admin else netvalue

    Your Group total should continue to work as even though Admin fees are suppressed they will be included in sub and report totals

    Repeat process for VAT, you can use same reset formula to zero the Vatadmin variable.

    Ian

    Add comment
    10|10000 characters needed characters exceeded