Moving record column value to previous record column

[Former Member]

if ({ZAR_VRM30_Q007.Document Number}=previous({ZAR_VRM30_Q007.Document Number})) and
{@Gross billing_1} = 0 and {ZAR_VRM30_Q007.Net Billing} = 0 and {@Cash Receipts} = 0 and {ZAR_VRM30_Q007.Retainage based on Currency Type} <> 0
then

previous({ZAR_VRM30_Q007.Retainage based on Currency Type}) = {ZAR_VRM30_Q007.Retainage based on Currency Type}

Hi ,

I have a scenario where I have to move the current record value to previous record column.

As highlighted in the above screenshot, I'm comparing two records which have same billing doc no and see if the retainage is shown in second record.

If yes, see that second records gross billing, net billing values are 0, then move the retainage to previous record.. In this example: value 141,417.17 should be moved to the previous record leaving its value 0 in the second record.

I tried using if statement to satisfy the condition and its working but I'm not able to assign the retainage value to first record column. Here I'm getting only true and false values.

Please see the code below:

if ({ZAR_VRM30_Q007.Document Number}=previous({ZAR_VRM30_Q007.Document Number})) and
{@Gross billing_1} = 0 and {ZAR_VRM30_Q007.Net Billing} = 0 and {@Cash Receipts} = 0 and {ZAR_VRM30_Q007.Retainage based on Currency Type} <> 0

then

previous({ZAR_VRM30_Q007.Retainage based on Currency Type}) = {ZAR_VRM30_Q007.Retainage based on Currency Type}

Please let me know how to achieve this requirement.

Thanks for all the help!

John

[abhilash_kumar]

Hi John,

The formula outputs True or False because the last line of your formula is trying to compare two values and see if they're equal.

previous({ZAR_VRM30_Q007.Retainage based on Currency Type}) = {ZAR_VRM30_Q007.Retainage based on Currency Type}

will always output True or False.

Change the last line to just:

{ZAR_VRM30_Q007.Retainage based on Currency Type}

Hope this helps!

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Hi Abilash,

Your statement will not change any value. I want the previous row value to be changed.

Please advise.

Thanks,

John

[abhilash_kumar]

Hi John,

Could you please attach the report with saved data. I do not see the retainage column in the above screenshot and anyway it becomes easier with the report itself.

Before you attach the report, please change the extension to .txt.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Hi John,

Just a suggestion to add, it looks from the snapshot you want the Retainage col should give you a descending order data. If this is right then make be you can add a Record Sort in the report based on Retainage col.

So record sort will be in descending order.

- Bwuser

[Former Member]

Hi Abilash,

Please see the attached report in .txt format

As mentioned above, there are two records with same billing doc no.(9000000012), with the second record having no values for all key figures except for retainage. In that case we have to move the retainage to the previous record making its value 0 in the second record.

Thanks,

John

CrystalReport BWQ_ERQ210.txt.zip

[abhilash_kumar]

John,

The file looks corrupt.

Could you please re-attach it with saved data?

- Abhilash

[Former Member]

Please find the attached file with saved data.

Check the above scenario for Billing doc # 9000000603

Let me know if anything else is required.

Crystal report2.txt.zip

[abhilash_kumar]

Hi John,

This one is corrupt too!

What's your e-mail address? I'll send you an e-mail and then you can reply with the report attached,

- Abhilash

[Former Member]

jmosesbo@gmail.com

[Former Member]

Hi Abhilash,

Can you fwd your email id? I will send the text file.

Thanks,

John

[abhilash_kumar]

Hi John,

The report you sent to me does not have contain data, so I could not test this solution.

Could you see if this works:

1) Create this formula and place it on the details section:

if ({ZAR_VRM30_Q007.Document Number}=next({ZAR_VRM30_Q007.Document Number})) then
(
     if next({ZAR_VRM30_Q007.Retainage based on Currency Type}) > 0 OR           next({ZAR_VRM30_Q007.Retainage based on Currency Type}) < 0) then
     (
          if next({ZAR_VRM30_Q007.Net Billing}) = 0 then
          (  
          if (next({ZAR_VRM30_Q007.Document Type\Document Type - Key}) = 'IW' or                next({ZAR_VRM30_Q007.Document Type\Document Type - Key}) = 'LC') then
          (
              if next({ZAR_VRM30_Q007.Amount based on Currency Type}) = 0 then
              (
                    if next({ZAR_VRM30_Q007.Retainage based on Currency Type}) <> 0 then
                    (
                         next({ZAR_VRM30_Q007.Retainage based on Currency Type})
                    )
              ) 
          )
     )
)

2) Go to Section Expert > Select the Details section > click on the formula button beside Suppress and use this formula:

if ({ZAR_VRM30_Q007.Document Number}=previous({ZAR_VRM30_Q007.Document Number}) and {ZAR_VRM30_Q007.Retainage based on Currency Type} <> 0) then

true else false

Please test this and let me know if this works.

Otherwise, send me the report with saved data and I'll play around this get back to you.

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces

[Former Member]

Hi Abhilash,

The above formula 1 gives value 0 when its inserted into the report.

Could you pls check?

Thanks,

John

[abhilash_kumar]

Hi John,

Could you please send me the report with 'saved data' again?

I'll test this formula and let you know.

- Abhilash

[abhilash_kumar]

Hi John,

What version of Crystal Reports for Enterprise are you using?

I tested this report (on CR4E SP04) and it works fine. The formula shows the correct retainage values on the repeating records.

Why so many duplicate posts ?

- Abhilash

Follow us on Twitter

Got Enhancement ideas? Try the SAP Idea Place

Share Your Knowledge in SCN Topic Spaces