Tag Archiv für Cognos Reporting

| Februar 1, 2017
Keine Kommentare

Macro functions in Cognos Report Studio

Hi,

today I will deal with macro functions. I had a customer who wanted to switch between two dimensions to drill up & down in a dimensional report. That means you have to dynamically change the MUN(=member unique name) for this element to decide whether to use dimension A or dimension B.

The two MUNs for the dimensions looked like this on the highest level:

  1. [Dimensional].[PG].[PG].[Product Group]
  2. [Dimensional].[AX].[AX].[Target Group]

To solve this task I used the following macro functions:

  • Operator +
  • Function substitute (<search pattern>, <replace text>, <orig text>)
  • Function prompt (<p_name>, <data type>, <default>)

To use macro functions you have to define a  macro block like this:  # ….… #. Between the # symbols you will place the macro functions. The macro then defines the expression throughout the macro functions. In our case it will result in one of the two MUNs described above.

Example: You could create a query with a query item that contains the following macro function: # ‘Test1‘+‘Hello‘ #. This will result in the string Test1Hello at runtime in this query item. It’s like you would place this string in the query item by yourself but with macro functions you have the opportunity to dynamically define such expressions.

If we take a look at our example. We’ve create a text: Test1Hello. If we placed this into the query item we would receive an error because of the syntax. We need to put ‘  ‘ around it to  make it a string. For this we can use the macro function sq( … ). Sq stands for single quotes. We will enhance our little example to this: # sq(‘Test1‘ + ‘Hello‘) #. This will result in the string ‘Test1Hello‘ and no error will occur. By the way the operator + concats two strings.

To get back at my customer’s problem. The function substitute(…) will replace the string <search pattern> within the string <orig text> by the string <replace text>. Prompt(…) will ask for parameters and returns the value of a parameter. First argument of this function is the name oft he parameter, second the datatype and third a default value. The third argument is optional. Regarding the datatype. There is the string which is text with single quotes around it and there exists token which is a text too but without single quotes. If you have a prompt page with this parameter it won’t be asked for it again but the value is returned here as well.

With the knowledge of these functions we can now come to the solution for my problem:

# ‘[Dimensional].[‚ +  prompt(‚p_proddim‘, ‚token‘ ,’PG‘)  +  ‚].[‚  +  prompt(‚p_proddim‘, ‚token‘ ,’PG‘)  +  ‚].[‚  +  substitute(‚AX‘, ‚Target Group‘,  substitute(‚PG‘, ‚Product Group‘, prompt(‚p_proddim‘, ‚token‘ ,’PG‘)))  +  ‚]‘   #

