on 04-24-2014 4:17 AM
Hi All,
I am creating a report with the below fields.
Account number | Amount | Amount With Deposit | Total Amount |
---|---|---|---|
123456 | $10300 | $0 | $10300 |
987654 | $9000 | $200 | $9200 |
Account Name: Is the name of the company
Amount: Total Amount - Amount With deposit
Amount With Deposit: This one i need help as i not sure how to get the coding. Will explain below.
Total Amount: will be the total amount store in the system.
in my Databse:
ID | Plan ID | Invoice | Invoice Date | Desc | Amount |
---|---|---|---|---|---|
123456 | 5678 | 99990 | 21/02/2014 | Deposit 20% | $1000 |
123456 | 5678 | 99991 | 23/02/2014 | Miscellaneous Charges | $200 |
123456 | 5678 | 99999 | 28/02/2014 | Other Charges | $9000 |
123456 | 5678 | 99996 | 05/03/2014 | Final Invoicing | $100 |
987654 | 9898 | 54321 | 15/01/2014 | Deposit 20% | $200 |
987654 | 9898 | 54321 | 20/01/2014 | Miscellaneous Charges | $1000 |
987654 | 9898 | 54321 | 22/01/2014 | Other Charges | $8000 |
My question:
In my database i will check for "Desc" like "Deposit".
"Amount With Deposit" cloumn report will only capture the amount which "Desc" like "Deposit" and with the same ID number without "Desc" like "Final Invoicing".
For example: ID ="123456" there are "Desc" with "Final Invocing", the amount $1000 will not show in "Amount With Deposit" column.
Likewise, for ID = "987654" there isnt any "Desc" with "Final Invocing" then the deposit amount of $200 will be shown in "Amount With Deposit" column.
Any one can advise me how can I write this in my report?
I need this solution urgently.
Thanks.
Regards,
Mavis
Hi Mavis,
Here's what you need to do:
1) Create a Group on the 'ID' field
2) Create a formula (@FInv) with this code:
If {Desc} LIKE "Final Invoicing%" then "x"
3) Create another formula (@Dep) with this code:
If {Desc} LIKE "Deposit%" then {Amount}
4) Create another formula called 'Amount_with_deposit' and place this formula on the Group Header/Footer under the 'Amount with Deposit' column:
If Maximum({@FInv}, {Group_Field}) = "x" then
0
else
Maximum({@Dep}, {Group_Field})
Replace {Group_Field} with the field that is used as the group which I guess is the ID field.
-Abhilash
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hi All,
I still unable get what i need for the above query.
Really need help one this or not i can't complete this report.
Can anyone help me pls?
Thanks.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yes, remove all the formulae I suggested above from the report sections.
Drag and drop the fields used in those formulae like:
{ER201_PAYMENT_PLAN_HDR.ER201_DESC},
{ER201_PAYMENT_PLAN_HDR.ER201_INVOICE_DATE},
{ER201_PAYMENT_PLAN_HDR.ER201_AMT_TOTAL},
on the details sections.
Do you see rows duplicating?
-Abhilash
Hi Abihilash,
I have one more question which is I want to have a total amount of OrderTotalBeforeTax - AmyWithDeposit in the "Invoice Amt (w/o inv on Advance Rental)Before Tax"
I have created the below forumale
{OrderAmountBeforeTax.OrderTotalBeforeTax} - {@AmtWithDeposit}
Then i noticed that i cannot Sum this amount in the group.
Can you advice?
Should i open a new request?
looks like Abhilash beat me to the reply
hi Mavis,
1) syntax for the @AmountWithDeposit formula would be something like
if instr({desc field}, 'Deposit') > 0 then {amount field}
2) syntax for the @Amount formula would be something like
if instr({desc field}, 'Deposit') = 0 then {amount field}
4) place both formulas on the details section
5) now create a group on account number and hide the details section
6) right click on each formula and choose Insert > Summary and leave as type Sum, but change the Summary location so that it is on the Group
7) now you can suppress the Group Footer and move the summaries up to the group header
cheers,
jamie
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
80 | |
24 | |
11 | |
9 | |
7 | |
6 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.