Group selection with a manual crosstab 1

[Jcfx]

Hello
CR 10 , SQL database.

I have a manual cross tab that was created to track pricing errors due to inaccurate data entry.
Two tables, Item and ItemPric, left inner join from item.itemid to itemPric.itemid
For every itemid there can be four price matrix. Walkin, Small, Medium and Large. There should only be one of each, for example Walkin, Small, Small and Large is a data entry error.
The amounts that are in the matrix fields are based on a calculation in the application that looks at the average price of the item and applies a defined multiplier.
My report at the moment looks like this: (I’ll use field names for the column headers)

Item.Itemid—Item.Desc—Item.AvgCost—Item.LastCost--@Walkin--@Small--@Medium--@Large
12345---------Light---------1.00---------------1.00---------------1.35-------1.25------1.15----------1.10----
-------------------------------------------------------------------------0.00-------1.25------0.00---------0.00-----
-------------------------------------------------------------------------1.35--------0.00------0.00--------0.00-----
-------------------------------------------------------------------------0.00--------0.00------1.15--------0.00-----
-------------------------------------------------------------------------0.00---------0.00------0.00--------1.10----

The first line is GH1and are sums of the following, the second is the detail made up of these individual formulas.

//@Large$
if {ITEMPRIC.PRICETYPE} = "LARGE FLEET" then {ITEMPRIC.PRICE} else 0
//@Medium$
if {ITEMPRIC.PRICETYPE} = "MEDIUM FLEET" then {ITEMPRIC.PRICE} else 0
//@Small
if {ITEMPRIC.PRICETYPE} = "SMALL FLEET" then {ITEMPRIC.PRICE} else 0
//@Walkin
if {ITEMPRIC.PRICETYPE} = "WALK IN" then {ITEMPRIC.PRICE} else 0

Now I need to test for Data Errors:
//@TestforDataErrors
(isnull({ITEMPRIC.PRICETYPE})) or
({ITEM.LASTCOST}=0)or
({ITEM.AVGCOST}=0)or
({ITEMPRIC.PRICE}<={ITEM.AVGCOST})

I have this placed in Report/selection formulas/group. And it works great, I am just missing one thing, how to test for Walkin/Small/Small/Large. In the above example if someone had accidentally set up the matrix this way there would be two detail lines for small greater than 0.00 and Medium would be all 0. I’ve tried doing a count of the detail lines >0 and if it is 2 or greater kibosh it, but I run into evaluation issues. Is there another approach?
Before I forget, this is my record selection formula:

 {ITEM.SHOPID} = "MAIN" and
not ({ITEM.ITEMTYPE} in ["CORE", "KIT", "SUPPLIES"])

Thanks for any assistance,

Julie
CR 9,10 CE10 Sql DB

 

[lbass]

Add the following to your GROUP selection formula:

or
(
sum({@small},{table.itemID}) = 0 or
sum({@small},{table.itemID}) <> maximum({{@small},{table.itemID})
) or
(
sum({@memdium},{table.itemID}) = 0 or
sum({@medium},{table.itemID}) <> maximum({{@medium},{table.itemID})
) or //etc.

...i.e., checking for all zero values or more than one non-zero value.

-LB

 

[Jcfx]

Thanks for the assistance,
This is the formula I now have as my group selection. With your addition I am getting an evaluation error, 'This formula cannot be used because it must be evaluated later'

//@TestforDataErrors
(
isnull({ITEMPRIC.PRICETYPE})
) or
(
{ITEM.LASTCOST}=0
)or
(
{ITEM.AVGCOST}=0
)or
(
{ITEMPRIC.PRICE}<={ITEM.AVGCOST}
)
or
(
Sum ({@WalkIn$}, {ITEM.PARTID}) = 0 or
Sum ({@WalkIn$}, {ITEM.PARTID}) <> maximum({@WalkIn$}, {ITEM.PARTID})
) or
(
Sum ({@Small$}, {ITEM.PARTID}) = 0 or
Sum ({@Small$}, {ITEM.PARTID}) <> maximum({@Small$}, {ITEM.PARTID}) 
) or
(
Sum ({@Medium$}, {ITEM.PARTID}) = 0 or
Sum ({@Medium$}, {ITEM.PARTID}) <> maximum({@Medium$}, {ITEM.PARTID}) 
) or
(
Sum ({@Large$}, {ITEM.PARTID}) = 0 or
Sum ({@Large$}, {ITEM.PARTID}) <> maximum({@Large$}, {ITEM.PARTID})
)

Julie
CR 9,10 CE10 Sql DB

 

[lbass]

You shouldn't be getting that error if you are in fact working in the report->selection criteria ->GROUP area, and if your formulas for small, medium, etc. are as you showed above.

-LB

 

[Jcfx]

I placed @TestforDataErrors
under Report/Selection Formulas/group and once I executed the report that is the error I received.

Those are the formulas I am using, I copied them right from the editor to here.

In my Detail I have @Walkin$, @Small$,@Medium$ and @Large$.
I am grouping by Item.PartID.
in GH1 I have the ItemId, Description, AvgCost and Last cost, then the Sums of @Walkin$, @Small$, @Medium$ and @Large$.





Julie
CR 9,10 CE10 Sql DB

 

[Jcfx]

Wait... I think I have it, but would someone explain to me exactly what the difference is

I had created the formula @TestforDataErrors then in the Record/Select Formulas/Group I placed {@TestforDataErrors}

Not the actual CONTENT of the formula.

Once I did that, it works perfectly, my thanks Lbass.

Why does {@TestforDataErrors} work if I rem out your additions, but in order to Use your additions I have to actually place the contents of the formula there instead of referencing it?

aPologies for the newbish question

Julie
CR 9,10 CE10 Sql DB

 

[lbass]

I'm not sure, but it's related to the evaluation time. Sometimes group selection formulas that won't work (because they are formulas), WILL work, once the content of the formula is entered instead.

-LB