12 min read

Cognos Report Studio is widely used for creating and managing business reports in medium to large companies. It is simple enough for any business analyst, power user, or developer to pick up and start developing basic reports. However, when it comes to developing more sophisticated, fully functional business reports for wider audiences, report authors will need guidance.

In this article, by Abhishek Sanghani, author of IBM Cognos 8 Report Studio Cookbook, we will show you  that even though macros are often considered a Framework Modeler’s tool, they can be used within Report Studio as well. These recipes will show you some very useful macros around security, string manipulation, and prompting.

(Read more interesting articles on Compiere here.)

Introduction

This article will introduce you to an interesting and useful tool of Cognos BI, called ‘macros’. They can be used in Framework Manager as well as Report Studio.

The Cognos engine understands the presence of a macro as it is written within a pair of hashes (#). It executes the macros first and puts the result back into report specification like a literal string replacement. We can use this to alter data items, filters, and slicers at run time.

You won’t find the macro functions and their details within Report Studio environment (which is strange, as it fully supports them). Anyways, you can always open Framework Manager and check different macro functions and their syntaxes from there. Also, there is documentation available in Cognos’ help and online materials.

Working with Dimensional Model (in the”Swapping dimension” recipe). In this article, I will show you more examples and introduce you to more functions which you can later build upon to achieve sophisticated functionalities.

We will be writing some SQL straight against the GO Data Warehouse data source. Also, we will use the “GO Data Warehouse (Query)” package for some recipes.

Add data level security using CSVIdentityMap macro

A report shows the employee names by region and country. We need to implement data security in this report such that a user can see the records only for the country he belongs to. There are already User Groups defined on the Cognos server (in the directory) and users are made members of appropriate groups.

For this sample, I have added my user account to a user group called ‘Spain’.

Getting ready

Open a new list report with GO Data Warehouse (Query) as the package.

How to do it…

  1. Drag the appropriate columns (Region, Country, and Employee name) on to the report from Employee by Region query subject.

    Use of macros in IBM Cognos 8 Report Studio

  2. Go to Query Explorer and drag a new detail filter.
  3. Define the filter as:

    [Country] in (#CSVIdentityNameList(‘,’)#)

    Use of macros in IBM Cognos 8 Report Studio

  4. Run the report to test it. You will notice that a user can see only the rows of the country/countries of which he is a member.

How it works…

Here we are using a macro function called CSVIdentityNameList. This function returns a list of groups and roles that the user belongs to, along with the user’s account name. Hence, when I run the report, one of the values returned will be ‘Spain’ and I will see data for Spain.

The function accepts a string parameter which is used as a separator in the result. Here we are passing a comma (,) as the separator.

If a user belongs to multiple country groups, he will see data for all the countries listed in the result of a macro.

There’s more…

This solution, conspicuously, has its limitations. None of the user accounts or roles should be same as a country name, because that will wrongly show data for a country the user doesnot belong to. For example, for a user called ‘Paris’, it will show data for the ‘Paris’ region. So, there need to be certain restrictions. However, you can build upon the knowledge of this macro function and use it in many practical business scenarios.

Using prompt macro in native SQL

In this recipe, we will write an SQL statement straight to be fired on the data source. We will use the Prompt macro to dynamically change the filter condition.

We will write a report that shows list of employee by Region and Country. We will use the Prompt macro to ask the users to enter a country name. Then the SQL statement will search for the employee belonging to that country.

Getting ready

Create a new blank list report against ‘GO Data Warehouse (Query)’ package.

How to do it…

  1. Go to the Query Explorer and drag an SQL object on the Query Subject that is linked to the list (Query1 in usual case).

    Use of macros in IBM Cognos 8 Report Studio

  2. Select the SQL object and ensure that great_outdoor_warehouse is selected as the data source.

    Use of macros in IBM Cognos 8 Report Studio

  3. Open the SQL property and add the following statement:

    select distinct “Branch_region_dimension”.”REGION_EN” “Region” ,
    “Branch_region_dimension”.”COUNTRY_EN” “Country” , “EMP_EMPLOYEE_
    DIM”.”EMPLOYEE_NAME” “Employee_name”
    from “GOSALESDW”.”GO_REGION_DIM” “Branch_region_dimension”,
    “GOSALESDW”.”EMP_EMPLOYEE_DIM” “EMP_EMPLOYEE_DIM”,
    “GOSALESDW”.”GO_BRANCH_DIM” “GO_BRANCH_DIM”
    where (“Branch_region_dimension”.”COUNTRY_EN” in
    (#prompt(‘Region’)#))
    and “Branch_region_dimension”.”COUNTRY_CODE” = “GO_BRANCH_
    DIM”.”COUNTRY_CODE” and “EMP_EMPLOYEE_DIM”.”BRANCH_CODE” = “GO_
    BRANCH_DIM”.”BRANCH_CODE”

  4. Hit the OK button. This will validate the query and will close the dialog box. You will see that three data items (Region, Country, and Employee_Name) are added to Query1.
  5. Now go to the report page. Drag these data items on the list and run the report to test it.

How it works…

Here we are using the macro in native SQL statement. Native SQL allows us to directly fire a query on the data source and use the result on the report. This is useful in certain scenarios where we don’t need to define any Framework Model. If you examine the SQL statement, you will notice that it is a very simple one that joins three tables and returns appropriate columns. We have added a filter condition on country name which is supposed to dynamically change depending on the value entered by user.

The macro function that we have used here is Prompt(). As the name suggests, it is used to generate a prompt and returns the parameter value back to be used in an SQL statement.

Prompt() function takes five arguments. The first argument is the parameter name and it is mandatory. It allows us to link a prompt page object (value prompt, date prompt, and so on) to the prompt function. The rest of the four arguments are optional and we are not using them here. You will read about them in the next recipe.

Please note that we also have an option of adding a detail filter in the query subject instead of using PROMPT() macro within query. However, sometimes you would want to filter a table before joining it with other tables. In that case, using PROMPT() macro within the query helps.

There’s more…

Similar to the Prompt() function, there is a i macro function. This works in exactly the same way and allows users to enter multiple values for the parameter. Those values are returned as a comma-separated list.

Making prompt optional

The previous recipe showed you how to generate a prompt through a macro. In this recipe, we will see how to make it optional using other arguments of the function.

We will generate two simple list reports, both based on a native SQL. These lists will show product details for selected product line. However, the product line prompt will be made optional using two different approaches.

Getting ready

Create a report with two simple list objects based on native SQL. For that, create the Query Subjects in the same way as we did in the previous recipe. Use the following query in the SQL objects:

select distinct “SLS_PRODUCT_LINE_LOOKUP”.”PRODUCT_LINE_EN” “Product_
line” , “SLS_PRODUCT_LOOKUP”.”PRODUCT_NAME” “Product_name” , “SLS_
PRODUCT_COLOR_LOOKUP”.”PRODUCT_COLOR_EN” “Product_color” , “SLS_
PRODUCT_SIZE_LOOKUP”.”PRODUCT_SIZE_EN” “Product_size”
from “GOSALESDW”.”SLS_PRODUCT_DIM” “SLS_PRODUCT_DIM”,
“GOSALESDW”.”SLS_PRODUCT_LINE_LOOKUP” “SLS_PRODUCT_LINE_LOOKUP”,
“GOSALESDW”.”SLS_PRODUCT_TYPE_LOOKUP” “SLS_PRODUCT_TYPE_
LOOKUP”, “GOSALESDW”.”SLS_PRODUCT_LOOKUP” “SLS_PRODUCT_LOOKUP”,
“GOSALESDW”.”SLS_PRODUCT_COLOR_LOOKUP” “SLS_PRODUCT_COLOR_LOOKUP”,
“GOSALESDW”.”SLS_PRODUCT_SIZE_LOOKUP” “SLS_PRODUCT_SIZE_LOOKUP”,
“GOSALESDW”.”SLS_PRODUCT_BRAND_LOOKUP” “SLS_PRODUCT_BRAND_LOOKUP”
where “SLS_PRODUCT_LOOKUP”.”PRODUCT_LANGUAGE” = N’EN’ and “SLS_
PRODUCT_DIM”.”PRODUCT_LINE_CODE” = “SLS_PRODUCT_LINE_LOOKUP”.”PRODUCT_
LINE_CODE” and “SLS_PRODUCT_DIM”.”PRODUCT_NUMBER” = “SLS_PRODUCT_
LOOKUP”.”PRODUCT_NUMBER” and “SLS_PRODUCT_DIM”.”PRODUCT_SIZE_CODE”
= “SLS_PRODUCT_SIZE_LOOKUP”.”PRODUCT_SIZE_CODE” and “SLS_PRODUCT_
DIM”.”PRODUCT_TYPE_CODE” = “SLS_PRODUCT_TYPE_LOOKUP”.”PRODUCT_TYPE_
CODE” and “SLS_PRODUCT_DIM”.”PRODUCT_COLOR_CODE” = “SLS_PRODUCT_COLOR_
LOOKUP”.”PRODUCT_COLOR_CODE” and “SLS_PRODUCT_BRAND_LOOKUP”.”PRODUCT_
BRAND_CODE” = “SLS_PRODUCT_DIM”.”PRODUCT_BRAND_CODE”

This is a simple query that joins product related tables and retrieves required columns.

How to do it…

  1. We have created two list reports based on two SQL query subjects. Both the SQL objects use the same query as mentioned above. Now, we will start with altering them. For that open Query Explorer. Rename first query subject as Optional_defaultValue and the second one as Pure_Optional.

    Use of macros in IBM Cognos 8 Report Studio

  2. In the Optional_defaultValue SQL object, amend the query with following lines: and

    “SLS_PRODUCT_LINE_LOOKUP”.”PRODUCT_LINE_EN” = #sq(prompt (‘Product
    Line’,’string’,’Golf Equipment’))#

  3. Similarly, amend the Pure_Optional SQL object query with the following line:

    #prompt (‘Product Line’,’string’,’and 1=1′, ‘ and “SLS_PRODUCT_
    LINE_LOOKUP”.”PRODUCT_LINE_EN” = ‘)#

  4. Now run the report. You will be prompted to enter a product line. Don’t enter any value and just hit OK button. Notice that the report runs (which means the prompt is optional). First, list object returns rows for ‘Golf Equipment’. The second list is populated by all the products.

How it works…

Fundamentally, this report works the same as the one in the previous report. We are firing the SQL statements straight on the data source. The filter condition in the WHERE clause are using the PROMPT macro.

Optional_defaultValue

In this query, we are using the second and third arguments of Prompt() function. Second argument defines the data type of value which is ‘String’ in our case. The third argument defines default value of the prompt. When the user doesn’t enter any value for the prompt, this default value is used. This is what makes the prompt optional. As we have defined ‘Golf Equipment’ as the default value, the first list object shows data for ‘Golf Equipment’ when prompt is left unfilled.

Pure_Optional

In this query, we are using fourth argument of Prompt() function. This argument is of string type. If the user provides any value for the prompt, the prompt value is concatenated to this string argument and the result is returned.

In our case, the fourth argument is the left part of filtering condition that is, ‘and

.

‘and “SLS_
PRODUCT_LINE_LOOKUP”.”PRODUCT_LINE_EN” =’.

So, if the user enters the value as ‘XYZ’, the macro is replaced by the following filter:

and

"SLS_PRODUCT_LINE_LOOKUP"."PRODUCT_LINE_EN" = 'XYZ'

Interestingly, if the user doesn’t provide any prompt value, then the fourth argument is simply ignored. The macro is then replaced by the third argument which is in our case is ‘and 1=1’.

Hence, the second list returns all the rows when user doesn’t provide any value for the prompt. This way it makes the PRODUCT_LINE_EN filter purely optional.

There’s more…

Prompt macro accepts two more arguments (fifth and sixth). Please check the help documents or internet sources to find information and examples about them.

Adding token using macro

In this recipe, we will see how to dynamically change the field on which filter is being applied using macro. We will use prompt macro to generate one of the possible tokens and then use it in the query.

Getting ready

Create a list report based on native SQL similar to the previous recipe. We will use the same query that works on the product tables but filtering will be different. For that, define the SQL as following:

select distinct “SLS_PRODUCT_LINE_LOOKUP”.”PRODUCT_LINE_EN” “Product_
line” , “SLS_PRODUCT_LOOKUP”.”PRODUCT_NAME” “Product_name” , “SLS_
PRODUCT_COLOR_LOOKUP”.”PRODUCT_COLOR_EN” “Product_color” , “SLS_
PRODUCT_SIZE_LOOKUP”.”PRODUCT_SIZE_EN” “Product_size”
from “GOSALESDW”.”SLS_PRODUCT_DIM” “SLS_PRODUCT_DIM”,
“GOSALESDW”.”SLS_PRODUCT_LINE_LOOKUP” “SLS_PRODUCT_LINE_LOOKUP”,
“GOSALESDW”.”SLS_PRODUCT_TYPE_LOOKUP” “SLS_PRODUCT_TYPE_
LOOKUP”, “GOSALESDW”.”SLS_PRODUCT_LOOKUP” “SLS_PRODUCT_LOOKUP”,
“GOSALESDW”.”SLS_PRODUCT_COLOR_LOOKUP” “SLS_PRODUCT_COLOR_LOOKUP”,
“GOSALESDW”.”SLS_PRODUCT_SIZE_LOOKUP” “SLS_PRODUCT_SIZE_LOOKUP”,
“GOSALESDW”.”SLS_PRODUCT_BRAND_LOOKUP” “SLS_PRODUCT_BRAND_LOOKUP”
where “SLS_PRODUCT_LOOKUP”.”PRODUCT_LANGUAGE” = N’EN’ and “SLS_
PRODUCT_DIM”.”PRODUCT_LINE_CODE” = “SLS_PRODUCT_LINE_LOOKUP”.”PRODUCT_
LINE_CODE” and “SLS_PRODUCT_DIM”.”PRODUCT_NUMBER” = “SLS_PRODUCT_
LOOKUP”.”PRODUCT_NUMBER” and “SLS_PRODUCT_DIM”.”PRODUCT_SIZE_CODE”
= “SLS_PRODUCT_SIZE_LOOKUP”.”PRODUCT_SIZE_CODE” and “SLS_PRODUCT_
DIM”.”PRODUCT_TYPE_CODE” = “SLS_PRODUCT_TYPE_LOOKUP”.”PRODUCT_TYPE_
CODE” and “SLS_PRODUCT_DIM”.”PRODUCT_COLOR_CODE” = “SLS_PRODUCT_COLOR_
LOOKUP”.”PRODUCT_COLOR_CODE” and “SLS_PRODUCT_BRAND_LOOKUP”.”PRODUCT_
BRAND_CODE” = “SLS_PRODUCT_DIM”.”PRODUCT_BRAND_CODE”
and
#prompt (‘Field’,’token’,'”SLS_PRODUCT_LINE_LOOKUP”.”PRODUCT_LINE_
EN”‘)# like #prompt (‘Value’,’string’)#

