I have a List report with 6 columns . First column is the product name and the rest 5 columns is the territory names . Now the prompt page has a territory Check box prompt in which the user is shown around 100 Territories. The user can select 5 territories in this prompt. The 5 territories selected by user should come into 5 columns of the list.
For eg : User selects Australia , Belgium, France ,Germany & Italy . Then my list would contain 6 columns with Product name as first col , Aus as second col,Belgium as 3rd, France as 4th ,Germany as 5th & Italy as 6th.
Considering Checkbox prompt is multiselect and when we select 5 territories then it would create a comma seperated territory values . How do we split each terriotry passed from the check box prompt to achieve this requirement ?
Thanks
Does the columns of the territories display a measure?
If yes I would suggest to use a crosstab and not a list, to put the data item of the territories to the column and apply a filter to it.
It displays text items as well as numbers. I want this in a list instead of crosstab .
So I think you have to go with the hard way
You have to create a list with 101 columns and and a string variable with 100 values and conditionally show or hide the columns based on the selection.
Just for curiosity how do you populate the propmt?
In your data source do you have 100 columns one for each territory?
Its a territory Column with more than 100 territory values inside that column. I mean around 100 records. :)
The method you said is fine , but the problem is terriotry is added newly , then i will need to again add one more column to report . I dont wnt to change report design when a new terriotry is added.
Thanks
Another way is to use 5 prompt one for each column.
How do you transform row into columns?
The territory names should only come in the list title of the column eg , Aus , Brazil , belgium, US, China ( These are header Values in the list . The values inside the column is pulled by a case statemnt.
I hope you got what i meant to say. The Territories selected in the prompt are the ones which will be displayed in the individual header columns of the list
I think that you have use javascript and feed a prompt for each selection.
In your case you have to have 5 prompts and the feed them with the selections of the main prompt
I have come with a more convinient solution
Depending your datasource use the appropriate function to find the comma location and then substring
Which database do you use?
I am using Oracle . But the Substring would be required at Cognos Report level . Also the problem here is Using this Parameter values from check box & then splitting the 10 territories passed from the Prompt to 10 Individual Territories . All of them are passed as Comma seperated values by the check box prompt.
In order to find the positions of the commas you could use
substr and instr functions.
For the headers of the list you could select in the text source "Report expression"
and use
substring and position functions.
For example for the first column the expression could be as follow
substring
(
ParamDisplayValue('Country'),
1,
position (',',ParamDisplayValue('Country'))-1
)
Thanks Pricter :)
Your logic works