The parameter p_proddim contains the selected dimensions (‘PG’ or ‘AX’). First we take the constant text at the beginning (‘[Dimensional.[‘) then we concat the content of p_proddim then the string ‘].[‘ and then again the content of p_proddim. The result is: [Dimensional].[PG].[PG].[ Instead of PG could also be AX depending on the parameter’s selection. The next part is something like an if-then-else. But it’s realized via a nested substitute call. The inner substitute takes the parameter value of p_proddim and checks the value for ‘PG’. If it is found it’s replaced by ‘Product Group’. If not nothing happens. The result of this substitute is the text to be checked in the outer substitute. This one is checked for ‘AX’ and is then replaced by ‘Target Group’.

This is one example how to use macro functions. There are a lot others. In future articles I will deal with macro functions again.

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:, , , ,
| Januar 31, 2017
Ein Kommentar

How to filter a time interval in a dimensional report

Hi,

today we will filter on a time intervall in a dimensional report with the help of two parameters. When you are developing a dimensional report (DMR or OLAP) then you have to create so called member sets, which you then can query. Example: You are creating a meber set fo rthe current year and then you can calculate the turnover for this member set. The code looks like this: total([Umsatz] within set [<MemberSet>]

With this style of developing reports you are facing a problem right now, i.e. that you have to define explicitly the items you need for the calculations. For example if you want to query februrary to november of a certain year then you have to explicitly define all these months. In case of a time dimension this is not common and also not very practical. What you want is to just select the start and end month and months in between should be selected automatically.

If you were creating a relational report then you would have used the BETWEEN operator. This is not possible in dimensional report development, but what you can do is to create the meber set with the help of some functions. We will use the following functions:

  • Lastperiods(<no. of periods>, <last item>)
  • Operator  ->
  • Intersect (<Set1>, <Set2>)

The function lastperios (…) lets you define a set, which ends with <last item> and contains the <no. of periods> previous members. Lastperiods(12, [Dec. 2013]) would contain all months in 2013. If you chose a negative value for <no. of periods> then you receive the following months.

The operator -> picks a specific meber in a hierarchy. You can also use parameters with it, e.g. [Dimensional layer].[Date (Month)].[Date (Month)].[Month]->?pMonth? It possible to combine the function and the operator in order to receive for example 3 months before the selected month.

To eventually define our interval we a third function: intersect(…). This functions returns the intersection set of two given sets., i.e. the mebers that are existing a both sets are returned. If we have two sets: Set1=(1,2,3) & Set2=(3,4,5) then the intersection is (3) because this ist he only item which is contained by both sets.

To filter on a time inteval we can now build two sets:

  • Set1 = lastperiods (-120; [Dimensional].[…..].[Month]->?pMonthStart?)
  • Set2 = lastperiods (120; [Dimensional].[…..].[Month]->?pMonthEnd?)

Presented in a picture it looks like this:

BLOG_0026_PIC01_Time_Interval_Set

To create the result set we use the intersect function on the two sets. And then we can do the calculation on the result set.

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:, , , , ,
| Januar 25, 2017
Ein Kommentar

How to show/hide a parameter section in a Report

Hi,

For a customer I was developing a new report this week. He asked me to implement a section in the report header where you can see what parameters are selected in the report. Because there  are a lot of parameters in my customer’s report he wanted some fancy function so you can easily show or hide this parameter section.

The solution works with two html items, which just contain a <div …> and a </div> tag and a Java Script function which shows or hides the div block. The example report doesn’t contain a list or something. It’s just having the parameter section on the report page and a link to show/hide it. But you can easily implement it in your reports as well. It’s also not very beautiful. For example you could use a nice picture instead of a link.

As I’ve written before you need two html blocks.  These blocks contain the following code:

HTML Block 0(JavaScript)
<script type=“text/javascript“ >
 
function ShowHideDIV(){
   if (document.getElementById(‚testdiv‘).style.display != ’none‘) {
        document.getElementById(‚testdiv‘).style.display = ’none‘;
   }
   else {
       document.getElementById(‚testdiv‘).style.display = ‚block‘;
   };
};
</script>
HTML Block 1
<a href=“javascript: ShowHideDIV();“> Show/Hide parameters </a>
 
<div id=’testdiv‘>
 
HTML Block 2 </div>

This solution uses the CSS (Cascading Style Sheets) which describe formatting properties of an HTML document.  As the Cognos report results are normally HTML documents as well you can use this knowledge to dynamically set or unset the property for hiding a div block. If you want to enhance this solution you can pass the name of the block via a variable to the function.

Anyway these HTML blocks must then be inserted to the report like this:

BLOG_0024_PIC01_Overview_Report_Page

 

The very first block in the page content area must be the HTML item with the Javascript. Then around the table or text-items with the parameter description you have to place HTML Block 1 and HTML Block 2. I’ve chose a table to represent the prompt parametes. After the HTML Block 2 you can insert the report data itself.

The examples used in this article can be found at the very end of this article as zip-file. I will continue this series soon and we will then deal with some useful functions you can use if you are working with the rollup or cube statement.

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)!

Download

BLOG_0025_Example_Report.zip

Kategorie: Cognos Reporting | Tags:, , ,
| Januar 3, 2017
2 Kommentare

How to write back from Cognos RS to database

In this issue of my database and business intelligence blog I want to explain how it is possible to write  data from Cognos Report Studio back to the database. I will work on an Oracle database but the techniques are quite similar on other DBs.

First I need a table. I’ll create a new one, that looks like this:

TBL_RS_TEST
Column Name Data Type Description
TEXT_ID NUMBER Just an id.
TEXT VARCHAR2(2000) The column to store the Comment.

If we have a table we then need a function which will write a value to that table. This function looks like this in Oracle:

CREATE OR REPLACE FUNCTION F_RS_TEST(i IN NUMBER, t IN VARCHAR) RETURN NUMBER AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
 INSERT INTO TBL_RS_TEST VALUES(i,t);
 COMMIT;
 RETURN 1;
END;

This is just a very simple function. It just takes two parameters (id, text) and stores them to the corresponding columns in TBL_RS_TEST. Important is the second line. This is needed in order to do a commit within the function. And as we are going to use this function in a SELECT statement the commit is needed within the function. Then we are just inserting a new row to the table, commiting it and returning 1. It’s just a very simple example. Of course you could do an UPDATE or a DELETE as well and of course you can also have some more complex logic here. Whatever is needed in your case.

Then you have to run the complete script in script running mode on the Oracle DB. If the function was successfully created you can just check it with a statement like this:

SELECT F_RS_TEST(1, ‚Hallo‘)
FROM dual;

It should just return 1 as a result. And if you check the table TBL_RS_TEST afterwards there should appear a new line with 1 – Hallo. That’s all we had to do on database level.

Now we can step over to the Cognos part. We open Report Studio and then we create a pretty simple report. I am using a SQL item in the query with the following properties:

  • SQL-Syntax: Pass Through
  • Datasource: Just take a datasource that has a connection to the database where you have created the table

The SQL itself looks like this:

SELECT F_RS_TEST(2, ‘Test 2’) AS TEST
FROM dual

When you have created the query with the SQL item then you just have to drag the column TEST to the report page in the list or singleton or whatever container you prefer. This is important otherwise the query wouldn’t be executed. Finally just run the report and check on database level if you find the new value in the table.

This was just a very simple example. In the example of this blog article I’ve added a report which has a prompt page where you can type in an id and text. These parameters are then passed to the SQL via the macro function prompt and are then written to the database table via the function F_RS_TEST. You find the example in a zip-file at the end of this article. You need to change the package and datasource so that it can run in your environment. In a soon following part I will extend this example to a solution to store comments on values on an existing planned turnover table.

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)!

Downloads

BLOG_0021_RS_Stored_Procedure_Comments.zip

Kategorie: Cognos Reporting | Tags:, , , ,
| 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:, ,
« Ältere Beiträge