formula not working correctly. Please help

santosh1 · Jan 13, 2004

I have a a/c No. field for each item in the detail section.

For example:
Item
---------------------------------------
T Shirt
a/c: 6587-36580-9873-0000-0000

Poster
a/c: 6587-36580-9873-0000-0000

Brochure
a/c: 6587-36580-9873-0000-0000

Pens
a/c: 6587-36580-9873-0000-0000

If the a/c number is the same for all the items, then I want to display it at the top in the
page header section and suppress a/c in the detail section so that a/c no is displayed only once.
If a/c number is not the same for all line items, then display a/c no. at the detail section like above
and not show a/c number in the page header section.

To do this, I inserted three formula fields called @SameGL, @FlagSameGL and @EndSameGL.
I put @sameGL in the page header section, @FlagSameGL on the detail and @EndSameGL on the group footer
section.

Formula @sameGL has the following formula:

WhilePrintingRecords;
booleanVar sameGL := true

Formula @FlagsameGL has the following formula:
WhilePrintingRecords;
booleanVar sameGL ;

//If OnFirstRecord then //First record does not have a previous.
If {tglAccount.GLAcctNo} <> Next ({tglAccount.GLAcctNo}) Then
sameGL = false //Current and Next are matches
else If OnLastRecord Then //Last record does not have a Next.
If Previous( {tglAccount.GLAcctNo}) <> {tglAccount.GLAcctNo} Then
sameGL = false //Previous and Current 'match'.
Else If Previous( {tglAccount.GLAcctNo}) <> {tglAccount.GLAcctNo} OR {tglAccount.GLAcctNo} <> Next ( {tglAccount.GLAcctNo}) Then
sameGL = false //Checks Prevoius and Next record against Current record. (True)
else
sameGL = true

Finally, @EndsamGL has the following formula.

booleanVar sameGL ;

Finally, on detail section which shows the GL a/c, I did format section and suppressed this detail
section if boolean value for sameGL is true. However this method is not working. It shows GL a/c even
if the GL a/c no is same throughout the report.

Is there a different better way to do this? Thanks a lot.

lbass · Jan 13, 2004

You could accomplish something similar by creating a group on a/c number, and choosing "Repeat Group Header on Each Page." You could conditionally suppress the group header based on:

count({table.groupfield},{table.groupfield}) = 1

Then use the same formula except change the operator to <> and use it to conditionally suppress the a/c number field in the details section.

-LB

santosh1 · Jan 13, 2004

Thanks LB for the info.

I am customizing this report which was originally written
by different vendor. The vendor's report engine overrides
my settings and doesn't allow me to group/sort on any field
on this particular report. The solution you mention requires grouping on a/c otherwise it would have been great. Is there any other way to resolve this than by grouping? Thanks a lot.

lbass · Jan 13, 2004

What do you want to happen if there are multiple lines with the same a/c number on half of the page and then a different a/c number for the rest of the page?

-LB

santosh1 · Jan 13, 2004

If there are multiple lines with same a/c number on half of the page and then diferent a/c number for the rest of the page, then I would display the a/c number on the detail line
as above like:

T Shirt
a/c: 6587-36580-9873-0000-0000

Poster
a/c: 6587-36580-9873-0000-0000

Brochure
a/c: 6587-36580-9873-0000-0000

Pens
T Shirt
a/c: 6587-36580-9873-0000-0000

Poster
a/c: 6587-36580-9873-0000-0000

Brochure
a/c: 6587-36580-9873-0000-0000

Pens
a/c: 6587-36580-9873-0000-0000

But if all the items on the report has the same a/c no., then I want to suppress the a/c no. from the detail section and just show a/c no. once at the header level as:

a/c no: 6587-36580-9873-0000-0000

Thanks a lot.

santosh1 · Jan 14, 2004

If there are multiple lines with same a/c number on half of the page and then diferent a/c number for the rest of the page, then I would display the a/c number on the detail line
as above like:

T Shirt
a/c: 6587-36580-9873-0000-0000

Poster
a/c: 6587-36580-9873-0000-0000

Brochure
a/c: 6587-36580-9873-0000-0000

Pens
T Shirt
a/c: 6587-36580-9873-0000-0000

Poster
a/c: 6587-36580-9873-0000-0000

Brochure
a/c: 6587-36580-9873-0000-0000

Pens
a/c: 6587-36580-9873-0000-0000

But if all the items on the report has the same a/c no., then I want to suppress the a/c no. from the detail section and just show a/c no. once at the header level as:

a/c no: 6587-36580-9873-0000-0000

Thanks a lot.

Ngolem · Jan 14, 2004

Why don't you just group by a/c number??

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

santosh1 · Jan 14, 2004

Jim,

I am not able to use group by/order by clause for this report since the report engine of the original vendor who wrote the report overwrites the group by/order by setting that I use otherwise it would have been great.
I am trying to customize their report. Any help appreciated.
Thanks.

Ngolem · Jan 15, 2004

show us how this report is currently grouped

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

santosh1 · Jan 15, 2004

The above example was just an example.

Here's the SQL for the report. It was grouped by POKey when the vendor delivered the report.
I tried grouping by ItemID or tglAccount.glAccountNO, it doesn't group at all. Any other
way to check if the a/c no is same? Thanks for the help.

