Help needed to write a sql/Expression definition

[Annon]

Hi,
I am new to Cognos and i badly need help to solve a problem.
I have 2 data items i.e. [Phone Type] and [Phone Number].
In [Phone Type] i have different values like Phone, Office, Fax, Mobile, Main etc.
In [Phone Number] i have corresponding 10 digit phone numbers.
The requirement is if the [Phone Type]='Phone' then the report should use the corresponding [Phone Number] for the record.
But if the corresponding [Phone Number] is null then [Phone Type] should be equal to 'Office' and the corresponding [Phone Number]
should be used.

It is a relational package and i do not have access to FM. I can make changes only in the Report studio. I am using version 10.2.
It does not use Prompts.

It would be a great help.

Thanks   

[Francis aka khayman]

a little tough with limited information available and just using mental calculation but here goes nothing...

select [Row ID], [Phone Type], [Phone Number]
from
where [Phone Type] = 'Phone' and [Phone Number] is not null

union

select [Row ID], [Phone Type], [Phone Number]
from
where [Phone Type] = 'Office'


when you join this to your other query, a record will get 'Phone' with Number or 'Office'

[clamus17]

Hi,
I am new to Cognos and i badly need help to solve a problem.
I have 2 data items i.e. [Phone Type] and [Phone Number].
In [Phone Type] i have different values like Phone, Office, Fax, Mobile, Main etc.
In [Phone Number] i have corresponding 10 digit phone numbers.
The requirement is if the [Phone Type]='Phone' then the report should use the corresponding [Phone Number] for the record.
But if the corresponding [Phone Number] is null then [Phone Type] should be equal to 'Office' and the corresponding [Phone Number]
should be used.

It is a relational package and i do not have access to FM. I can make changes only in the Report studio. I am using version 10.2.
It does not use Prompts.

It would be a great help.

Thanks   

Hi Annon,

Can you provide any sample output of what you want to achieve? It would also be a great help.

Thanks

[Annon]

Package has the following values      
      
Customer   Phone Type Name                               Phone Number
a               Phone                                               123-456-7890
                  fax                                                234-567-8901
b                   Phone                                          345-678-9012
                  office                                                345-999-9999
                   fax                                                444-555-0000
c                  office                                                789-123-4567
                  main                                                 000-111-2222

I should get in my report as below   
   
Customer                 Phone Number
a                           123-456-7890
b                         345-678-9012
c                              789-123-4567

So basically if we have a type 'Phone' then the report should display the number and if it does not have any number for type 'Phone' then it should look for type 'Office'. Please let me know if you need more information on this.

Thanks Khayman and Clamus17 for your responses

[Annon]

This is Annon Again. I actually need expression definition for [Phone Number] as I ma Cognos developer and no SQL is needed.

Thanks a lot for your time and I am eagerly waiting for your reply specially from khayman and clamus17.

Annon

[cognostechie]

The following expression will work but depending on the granularity shown in the output of the report. It may not work if the report is granular to the lowest level
of Phone Type and Phone Number record so without knowing more , I cannot be certain about the solution.

Case
     When Phone Type = 'Phone' and Phone Number is not null then Phone Number
     When Phone Type = 'Phone' and Phone Number is null then

          Case
                When Phone Type = 'Office' and Phone Number is not null then Phone Number
                Else NULL
          End
End 

[Annon]

Hi cognostechie,
Thanks for your time and solution.
I used the below ones
a-Case
     When [Phone Type] = 'Phone' and [Phone Number] is not null then [Phone Number]
     When [Phone Type] = 'Phone' and [Phone Number] is null then

          (Case
                When [Phone Type] = 'Office' and [Phone Number] is not null then [Phone Number]
                else null
          End)
End 
b-Case
     When [Phone Type Name] = 'Phone' and [Phone Number] is not null then [Phone Number]
     When [Phone Type Name] <> 'Phone' and [Phone Number] is null then

          (Case
                When [Phone Type Name] = 'Office' and [Phone Number] is not null then [Phone Number]
               
          End)
End 

But the above ones gave me solution as below

customer              Phone Type            Phone Number
AAA                      Phone                    123-45-7890
AAA                      Office                     null

My report actually should give only the first record and not the second record since for the first record, Phone Type is Phone and it has corresponding Phone number.

One more information I would like to give to you about my situation. When Phone number is null then its Phone Type is also null.

It would be a great help if a solution can be found.

Thanks,
Annon

[Francis aka khayman]

if you know how to manipulate queries in Report Studio, you can try the solution i gave.

[cognostechie]

The problem with your solution is that if a customer has phone number for both 'Phone' and 'Office' then both will show up but in that case the user wants to see the phone number for 'Phone', not 'Office'

[cognostechie]

Hi cognostechie,
Thanks for your time and solution.
I used the below ones
a-Case
     When [Phone Type] = 'Phone' and [Phone Number] is not null then [Phone Number]
     When [Phone Type] = 'Phone' and [Phone Number] is null then

          (Case
                When [Phone Type] = 'Office' and [Phone Number] is not null then [Phone Number]
                else null
          End)
End 
b-Case
     When [Phone Type Name] = 'Phone' and [Phone Number] is not null then [Phone Number]
     When [Phone Type Name] <> 'Phone' and [Phone Number] is null then

          (Case
                When [Phone Type Name] = 'Office' and [Phone Number] is not null then [Phone Number]
               
          End)
End 

But the above ones gave me solution as below

customer              Phone Type            Phone Number
AAA                      Phone                    123-45-7890
AAA                      Office                     null

My report actually should give only the first record and not the second record since for the first record, Phone Type is Phone and it has corresponding Phone number.

One more information I would like to give to you about my situation. When Phone number is null then its Phone Type is also null.

It would be a great help if a solution can be found.

Thanks,
Annon

Here you go.   Report XML and a screenshot of the results attached.

I got your e-mail also. Don't send e-mails to people from here and I have also changed my settings to hide my e-mail .

[cognostechie]

2nd attachment

[Francis aka khayman]

if the solution is not perfect it can always be refined. but i think you already got this.

The problem with your solution is that if a customer has phone number for both 'Phone' and 'Office' then both will show up but in that case the user wants to see the phone number for 'Phone', not 'Office'

[Annon]

Hi Cognostechie,
I took some time to figure your solution out. It is a very nice idea to join queries. Thanks a lot for your time and great solution.

Annon

[cognostechie]

I am glad it worked out for you ! Always happy to help  :)