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!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Mailing vs physical address

Status
Not open for further replies.

veles

Technical User
Sep 1, 2006
57
CA
Hi,

I need some help on an address formula.

I have clients that have a physical address (all have one) and some have a mailing address.The physical and mailing address are stored in {ADDR.ADDR_TYPE_CD}

I created a report grouped by client, then a group by {ADDR.ADDR_TYPE_CD}.

I need to display a mailing address if there is one or the physical if there is no mailing.

I need the details of the report for showing license history for the client, but the clients that have both physical and mailing are showing double for the license history in the details.

Basicaly I need to somehow to show the proper address and the details not to duplicate.

I hope I explained this so it can be understood.

Thanks
 
Try this:

1. Take out the group by address code. Instead, sort by address code in whatever order gets you the mailing address, if there is one, first.

2. Put the client and address information in the client group header.

3. Create a formula that looks something like this. I'll call this {@AddressType}:
StringVar addrType;
if previousIsNull(<client group field>) or <client group field> <> previous(<client group field>) then addrType = {ADDR.ADDR_TYPE_CODE}​

4. Go to the Section Expert for your details section. Click on the button to the right of "Suppress" (do NOT check the Suppress checkbox!) and add the following formula:
StringVar addrType;
{ADDR.ADDR_TYPE_CODE} <> addrType​

This will set a variable with the code for the address type that's being used for this client and then suppress any rows that have a different address type.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
 
Dell,

Thanks for this

All works up to number 4. I did the 3 steps and for a client that has both address types I got to display the correct address in the client header as you described.

I created the variable in step 3, but where do I use it?
I created the variable in step 4 and when I used it as a conditional suppress for the details section as you mentioned, both records in the details were suppressed.

When trying out the variable in step 3, I put in in the details and got false for both license records.
 
Ok, there are a couple of things...

1. Put a semi-colon at the end of the formula. This will prevent it from displaying on the report.

2. Put the formula in the group header section. This will set the value when the group changes.

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
 
Thanks for the reply,

I'm not sure what I am doing wrong, but I tried what was suggested and the detail completely gets suppressed.

Here is the actual breakdown

G1 - Client - {ORG.CLIENT_NUM}
In this group I have all the client info I need, I have the address field {ADDR.ADDR_TYPE_CODE} and a sort of ascending to show the mailing if it existits or physical if mailing doesn't exist. (This is all showing correct)

In the details I have license info and for a client that has both mailing and physical it duplicates.

Now I created a formula based on step 3 above and put it in G1:

StringVar addrType;
if previousIsNull({ORG.CLIENT_NUM}) or {ORG.CLIENT_NUM} <> previous({ORG.CLIENT_NUM}) then addrType = {ADDR.ADDR_TYPE_CODE};

The result from this formula is displaying False for all of the clients. Is this what it is supposed to be the result?

In the details I created a conditional suppress based on step 4
StringVar addrType;
{ADDR.ADDR_TYPE_CODE} <> addrType

and this suppresses all details. It should suppress only the duplicate as I understand from the previous post.

I'm not sure, but my gut feeling is that something is not working in the variable in G1 as it shows False for all records.

I think it is very close.

Much appreciated.
 
My fault again - change this:

addrType = {ADDR.ADDR_TYPE_CODE};

to this:

addrType := {ADDR.ADDR_TYPE_CODE};

-Dell

DecisionFirst Technologies - Seven-time SAP BusinessObjects Solution Partner of the Year
 
[pc2]Thank you so much.

That worked like a charm
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top