SELECT
tpoPurchOrder."UserFld1", tpoPurchOrder."UserFld2", tpoPurchOrder."UserFld3",
tpoPrintPOHdrWrk."SessionID", tpoPrintPOHdrWrk."POKey", tpoPrintPOHdrWrk."BuyerID", tpoPrintPOHdrWrk."CompanyID", tpoPrintPOHdrWrk."CompanyName", tpoPrintPOHdrWrk."CompanyAddr1", tpoPrintPOHdrWrk."CompanyAddr2", tpoPrintPOHdrWrk."CompanyAddr3", tpoPrintPOHdrWrk."CompanyAddr4", tpoPrintPOHdrWrk."CompanyCity", tpoPrintPOHdrWrk."CompanyState", tpoPrintPOHdrWrk."CompanyPhone", tpoPrintPOHdrWrk."CompanyPostalCode", tpoPrintPOHdrWrk."CompanyCountry", tpoPrintPOHdrWrk."CurrID", tpoPrintPOHdrWrk."FOB", tpoPrintPOHdrWrk."PmtTermsDesc", tpoPrintPOHdrWrk."PurchAmt", tpoPrintPOHdrWrk."PurchFromName", tpoPrintPOHdrWrk."PurchFromAddr1", tpoPrintPOHdrWrk."PurchFromAddr2", tpoPrintPOHdrWrk."PurchFromAddr3", tpoPrintPOHdrWrk."PurchFromAddr4", tpoPrintPOHdrWrk."PurchFromAddr5", tpoPrintPOHdrWrk."PurchFromCity", tpoPrintPOHdrWrk."PurchFromState", tpoPrintPOHdrWrk."PurchFromPostalCd", tpoPrintPOHdrWrk."PurchFromCountry", tpoPrintPOHdrWrk."RequestDate", tpoPrintPOHdrWrk."ChngOrdNo", tpoPrintPOHdrWrk."ShipMethID", tpoPrintPOHdrWrk."ShipToName", tpoPrintPOHdrWrk."ShipToAddr1", tpoPrintPOHdrWrk."ShipToAddr2", tpoPrintPOHdrWrk."ShipToAddr3", tpoPrintPOHdrWrk."ShipToAddr4", tpoPrintPOHdrWrk."ShipToAddr5", tpoPrintPOHdrWrk."ShipToCity", tpoPrintPOHdrWrk."ShipToState", tpoPrintPOHdrWrk."ShipToPostalCode", tpoPrintPOHdrWrk."ShipToCountry", tpoPrintPOHdrWrk."STaxAmt", tpoPrintPOHdrWrk."TranAmt", tpoPrintPOHdrWrk."TranCmnt", tpoPrintPOHdrWrk."TranDate", tpoPrintPOHdrWrk."TranNo", tpoPrintPOHdrWrk."TranTypeDescStrNo", tpoPrintPOHdrWrk."VendID",
tpoPrintPOLineWrk."SessionID", tpoPrintPOLineWrk."POLineKey", tpoPrintPOLineWrk."ExtAmt", tpoPrintPOLineWrk."ItemDesc", tpoPrintPOLineWrk."ItemID", tpoPrintPOLineWrk."POLineCmnt", tpoPrintPOLineWrk."POLineNo", tpoPrintPOLineWrk."QtyOrd", tpoPrintPOLineWrk."UnitCost", tpoPrintPOLineWrk."UnitMeasID",
NMDP_item_spec."nmdp_spec",
tglAccount."GLAcctNo"
FROM
{ oj ((((("SES_app"."dbo"."tpoPurchOrder" tpoPurchOrder INNER JOIN "SES_app"."dbo"."tpoPrintPOHdrWrk" tpoPrintPOHdrWrk ON
tpoPurchOrder."POKey" = tpoPrintPOHdrWrk."POKey&quot

INNER JOIN "SES_app"."dbo"."tpoPrintPOLineWrk" tpoPrintPOLineWrk ON
tpoPrintPOHdrWrk."SessionID" = tpoPrintPOLineWrk."SessionID" AND
tpoPrintPOHdrWrk."POKey" = tpoPrintPOLineWrk."POKey&quot

INNER JOIN "SES_app"."dbo"."tpoPrnPOLineDtlWrk" tpoPrnPOLineDtlWrk ON
tpoPrintPOLineWrk."SessionID" = tpoPrnPOLineDtlWrk."SessionID" AND
tpoPrintPOLineWrk."POLineKey" = tpoPrnPOLineDtlWrk."POLineKey&quot

LEFT OUTER JOIN "SES_app"."dbo"."NMDP_item_spec" NMDP_item_spec ON
tpoPrintPOLineWrk."ItemKey" = NMDP_item_spec."itemKey&quot

INNER JOIN "SES_app"."dbo"."tpoPOLineDist" tpoPOLineDist ON
tpoPrnPOLineDtlWrk."POLineDistKey" = tpoPOLineDist."POLineDistKey&quot

INNER JOIN "SES_app"."dbo"."tglAccount" tglAccount ON
tpoPOLineDist."GLAcctKey" = tglAccount."GLAcctKey"}
ORDER BY
tpoPrintPOHdrWrk."POKey" ASC

Ngolem · Jan 16, 2004

I am not talking about the SQL ... I mean the report itself.

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

formula not working correctly. Please help

santosh1

Programmer

lbass

Technical User

santosh1

Programmer

lbass

Technical User

santosh1

Programmer

santosh1

Programmer

Ngolem

Programmer

santosh1

Programmer

Ngolem

Programmer

santosh1

Programmer

Ngolem

Programmer

Similar threads

Part and Inventory Search

Sponsor