Archiv für März 25, 2014

| März 25, 2014
3 Kommentare

Cognos trigger.bat

Sometimes you have the need to start a report execution in Cognos when the relevant ETL jobs are finished. In order to do this Cognos offers a tool called the trigger.bat. With this tool it is possible to start jobs or reports by external access. Normally it is installed on the Cognos Server in the following path:

<cognos install directory>\webapps\utilities\trigger\trigger.bat

If you want to run a report with the trigger.bat you first need to go to your report in Cognos and then configure a schedule. In the scheduling properties you have to set up a schedule by trigger. You simply need to type in a trigger name. This is needed in order to pass it to the trigger.bat which then runs the specific report.

The syntax for running a report with the trigger.bat utility is like this:

trigger.bat <cognos dispatcher> <User> <Password> <Namespace> <Trigger Name>

This method works fine as long as you want to use the trigger.bat on the Cognos server. If you have a separate server for the ETL processing it’s not as easy as described above.

In this case you should create a directory on the ETL server in which you copy several things:

  1. The complete trigger-directory
  2. Files out of the following directory: <cognos install dir>\webapps\p2pd\WEB-INF\lib
    • activation.jar
    • axis.jar
    • axisCrnpClient.jar
    • commons-discovery-0.2.jar
    • commons-logging-1.1.jar
    • commons-logging-adapters-1.1.jar
    • commons-logging-api-1.1.jar
    • jaxrpc.jar
    • mail.jar
    • saaj.jar
    • serializer.jar
    • wsdl4j-1.5.1.jar
  3. The JRE directory of Cognos installation: <cognos install dir>\bin\jre. If you have the same JRE installed on your ETL server as on the Cognos server it should work fine with it too.

You should copy the content of the trigger-directory directly to you target directory and the jar-files and the JRE should go to separate sub-directories. When you have that you have to edit the trigger.bat:

set JAVA_HOME=d:/trigger_test/jre/7.0/
set JAR_HOME=d:/trigger_test/lib
set CLASSPATH=d:/trigger_test/
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/activation.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/axis.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/axisCrnpClient.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/commons-discovery-0.2.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/commons-logging-1.1.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/commons-logging-adapters-1.1.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/commons-logging-api-1.1.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/jaxrpc.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/mail.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/saaj.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/serializer.jar
set CLASSPATH=%CLASSPATH%;%JAR_HOME%/wsdl4j-1.5.1.jar
„%JAVA_HOME%bin/java.exe“ -classpath %CLASSPATH%  d:/trigger_test/Trigger %1 %2 %3 %4 %5

You have to edit the bold marked lines. The JAVE_HOME hast to be set to the JRE dir. The JAR_HOME must point to the directory with the copied jar-files. The third bold marked line should be set to your created directory where the trigger.bat is located. The last bold line must be edited in the way that it points to the Trigger.class file (which is also located in the created directory).

The example above assumes that we have created the directory trigger_test on drive d: (windows machine). We copied the trigger directory of the Cognos server directly to this directory and created a sub-directory lib with the copied jar-files and a sub-directory JRE with the JRE of the Cognos Server.

Now we can run the trigger.bat also on the ETL server and it is possible to start Cognos reports as the last step in an ETL process for example.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Kategorie: Cognos Reporting | Tags:, ,
| März 4, 2014
2 Kommentare

Cognos Table of Contents Object

Sometimes when you create large (PDF-) documents with Cognos, it can be useful to add a table of contents at the beginning. For this Cognos supports a special toolbox object (since version 8.3). Unfortunately this object only works for PDF reports.

First of all we need a report with multiple pages. We create a report with a repeater table. Within this repeater table we create a simple list. This list contains all articles of a product group. We also put the product group label in the repeater table above the list. The result should look similar to this:

Fig1.: Repeater Table with inner list

Fig1.: Repeater Table with inner list

 

 

 

 

 

 

 

 

Now we create on a separate page (we put this page in front of the report main page) the table of content. For that we just put the table of content object on the report page. It should look something like that:

Fig2.: Table of contents

Fig2.: Table of contents

 

 

 

 

 

 

The second step is to put the “entry of table of content” object in front of the product group label on the report page. So go to the report page and add the entry as described in the screenshot:

Fig3.: Table of contents entry

Fig3.: Table of contents entry

 

 

 

 

 

 

 

 

Now go back to the TOC page. There you have to change the text source on the entry in the table of contents. Switch the source type property to „Date item value“ and then select the Productline. It should look like this:

Fig4.: Change Data Item Value for TOC entry

Fig4.: Change Data Item Value for TOC entry

 

 

 

 

 

 

Now your report is ready to be tested. Keep in mind that you run it as PDF output, otherwise you won’t see anything in the table of contents.

This was a very simple table of contents. If you want to create a table of contents based on your products instead of productlines you can unlock the report view and then move the “entry of table of content” element in the products column. Furthermore you have to change the data item in the table of contents entry to product now and you table of contents is created based on the products.

You can also create some kind of structured table of content where you have entries for product groups and products just by combining both methods and using the „Heading Level“ property on the entry in the TOC.

I hope this article gave you an overview how to easily build table of contents in Cognos.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Kategorie: Cognos Reporting | Tags:, ,
| März 4, 2014
Keine Kommentare

Oracle’s Virtual Columns

Since the version 11g Oracle supports so called virtual columns. Virtual columns consist of a calculation depending on other columns in a table. They are not stored persistently in the tablespace but calculated during run time. These virtual columns can be useful sometimes. For example if you want to implement interval partitioning on numbers in a varchar column (Oracle only supports interval partitioning on date or number columns). Or if you want to enhance an existing table easily or for testing reasons by new calculation without editing the complete table loading routine.

In order to build a virtual column in a table you easily add it to the CREATE TABLE statement as in the following example:

CREATE TABLE Test(
                ID               NUMBER,
                QTY              NUMBER(5,0),
                PRICE            NUMBER(10,2),
                TURNOVER        NUMBER GENERATED ALWAYS AS (QTY*PRICE) VIRTUAL
);

As you can easily see in this example the turnover column is a virtual column. During run time Oracle calculates the turnover by multiplying the quantity by the price.

If you want to insert new values you have to keep in mind that you cannot insert values in virtual columns. For that you have to explicitly define the relevant columns when inserting datasets:

 INSERT INTO Test (ID, QTY, PRICE) VALUES (1, 10, 5.75);

There are some other restrictions:

  • Virtual columns can’t be based on other virtual columns, i.e. only on “normal” columns
  • If the virtual column’s expression contains a user defined functions, it can’t be used as a partitioning key.

If you are interested in this or other advanced Cognos/SQL topics you can also attend training on my corporate homepage. At the moment my open courses are only available in German. Inhouse training and online courses are also available in English language. So check out my open trainings (German) or my online courses (English)!

Kategorie: Oracle SQL, SQL Tutorial | Tags:, , , ,