Create filter on Database Object in Framework Manager

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,……

FROM [SCHEMA].TABLE

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’)#

Create filter on Database Object

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. Continue reading