Show fields in the List report based on lookup value

[Karanag]

Hi Everyone,

I want to show all the fields based on lookup table value which is comma seperated.

-  As by the query Select category from lookup where type='XYZ' here in category, I have comma seperated fields name which is in the database and I want to show those in list.

-Suppose category= id,name,address,phone number, and these are the fields name which I want to show in the list report. so basically whatever comma seperated values are here I am taking form the lookup table only those values should present in the list report.

Could anyone please help me to get solution for this.

Regards,

[hespora]

Hey there,


there are several ways of handling external data within Cognos. Arguably the best option is: Get it inside your data source. Talk to the system owner in your org, and convince them to enhance the source and model to accomodate for your data.

If that fails or is not a viable way, there are several ways *per report* to enrich them with external data:

- if you have a simple lookup (anything taking in and giving out less than ~50 values), I'd build that in a case when construct.

- if you have a larger lookup, you may want to look at manage external data function (which supposes your admin having a DB2 running alongside cognos). I cannot give you any first hand advice on that, as my org does not have that. But you can google "cognos manage external data" for plenty of tutorials.

- if all else fails, build a manual SQL item (SQL Syntax is IBM Cognos) in your report. In your example, that would be:

Code Select Expand


SELECT tbl.ID, tbl.name, tbl.address, tbl.phone
FROM
(VALUES
('row1ID','row1name','row1address','row1phone'),
('row2ID','row2name','row2address','row2phone'),
. . .
('row999ID','row999name','row999address','row999phone')
) tbl(ID,name,address,phone)


This would be fairly easy to prepare in Excel and then simply copy&paste to Cognos.
Now, you can point a query at this SQL item to grab the 4 fields you have, and join this query with your query at your data source.

[Karanag]

Hi,
Thanks for the reposne. But I think I hav'nt explained it correctly.
This query is coming form datasource having only one value which is nothing but coma seperated fields in my actual report query item. I want report like which is haivng this comma seperated values only suppose there are 10 values in this lookup table so all 10 are th same field name which I need in my list.
User is creating this lookup to manager form their end the list of fields in cognos. So as sson as he entered some other comma sperated value only those column hould display in cgons list. Her I need help How to map value with my list query item.

[hespora]

okay, so just to make sure I understand correctly...

this is what you have:
[Col 1]
'row1val1,row1val2,row1val3,...'
'row2val1,row2val2,row2val3,...'

and this is what you need to build from that:
[Col 01]      [Col 02]       [Col 03]             ...
'row1val1'   'row1val2'    'row1val3'          ...
'row2val1'   'row2val2'    'row2val3'          ...

did I understand that correct?

[AnalyticsWithJay]

Which database vendor are you using? If SQL Server, there's an easy function PARSENAME() that could handle up to four parses.

IMO, you should be using DB functions for this, and preferably placing that whole table in a VIEW which separates the values into columns. There are a lot of solutions for this online using SQL, if a view is an option for you.
http://stackoverflow.com/questions/10581772/how-to-split-a-comma-separated-value-to-columns

I would not recommend exposing a table like this for reporting since you are asking for all kinds of trouble. If you don't have any DB option, let me know your DB vendor so we could construct a Cognos expression for you.

Jay