Hi,
In my Cognos 11 report, I have several part numbers. Most parts fit into a package. I would like the any Part Number that equals or starts with the Package Number to display the Package number.
Here is what I have:
Part Number Package
------------- --------------
100.A
100.A.002
100.A.002.AAB 100.A.002.AAB
100.A.002.AAB.0001
100.A.002.AAB.0002
100.A.002.AAB.0901
100.A.003
100.A.003.AAC 100.A.003.AAC
100.A.003.AAC.0001
100.A.003.AAC.0002
What I Would like:
Part Number Package
--------------- -------------
100.A
100.A.002
100.A.002.AAB 100.A.002.AAB
100.A.002.AAB.0001 100.A.002.AAB
100.A.002.AAB.0002 100.A.002.AAB
100.A.002.AAB.0901 100.A.002.AAB
100.A.003
100.A.003.AAC 100.A.003.AAC
100.A.003.AAC.0001 100.A.003.AAC
100.A.003.AAC.0002 100.A.003.AAC
For the null values in the package field on my "would like" report, it would be great if the field remained Null. But if I end up with a value there, it's OK. I'll never use those parts anyway
Thanks for your time!!
You've got what looks like a parent-child dimension in your source table. It would be nice to know how it is modelled in your source model/module for your report.
https://docs.microsoft.com/en-us/analysis-services/multidimensional-models/parent-child-dimension?view=asallproducts-allversions
https://docs.oracle.com/middleware/12211/biee/BIEMG/GUID-0424E7A7-C7DB-447A-B0C4-0BD6790888EA.htm#hpp_l_value_dimension
https://www.cognoise.com/index.php?topic=9011.0
Thanks for the info. I will read each link to get a better understanding of what I am up against.
One difference is that my Package number is equal to the main part number, and all of the children start with the parent. The strings are not different or random as shown in the employee/supervisor example.
Do you think a solution is possible?
I am assuming that character length should be used:
Try
case
when char_length([Part Number]) > 9
then [Part Number]
else null
end
Quote from: YazidKurdi on 28 Mar 2021 11:57:26 AM
I am assuming that character length should be used:
Try
case
when char_length([Part Number]) > 9
then [Part Number]
else null
end
case
when char_length([Part Number]) > 9
then (left([Part Number], 13))
else null
end
so you get the whole 13 characters of the package.
sdf