All,
I have an attribute that can have multiple values per customer. How do I output this with Output Set Builder.
For example
“Country Served” could be:
USA,
Canada,
England,
Japan,
Australia,
…
The Total List of possible “Countries served” is growing all the time and will continue growing in T_Keyword_Values at random intervals.
A particular constituent might serve 6 to 8 countries, and other none, and yet other just one.
For Output Set Builder I want to get all of the values of “country served” consolidated into a single data element per customer.
Customer_No Country_Served Other_Elements…
1 USA
2 USA, England
3 England, Japan, Africa
4
5 England
I do not want to re-write my approach each time a country served is added to t_keyword_values. The maximum number of countries per constituent will stay reasonable less than 1000 char.
Has anyone solved this problem in the context of Output Set Builder. I think someone may have discussed this during the Nashville Conference Prelude.
--Tom Brown
IT Project Manager
Brooklyn Academy of Music
www.bam.org
tbrown@bam.org
I’ve found this query on the internet that does some interesting things for a single customer. However, this does not seem to get me quite where I want to be.
declare @ValueList varchar(1000)
select @ValueList = coalesce(@ValueList + ', ', '') + key_value
from vxs_cust_keyword
where keyword_no = 424 and customer_no = 1251370
select 'Results = ' + @ValueList
I suspect that if I could get this into a correlated sub query that would help. Or maybe a function.
Tom Brown
Hi Tom
To do this you will need to make a function along the lines of the Tessitura function FS_CONST_STRING, which is used to build the constituent string.
I have used this in Output Set Builder and this how I have it set up:
· TR_QUERY_ELEMENT (single row element)
· Data_select !.constit
· Data_from (select customer_no, dbo.fs_const_string(customer_no) as 'constit', list_no from t_list_contents)
· Data_where list_no = <<p26>> (change the parameter token to match the id you will use)
· TR_QUERY_ELEMENT_PARAMETER links to vs_list and uses list_no and list_desc. It is not a multi-select parameter. This means you can link it to the list that you are outputting so only the results for the people on the list are returned.
Hope that gives you a starting point.
Cheers
Sandra