I need help to write a formula 2

[hramirez1]

Hi Experts

I'm having an issue with one of my reports. I have a group by PackageID in to this group I have different ObjectId: 1000 refers to Agents
1001 refers to Policy
1015 refers to accounting
and there are certain conbinations like this one that is causing me a big issue
PackageID 2525
DomainId ObjetId
1000 98 (AgentId)
1001 98 (PolicyId)
1015 2200 (Acount Number)
My question is: How can I get only the Account Number on the detail section and at the same time show up the Policy number on the group header section? (Notice that the AgentId is equal PolicyId)

 

[synapsevampire]

You might create a formula which conditionally supresses the details where DomainId <> 1015

Right click the details and select format section and in the X 2 next to the suppress place something like:

{table.PackageID} = 2525
and
{table.DomainId} <> 1015

Hope this gets you closer.

Note that suppressing does not keep totals from using the fields, you might also consider filtering them out of the report completely using Report->Edit Selection Formula->Record and place something like:

(
{table.PackageID} = 2525
and
{table.DomainId} <> 1015
)

-k

 

[lbass]

How do you know whether ObjetID is a policy number, account number or agent ID? Is there another field which tells you? If so, you could create a formula like:

if {table.identifier} = "PolicyID" then {table.ObjetID} else ""

Then insert a Maximum on this formula and display the result in the group header. For the detail field, create a formula:

if {table.identifier} = "AcctNo" then {table.ObjetID} else ""

If length distinguishes the fields, then changethe above formulas to:

if len({table.ObjetID} = 2 then {table.ObjetID} else 0

Insert a maximum on this to get the policy number in the header. Use the following for the account number in the details section, then format the formula->format field->number->customize->suppress if zero.

if len({table.ObjetID} = 4 then {table.ObjetID} else 0

I'm unclear about whether you want all domain IDs to appear even though you want only one instance of the objetID to appear. If you only wanted the domain for the account number field, then use the section expert to suppress the details based on one of the above concepts:

len({table.ObjetID}) <> 4 //or

{table.identifier} <> "AcctNo"

-LB