Evaluation times?

[Former Member]

Hi,

I'll start with the basics...

I have a report pulling in data via a query.

I have formulas such as:

@Type1

formula: {PRODUCT} >= 9600 and {PRODUCT} <= 9610

@Type2

formula: {GROUP} like '1H*'

@Type3

formula: {PRODUCT} = 1000020 or {PRODUCT} = 1155020 or ( {PRODUCT} >= 4904030 and {PRODUCT} <= 4999025 )

( Seven more..)

In order to see what doesn't fall into any of the formulas, I have a suppress formula on the Detail line:

@Type1 or @Type2 or @Type3 ...

Well, that's not working, it filters out maybe 95+%, but many items that meet the suppress are still displaying, and I can see no apparent pattern.

I've tried adding WhilePrintingRecords to the suppress formula, but that it flags it as and error when I try to save.

Halp!

Thanks!

Matt

[abhilash_kumar · Accepted solution]

Hi Matthew,

Create a Details B section and place each of those seven formulae on this new section.

See where the formulae show false/true combination and that can help you find out which record could be causing the issue.

P.S: Remove the suppression logic whilst doing this exercise.

-Abhilash

[Former Member · Accepted solution]

Thank you Abhilash,

I had little color flags set for the formulas, but actual placing the formulas shows me where the problem is, because they don't display at all...

The records I'm bringing in are "Line" items.  Sometimes a Line item has a BOM, but not always.  If it doesn't, the allowed fields are blank:

Name     Invoice     Line#     LineGrp     BOM#     BOMGrp

Frank     11111      145       302           789          1H2

Bah        22222      147       303         

Lahdie     55555     189       7UQ          999          1M0

In the suppress (and elsewhere), I'm actually checking both Line and BOM:

@Line.Type1 or @BOM.Type1 or

@Line.Type2 or @BOM.Type2 or

@Line.Type3...

If there is no BOM data, it seems to ignore the formula.

How can code around this or otherwise dummy the data?

Matt

[abhilash_kumar · Accepted solution]

Ahh I see.

Check for NULLs in each of your formulae:

Something like this:

IF isNull({PRODUCT}) OR

(

{PRODUCT} >= 9600 and {PRODUCT} <= 9610

)

If you do not wish to modify each formula, an easy way out could be by checking the two Convert Null options under File > Report Options. The same option is also available in the Formula Editor for each formula; the default is set to 'Exceptions for Nulls' - meaning, the formula stops executing as soon as it hits a NULL value.

-Abhilash

[Former Member · Accepted solution]

Hi,

Actually, if I change from:

Line.Type1 or BOM.Type1 or

Line.Type2 or BOM.Type2 or

Line.Type3 or BOM.Type3...

to

Line.Type1  or Line.Type2   or Line.Type3 or

BOM.Type1 or BOM.Type2 or BOM.Type3

it give me what I need... for now...

but I do foresee circumstances where I'm not going to rely on that...

Matt

[Former Member · Accepted solution]

Thank you both.

Changing exception handling works fine.

Now I remember I had to set this globally for pulling data from a database I used to work with.

What is the default value?

Somewhere in there you used to be able to set the default, but I don't see that anywhere.

Matt

[abhilash_kumar · Accepted solution]

Global settings are under File > Options > Database tab.

-Abhilash

[Former Member · Accepted solution]

Can I see or set what the default value is?

Matt

[abhilash_kumar · Accepted solution]

The Default for string fields is blank ("") and the default for numeric fields is zero.

-Abhilash

[former_member477249]

May be, its relevant to start with the basics again.

If the report is based off a query as per your description, then it makes sense to change the where clause of the query and test the results after including all the required conditions to filter the records.

Prathamesh

[Former Member]

Hi,

Thank you.

This is pulling data from two different instances, with a couple dozen tables, data type conversions  in order to link (gotta love AccPac), and several nested selects.

At this point I suspect that filtering within CR is going to be the more flexible solution.

Matt

[former_member477249]

I agree, in that case going one by one while applying the suppression logic might be helpful.

For e.g just do a @Type1 and check the results, then put a @Type1 OR @Type2 and so on. For sure, there is a combination thats slipping the suppression funnel.

Prathamesh