how to split the dataitem in value prompt

[annareddy]

 I have a dataitem having a set of values i need to split them into individual value and populate the value prompt. could you let me know if there is any way out through cognos functions or using javascript

My data item is : Jan|~*~|Feb|~*~|Mar|~*~|Apr|~*~|MayFall|~*~|MaySpring|~*~|JunFall|~*~|Jun|~*~|Jul|~*~|Aug|~*~|Sep|~*~|Oct|~*~|NovFall|~*~|Nov|~*~|DecFall|~*~|Dec|~*~|
[/glow]
Thanks,
Annareddy

[MFGF]

Oh dear. It looks like you have data that isn't even at first normal form. What kind of a database are you using that delivers data items containing multiple values like this? I remember Pick/Prime Information/Reality/Universe/Unidata used to store data like this (in prehistoric times) but I think they provided functions to allow you to address the multi-values individually.

You're going to find this very difficult in any "normal" BI tool - tools like this are designed to operate on data in relational databases where the data has been normalized. One of the first steps in normalizing data is to remove "arrays" and pivot the data into list form.

If you have some sort of ETL tool, generally this process is made easy. Cognos Data Manager has inbuilt functionality to do this kind of thing, for example. If you're stuck with working with the data in its current form, you will have huge issues with these multiple values, I suspect.

If you can figure out any sort of pattern in the data presented within this item, you could perhaps build some complex calculations to strip out each value using combinations of the position() and substring() functions?

Good luck!!

MF.