Replacing Query Studio with Cognos Workspace Advanced: Advanced Combined Filters

Even if IBM Cognos Workspace Advanced (WSA) has greater flexibility than the older IBM Cognos Query Studio tool, they work in different ways and the transition from one tool to the other represents somewhat of a learning curve.  Following are the steps to create an advanced combined filter in WSA.
Exercise Objective:
Using the package Go Sales (query), create a filter on the Sales that match the condition below:

(Year from 2005 to 2006 and Product brand code from 752 to 755) or
(Year equals 2007 and Product line code equals 718)

Step 1:  Create a simple report
Create a new report in WSA using the Go Sales (query) sample package, with the following items:
 Sales (query) > Time dimension > Current Year
 Sales (query) > Products > Product brand code
 Sales (query) > Sales > Revenue

Step 1 - Create a Simple Report

Step 2: Create filter and first condition
In the Filters dialog, click the + icon, and then select Combined and press OK.

Step 2 - Create 1st and 2nd filters

On the Filter Condition dialog, make the selections as per the red items encircled in the below figure:

Step 2a - Filter condition

Click OK to create the filter:

Step 2b - Combined filter

Step 3: Create a second condition
In a similar way done in Step 2, add a filter on Product brand code for the range 752 to 755:

Step 3 - Create 2nd filter

Step 4: Add the third and fourth conditions

We will keep adding the filters as before. First for year 2007, and then for Product brand code 718. The result should look like the following below:

Step 4 - Add 3rd and 4th filter

The filter is close to the desired one.  In the next step we will tweak it to get it right.

Step 5: Modify the operator between the two conditions
To change the AND for an OR, click on the AND operator and then click on the OR icon:

Step 5

Step 6: Combine the conditions
We need to force the order of the statements by inserting proper parentheses.
Control-click the first and second conditions to relate them, and then press the AND button:

Step 6a

At this point the first part of the filter will have the correct parentheses:

Step 6b

To correct the second statement, we apply the same technique. Control-click the conditions in blue and relate them by selecting the AND button:

Step 6c

For the last step, click on the 2nd AND to select it and then click on the OR icon operator to replace the AND:

Step 6d

Here is how the resultant filter should appear:

Step 6e

Step 7: Final Result and Conclusion

Step 7

IBM Cognos Workspace Advanced (built on top of the flexible Report Studio framework) is a significant improvement over Query Studio.  However, it does require some time to explore and master all of the new features and nuances that it provides.

Please contact Jennifer Vezina at jvezina@newintelligence.ca for any questions or inquiries.

Submit a Comment

Your email address will not be published.