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
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'
Quote from: Annon on 28 Sep 2015 09:56:43 PM
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
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
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
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
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
if you know how to manipulate queries in Report Studio, you can try the solution i gave.
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'
Quote from: Annon on 29 Sep 2015 06:51:09 PM
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 .
2nd attachment
if the solution is not perfect it can always be refined. but i think you already got this.
Quote from: cognostechie on 30 Sep 2015 04:52:35 PM
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'
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
I am glad it worked out for you ! Always happy to help :)