Dynamic SQL for Report Studio

[pramsam1]

Hello,
I was wondering if this at all possible to do. I am creating a cognos report using native sql. I would like to be able to have the user select the column during the run time to build the sql. For example, the user will type in customer_name (a field in customer table) for the prompt below. Obvioulsy, the following does not work. Is there a way to achieve this in Cognos? What I'm asking is to build a dynamic sql with the user input...

select * from Customer
where date = '31-jul-2013'
and #prompt('customer name:')# like ('A%')

Thanks in advance...

 

[blom0344]

Cognos Report Studio is a BI tool , not a SQL generator in the sense you are after. However there are ways to circumvent this. You basically fetch all columns from the customertable , put them in a list and use conditional formatting to show/hide the list columns.

Another approach (if you can for instance cast all columns types to a string) is to build an SQL query subject within a model (or write the union in code) to put all data within one field, adding a second field to apply a filter on..

A bit like:

SELECT 'Customername',cust_name from customer
UNION ALL
SELECT 'Customeradress',cust_adres from customer
UNION ALL
SELECT 'Customercity',cust_city from customer
UNION ALL
..........
..........

Obviously, using native SQL is really NOT using the tool how it was meant to be used. A good model underneath will give tremendous advantages!

Ties Blom