Negative as Positive Total? 1

[supportsvc]

Hello,
I have this issue when trying to multiply the Quantity Shipped to the Unit Price to get Extended Total.
Which is, it turns it into a positive total since it's multiplying negative by negative.
I can't think of how to change it so it results in the correct negative / positive total?

 

[SkipVought]

Why do you have negative quantities AND unit prices?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[supportsvc]

Returns
So it's a negative quantity and the negative unit price for the return entry.
that's how the system stores it

 

[SkipVought]

Well I can understand the negative QUANTITY. That's what happens with an Inventory System. But the unit price is the unit price.

A negative unit price? That's weird! What magic converts a unit price?

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]
"The most incomprehensible thing about the universe is that it is comprehensible" A. Einstein

 

[supportsvc]

figuring it out

in the meanwhile, I am also trying to use a subreport to pull the MAX date for the foreign exchange rate they want to use to convert to USD.

I have the subreport with the MAX (used group to only get the MAX date for the currency code)
This is good.

However, not sure how to create a formula to use the CurrencyRate from the subreport?

Which will be used in the formula in the USD Total Price Ext column

 

[lbass]

For the double negative issue, convert the quantity to an absolute amount, using:

Abs(quantity_sold)

In your subreport, set up the exchange rate as a shared variable:

Shared currencyvar xchgrt := {table.xchgrate;

Then reference the variable in your calculation in the main report. The subreport must be in a section above the one in which it is referenced in a section that makes sense. Are you grouping by country in the main report? Then you could place the sub in the country group header, link it to the main report on country, and then reference the shared variable in the detail section in a formula.

Shared currencyvar xchgrt;
xchgrt*abs(quantity_sold) //for example—not sure how you are using the exchange rate

-LB

 

[supportsvc]

Ah GREAT! RE: Absolute!
THANK YOU!

There is no grouping on the main report

The subreport is grouped on the CurrencyCode which gives me the max date and the CurrencyRate

 

[lbass]

Where ist he sub located? How is it linked to the main report? What is the remaining issue?

-LB

 

[supportsvc]

I currently just placed it at the report footer

it's linked on the CurrencyCode

Goal is they want to see the the USD Total Price Ext (QuantityShipped * USD Price/Unit) converted all to USD using the current exchange rate (comes from the subreport) as there are some entries with foreign currency.

So it'll be a conversion when the CurrencyCode on the invoice is NOT <> "USD" to use the CurrencyRate from the subreport (the last currency rate, which is "today's rate).

 

[lbass]

So the currency rate could be different in each row of the main report? If so, I would insert another detail section in the main report, and in design mode, drag the new detail section up so that it becomes detail_a. Then put the subreport in detail_a. I’m assuming that within the subreport, you have grouped by currency code and that your shared variable is set up in the group footer, with dates in ascending order.

To are the subreport disappear after ensuring it is working properly with the main report, do the following.
1-suppress all sections within the subreport.

2-in the main report, go to format->subreport-> and check “suppress blank subreport”.

3-remove the borders on the subreport in the format sscreen.

4- in the section expert, select detail_a and check “suppress blank section”.

You cannot suppress the subreport or the detail_a section directly or the shared variable will not pass.

-LB

 

[supportsvc]

Yes, the currency can change in each row, basically per invoice.

Ok, inserted another Detail then moved it up so it's Detail_A and added the subreport there.

Yes, the subreport is Grouped by CurrencyCode and sorted descending on the Date for the max / last date with the CurrencyRate. This grouping and sort order produces the last date and rate which is correct when displaying the result.

I'm sorry, I am unsure how to add the variable in either report?

1. Done
2. Done
3. I think done
4. Done

Please see attached. Saved it with data

In the Formulas, the ones starting with Foreign, on the main report, are the ones using:
{AR_InvoiceHistoryDetail.ARMC_234_EntryRate} needs to be replaced with the field from the subreport on Group
{ARSOExchangeRate}

 

[lbass]

First, place your fields in the subreport in the group footer, NOT the group header. Change the sort order for the date field to ascending, so the most recent date will appear in the group footer (you can check this by placing the date field in the detail section and unsuppressing the detail and group footer sections temporarily).

In the group footer, add a formula which sets the shared variable equal to the most recent date (which is determined by the sort order only):

whileprintingrecords;
shared xchgrt := {CX_CurrencyCodeDetail.ARSOExhangeRate};

In the main report,change {@ForeignPrice/Unit} to:

whileprintingrecords;
shared xchgrt;
if {AR_InvoiceHistoryDetail.ARMC_234_EntryCurrency} <>"USD"
then {AR_InvoiceHistoryDetail.UnitPrice} / xchgrt else
{AR_InvoiceHistoryDetail.UnitPrice}

Change {@ForeignTotalCost/Unit} to:
whileprintingrecords;
shared xchgrt;
if {AR_InvoiceHistoryDetail.ARMC_234_EntryCurrency} <>"USD"
then {@Total Cost/Unit} / xchgrt else
{@Total Cost/Unit}

I don't know how exchange rates work and so I'm can't weigh in on the logic of your formulas. I am just showing you how the shared variables will work. You should keep the subreport content visible until you can verify that your are getting the expected results and then take the steps to make the sub inivisible that I mentioned earlier.

To check the value that is being passed to the main report, create a formula in the main report:

whileprintingrecords;
shared xchgrt;

...and place it in detail_b. You can either remove or suppress it once you have the report working properly.

-LB

 

[supportsvc]

Thank you

Ok, moved the fields to the Group Footer and changed the sort to Ascending on the subreport and it's resulting in the last date and currency rate.
This is good to go.

Had to add numbervar

But getting this error when trying to Preview the report

 

[lbass]

Sorry about forgetting the variable type and for not noticing that you were then trying to insert sums on variables. You need to change your report footer formulas to also reference variables and also change the detail level formulas to collect the sums into those variables.

Detail formulas:
Change {@ForeignTotalCost/Unit} to:

whileprintingrecords;
shared numbervar xchgrt;
numbervar FTCU;
numbervar sumFTCU;
if {AR_InvoiceHistoryDetail.ARMC_234_EntryCurrency} <>"USD" then
FTCU := {@Total Cost/Unit} / xchgrt else
FTCU := {@Total Cost/Unit};
sumFTCU := sumFTCU+FTCU;
FTCU //this will display the individual value while the sum in the previous line is collecting behind the scenes


Change {@ForeignTotalPrice/Ext} to:

whileprintingrecords;
shared numbervar xchgrt;
numbervar FPU;
numbervar FTPext;
numbervar sumFTPext;
if {AR_InvoiceHistoryDetail.ARMC_234_EntryCurrency} <>"USD" then
FPU := {AR_InvoiceHistoryDetail.UnitPrice} / xchgrt else
FPU :={AR_InvoiceHistoryDetail.UnitPrice};
FTPext := FPU*{AR_InvoiceHistoryDetail.QuantityShipped};
sumFTPext := sumFTPext+FTPext;
FTPext //for display in detail

In the report footer, add formulas to display the summaries (remove the inserted summaries):

//{@displaysumFTPext}:
whileprintingrecords;
numbervar sumFTPext;

//{@displaysumFTCU}:
whileprintingrecords;
numbervar sumFTCU;

//{@TotalMarginAmt}:
whileprintingrecords;
numbervar sumFTPext;
numbervar sumFTCU;
sumFTPext - sumFTCU

//{@TotalMargin%}:
whileprintingrecords;
numbervar sumFTPext;
numbervar sumFTCU;
if sumFTPext = 0 then
0 else
((sumFTPext - sumFTCU)*100)/sumFTPext

-LB

 

[supportsvc]

ah, ok, thank you!

Now I am getting Division by Zero message

Here's the updated report:

https://files.engineering.com/getfi...6c-b4ab6ae8489b&file=newsellbydate_custom.RPT

 

[lbass]

According to the error explained on the left, xchgrt = 0, which means the value did not pass from the subreport. Did you suppress the subreport or detail_a? Are you sure the values were passing correctly before you changed these new formulas? As I said earlier, you can test by adding a formula to detail_b:

whileprintingrecords;
shared numbervar xchgrt;

-LB

 

[supportsvc]

Humh,

detail_a Suppress Blank Section checked
subreport Suppress checked

I may be doing something wrong

https://files.engineering.com/getfi...c0-15513d1a9bb3&file=newsellbydate_custom.RPT

Not sure why there are 0's but the 1.33 is showing?

 

[lbass]

You need to unsuppress the subreport. Instead, in design mode, select the subreport and right click on it->format subreport->(here is where you uncheck suppress)->then go to the subreport tab->check "suppress blank subreport."

You ALSO need to go into subreport-> go to the section expert and suppress every section WITHIN the subreport.

-LB

 

[supportsvc]

Ah, ok

Done and done

however there are still 0 exchange rate values, not sure why there would be?

it should always be the last exchange rate, which currently is 1.33

and showing the subreport on the main report.

 

[lbass]

I don’t know what I’m seeing. Maybe save this version of the report with data and send it to me again.

-LB