This is the same basic query that joins the product related tables and fetches required columns. The last statement in WHERE clause uses two prompt macros. We will talk about it in detail.

How to do it…

  1. We have already created a list report based on an SQL query subject as mentioned previously. Drag the columns from the query subject on the list over the report page.

    Use of macros in IBM Cognos 8 Report Studio

  2. Now create a new prompt page.
  3. Add a value prompt on the prompt page. Define two static choices for this.

    Display value Use value
    Filter on product line “SLS_PRODUCT_LINE_LOOKUP”.”PRODUCT_LINE_EN”
    Filter on product name “SLS_PRODUCT_LOOKUP”.”PRODUCT_NAME
  4. Set the parameter for this prompt to ‘Field’. This will come pre-populated as existing parameter, as it is defined in the query subject.
  5. Choose the UI as radio button group and Filter on Product Line as default selection.
  6. Now add a text box prompt on to the prompt page.
  7. Set its parameter to Value which comes as a choice in an existing parameter (as it is already defined in the query).
  8. Run the report to test it. You will see an option to filter on product line or product name. The value you provide in the text box prompt will be used to filter either of the fields depending on the choice selected in radio buttons.

    Use of macros in IBM Cognos 8 Report Studio

LEAVE A REPLY

Please enter your comment!
Please enter your name here