How do I write a formula using input paramaters

[TimothyP]

I am trying to write a Commodity Utilization report.
My database table has the following fields: Commodity id, Year, Qty-Ordered-Jan, Qty-Ordered-Feb,
Qty-Ordered-March… through Qty-Ordered-Dec

Commodity id – identifies what commodity it is
Year – Identifies what year (i.e. 2001)
Qty-Ordered-Jan – stores the quantity ordered for January
Qty-Ordered-Feb - stores the quantity ordered for February
Qty-Ordered-March - stores the quantity ordered for March

I want a report that will prompt the client for an input range. (9/15/2001 to 9/15/2002)
Once the report runs it should display the commodity id, and the total Qty-Ordered for the input range. Somehow, I have to add up all the months in the input range.
What formulas do I have to create to accomplish this?

Any suggestions would be appreciated.

Thanks,
Tim

 

[Naith]

Assuming you'll drive the Record Selection criteria on Year, because it doesn't appear you have an corresponding date field in the database, then you'll always have to extract a years worth of data. (Is this an accurate assumption? I'm guessing that you must have an applicable date somewhere - otherwise, why would you be interested in quoting the day in your parameter range, instead of just Jan2001-March2002, for instance?)

If you can only drive the report off year, then you could use an If statement for monthly quantity aggregation;

WhilePrintingRecords;
NumberVar PeriodQuantity;

If {Qty-Ordered-Jan} in {?YourPeriod}
Then PeriodQuantity := PeriodQuantity + {Qty-Ordered-Jan}
Else PeriodQuantity;
If {Qty-Ordered-Feb} in {?YourPeriod}
Then PeriodQuantity := PeriodQuantity + {Qty-Ordered-Feb}
Else PeriodQuantity;
...
If {Qty-Ordered-Dec} in {?YourPeriod}
Then PeriodQuantity := PeriodQuantity + {Qty-Ordered-Dec}
Else PeriodQuantity;

If your period is longer than a year, then any months which feature more than once will both be accounted for in the formula.

However, keep in mind that if you always extract a minimum of a years worth of data every time the report is run, then it's not going to be very economical on resources.

Naith

 

[synapsevampire]

Naith:
IF If {Qty-Ordered-Dec} is a quantity, how could the following work?

"If {Qty-Ordered-Dec} in {?YourPeriod}"

Tim:
whileprintingrecords;
numbervar y;
numbervar x;
numbervar summing;
booleanvar multiyear;

If year(minimum({?datetest})) <> year(maximum({?datetest})) then
multiyear := true; //Here's the rub

//If you just have one year:
for x = month(minimum({?datetest})) to 12 do
(

Case x
When 1 then
summing := summing+{Qty-Ordered-Jan}
When 2 then
summing := summing+{Qty-Ordered-Feb}

When 12


The rub is with multiyears, but not that bad - I don't have time to flesh it out, but the basic looping is here.

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com

 

[TimothyP]

Naith:

You are right, the day would not be included in the input parameter. However, each year has a corresponding month 01, 02, 03, ...

The client may want to have an input parameter that is not a full year. For example, from 10/2001 - 12/2001.

I know it's been a while since I first posted the question, but it has bubbled up once again now that our Fiscal Year end is over.

 

[Naith]

Tim,

Been a while since I looked at this one, but I'm not sure where my mind was when I tried to answer you before. My offered solution assumes that you your Qty Ordered periods are date based - although you made it clear that they aren't. I can only apologise for that lapse in concentration.

If you have v8.0+, SynapseVampire's solution should point you in the right direction. If you have a previous version, let us know, and someone will come up with an alternative for you.

Best wishes,

Naith

 

[chelseatech]

Naith,

Great suggestion, but one small correction needed. You should initialise the working total to zero at the start. You can do this on the second line with
NumberVar PeriodQuantity := 0 ;


(I added a &quot;:=0&quot; to your line)
Editor and Publisher of Crystal Clear

http://www.chelseatech.co.nz/pubs.htm

 

[Naith]

I think you fell into the same trap I did, ChelseaTech. My original post is a completely unworkable solution. Checking for Qty Ordered in date fields won't work as Qty Ordered is a number field.

I'm sure you could bastardise the formula with variables assigned to month counters to make it work, but, if Tim's version allows it, I think looping is the most decent way to go, as Kai's pointed out.

For the record, if my 'solution' was viable, you're right about the initialisation, ChelseaTech. Thanks for the catch.

Naith