In this example we have a set of data in a table witch has multiple of rows for each date.
In the Cognos report we want to filter the data for a specific date (or value).
We can solve this by adding a filter on the business view object in the Framework Manager (FM). This filter is a prompt witch pops up on opening the report.
In the FM under Model, create a Namespace called ‘Database View’. In here you import all the database tables/views you need for a report.
Right click on the Namespace ‘Database View’ and choose ‘Run Metadata Wizzard’ to import Database objects.
When you double-click the database object, you see:
SELECT * FROM [DB].TABLE
In the FM under Model, create a Namespace called ‘Business View’ (In here you create all the relational objects you need for a report).
Right click on the Namespace ‘Business View’ and choose Create à Query Subject
Give a name and select ‘Data Source (Tables and Columns)’ and click OK
Select the source Database and click Next
Choose the right Schema and click once on the object (table/view) you need data from.
Click on the green arrow to put the object in ‘Included Columns’ and click Next.
Click Next, Next and Finish
Double click on the before created object
The SQL shows SELECT column, column,…… FROM [SCHEMA].TABLE
With the prompt we add a line to filter for example on a date column:
SELECT column, column,……
WHERE DATE = #prompt(‘Date (Choose a date e.g.: 2010-12-31)’,’date’)#
For filtering on text:
WHERE CHAR_FIELD= #prompt(‘Char (Choose a value)’,’integer’)#