Formula for Selection of Values 1

[mwhager]

I have a datasource that consists of a single database. The database is a collection of general ledger account information and values by period. In the database are fields like:
GL-ACCT, GL-DESCRIPTION, and there are also unique fields for each accounting period of the current year and prior year. These unique fields carry the value of the GL-ACCT for that unique period. So I have 28 of these unique fields (12 periods for current year, 2 adjustment periods for current year, 12 periods for prior year, 2 adjustment periods for prior year). I want the total of the values from the GLCRYVAL5 field (current year period 5) when the GL-ACCT equals accts 1400, 1401, 1402. I'm not having any luck any way that I write this and would appreciate some help. Using CR 8.5. Many thanks.

 

[Naith]

We need to see a paste of your current selection criteria, and ascertain what results you're getting back.

From your description, it seems like the core of your criteria should be something like:

{DateField} >= {?Start_Date_Parameter} and
{DateField} <= {?End_Date_Parameter} and
{GL-ACCT} in (1400,1401,1402)

Naith

 

[mwhager]

There is no selection criteria. It is a formula in a single cell in an income statement. Here is what I was playing around with:

if {GLCGLMS.GL_ACCT} = &quot;4000&quot; then numbervar a := {GLCGLMS.GL_ACCT_CRY_VAL_5};
if {GLCGLMS.GL_ACCT} = &quot;4100&quot; then numbervar b := {GLCGLMS.GL_ACCT_CRY_VAL_5};
if {GLCGLMS.GL_ACCT} = &quot;4200&quot; then numbervar c := {GLCGLMS.GL_ACCT_CRY_VAL_5};
if {GLCGLMS.GL_ACCT} = &quot;4300&quot; then numbervar d := {GLCGLMS.GL_ACCT_CRY_VAL_5};
numbervar z := a+b+c+d

however when validating the data, z only returns the value of numbervar a - not a+b+c+d. if i test numbervar z by saying numbervar z:=b the formula returns 0 (yes i have checked and there is a value for b) same as when i test with numbervar z:=c or numbervar z:=d. what am i doing wrong here?

 

[Naith]

WhilePrintingRecords;

NumberVar A;
NumberVar B;
NumberVar C;
NumberVar D;

If {GLCGLMS.GL_ACCT} = &quot;4000&quot;
Then A := A + {GLCGLMS.GL_ACCT_CRY_VAL_5}
Else
If {GLCGLMS.GL_ACCT} = &quot;4000&quot;
Then B := B + {GLCGLMS.GL_ACCT_CRY_VAL_5}
Else
If {GLCGLMS.GL_ACCT} = &quot;4000&quot;
Then C := C + {GLCGLMS.GL_ACCT_CRY_VAL_5}
Else
If {GLCGLMS.GL_ACCT} = &quot;4000&quot;
Then D := D + {GLCGLMS.GL_ACCT_CRY_VAL_5}


I put in <Variable> + {GLCGLMS.GL_ACCT_CRY_VAL_5} because if you want a running total, the variable needs to be added to itself. However, if you have the totalling being done via other means, then just remove the variable +.

i.e.
Then D := {GLCGLMS.GL_ACCT_CRY_VAL_5}
as opposed to
Then D := D + {GLCGLMS.GL_ACCT_CRY_VAL_5}

All the best,

Naith

 

[Ngolem]

it isn't really clear what you are doing here...other than adding some numbers.

Please show us what a simple report would look like and how the report is organized.

If I were doing this report (based on the dearth of info shown) I would have the report Grouped on {GLCGLMS.GL_ACCT} , but your formula implies there is only 4 possible values for {GLCGLMS.GL_ACCT} and the values of {GLCGLMS.GL_ACCT_CRY_VAL_5} are not repeated.

this formula...if placed in a detail section and viewed should show a progressive incease until the final value of {GLCGLMS.GL_ACCT} is totaled.

It may be that you have a reset formula somewhere that is setting the values of a,b,c,d back to zero and you are not showing it.

But show us what this thing should look like ....I could speculate and waste my time but I won't right now
Jim Broadbent

 

[basil3legs]

It sounds like your best bet is to create a formula something like:

IF {GL-ACCT} in (1400,1401,1402) then {GLCGLMS.GL_ACCT_CRY_VAL_5} else 0

Your current formula looks very wrong. 'a','b','c' and 'd' are never reset if they fail the IF statements. If they are being reset to 0 elsewhere then you are only going to get the result of the last data entry checked. This may explain why you are only getting 'a' back and not 'b','c','d'. It is also doing unnecessary IFs which will slow the report down.

If you do this as a formula in the details section, you can then use this as though it were another field, using sum etc.

Also, your method doesn't really fit your problem description in your original post. You start off mentioning 1400, 1401 and 1402 but then are using 4000, 4100, 4200 and 4300!

If the formula above is not what you are after, please clarify exactly what you are trying to do.

Thanks.

 

[mwhager]

basil3legs - the array formula did not behave as i expected it to. yes you're right i had the gl nos wrong, i was trying to shorten the acct nos and that's why the mistype. anyway, here's the formula i used based on what you sent:

IF {GLCGLMS.GL_ACCT} in (&quot;10-00-4000&quot;,&quot;10-00-4100&quot;,&quot;10-00-4200&quot;,&quot;10-00-4300&quot then {GLCGLMS.GL_ACCT_CRY_VAL_5} else 0

(i put the accts in &quot;&quot; b/c they are strings). anyway when i do a syntax check i get &quot;the ) is missing&quot; and it lines up after the close quote on the first entry in the array.

to ngolem - there is no selection criteria. the report is an income statement that has entries like this:

prior mo curr mo etc
net sales
cost of sales
overhead
other income
etc.

each line has a different formula based on the gl acct nos that make up that line on the income statement. all the accts in the database will be used at some point or another on an individual line. in any particular formula there are only certain values for certain accounts that can be considered. so - i understand i need to take this:

if {GLCGLMS.GL_ACCT} = &quot;10-00-4000&quot; then numbervar a := {GLCGLMS.GL_ACCT_CRY_VAL_5};
if {GLCGLMS.GL_ACCT} = &quot;10-00-4100&quot; then numbervar b := {GLCGLMS.GL_ACCT_CRY_VAL_5};
if {GLCGLMS.GL_ACCT} = &quot;10-00-4200&quot; then numbervar c := {GLCGLMS.GL_ACCT_CRY_VAL_5};
if {GLCGLMS.GL_ACCT} = &quot;10-00-4300&quot; then numbervar d := {GLCGLMS.GL_ACCT_CRY_VAL_5};
numbervar z := a+b+c+d
and convert it to more of a running total formula so that it accumulates the values and doesn't reset anything to 0.

hope this explains it better.

 

[basil3legs]

Sorry, they should be square brackets around the options.

i.e.

IF {GLCGLMS.GL_ACCT} in [&quot;10-00-4000&quot;,&quot;10-00-4100&quot;,&quot;10-00-4200&quot;,&quot;10-00-4300&quot;] then {GLCGLMS.GL_ACCT_CRY_VAL_5} else 0

 

[Naith]

You've probably already figured this out for yourself, but the 4000s in the formula I gave you should be incrementing like 4000, 4100, 4200... with each variable.

Naith