Column Display Question

building reports desktopintelligence
1

Hi, All:

I have one report that requires to display a column as follows:

· If one deal has more than one policy, then display only Homeowner policy.
· If one deal has one policy and it is Lender Policy, then display “N/A”.
· If one deal has one policy and it is Homeowner Policy, then display that Homeowner policy number.

(Note: One deal can have one or more policies. Each policy can have both “Lender Policy” and “Homeowner Policy”, or just one lender policy, or just one homeowner policy, this depends on the flag of , one column from the database.
There is at most only one homeowner policy for each deal, but there could be more than one Lender policies for each deal).

I created a variable in the report, but can’t get rid of the empty rows. Filter can’t be applied, as the count() is done in the report level. What is the best way of doing this? In the universe or in a view?

DB: SQL Server
BO: V 5.1.5

Here is the object defined in the Reporter:

= If (Count() In >=2) Then <v_Homeowner PolicyNum> Else If (Count() In =1 And =79) Then “N/A” Else <v_Homeowner PolicyNum>

If anyone could shed light on it, that will be great.

Thanks a lot.

Lilly J (BOB member since 2002-08-22)

2

One thing you could try is applying the filter, but using the NoFilter() function on your count.
Or you could build a CASE statement in Designer to cater for this.

3

Hi:

I tried to define an object in the Designer, but no luck. I need to use both count ( and . I got some logic error msg when I tried to use Case When statement: The logic is as follows:

*If (count()=1 and =78) Then display

*If (count()=1 and =79) Then display “N/A”

*If (count()>1) Then display When (=78)

I really appreciate it if give some hints or suggestions.

The Database is: SQL Server 2000

Thanks a lot.

Lilly

Lilly J (BOB member since 2002-08-22)