cancel
Showing results for 
Search instead for 
Did you mean: 

Show Value only with condition

Former Member
0 Kudos

Hi All,

I am creating a report with the below fields.

Account numberAmountAmount With DepositTotal 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:

IDPlan IDInvoiceInvoice DateDescAmount
12345656789999021/02/2014Deposit 20%$1000
12345656789999123/02/2014Miscellaneous Charges$200
12345656789999928/02/2014Other Charges$9000
12345656789999605/03/2014Final Invoicing$100
98765498985432115/01/2014Deposit 20%$200
98765498985432120/01/2014Miscellaneous Charges$1000
98765498985432122/01/2014Other 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

Accepted Solutions (1)

Accepted Solutions (1)

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos


Hi Abhilash,

Thanks for your prompt assist.

Will try it out and get back to you.

Thanks again.

Former Member
0 Kudos

Hi Abhilash,

I tried your suggestion but the value show all 0.00.

Not sure what is the reason.

Thanks.

abhilash_kumar
Active Contributor
0 Kudos

Sorry, change the formula in Step 2 to:

If {Desc} LIKE "Final Invoicing*" then "x"


Also change the formula in Step 3 to:


If {Desc} LIKE "Deposit*" then {Amount}


-Abhilash

Former Member
0 Kudos

Hi Abihilash,

After change the "*" value did appear but for those which have Final Invocing it also appear in "Amount With Deposit".

abhilash_kumar
Active Contributor
0 Kudos

I have something similar working fine on my machine, so I'm not too sure why it doesn't work for you.

Could you attach the report with saved data for me to take a look at please?

-Abhilash

Former Member
0 Kudos

Hi Abihilash,

I got it.

Checked wrong column tot the value didnt appear.

Thanks for your help!!!!

Former Member
0 Kudos

Hi Abihilash,

I have one issue.

Crystal report does not allow me to sum up "'Amount_with_deposit'".

How can i sum up this field?

Thanks.

Former Member
0 Kudos

Hi Abihilash,

I need to sum the 'Amount_with_deposit' in the one of the Group header and Report Footer.

Thanks.

abhilash_kumar
Active Contributor
0 Kudos

Hi Mavis,

Please create a new discussion for this issue per SCN's rules.

-Abhilash

Former Member
0 Kudos

Hi Abihilash,

I noticed that when i add the Amount_with_Deposit into the report, my other field in the same row will be x 3.

Not sure what is the reason.

Answers (2)

Answers (2)

Former Member
0 Kudos

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.

abhilash_kumar
Active Contributor
0 Kudos

Hi Mavis,

Based on the dataset you provided, I have this working fine on my sample report.

What exactly is the issue on your report?

-Abhilash

Former Member
0 Kudos

Hi Abihilash,

You have adviced me to add the Dep and FinV formulae on the Details Section but after i added in, all my figure will either multiple by double or triple.

Have sent you the sample report after i add the Dep and FinV.

I am not sure why too.

abhilash_kumar
Active Contributor
0 Kudos

Try this:

1) Remove the formulae from the Details Sections

2) Drag and drop the database fields used in the formulae on the Details Section. For e.g: The Desc field, the Invoice Date field etc.

Do the rows multiply? If yes, then there is something wrong with the Joins.

-Abhilash

Former Member
0 Kudos

Hi Abihilash,

1. Which formulae do you mean? Dep and Finv?

2. Which formulae are you refering to?

in my Details section there are Invoice field, Amt field and etc.

Thanks.

abhilash_kumar
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Abihilash,

I have removed the formulae and add in the fields used in those formulae in the detail sections.

The rows still duplicating.

I tried to change the join of the database expert but the result is the same as well.

Will you be able to advice?

Former Member
0 Kudos

Hi Abihilash,

I finally manage to get it.

I rejoin my Pay_plan table to Invoice table instead.

Now the figure does not duplicate.

Thanks for your help.,

Former Member
0 Kudos

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?

abhilash_kumar
Active Contributor
0 Kudos

Please mark one of the replies in this thread as Answered and create a new Discussion for the new issue per SCN rules.

-Abhilash

Former Member
0 Kudos

Noted with thanks.

I have done so.

JWiseman
Active Contributor
0 Kudos

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

Former Member
0 Kudos

Hi Jamie,

Thanks for your advise.

I am currently trying Abhilash's suggestion.